Expense categories

Measure and shrink your uncategorised pile with two spreadsheet formulas

Use two spreadsheet formulas to measure how much of your Canadian dollar spending is uncategorised, then clear the largest rows to make reports honest.

Editorial illustration for Measure and shrink your uncategorised pile with two spreadsheet formulas

You can tell how badly an uncategorised pile distorts your reports with two formulas: one that adds up the uncategorised dollars and one that divides that by total spend. Once you have the share, work the largest rows first and watch the percentage fall.

This works in Excel and Google Sheets, with the layout set out below.

What layout do the formulas assume?

  • Column A holds the date, column B the merchant, column C the amount as a positive number for spending, and column D the category.
  • Rows run from 2 to 500, so every formula uses the range $2 to $500 with absolute references.
  • Uncategorised rows have the word Uncategorised in column D.

If your export shows spending as negative numbers, put a minus sign before SUM and SUMIF, or add a helper column with =-C2.

Which two formulas measure the pile?

  1. Rows: =COUNTIF($D$2:$D$500, "Uncategorised")
  2. Dollars: =SUMIF($D$2:$D$500, "Uncategorised", $C$2:$C$500)
  3. Share: =SUMIF($D$2:$D$500, "Uncategorised", $C$2:$C$500) / SUM($C$2:$C$500)
  4. Biggest single row: =MAXIFS($C$2:$C$500, $D$2:$D$500, "Uncategorised") in Excel 2019, Excel 365 or Google Sheets

Format the share cell as a percentage.

What do the numbers tell you?

Say a month totals C$4,200 and 190 rows, of which 58 are uncategorised and add to C$1,176. The share is 1,176 divided by 4,200, which is 28 percent. By rows it is 58 out of 190, about 31 percent, so the pile is bigger in count than in value: many small rows and a few big ones.

MeasureBeforeAfter clearing 6 big rows
Uncategorised rows5852
Uncategorised dollarsC$1,176C$480
Share of spend28 percentabout 11 percent

Six rows removed C$696 of the pile (1,176 minus 480), and the share dropped from 28 to about 11 percent (480 divided by 4,200). That is a better return than clearing the other 52 rows one at a time.

How do you find those big rows quickly?

Filter column D to Uncategorised, then sort column C from largest to smallest. Work down the list until the amounts are small enough that you no longer care. The MAXIFS result above tells you what you are looking at before you even sort.

Two cautions about the formulas. First, if you type the category inconsistently, for example Uncategorised in some rows and Uncategorized or a blank cell in others, the count will miss rows. Use a drop-down list in column D, or add a second formula, =COUNTBLANK($D$2:$D$500), to catch empty cells. Second, whole-column references such as D:D work too, but fixed ranges are easier to reason about while you are learning.

What share should you aim for?

There is no official figure. Choose one that you can keep, such as under 5 percent of spend, and measure it every month with the formula above. A target you can track is more useful than one you merely hope for.

One more habit pays off: when you clear a big row, ask yourself whether it will recur. A rent payment, a loan instalment or an insurance premium will be back next month, so give it a rule or a recurring entry now. A one-off, such as a repair, is done once and forgotten. Treating the two differently means the pile shrinks for good, instead of refilling with the same rows.

When does a spreadsheet stop being enough?

When you are pasting statements in every month and rebuilding the same rules by hand, a tool that remembers your rules and flags unsorted rows saves time. SpendixAI, for instance, can hold unmatched items for your review when AI categorisation is off. It is built around India and INR, so it may not suit Canadian accounts; use it only if that fits your situation.

Treat the share as a quality indicator for your own records, not as anything you would send to a tax authority.

SPENDIXAI JOURNAL

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

Explore your reports