Categorizing business expenses

TL;DR:

• Start with a short category list you can keep for years, not a long one you abandon.

• Turn repeat decisions into rules: the same vendor always goes to the same category.

• Keep a Needs review category for anything unclear, and decide those in one sitting.

A bank export is a long list of names like a card processor reference and a date. It tells you nothing about where the money went. Categorizing turns that list into a handful of totals, such as software, travel and supplies, which is what you actually need for budgeting, reports and conversations with your accountant.

When you need to do it

Do it every month, soon after the statement closes, while you still remember what the charges were. A month is a comfortable size: one sitting, a few dozen lines. Waiting a whole year turns it into a puzzle.

Build the category list first

Write six to ten categories before you touch a transaction. Each should answer a question you really ask, like how much do we spend on travel. Too few and the totals are vague; too many and you will hesitate on every line and file similar things in different places.

• Software and subscriptions

• Office supplies

• Travel

• Meals

• Advertising

• Bank fees

• Needs review (a holding pen, not a real category)

Which categories make sense for tax purposes depends on your country and business type, and it is worth asking your accountant for their preferred list before you start. This article covers the sorting method, not tax treatment.

Set up the sheet and the rules

1. Import the transactions onto a tab named Data: Use the columns Date, Vendor, Amount and Category. Keep amounts as numbers, with money out as positive costs, so they can be added up.

2. Clean the vendor names: Bank lines often carry extra codes. Put a tidy name in the Vendor column, such as the shop or service name only. Our guide on cleaning a messy spreadsheet covers the usual fixes for stray spaces and capitals.

3. Make a Rules tab: Two columns: Vendor in A and Category in B, one row per vendor you have decided. The first time you categorize a vendor, add it here. After that it is automatic.

4. Look up the category: In D2 on the Data tab enter =IFERROR(VLOOKUP(B2,Rules!$A$2:$B$100,2,FALSE),"Needs review") and fill down. Known vendors get their category; unknown ones land in Needs review.

5. Decide the review pile: Filter for Needs review, decide each line, and add new vendors to the Rules tab so you never decide the same thing twice.

Worked example: sorting 30 transactions into categories

Say a one-person business has 30 transactions in the month, in rows 2 to 31 of the Data tab. The Rules tab already recognizes most vendors, so after the lookup formula runs, 25 lines have a category and 5 say Needs review. Those five are a mixed bag: a one-off charge from a shop you do not recognize, two payments to the same unclear name, a refund and a cash withdrawal.

You open each one. The unclear name turns out to be a printing service, so you add it to the Rules tab as Office supplies and the two payments fill in at once. The refund is categorized to match the purchase it reverses, which keeps that category's net figure honest. The cash withdrawal is a problem: you cannot say what it was for, so you check the receipts and your notes before deciding, and if you still cannot tell, you leave it flagged for your accountant instead of guessing. (All numbers here are an example.)

Put two formulas on a Summary tab as a cross-check. One tallies the number of rows in each category, and the other sums their amounts:

=COUNTIF(Data!$D$2:$D$31, A2)
=SUMIF(Data!$D$2:$D$31, A2, Data!$C$2:$C$31)

Here A2 holds a category name. Fill both formulas down the list of categories. The summary for the example month looks like this:

Check the sums twice. The counts are 6 + 5 + 4 + 5 + 3 + 2 + 5 = 30, matching the 30 rows. The totals are 184.00 + 126.75 + 412.40 + 138.60 + 275.00 + 24.00 + 97.25 = 1,258.00. Finally compare that grand total with =SUM(Data!C2:C31). If the two do not match, a row has a blank or misspelled category and fell out of every SUMIF.

Once the five review lines are decided, the Needs review row should read 0 transactions and 0.00, and the lines have moved into the real categories.

Keeping it consistent

• Change the category list only at the start of a new year or period, so months stay comparable.

• Write one line of definition next to each category, such as Meals: food bought for business reasons, so that you and anyone helping you decide the same way.

• Spell categories exactly the same everywhere. A trailing space makes Travel and Travel two different categories in a SUMIF.

• Use a data validation dropdown on the Category column so typos are impossible. In both Excel and Google Sheets the feature is called data validation.

Mistakes to avoid

• Creating a miscellaneous category and dumping everything in it. It becomes your biggest line and says nothing.

• Splitting a mixed purchase wrongly. If one receipt covers two things, split it into two rows.

• Mixing personal and business spending in one account without marking it.

• Categorizing without receipts for charges you cannot recognize.

Doing it faster

The Rules tab is the speed-up: each decision is made once. After two or three months, almost every line categorizes itself and the review pile shrinks to a few new vendors. If you want company while you tackle a long review pile, DuckyHelper can sit with you, see the sheet on your screen and talk it through out loud, but the category decisions stay yours.

Frequently asked questions

How many categories should I use?

Six to ten is a good start for a small business. Add one only when you find yourself asking a question the current list cannot answer.

Can I categorize by keyword instead of exact vendor?

You can, but keyword rules misfire when names overlap. An exact vendor lookup is slower to build and much more predictable, which matters for numbers other people will rely on.