The export lands on a Monday, a few thousand rows out of the booking system or the point of sale. Somebody wants to know which service sold most last quarter, by month, at each location. A pivot table answers that quickly, once the file is in a shape the pivot can read.
Put the question in one line before you open anything. “Which service brought in the most, by month, at each location?” Every noun in it is a column you need: Service, Month, Location, and a number to add up, here Amount.
Tick them off against the export’s header row. If one is missing, go back to the system it came from. If its export screen lets you pick columns, adding Location there beats rebuilding it by hand.
Leave the extra columns in. A Staff or Channel column you don’t need today makes a useful slicer tomorrow.
Hadley Wickham’s paper Tidy Data describes a tidy table as one with each variable in a column and each observation in a row, which is the shape a pivot reads. In an export, that means one sale or one booking per row, with every fact about it in its own column. The UK Government Analysis Function’s spreadsheet guidance adds the practical rules for headers: one header row, text in every header cell, and no two headers the same.
Oppermind’s pivot reads the first row of the source range as field names, and the table below covers what it does with the usual export habits.
| What the export does | What the pivot does with it | What to change |
|---|---|---|
| Report title and run date in rows 1 to 3 | Treats row 1 of your range as headers, so “Sales report” becomes a field | Start the source range at the real header row, or delete the title rows |
| A column with no header | Leaves it out of the field list | Give it a name |
| Two columns both called “Amount” | Keeps only one of them as a field | Rename one, for example “Amount ex GST” |
| A subtotal row after each week | Adds it in again as if it were a sale | Delete the subtotal rows |
| Blank spacer rows | Shows a “(blank)” line in the pivot | Delete them |
| “Bris” in some rows, “Brisbane” in others | Two separate lines | Use one spelling throughout |
| Amounts like “$1,250.00” | Reads them as numbers | Nothing |
| Amounts like “A$1,250”, “AUD 1250” or “(85.00)” for a refund | Skips them in sums, with no warning | Make them plain numbers, with a minus sign for refunds |
The last row is the one that hides. A sum quietly ignores any amount it can’t read, so a file full of bracketed refunds produces a total that is too high and looks fine. A count still counts those rows, which is a quick way to spot the gap.
If the file is in worse shape than this, with merged cells or dates in four formats, do the structural clean-up first. The accountants and bookkeepers post covers that pass and why you should ask for a list of what changed.
The pivot groups rows by their exact value. A Date column used as a row field gives you one line per day, and there is no setting inside the pivot that rolls days up into months.
So the export needs a Month column. Check the export screen first, since some systems offer a period or month column alongside the date. If yours doesn’t, ask the assistant in the Sheet editor to insert a Month column beside Date and write the month for every row as a plain value. Then check a handful of rows yourself, especially the last day of one month and the first day of the next.
Write months as year then month, such as 2026-07. That keeps July 2026 and July 2025 apart and reads in order. The pivot lists groups in the order it first meets them in the data, so an export sorted by date gives you months in date order.
Select the whole block, headers included, then open the More menu in the Sheet editor and choose Pivot table. The Academy lesson walks through the dialog click by click. The decisions that matter for an export are these.
For “which service sold most”, Service as rows and Location as columns puts the winner at the top of each column’s numbers, and a slicer can handle the month. If a layout is hard to read, build a second one beside it.
Use countDistinct on a Booking ID column and it tells you how many bookings there were, while count on Amount tells you how many rows had an amount. If those two differ, some bookings are split across several rows, which is common in exports that list each line item separately.
Before you trust any of it, compare the Grand Total with the total the export system reports for the same period. If the pivot is lower, look for amounts it couldn’t read. If it’s higher, look for subtotal rows or duplicated days.
A slicer is a card of buttons, one per value in a column, that filters the pivot when you click. Choose Insert slicer from the same More menu, pick the pivot and the field, and the card appears over the top-right of the grid.
Every value starts switched on, with a count of rows beside it. Clicking a value switches it off and takes it out of the totals. Clicking again brings it back. To see one location on its own, switch the others off. The ⌫ on the card turns everything back on, and × removes the card and clears its filter.
The slicer field doesn’t have to be in the pivot. A pivot of Service by Month can carry a slicer on Staff or Channel, which is where those spare columns from the export earn their place. Two slicers on one pivot combine, so Location and Staff together narrow it to one person at one site. Slicers are saved with the sheet and come back when you reopen it.
The pivot recalculates whenever the sheet changes, but only from the source range you gave it. Rows below that range are left out.
If you sized the range with room to spare, paste the new rows directly under the old ones, inside the range. The empty rows still waiting at the bottom show as a “(blank)” line, and a slicer on any row field can switch that line off. Otherwise, build the pivot again from the longer range in a fresh empty area.
Then deal with the slicers. A slicer lists the values that were in the data when you added it, so a new location in this month’s file won’t appear on the card. If any value on that card is switched off, rows with the new value are filtered out too. Remove the slicer and insert it again after each new paste.
Watch the header row as well. If the export system renames a column, say Amount becomes Total, the pivot can no longer find its field and stops updating, while still showing last month’s figures. Paste the same columns, in the same order, under the same names.
Finish the same way as the first time, by checking the Grand Total against the export’s own total.
The pivot lives in the Sheet editor. When you export the workbook as .xlsx, the pivot goes out as plain cells holding the numbers, and the slicers stay behind. That suits a report someone will read. If they want to slice it themselves, share the sheet in Oppermind instead.
If the next step is a picture for the board pack or the team meeting, the chart post covers matching the question to the chart.
Build a pivot and slicers from your next export in a workspace with documents, sheets and decks.
No. It groups by exact value, so add a Month or Quarter column to the data first.
A row field has empty cells, or the source range includes empty rows. Fill the cells, shrink the range, or switch the line off with a slicer.
Some amounts are text it can’t read as numbers, usually a currency code in front or brackets around refunds. Run a count on the same column and compare it with the number of rows.
Yes. Give each its own empty destination area so they don’t write over each other as they grow.