Before importing, list every distinct category in your sheet, decide what each should be called in the new tool, and put the pairs in a two-column table. A lookup formula then rewrites the whole category column in one go. That fixes near-duplicates such as Food, Foods and Groceries & Food, and it takes under an hour for most household sheets.
Step one: how many categories do you really have?
Extract the distinct names. In Excel 365, Excel 2021 or Google Sheets, =UNIQUE(C2:C2000) lists each name once. In older Excel, use a pivot table with the category in Rows. Next to each name, count its rows with =COUNTIF($C$2:$C$2000,E2), where E2 is the name. Sort by count and you will see a few big categories and a long tail of oddities with one or two rows.
Clean obvious spacing first. =TRIM(C2) removes extra spaces, so 'Food ' and 'Food' stop counting as different names.
Step two: build the mapping table
On a new tab called Map, put old names in column A and the new name in column B.
| Old name (A) | New name (B) | Rows |
|---|---|---|
| Food | Groceries | 214 |
| Foods | Groceries | 9 |
| Groceries & Food | Groceries | 46 |
| Eating out | Restaurants | 88 |
| Hotel | Restaurants | 12 |
| Petrol | Fuel and transport | 61 |
| Auto, Ola | Fuel and transport | 73 |
The counts are invented. The point is that seven old names collapse into three. Aim for a list short enough that you can pick a category in a second; a few dozen at most is plenty for most homes.
How do you apply the mapping?
In a new column next to the old categories, look up each name. Three versions work, depending on your tool:
- Any Excel or Sheets version: =IFERROR(VLOOKUP(TRIM(C2),Map!$A$2:$B$40,2,FALSE),"UNMAPPED")
- Excel 365, Excel 2021 and Google Sheets: =XLOOKUP(TRIM(C2),Map!$A$2:$A$40,Map!$B$2:$B$40,"UNMAPPED")
- Google Sheets with a long list: put the formula in the first row only and wrap it in ARRAYFORMULA
Filter the new column for UNMAPPED, add those names to the Map tab and repeat until none remain. Then copy the column and use Paste special, Values only, over the old category column, so the file you export holds plain text rather than formulas.
Can't you just use Find and Replace?
You can, with one warning: tick the option to match the entire cell contents. Without it, replacing Food with Groceries also turns Fast Food into Fast Groceries. A lookup is safer for more than three or four renames, because the table stays as a record of what you changed and can be reviewed.
What about categories that should split instead of merge?
Sometimes one old name hides two needs. A category called Home might mix rent, electricity and repairs. If you want to separate them, add a keyword column and a lookup on the description, for example a row whose description contains 'electric' goes to Utilities. Do this only for the big mixed category; leave the rest alone.
Use sub-categories for detail so the top level stays short. Groceries can hold vegetables, milk and staples, and Restaurants can hold delivery and dining out. You can still see the big picture at the top and drill down only when you ask why a month was high.
Setting up the new tool
Create the new categories before importing, so the rows have somewhere to land. SpendixAI lets you create custom categories and sub-categories, each with a colour, a GST rate, ITC-eligible and tax-deductible settings and an accounting code. Those tax settings are labels for your own records and the GST figures in reports are estimates, so confirm them with your CA. Your own rules are applied first when categorising, and your corrections become history, so a quick pass after the first import pays off later.
Checklist before you export
- Distinct names listed and counted.
- Mapping table built, with no UNMAPPED left.
- Values pasted over the formulas.
- Original categories kept in a separate column.
- A short test import of 20 rows checked by eye.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

