If you keep your bookkeeping in a long list of transactions, a pivot table is the fastest way to make sense of it. In a few clicks you can see how much you spent on each category, how income moved month by month, or which customer brings in the most. Best of all, you do not need to write a single formula. This guide shows you how to build a bookkeeping pivot table from scratch, and once you have done it once you will wonder how you managed without.
Most small businesses end up with a transaction list that grows to hundreds or thousands of rows over a year. Scrolling through that to answer a simple question, like “how much did I spend on travel last quarter”, is slow and error-prone. A pivot table answers that question in seconds and, crucially, recalculates the moment you add more data. It is the difference between a static list and a living report, and it is the single most useful Excel skill I teach new bookkeepers.
Step 1: Get your data into a clean list
A pivot table needs a tidy table: one row per transaction, a single header row, and no blank rows or merged cells. For bookkeeping I always use these columns.
| Date | Type | Category | Description | Amount (£) |
|---|---|---|---|---|
| 05/04/2026 | Income | Sales | Invoice 1042 | 1,450.00 |
| 09/04/2026 | Expense | Travel | Train to client | 62.40 |
| 14/04/2026 | Expense | Software | Accounting app | 29.00 |
Consistency is everything. “Travel” and “travel ” with a trailing space become two different categories in a pivot, so tidy your text before you start. A quick way to standardise is to use a data validation drop-down on the category column, so you can only pick from an approved list rather than typing freehand. That one step prevents most of the messiness that trips up pivot tables, and it makes your reports trustworthy from the outset.
It also pays to use a consistent sign convention. I record income as positive and expenses as negative, so that a single total tells me whether a category or month was net positive or negative. If you prefer to keep both as positive numbers and rely on a Type column instead, that works too, as long as you are consistent across every row. Pivots are only as good as the data underneath them.
Step 2: Insert the pivot table
Click anywhere in your data, then go to Insert > PivotTable. Excel detects the range and offers to drop the pivot on a new sheet, which keeps things uncluttered. Accept it, and you will see an empty pivot with a field list on the right. Do not be put off by the blank grid; it stays empty until you tell it what to show, and you build the report simply by dragging field names into the four areas at the bottom: Filters, Columns, Rows and Values.
Think of those four areas as the questions you are asking. Rows and Columns set what you want to compare, such as categories or months. Values set what you want to measure, almost always the amount. Filters let you narrow the whole thing down, perhaps to a single quarter. There is no wrong way to experiment here: drag a field in, see what happens, and drag it out again if it is not what you wanted. Nothing you do in the pivot changes your underlying data.
Step 3: Summarise spending by category
Drag Category into the Rows area and Amount into the Values area. Excel adds up the amount for each category automatically. If it shows a count instead of a total, click the value field, choose Value Field Settings, and switch it to Sum. You now have a clean total per category with no formulas at all.
Step 4: Break it down by month
Drag Date into the Columns area. Modern Excel groups dates into months automatically; if it does not, right-click a date in the pivot and choose Group, then pick Months. You can now read across to see how each category changed through the year, which is exactly the view you want before a quarter end.
Step 5: Split income from expenses
Drag Type into the Filters area at the top. A drop-down appears that lets you show only income or only expenses with a single click. This is the quickest way to sanity-check your figures against your profit and loss template before you finalise anything. If you would rather not use a filter, you can drag Type into the Rows area above Category, which nests every category neatly under either Income or Expense and gives you subtotals for each.
For an even faster experience, add a Slicer. With the pivot selected, go to PivotTable Analyze and choose Insert Slicer, then tick Category or Type. A slicer is a set of clickable buttons that filter the pivot visually, which is far friendlier than a drop-down when you are exploring the numbers in a meeting. It is the kind of small touch that makes your figures approachable to people who would normally glaze over at a spreadsheet.
Keeping the pivot up to date
A pivot does not refresh on its own. When you add new transactions, right-click the pivot and choose Refresh. If you turn your list into a proper Excel Table first (Ctrl+T), the pivot will automatically include new rows when you refresh, so you never have to redraw the range. I make this the very first thing I do with any new bookkeeping file, because it removes a whole category of “why is my total wrong” problems down the line.
Once you are comfortable, try dragging the same field into more than one place. A pivot can show totals and a running percentage of the whole at the same time, so you can see not just that travel cost £1,200, but that it was 8% of your spending. Right-click a value, choose Show Values As, and pick “% of Grand Total”. Little touches like this turn a plain summary into something genuinely useful for decisions, and none of it requires a formula.
Common mistakes
- Blank rows in the source. A gap stops Excel from reading the whole list. Keep the data solid.
- Inconsistent category names. Trailing spaces and different spellings split your totals. Standardise them first.
- Forgetting to refresh. New transactions will not appear until you refresh the pivot.
- Counting instead of summing. If the values look like small whole numbers, switch the field setting from Count to Sum.
Frequently asked questions
Do I need formulas to use a pivot table?
No. That is the beauty of pivots. You drag fields into place and Excel does the summarising for you, which makes them ideal for anyone who finds formulas daunting.
Why is my pivot showing a count rather than a total?
It usually means the column has blank cells or text mixed in, so Excel treats it as text. Clean the column, then change the value field setting to Sum.
How do I make the pivot include new rows automatically?
Convert your source list to an Excel Table with Ctrl+T before building the pivot. The pivot then expands to cover new rows whenever you refresh.
Can I use pivot tables in Google Sheets?
Yes. Google Sheets has its own pivot table feature under Insert, and the idea is the same: rows, columns, values and filters.
