Budgeting

Merging budget categories without losing last year's figures (UK)

Merging categories such as subscriptions and entertainment often scrambles budget history. Use XLOOKUP and a mapping tab, with a worked example in pounds.

Editorial illustration for Merging budget categories without losing last year's figures (UK)

Merging two categories feels tidy, but it breaks the comparison with every month that used the old pair. The cure is to keep your original entries untouched, hold a short mapping list, and let a lookup roll old and new names up to one current label.

What goes wrong when you merge?

Say you used Subscriptions and Entertainment separately for a year, then decided they belong together. If you simply rename one, last year's Subscriptions rows disappear from your totals, and this year's combined line looks bigger than the pair ever did. You then conclude your spending rose when only the labels changed.

Step by step

  1. Create a tab called Map with old names in column A and the label to use now in column B.
  2. List both Subscriptions and Entertainment in column A, each pointing to Media and Leisure in column B.
  3. Add a helper column beside your rows. Do not overwrite the original category.
  4. Enter =XLOOKUP(D2, Map!$A$2:$A$60, Map!$B$2:$B$60, "Unmapped") in the first row and copy it down.
  5. Total from the helper column.

XLOOKUP is available in Excel 365, Excel 2021 and Google Sheets. Older versions of Excel do not have it, so use INDEX and MATCH there.

A worked example in pounds

PeriodSubscriptionsEntertainmentCombined
Last year, per month£34£58£92
This year, per month£38£61£99

Last year's combined figure is 34 + 58 = £92, and this year's is 38 + 61 = £99, so the real change is £7 a month. Without the mapping, one line would appear to jump from £34 to £99, and you would chase a rise of £65 that never happened.

How do I test that nothing went missing?

  • Total the original column and the helper column for the same period. They should agree.
  • Filter the helper column for Unmapped. Each hit is a name not yet in the Map tab.
  • Spot-check three old months against the figures you remember.

When should I not merge?

If the two categories have different purposes, for example one is a fixed cost you can cancel and the other is a fun budget you adjust weekly, keeping them separate may be more useful. Merge for reporting, but keep what you act on.

A note on tools

SpendixAI is built around India and INR, so it is not meant for a household in the UK. Its category tree and merchant defaults are designed so the same merchant keeps the same category, which is a useful feature to look for in any budgeting tool.

What if the old sheet is already a mess?

Start from a copy. Export your categories with counts, sort by frequency and list every name used. You will probably find variants such as Eating out and Eatingout. Map each to one label in the Map tab, run the lookup and look at the Unmapped list until it is empty. It may take an hour, but afterwards every older month is readable again.

  • Work on a copy, never the original.
  • List every distinct name used.
  • Clear the Unmapped list before building charts.

Keep one more habit: when you add a new merchant, check the Map tab before typing a category. A small guard like a drop-down list sourced from column B of the Map tab prevents most future naming drift and saves you from running the Unmapped check every month.

Before you merge

  • A mapping list for every old name.
  • A helper column, not an overwritten original.
  • A before-and-after total that agrees.
SPENDIXAI JOURNAL

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

Explore your reports