To add a category dropdown in Google Sheets, keep your list of categories on a separate Settings tab, select the Category column on your expense tab, open Data, then Data validation, and choose Dropdown (from a range) pointing at that list. Set invalid entries to be rejected. From then on nobody can type Grocery, groceries or Groceries with a stray space, which is exactly what makes SUMIFS totals quietly wrong.
Why free-typed categories break your totals
SUMIFS matches text exactly, apart from capital letters. A trailing space or a different spelling counts as a different category. Say a month has three entries: ₹4,200 typed as Groceries, ₹1,850 typed as Grocery and ₹960 typed as Groceries followed by a space. The real grocery spend is ₹7,010, but =SUMIFS($C$2:$C$500, $D$2:$D$500, "Groceries") returns only ₹4,200. Nothing turns red, so you simply believe the wrong number.
How do I build the dropdown step by step?
- Add a tab named Settings. In A1 type Category, then list one category per row from A2 downwards: Groceries, Milk and vegetables, Domestic help, Society maintenance, Fuel, Medical, Eating out, Other.
- On your Expenses tab, keep Date in A, Description in B, Amount in C and Category in D.
- Select D2:D500, then choose Data, Data validation, Add rule.
- Under Criteria choose Dropdown (from a range) and type =Settings!$A$2:$A$30. The extra blank rows are fine; the list simply grows when you add a name.
- Open Advanced options and set the invalid-data option to Reject input, and keep the dropdown arrow visible. Click Done.
In Excel the same job is Data, Data Validation, Allow: List, Source: =Settings!$A$2:$A$30. The tick box for an in-cell dropdown is on by default. Under Error Alert, pick Stop so wrong entries are refused.
Can I use a named range instead?
Yes, and it is tidier. Select Settings!A2:A30, give it the name CategoryList (Data, Named ranges in Sheets; the Name Box or Formulas, Define Name in Excel), then use =CategoryList as the source. When you later add a category you only extend that one range, and every dropdown that uses the name follows.
What about rows I already typed?
Validation only checks new entries. For old data, do a quick clean-up first. In Google Sheets, an existing value that is not on the list shows a small red mark if you chose the warning option while setting the rule. In Excel use Data, Data Validation, Circle Invalid Data to ring the odd ones. Fix each ring by picking from the dropdown, then clear the circles. A pivot table or a SORT on the Category column also surfaces odd spellings, because the variants line up next to each other.
Check that the totals now agree
| Check | Formula | What you want to see |
|---|---|---|
| Category total | =SUMIFS($C$2:$C$500, $D$2:$D$500, "Groceries") | Matches the sum of those rows by eye |
| Everything counted | =SUM($C$2:$C$500)-SUM(F2:F10) | Zero, where F2:F10 holds your category totals |
| Blank categories | =COUNTIFS($C$2:$C$500, ">0", $D$2:$D$500, "") | Zero rows with an amount but no category |
The second row is the useful one. If the difference is not zero, some amount is sitting under a name your summary does not list.
When a sheet stops being enough
A dropdown cleans what you type, but typing every line is still the slow part. If you would rather import bank or card statements and let rules and your past choices do the categorising, SpendixAI does that, and you review the rows before saving. Until then, the Settings tab and one validation rule are a sound foundation.
- Keep one list on the Settings tab and never type a category by hand.
- Extend the range to row 30 or 50 so new categories need no re-setup.
- Add an Other category so odd items have a home and the blank check stays at zero.
- Review the Settings list once a year before April and merge near-duplicates.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

