A dependent dropdown means the second list changes with the first: pick Food in the category cell and the next cell offers only Groceries, Dining and Delivery. The classic build uses one named range per category and a validation source of =INDIRECT($B2). A second method with OFFSET suits a single long list, and Excel 365 can do it with FILTER for a one-off entry cell.
How should the Lists sheet look?
Put each category as a heading in row 1 of a Lists sheet, with its subcategories listed underneath. The headings must be spelled exactly as you will pick them.
| A (Food) | B (Transport) | C (Bills) |
|---|---|---|
| Groceries | Fuel | Electricity |
| Dining | Cab | Internet |
| Delivery | Metro | Society maintenance |
Select A1:C4 and choose Formulas, Create from Selection, then tick Top row only. Excel makes three names: Food, Transport and Bills, each pointing at the cells under its heading. If your lists are different lengths, make the names one by one in the Name Box so that none includes blank cells. Then name the header row itself: select A1:C1 and call it Categories.
How do you attach the two dropdowns?
- Select B2:B500, open Data, Data Validation, Allow List, Source =Categories. This is the first dropdown.
- Drag from C2 down to C500 so that C2 is the active cell. Open Data Validation, Allow List, Source =INDIRECT($B2).
- If Excel warns that the source currently evaluates to an error, that is only because B2 is empty. Click Yes.
- Pick Transport in B2 and open C2. You should see Fuel, Cab, Metro.
INDIRECT turns the text in B2 into a reference to the named range of the same name. That is why the spelling must match, and why a blank B2 leaves C2 with nothing to offer.
What if a category has a space in its name?
Range names cannot contain spaces. Name the range Eating_Out and change the second source to =INDIRECT(SUBSTITUTE($B2," ","_")). SUBSTITUTE swaps each space in the chosen category for an underscore before INDIRECT looks for the name. Avoid ampersands and hyphens in category headings, as names do not allow those either.
Is there a way without a name for each category?
Yes, with one long, sorted list. Put Category in column A and Subcategory in column B, with all rows of one category together, in A2:B9. Say Food takes rows 2 to 4, Transport rows 5 to 7 and Bills rows 8 and 9. Then the second source is:
=OFFSET(Lists!$B$1,MATCH($B2,Lists!$A$2:$A$9,0),0,COUNTIF(Lists!$A$2:$A$9,$B2),1)
Check it for Transport: MATCH finds Transport at position 4 in A2:A9, so OFFSET moves 4 rows below B1, landing on B5. COUNTIF counts 3 Transport rows, so the list is B5:B7. A new subcategory means inserting a row inside its block, and you must keep the categories grouped together or the count will be wrong.
And in Excel 365?
For one entry cell, such as a form at the top of a sheet, you can put =FILTER(Lists!$B$2:$B$9,Lists!$A$2:$A$9=$B$1) in a helper cell, say E2, and set the dropdown source to =$E$2#. That one cell cannot serve a whole column of different rows, so use the INDIRECT or OFFSET method for a long expense log.
Quick checklist
- Names and headings match letter for letter.
- Second dropdown applied with C2 as the active cell.
- Test the first and last row of the table.
- Keep a Lists backup copy before you restructure categories.
Reports work best when categories are consistent. SpendixAI lets you set up categories and sub-categories with colours, and its categorisation starts from your own rules and history, which is useful once a sheet becomes a daily habit.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


