Excel PivotTables Explained for Finance Professionals

A PivotTable is Excel's tool for summarising a long list of rows into a compact table without writing formulas for every total. Microsoft's support page describes PivotTables as a way to summarise and analyse data to show comparisons, patterns and trends. For finance professionals, that usually means turning a ledger or transaction export into totals by account, cost centre, month or customer. This guide explains the idea and the documented steps in plain terms, using Microsoft's "Create a PivotTable to analyze worksheet data" page. That page's metadata shows a content date of 01/23/2026 and an updated value of 2026-06-25.

Menus, button names and availability vary by Excel version and platform, so check Microsoft's current documentation for your own setup. If you are building towards a recognised qualification, our guide to the Microsoft Excel certification exam for Dublin professionals covers that route.

What a PivotTable does

You start with a list of rows, one per transaction or record. A PivotTable lets you choose which fields to use as row labels, which as column labels and which to total, and it builds a summary table from those choices. Because the summary is driven by the field choices rather than typed formulas, you can rearrange it by dragging fields instead of rewriting anything.

The Microsoft page says the feature applies to Excel for Microsoft 365 (including Mac), Excel 2024, Excel 2021, Excel 2019 and Excel 2016, and that behaviour varies by platform.

Get the source data right first

The page gives guidance on how the source should be laid out. It says your data should be organised in columns with a single header row. More specifically, it advises:

• Use clean, tabular data organised in columns.
• Give every column a header, using one header row with unique, non-blank labels.
• Avoid double rows of headers or merged cells.
• Format the data as an Excel table.
• For complex or nested data, use Power Query. The page gives unpivoting as an example.

Many finance exports fail these tests: report-style layouts with subtotal rows, merged title cells, or monthly columns across the page. Our suggestion: spend time fixing the layout of the source before you build the PivotTable. If you need to bring in missing attributes such as account names, see our guide to VLOOKUP for accountants; for reshaping complex or nested data, the Microsoft page points to Power Query.

Creating a PivotTable

The documented steps for Windows are:

• Select the cells that hold your source data.
• Go to Insert, then PivotTable.
• Confirm the table or range. You can optionally add the data to the Data Model.
• Choose whether to place the PivotTable on a new worksheet or an existing one.
• Select OK.

The page also notes that the dropdown arrow on the PivotTable button offers other sources: an external data source, the Data Model (described for multiple tables, custom measures or very large datasets), and Power BI datasets if your organisation uses Power BI and you have access.

Fields and the four-way layout

To add a field, you select its checkbox in the PivotTable Fields pane. The page describes default placement: non-numeric fields go to Rows, date and time hierarchies go to Columns, and numeric fields go to Values. To move a field, you drag it to another area. The page describes Rows, Columns and Values; it does not describe a Filters area, so we do not cover one here.

Illustrative example (made-up figures): imagine a list of 200 expense lines, each with a department, a month and an amount. Putting Department in Rows, Month in Columns and Amount in Values would give a grid of totals by department and month. Dragging Month into Rows instead, below Department, would give a list view. The data has not changed; only the layout has.

Sum or Count: the check that matters

By default, fields in Values are shown as a SUM. The page warns that if Excel reads the data in a field as text, it displays a COUNT instead, and advises avoiding mixing data types in value fields. In finance, this is a classic trap: amounts stored as text, perhaps after an export, give a count of rows when you expected a euro total. Our suggestion: whenever a PivotTable total looks strangely small or round, check whether the heading reads "Count of" rather than "Sum of".

To change the calculation, the page says to open Value Field Settings and use Summarize Values By. Changing the calculation also changes the field name, such as "Sum of" followed by the field name, so the page advises finishing setup before renaming fields.

The page also describes Show Values As, which displays values as a percentage such as "% of Grand Total". To show both a calculation and a percentage, you add the field to Values twice and set each one.

Refreshing: PivotTables do not update by themselves

The page says to refresh any PivotTable built on a source that has new data. For one PivotTable, right-click in its range and select Refresh. For several, select a cell in any PivotTable, go to PivotTable Analyze and choose Refresh All from the Refresh dropdown. In a monthly close, the sequence that follows from this is: update the source, refresh, then check totals against a control figure. That sequence is our suggestion rather than a Microsoft requirement.

AI features and data privacy

The page mentions PivotTable Recommendations as part of the connected experience in Microsoft 365, which uses AI services to analyse your data, and says that if you opt out of the connected experience your data is not sent to those services and you cannot use the feature. It also says that, with the required licence, you can ask Copilot to help create a PivotTable. If your data includes personal or confidential information, check your organisation's policy before using cloud-connected features. The page does not give further detail on how data is handled.

From PivotTable to report

A PivotTable is a working tool, not necessarily a finished report. Many teams use it as the engine and then present results in a more formal layout. Our guide to financial dashboards in Excel for executive reporting picks up from there.

Learnsignal's offices are off Baggot Street in Dublin, and we offer in-person training from there. For current details, see our CPD pages.

Frequently asked questions

How should my data be laid out for a PivotTable?

Microsoft's page says in columns with a single header row, unique non-blank headers, no double header rows or merged cells, and ideally formatted as an Excel table.

Why does my PivotTable show Count instead of Sum?

The page says Values fields default to SUM, but if Excel reads the data as text it shows COUNT. Check that your amounts are stored as numbers.

Does a PivotTable update when the source changes?

Not automatically, according to the page, which says to refresh a PivotTable built on a source with new data, using Refresh or Refresh All.

Which Excel versions does the page cover?

Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016, with behaviour varying by platform.

If the source is clean and you check Sum versus Count and refresh after every update, a PivotTable can turn a raw export into a summary in minutes.