Creating Dynamic Dropdowns from Tables

You already learned how to point a dropdown at a fixed range such as the Status column. That works, but it breaks the moment your list grows — the new entry sits outside the reference and never shows up in the dropdown.

Excel Tables solve this cleanly. When a dropdown's source is an Excel Table column, the reference expands automatically as you add rows.

Setting Up a Reference Sheet

Before creating any table, it's good practice to keep your list data on a dedicated sheet — separate from the main data entry area. This keeps the workbook clean and makes lists easy to manage.

Step 1 — Creating a new sheet:

1. Simply click the + icon next to the last tab;
2. Double-click the new tab and rename it to Lists.

Step 2 — Adding your first list:

1. Click cell A1 and type a header — e.g., Status;
2. Enter each value in the rows below, one per cell:
   • A2: Open
   • A3: Closed
   • A4: Pending
3. Keep the column clean — no blank rows, no extra spaces, no merged cells.

Step 3 — Converting a Range to an Excel Table:

Before linking a dropdown to a table, your list data needs to be formatted as one:

1. Click anywhere inside your list range;
2. Press Ctrl + T (Win) or cmd + T (Mac);
3. Confirm the range and check My table has headers;
4. Click OK.

Excel assigns the table a default name like Table1. Rename it to something meaningful — e.g., Statuses — via the Table Design tab.

Referencing a Table Column in Validation

You can't type a structured reference like =Statuses[Status] directly into the Data Validation source field — Excel doesn't accept it there. One clean workaround is using the INDIRECT function, which converts a text string into a valid range reference.

Step by step:

1. Select the Status column cells in your main table;
2. Open Data Validation → Settings → List;
3. In Source, type: =INDIRECT("Statuses");
4. Click OK.

Now when a new value is added to the Statuses, the dropdown reflects it immediately — no Name Manager required.

Task

File: continue to work with the same file from the previous section.

1. Go to the Lists sheet and add a new value below Pending in the Statuses table:
   • A5: Cancelled
2. Return to the main sheet and click the dropdown in any Status cell.
3. Confirm that Cancelled now appears in the list alongside Open, Closed, and Pending.

This confirms that the INDIRECT reference is live — the dropdown updated without any changes to the validation rule itself.