Once your receipts are in a spreadsheet, the next step is usually a Category column: groceries, transport, household and so on, so you can see where the money went. Type those by hand for a few months, though, and you get Groceries, groceries, Grocery, Food and Groceries with a trailing space. Your SUMIF or PivotTable then splits one category into five rows. A dropdown list fixes this at the source.

1. Settle on a short list

Aim for ten categories or fewer. Finer splits make every row a small decision and every monthly summary harder to read. For a household budget, a starting point might be:

  • Groceries
  • Eating out
  • Transport
  • Household
  • Health
  • Kids
  • Gifts
  • Entertainment
  • Bills
  • Other

A club or society might use Venue hire, Equipment, Refreshments, Events, Travel, Other. For business costs, the list in Expense categories for freelancers is a good base. Always keep an Other so awkward expenses don't get forced into the wrong bucket.

2. Keep the list on its own sheet

Add a sheet named Categories. Type Category in A1 and your list below it, one per row. Select the list and press Ctrl+T to turn it into a table; new categories you add later will extend the table automatically.

Then go to Formulas → Define Name, call it CategoryList, and in Refers to point at the table column, for example =Table1[Category]. Naming it this way means the dropdown always follows the full table, however long it grows.

3. Create the dropdown

  1. On your receipts sheet, type Category as the header of the empty column to the right of Total (column E).
  2. Select the cells that will hold categories, from E2 down to your last receipt.
  3. Go to Data → Data Validation.
  4. Under Allow, choose List, and in Source type =CategoryList.
  5. On the Error Alert tab, the Stop style blocks anything not on the list. Choose Warning if you'd like to type a one-off category now and then.

Each cell now shows an arrow when selected. From the keyboard, Alt+↓ opens the list, and the arrow keys plus Enter pick an item.

4. Careful when pasting in new receipts

When you download next month's receipts from the receipt tool and paste them into the same file, a normal paste can wipe the dropdown from the cells you paste over, because Excel copies the source cells' (empty) validation along with the values.

  • Paste with Paste Special → Values instead. The dropdown stays in place.
  • Apply the dropdown to a generous range up front, say down to row 500, so new rows are already covered.
  • To find existing entries that aren't on the list, open the arrow next to Data Validation and choose Circle Invalid Data. Every off-list value gets a red circle.

5. Totals by category

With clean category names, totals are a single formula. On the Categories sheet, put this in B2 and fill down:

=SUMIF(Receipts!E:E, A2, Receipts!D:D)

Change Receipts if your sheet has a different name. To break the totals down by month as well, a PivotTable by category and month is quicker, and it only groups correctly when each category is spelled one way.

6. In Google Sheets

  1. Select the cells and choose Insert → Dropdown.
  2. In the rules panel, change the criteria to Dropdown (from a range) and select the list on your Categories sheet, such as Categories!A2:A20. For a short list you can keep Dropdown and type the items in directly.
  3. Under Advanced options, choose Reject the input for invalid data to keep off-list values out.

You can give each item a color, so categories show up as tidy chips. For the rest of the Sheets workflow, see How to track receipts in Google Sheets.

Summary

Keep ten categories or fewer, list them on their own sheet, and pick them from a dropdown. Paste new receipts with Paste Special → Values so the dropdown survives. With one spelling per category, SUMIF and PivotTables give you the right totals the first time.