Home · Blog · Data
Article · Data

Pivot tables and slicers from a messy export

17 NOV 2026 · 7 MIN READ · OPPERMIND PTY LTD
TL;DR. Write the question first, then check the export has a column for every noun in it. A pivot needs one header row, one record per row, the same spelling for the same thing, and amounts it can read as numbers. Oppermind’s pivot groups by exact value, so add a Month column before you build. Build it from a fixed range with room to grow, filter it with slicers, and compare the Grand Total with the export’s own total every time new data goes in.

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.

Write the question as columns

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.

Check the shape the pivot will read

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 doesWhat the pivot does with itWhat to change
Report title and run date in rows 1 to 3Treats row 1 of your range as headers, so “Sales report” becomes a fieldStart the source range at the real header row, or delete the title rows
A column with no headerLeaves it out of the field listGive it a name
Two columns both called “Amount”Keeps only one of them as a fieldRename one, for example “Amount ex GST”
A subtotal row after each weekAdds it in again as if it were a saleDelete the subtotal rows
Blank spacer rowsShows a “(blank)” line in the pivotDelete them
“Bris” in some rows, “Brisbane” in othersTwo separate linesUse one spelling throughout
Amounts like “$1,250.00”Reads them as numbersNothing
Amounts like “A$1,250”, “AUD 1250” or “(85.00)” for a refundSkips them in sums, with no warningMake 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.

Give it a Month to group by

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.

Build the pivot

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.

Filter it with slicers

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.

When next month’s export lands

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.

Before you send it on

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.

Ask the question. Check the total.

Build a pivot and slicers from your next export in a workspace with documents, sheets and decks.

Frequently asked questions

Can the pivot group dates into months or quarters on its own?

No. It groups by exact value, so add a Month or Quarter column to the data first.

Why is there a “(blank)” line in my pivot?

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.

My pivot total is lower than the export’s total.

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.

Can I have two pivots from the same export?

Yes. Give each its own empty destination area so they don’t write over each other as they grow.

Related reading

Sources

  1. Hadley Wickham, “Tidy Data”, Journal of Statistical Software, Vol. 59, Issue 10, published 12 September 2014. jstatsoft.org (each variable a column, each observation a row). Accessed 28 October 2026.
  2. Government Analysis Function (UK), “Releasing statistics in spreadsheets”, published 30 June 2021 (no later update date visible in the fetched text). analysisfunction.civilservice.gov.uk (one header row, every column headed, unique headers, no merged cells, no blank rows inside tables, totals at the edge). Accessed 28 October 2026.