Budgeting

Splitting a budget category without breaking your history (Canada)

Splitting a category such as Transport into car and transit needs a date cut-off. See a SUMIFS method to keep totals comparable, with Canadian dollar examples.

Editorial illustration for Splitting a budget category without breaking your history (Canada)

Splitting one category into two is the change that most often wrecks a budget history. The new categories only exist from the day you created them, so earlier months have nothing to compare with. The answer is a cut-off date, a combined view for comparison and a note on what you did.

What is the problem with splitting?

Say Transport was a single line through the year. In July you split it into Car and Transit. From July you can see how much goes to each, but for January to June everything sits in Transport, so a month-by-month chart of Car looks empty for half a year.

Choose a cut-off and keep both views

  1. Write the split date in a cell, for example $H$1 holding 1 July.
  2. Keep the old Transport label for rows before that date.
  3. From that date, use Car and Transit.
  4. For comparisons across the year, build a combined line that adds the three.

For the combined figure, use two SUMIFS: =SUMIFS($C$2:$C$600, $D$2:$D$600, "Transport") + SUMIFS($C$2:$C$600, $D$2:$D$600, "Car") + SUMIFS($C$2:$C$600, $D$2:$D$600, "Transit"). Spread across a long line for readability, but it simply adds the three categories.

A worked example in Canadian dollars

PeriodTransportCarTransitCombined
January to June, total$1,320$0$0$1,320
July to December, total$0$1,080$330$1,410

Per month, the first half is 1,320 ÷ 6 = $220, and the second is 1,410 ÷ 6 = $235. The combined line shows a modest rise of $15 a month, while the individual lines alone would suggest a leap from nothing.

Can I fix earlier rows instead?

You can, if you still have the statements and enough patience. Go back through the months, tag each row as Car or Transit, and keep a record that you did so. It is worth the effort only when the finer detail matters to a real decision, such as whether to keep a vehicle. Otherwise the combined view will do.

What should I write down?

  • The date of the split.
  • The names of the old and new categories.
  • Whether earlier rows were re-tagged or left as they were.
  • The cell or tab where the combined total is calculated.

A note on tools

SpendixAI is built around India and INR, so it is not offered for a household in Canada. It has categories with sub-categories, which suits splits like this one: Transport becomes a parent and Car and Transit sit beneath it. Look for that structure in any tool.

How do I chart across the split?

Build the chart from the combined line, and add the split lines on a second chart that starts at the cut-off date. Readers then see the long-term trend without a gap, and the new detail from the day it exists. Label the chart with the date of the split so nobody mistakes the change in detail for a change in spending.

  • Chart the combined line for the full year.
  • Chart the new lines from the split date only.
  • Add the split date to the chart title.

If you share the sheet with a partner, put the cut-off date in a visible cell at the top and name it. Anyone filtering the data then knows that Transport before that date and Car or Transit after it are the same money, described at different levels of detail.

Before you split

  • A cut-off date in a cell.
  • A combined line for comparison.
  • A one-line note explaining the change.
SPENDIXAI JOURNAL

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

Explore your reports