How can I make a dropdown list that updates when I add new inventory categories?

Use a dedicated category list formatted as an Excel table, and set the dropdown’s source to the table’s category column. Excel supports table-column sources, and a dropdown associated with that table updates when you add or remove items in it. [S1]

For example, put one category per row in a separate list: “Office supplies,” “Packaging,” and “Tools.” Give the column a clear heading such as “Category.” Keep the inventory rows elsewhere, so the source list is easy to maintain.

After setting up the source, open an inventory cell’s dropdown and check the choices. The important distinction is that the source should be the table column, rather than a fixed set of cells that stops at the categories you had on setup day.

A simple layout could be:

Category table:

• Category
• Office supplies
• Packaging
• Tools

Inventory table:

• Item
• Category
• [example item]
• [choose from dropdown]

Treat “Category” as the table heading, not as a choice. Keep only intended choices in the category rows. Then open a dropdown in an inventory row and confirm that it shows the categories you expect, rather than a heading or an unintended blank.

To test expansion, add a temporary row such as “Test category” to the category table and open the dropdown again. If it appears, remove the temporary row and check that it is no longer offered. This practical check helps confirm that your workbook is using the source you intended.

Check the dropdown list and existing inventory values separately. The table-backed dropdown updates when table items are added or removed, but that does not establish that existing inventory entries are automatically changed or flagged. [S1]

Before renaming or removing a category, find rows that use it and decide what should replace it. For a rename, update the affected entries to the new label and check a few rows. For a removal, assign affected items to an appropriate remaining category, or keep the category if it is still needed for your records. Don’t assume that changing the source list has also cleaned up the inventory data.

Use this checklist:

1. Create a separate category list with one intended choice per row and a clear heading.
2. Format the list as an Excel table and use its category column as the dropdown source. [S1]
3. Add a temporary category and check that it appears; remove it and check again.
4. Review existing inventory entries before renaming or removing a category.
5. Check the dropdown in the Excel version or mode your team will use, and make sure the cell is wide enough to show entries. Excel for the web has editing limitations. [S1]

Excel also supports cell ranges and named ranges as dropdown sources, but a named range may need its definition updated as the list changes. [S1] If you choose one of those alternatives, include its range definition in your maintenance checks.