Spreadsheets

Create a category dropdown list in Excel with Data Validation

Create a category dropdown in Excel with Data Validation: a Lists sheet, a named range, the list rule, and a Stop alert that blocks typos in your expenses.

Editorial illustration for Create a category dropdown list in Excel with Data Validation

To create a category dropdown in Excel, type your categories on a separate sheet, name that range, then select the category column on your expense sheet and choose Data, Data Validation, Allow List with the source set to your named range. Add a Stop style error alert and nobody, including you on a tired Sunday, can type Grocries and split a category in two.

How do you set up the Lists sheet and the name?

  1. Add a sheet called Lists. In A1 type Category as a heading, and in A2:A15 type one category each: Groceries, Rent, Transport, Fuel, Dining out, and so on.
  2. Select A2:A15. Click the Name Box (left of the formula bar), type Categories and press Enter.
  3. Check with Formulas, Name Manager that the name refers to Lists!$A$2:$A$15.

Leave a few empty cells at the bottom if you expect to add categories. Blank cells in the list show up as a blank option in the dropdown, which is a small price for room to grow.

How do you add the dropdown itself?

  1. On your expense sheet, select the whole category column range, for example C2:C500.
  2. Choose Data, Data Validation. On the Settings tab, set Allow to List.
  3. In Source type =Categories. Keep In-cell dropdown ticked.
  4. Open the Error Alert tab and tick Show error alert after invalid data is entered. Set Style to Stop, give it a title such as Pick from the list and a message such as This category is not on the Lists sheet.
  5. Press OK, then click a cell in column C to see the arrow.

What are the three error alert styles?

StyleWhat it doesUse it when
StopRefuses the entry and makes the person retryCategories must match exactly
WarningAsks Yes, No or Cancel and allows the entryA new category might be legitimate
InformationShows a message but accepts the entryYou only want a nudge

For a report that groups by category, Stop is the safest. If you choose Warning, remember that every accepted exception is a row that your SUMIFS formulas will miss.

Does capitalisation matter?

When the source is a range or a named range, as here, validation treats Dining out and dining out as the same entry. If you instead type the items straight into the Source box, such as Food,Rent,Travel, the check becomes case sensitive. A named range is also easier to maintain than a typed list.

What if wrong entries already exist?

Validation only checks what is typed after you set it up, and it does not stop pasted values from replacing cells. To find existing problems, open Data, Data Validation, then Circle Invalid Data. Excel draws red circles around every cell that does not match the list, and you can fix them one by one. Choose Clear Validation Circles when done.

A checklist for the finished sheet

  • Dropdown arrow appears on the first and last data row.
  • Typing a made-up word is blocked with your own message.
  • The Lists sheet is tidy, with no duplicate or trailing-space entries.
  • Reports use SUMIFS on the category column, so a stray spelling cannot hide spending.

Keep the category names short and distinct. A list with both Food and Groceries, or Travel and Transport, invites inconsistent choices, and the dropdown cannot decide for you. Agree the meaning of each category once, and write a one-line description beside it on the Lists sheet.

If you would like categories assigned for you, SpendixAI's own rules, your history and similar merchants do the first pass on imported rows, and you can add custom categories and sub-categories yourself.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports