Expense categories

How to bulk edit 200 transaction categories at once (US)

Fix hundreds of miscategorised rows in a spreadsheet by filtering one merchant at a time and pasting a category into only the visible cells. Dollar example.

Editorial illustration for How to bulk edit 200 transaction categories at once (US)

To bulk edit categories in a spreadsheet, filter the list to one merchant, type the right category into the first visible cell, copy it, and paste it down the visible cells of the category column. Repeat per merchant, starting with the one that has the most rows. A 200-row export usually falls in about a dozen passes.

What do you need before the first edit?

  • The export saved under a new name, so the original stays untouched.
  • A column for the merchant or description and a column for the category.
  • A spare column called Old category, filled with a copy of the category column before you change anything.

How do you filter and paste?

  1. Select the header row and turn on filters.
  2. On the description column, choose a text filter that contains the merchant name, for example coffee.
  3. Type the new category into the first visible cell of the category column and copy it.
  4. Select the rest of the visible category cells and paste. In a filtered list the paste lands only on visible rows.
  5. Clear the filter and check the count.

Using Excel or Google Sheets the steps are the same; only the menu names differ. Avoid dragging the fill handle across a filtered range, because behaviour can differ between programs and hidden rows are easy to get wrong.

How do you know nothing went wrong?

Sum the amount column before and after. Say the 200 rows total $6,482.50 before you start. Categorising does not change amounts, so the total should still be $6,482.50 afterwards. Then check one category: filter Groceries and look at the top and bottom of the list for anything that does not belong.

CheckBeforeAfter
Row count200200
Sum of amounts$6,482.50$6,482.50
Rows marked Uncategorised749

Which merchants should you do first?

Sort a pivot or a copy of the merchant column and count. If one grocery chain appears 31 times and a coffee shop 18 times, those two passes take care of 49 rows, about a quarter of the list. Work down from the biggest repeat, and leave one-off purchases for the end, when fewer rows remain.

What if a merchant fits two categories?

Some stores sell groceries and household goods. Choose the one that fits the majority and accept a small error, or add an amount filter so large baskets get a different category. Do not spend an hour perfecting a $12 distinction.

How do you stop the mess coming back?

Most miscategorised rows are the same merchants returning every month. After you finish, save your merchant list in a second tab, with the merchant text and the category beside it. Next month you can paste new rows below and apply the same pairs. Even a basic list of twenty merchants usually categorises more than half the rows on arrival.

  • Keep the list sorted alphabetically.
  • Add new merchants as you meet them.
  • Remove entries that turned out wrong.

One more safeguard is to freeze the header row and keep the Old category column visible while you work. A quick glance across a row then shows what it was and what it is now, which catches typos in the category name before they spread to thirty rows.

When a spreadsheet stops being enough

SpendixAI transactions support bulk edit and delete, deleted ones sit in a recycle bin for 30 days, and corrections become history for future matches. It is built around India and INR, so it is mentioned only for readers working in rupees; the spreadsheet method above works anywhere.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports