Keeping the old value beside the new one, changing rows in batches, and reconciling the total after each batch is the safest approach to bulk editing hundreds of categories. If something goes wrong you can see what changed and put it back.
What is the safeguard before any edit?
- Save a copy of the file with today's date in the name.
- Add a column called Old category and paste the existing categories into it as values.
- Add a column called Changed, empty for now.
- Note the grand total of amounts. Say it is CA$5,236.40.
These four steps take two minutes and remove most of the fear.
How do you edit in batches?
Pick a batch size you can verify, such as all rows from one merchant or all rows below a certain amount. Apply the new category, then mark the batch in Changed with a short note like batch 3 coffee shops. After each batch, filter the Changed column and read the rows.
How do you reconcile with SUBTOTAL?
SUBTOTAL ignores rows hidden by a filter, so it totals exactly what you can see. With amounts in column C:
- =SUBTOTAL(109, $C$2:$C$201) sums the visible amounts.
- =SUBTOTAL(103, $C$2:$C$201) counts the visible non-empty cells.
Filter to Groceries and the count and total show how many rows and how much you assigned to it. Filter to Uncategorised and watch it fall.
| Stage | Uncategorised rows | Total of all rows |
|---|---|---|
| Start | 96 | CA$5,236.40 |
| After batch 1 | 61 | CA$5,236.40 |
| After batch 2 | 28 | CA$5,236.40 |
| After batch 3 | 7 | CA$5,236.40 |
The right column should never change. If it does, an amount was edited by mistake.
When should you stop editing?
When the remaining uncategorised rows are small one-offs. Label them Other and move on; chasing the last seven rows is rarely worth the time.
How big should a batch be?
Small enough that you can review every row in it. For a first attempt, 20 to 40 rows is comfortable; as you grow confident, move to one merchant at a time regardless of count. A large batch is fine if the rule is simple, such as every row that contains one particular word. A small batch is wiser if the rule is fuzzy.
If you ever find a mistake in an earlier batch, the Changed column tells you exactly which rows to look at, which is the main reason for keeping it.
Add a final column called Reviewed once a row has been looked at by a person, not a rule. At the end, filter for empty Reviewed cells and scan the biggest amounts first. This gives you a simple way to know when you are done, and a clear record that every large row had human eyes on it.
It also helps to decide your category names before you start, and to stick to exactly the same spelling. Groceries and grocery are two different labels to a filter, and a bulk edit that mixes them leaves you with two half-empty categories. Pick a list of ten to fifteen names, write them at the top of the sheet, and copy from that list.
What if you use an app instead?
Look for a tool that edits several rows at once, shows what is selected before you confirm, and keeps deleted rows recoverable. SpendixAI offers bulk edit and delete on transactions, with a 30-day recycle bin for deleted ones, but it is built around India and INR and is not described as available in Canadian dollars.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

