Sorting a month of bank transactions into categories is one of those jobs that feels like it should take five minutes and somehow eats an afternoon. The reason is almost never the categorizing itself. It is the shape of the data. If your statement is sitting in Excel as four tidy columns of Date, Description, Amount, and Balance, the whole thing is quick. If it landed in the spreadsheet as one long smear of text down column A, you will fight the file before you ever get to the interesting part.
So this guide assumes you have real rows to work with, and it shows you how to add categories, total them with SUMIF, and roll everything up in a pivot table. If your data is not in that shape yet, start at the bottom of this article, get the file clean, then come back up.
What a workable statement looks like
Open your file and check three things. Each transaction should sit on its own row. The date, the description, and the amount should each live in their own column. And the amount column should be actual numbers, not text that looks like numbers.
That last one trips people up. A value like $1,250.00 with a currency symbol and a comma is often stored as text, and Excel will not add text. Select the amount column, and if the values hug the left edge of the cell, they are text. Numbers align right. To fix it, strip the symbols and separators so a cell reads 1250.00 or -1250.00, and negatives mark money going out.
If your statement instead splits money into separate debit and credit columns, that is fine too. You can either work with both columns or combine them into a single signed amount with a quick formula like =C2-D2 (credits minus debits) so you have one number per row to total later.
Add a category column
Put your cursor in the first empty column to the right of your data, and label the header Category. Now you tag each row. There are two honest ways to do this, and which one you pick depends on how much you care about consistency.
The fast way is to type the category straight into each cell: Groceries, Rent, Fuel, Eating Out, Salary, and so on. It works, but by row 80 you will have typed "Eating Out", "Eating out", and "Dining" and treated them as three different things, which quietly breaks every total downstream.
The cleaner way is a dropdown so you can only pick from a fixed list. Write your categories in a spare column somewhere, say cells H1 through H12. Select your whole Category column, go to Data > Data Validation, choose List under Allow, and point the source at =$H$1:$H$12. Now every category cell has a little arrow, and you pick instead of type. Consistent spelling, every time.
If you have a lot of rows, you can speed up the tagging with a lookup. Build a small table that maps a keyword to a category, for example "UBER" to Transport and "TESCO" to Groceries, then use a formula in the Category column to guess based on the description. A SEARCH inside an IFS or a helper VLOOKUP gets you most of the way, and you fix the leftovers by hand. It is worth setting up once if you do this every month.
Total each category with SUMIF
Once rows are tagged, you want to see the damage per category. SUMIF is built for exactly this. It adds up every amount where the category matches.
List your categories down a clean area, one per row. Next to the first one, write:
=SUMIF(E:E, H1, C:C)
Read that as: look through column E (your Category column), find every row equal to whatever is in H1 (say, Groceries), and add up the matching values from column C (your Amount column). Drag it down and each category gets its own total. If your amounts are signed, groceries will come back as a negative number because that money left your account, which is correct. Wrap it in ABS() if you would rather read a positive spend figure.
Want to slice by month as well as category? SUMIFS takes more than one condition, so you can total Groceries in January specifically. That gets useful when you are staring at several months in one sheet.
SUMIF is great for a fixed list of categories you already know. The moment you want to explore the data, reshuffle it, or catch a category you forgot to list, a pivot table is the better tool.
Roll it up with a pivot table
A pivot table summarizes your rows without a single formula, and it updates when you change the underlying data. Click any cell inside your data, go to Insert > PivotTable, and accept the range Excel proposes. Drop it on a new sheet.
In the field list, drag Category into the Rows box and Amount into the Values box. Excel gives you the total spend per category instantly. If Values shows a count instead of a sum, click the field, choose Value Field Settings, and switch it to Sum. That count-instead-of-sum surprise is almost always the text-versus-number problem from earlier, so if it will not sum, go back and check your amount column is really numeric.
From here you can drag Date into Columns to see categories across months, or into a Filters slot to look at one month at a time. Right-click any category total and choose Expand to see the individual transactions that make it up, which is how you catch the one $340 charge you filed under the wrong bucket. When you add or retag rows, right-click the pivot and hit Refresh.
Why the clean file matters so much
Everything above rests on one assumption: your transactions are real rows in real columns. This is where most of the frustration actually lives, and it is usually invisible until a formula misbehaves.
When people copy a PDF statement and paste it into Excel, the columns tend to collapse into one. Dates fuse to descriptions, amounts wander into the wrong cells, and multi-line memos break a single transaction across several rows. SUMIF cannot read that, a pivot table cannot summarize it, and no amount of tagging fixes a file where the amount is stuck to the end of a description string. We wrote up exactly why that happens in why copy-paste from a PDF statement breaks in Excel, and the short version is that a PDF has no column structure for the clipboard to preserve.
The reliable path is to convert the PDF into a proper spreadsheet before you touch categories. ConvertFin reads the layout of the statement and gives you back an Excel or CSV file with separate Date, Description, Amount, and Balance columns, which is the exact shape this whole workflow needs. It runs in your browser, so the file is parsed on your own machine and never uploaded, which matters when the statement is a client's rather than your own. You can convert a statement to Excel here and have clean rows in under a minute, then add your category column on top.
One honest caveat. This only works on PDFs that hold real, selectable text. If your statement is a scanned image, the kind where you cannot highlight any words with your cursor, there is no text to read and a converter has nothing to extract. You would need OCR or, better, a fresh PDF downloaded straight from online banking. On a Mac the process is the same as anywhere else, and the Mac walkthrough covers the small differences.
If you do this for other people rather than yourself, the bookkeeper's guide to converting statements goes deeper on handling volume and keeping client data private.
Get the file right first, and categorizing a month of spending really does take five minutes. SUMIF for the totals you already care about, a pivot table for everything you want to explore, and a category column that only exists because the rows underneath it were clean to begin with.