Google Sheets cannot read a dropdown source from a formula directly, so a dependent dropdown needs a helper range. The first cell offers categories from a list, a helper formula using FILTER or QUERY writes the matching subcategories into spare cells, and the second dropdown reads those cells. Test the first two rows before you copy anything down.
How should the Lists tab be laid out?
Use a long table with one subcategory per row.
| Category (A) | Subcategory (B) |
|---|---|
| Food | Groceries |
| Food | Dining |
| Food | Delivery |
| Transport | Fuel |
| Transport | Cab |
| Bills | Electricity |
Keep headings in row 1 and data from row 2 to row 40, which leaves space to add more. Order does not matter here, unlike some Excel methods.
How do you set up the first dropdown?
On the expense tab, with Category in B and Subcategory in C, select B2:B500, choose Data, Data validation, Add rule, Dropdown (from a range) and pick a range holding each category once. You can keep a short column of the distinct categories on the Lists tab, in D2:D10. Under Advanced options, choose Reject the input.
Where does the helper formula go?
Pick a block of empty columns off to the right, for example K to T, for each row. In K2 enter:
=IFERROR(TRANSPOSE(FILTER(Lists!$B$2:$B$40,Lists!$A$2:$A$40=$B2)),"")
FILTER returns the subcategories whose category equals B2, and TRANSPOSE turns that vertical answer into a row, spilling across K2:T2 as far as it needs. Fill K2 down the column. A QUERY version does the same job: =IFERROR(TRANSPOSE(QUERY(Lists!$A$2:$B$40,"select B where A = '"&$B2&"'",0)),""). Note the single quotes around the category inside the query text.
How does the second dropdown find its row?
Select C2:C500, add a Dropdown (from a range) rule and type the range K2:T2 with no dollar signs. Written relative to the first cell, the rule should shift to K3:T3 for row 3, and so on. Do not trust that: pick Food in B2 and B3 and Transport in B4, then open C2, C3 and C4 to confirm each shows its own options. If every row points at row 2, the reference was locked and you will need to fix the range in the rule.
What limits should you know about?
- Empty cells must exist to the right of each helper formula. If something sits in the way, FILTER shows a #REF! error instead of spilling.
- The second dropdown can show only as many items as the helper spans. A category with twelve subcategories needs at least twelve helper columns, or the extras silently go missing.
- The dropdown reads the helper cells when you open it, so a new subcategory on the Lists tab appears at once, but a value already chosen in column C does not change by itself.
- Changing the category in B does not clear C. Check for mismatches with a column such as =IF(C2="","",IF(COUNTIFS(Lists!$A$2:$A$40,$B2,Lists!$B$2:$B$40,$C2)=0,"Check","OK")).
Quick test with a few rows
Say Food has three subcategories, as in the table. In B2 pick Food. K2:M2 should show Groceries, Dining, Delivery, and N2 to T2 stay empty. Pick Bills in B3 and K3 should show only Electricity. If a row of the helper range shows a stray comma or a blank, there is probably a space after a category name on the Lists tab, and TRIM on the Lists tab will clear it.
Before you copy it down
- Test rows 2 to 4 with different categories.
- Fill the helper formula down the same number of rows as the expense table.
- Protect the Lists tab or the helper columns from accidental edits.
If a hand-built pair of dropdowns feels heavy, SpendixAI keeps custom categories and sub-categories in the app and lets you review each imported row before saving. A sheet like this remains good for the views you control.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


