Spreadsheets

Dropdown from a range in Google Sheets for expense categories

Make a category dropdown in Google Sheets from a range: keep the list on its own tab, choose whether other values are rejected, and check pasted data.

Editorial illustration for Dropdown from a range in Google Sheets for expense categories

In Google Sheets, select the category column, open Data, then Data validation, then Add rule, and choose Dropdown (from a range) as the criteria. Pick the range on your Lists tab, and under Advanced options decide whether other values show a warning or are rejected. The list stays on its own tab, so changing it changes every dropdown.

How do you create the dropdown?

  1. Add a tab called Lists. Type Category in A1 and your categories in A2 downwards: Groceries, Rent, Transport, Fuel, Dining out.
  2. On the expenses tab select the category cells, for instance C2:C500.
  3. Choose Data, Data validation, then Add rule.
  4. Set Criteria to Dropdown (from a range). Click the grid icon, go to the Lists tab, select A2:A30 and press OK.
  5. Press Done.

Choosing A2:A30 leaves room for new categories while empty cells are ignored, so no blank option appears. You can also use an open-ended range such as Lists!A2:A, which grows as you type, and Sheets treats blanks sensibly.

Should other values be rejected or only warned about?

Open the rule and expand Advanced options. Under If the data is invalid you can pick Show a warning, which paints a red corner on the cell, or Reject the input, which blocks the entry. For categories that feed SUMIFS formulas, rejecting is cleaner. Warnings are better where a person may genuinely need a one-off label and you would rather see it flagged than blocked.

SettingEffect
Show a warningEntry is accepted; the cell gets a warning mark
Reject the inputEntry is refused until it matches the list
Display style: ChipItems look like coloured tags
Display style: ArrowPlain cell with a small arrow
Display style: Plain textNo visual cue until you click

How do you make each category a colour?

With the Chip style on, the rule can let you choose a colour for each item. Rent can be blue and Groceries green. Colours on the chips are separate from conditional formatting, and they are saved with the rule. If you have a dozen categories, use a few colours with meaning rather than twelve.

If you use the same list on several tabs, give it a name first: select Lists!A2:A30, choose Data, then Named ranges, and call it Categories. Then in the rule you can type Categories as the range. Changing the named range later, for example extending it to A2:A40, updates every dropdown that uses it in one go, and a formula such as =COUNTIF(Categories,C2) becomes easier to read than a sheet reference.

What happens when you rename a category?

Changing a name on the Lists tab updates the dropdown, but cells already filled in keep the old text. Rename Fuel to Petrol and old rows still say Fuel, so a SUMIFS on Petrol misses them. Use Edit, then Find and replace to update the old rows, in the category column only.

Quick tips for a clean setup

  • Keep one list per tab and never type categories into the expense rows directly.
  • Sort the Lists tab alphabetically so that the dropdown is easy to scan.
  • If your list has sub-categories, a second dropdown can depend on the first. That needs a helper formula, which is a separate exercise.
  • Freeze the heading row of the expense tab so that you can see which column is which while scrolling.

Because the list lives on its own tab, you can share the expense tab with a family member and keep the Lists tab protected. Use Data, then Protect sheets and ranges, so that only you can change categories while others pick from the dropdown.

If a sheet still feels like too much typing, SpendixAI imports statements, applies your categories to the rows and lets you correct them, so the dropdown becomes the exception and not the routine.

SPENDIXAI JOURNAL

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

Explore your reports