PivotTables in twenty minutes: summarise a sales export

A sales export from your shop platform or accounting tool usually arrives as thousands of rows, one per order. Questions such as “which region sold most last quarter?” or “what share of revenue came from online?” are hard to answer by scrolling and slow to answer with formulas.

A PivotTable answers them in a few clicks. It groups the rows, adds them up, and lets you rearrange the summary by dragging fields around. This guide walks through one example, from raw export to a summary you could put in a report, in about twenty minutes.

Start with clean data (example is illustration only)

The steps below use an invented export from a small retailer that sells online and in store. Each row is one order:

Picture a few thousand rows like these covering July to September. Microsoft’s guidance is to use clean, tabular data, which in practice means:

• one header row, with a unique, non-blank label for every column;
• no merged cells and no double header rows;
• data in columns, with one record per row;
• dates stored as dates and numbers stored as numbers, not as text.

Then turn the range into an Excel table: click any cell in it and choose Insert, then Table. A table expands when you add rows to it, so a PivotTable built on it picks up new data when you refresh.

Create the PivotTable

1. Click any cell in the table.
2. Choose Insert, then PivotTable.
3. Confirm the table or range in the dialog box.
4. Choose New Worksheet, or Existing Worksheet and a location.
5. Select OK.

Excel opens an empty PivotTable and the PivotTable Fields pane. These steps follow Microsoft’s guide for Excel for Windows. Menu names can differ on a Mac or in Excel for the web, so check the guide for your version if something doesn’t match.

Rows, columns, values and filters

The Fields pane lists every column header from your data. Below the list are four areas, and where you drop a field decides what it does:

• Rows: each item becomes a row label down the left side.
• Columns: each item becomes a column heading across the top.
• Values: numbers here are summarised in the body of the table.
• Filters: fields here become report filters above the PivotTable, narrowing the whole report.

For the example, drag Region to Rows, Channel to Columns and Revenue to Values. You now have revenue by region, split into online and store. A result might look like this (illustration only):

Drag Category into Filters and a drop-down appears above the table, so you can show only Bags or only Accessories. Move fields between areas to try other views. Your source data stays exactly as it was.

Change how values are summarised

By default, numeric fields in Values show a Sum. If Excel reads a field as text, it shows a Count instead. Seeing “Count of Revenue” where you expected a sum usually means some revenue cells are stored as text.

To change the calculation, click the arrow beside the field in the Values area, choose Value Field Settings, and pick from the Summarize Values By list. For a sales export where each row is one order, three choices are especially useful:

• Sum of Revenue gives total sales.
• Count of any order field gives the number of orders.
• Average of Revenue gives average order value.

You can drag the same field into Values twice, once as a Sum and once as an Average, to see both side by side.

Show values as a percentage of the total

Raw totals show size. Percentages show share. Right-click a number in the Values area, choose Show Values As, then pick % of Grand Total. You can also reach the same options from the Show Values As tab in Value Field Settings.

In the example, the grand total column then reads North 35%, South 35% and West 30%. Choose % of Row Total instead and each channel is shown as a share of its own region. That view makes West stand out: £5,500 of its £6,000, about 92%, came from online orders, against 60% in the North and about 44% in the South.

The same menu also offers % of Column Total, Difference From and Running Total In, among others.

Group dates by month or quarter

An export with a different date on most rows produces a very long PivotTable if you put Order date in Rows. Grouping fixes that.

Right-click any date in the PivotTable and choose Group. In the Grouping box, check the Starting at and Ending at dates, choose a period such as months or quarters under By, and select OK. Excel can also detect and group time fields automatically when you add them. To undo a grouping, right-click an item in it and choose Ungroup.

With Order date grouped by month in Rows, Channel in Columns and Revenue in Values, you have a monthly online-versus-store trend for the quarter.

Filter with slicers, then refresh

A slicer is a panel of buttons that filters a PivotTable. It is easier for report readers to use than a drop-down, and it shows at a glance which filter is on. Click inside the PivotTable, choose Slicer on the Insert tab, and tick the fields you want, such as Region and Category.

Click a button to filter. Hold Ctrl to select more than one item, and use the Clear Filter button on the slicer to show everything again. One slicer can control several PivotTables that share the same data source: select the slicer, open the Slicer tab and choose Report Connections.

A PivotTable doesn’t update on its own when the source data changes. Right-click inside it and choose Refresh. If the workbook has several PivotTables, go to PivotTable Analyze, click the arrow under Refresh and choose Refresh All.

A simple monthly routine follows from this. Paste the new month’s rows at the bottom of the Excel table, then choose Refresh All. Because the PivotTable reads from the table, the new rows are included, and your layout, grouping and slicers stay as you left them.