How to make a pivot table in Excel from sales data

A pivot table is Excel's tool for turning a list of rows into totals by the fields you choose, with no formulas. This guide builds one from a ten-line sales ledger: sales by region and month, each region's share of the total, a slicer by product line, and the check that proves the pivot ties to the ledger.

What a pivot table is

A pivot table reads a list with one record per row and adds up a number column by the categories in other columns. The source is never changed; the pivot is a separate report built on top of it. You lay it out by dragging column names into four areas:

Prepare the ledger

The sample is ten invoice lines for Q1 2026, in USD, including one credit memo:

Before you build anything, the data has to meet four conditions, which Microsoft's PivotTable guide also lists:

• One header row, every column named, no two names the same.
• One row per invoice line. No blank rows, and no subtotal or total rows carried over from the export.
• Dates as real dates. =ISNUMBER(A2) returns TRUE for a date and FALSE for text that only looks like one.
• Amounts as numbers, with the credit memo as a negative.

Then click any cell in the data, press Ctrl+T (Insert > Table), confirm that the table has headers, and on the Table Design tab set Table Name to Ledger. A Table grows when rows are added, so a pivot built on it picks up next month's lines when you refresh. If the export arrives with title rows and text amounts, clean it first with Power Query, which records the steps so you can rerun them.

Create the pivot table

1. Click any cell in the Ledger table.
2. Choose Insert > PivotTable. In Microsoft 365 the button opens a short menu; choose From Table/Range. The Table/Range box shows Ledger.
3. Choose New Worksheet, then OK.

Excel adds a sheet with an empty pivot starting at A3 and opens the PivotTable Fields pane: the five column names at the top and the four areas below. Rename the sheet Pivot; the check formula later refers to it. Excel for Mac and Excel for the web have the same Insert > PivotTable command, with a slightly different pane.

Sales by region and month

1. Drag Region to Rows.
2. Drag Date to Columns. Recent versions of Excel for Windows may group dates automatically as you drop them. If you see one column per day instead, right-click any date in the column headers, choose Group, select Months and click OK. Select Years as well when the data spans more than one year, or January 2026 and January 2027 land in the same column. Microsoft's grouping page covers the Grouping dialog.
3. Drag Net amount to Values. It appears as Sum of Net amount.

The result:

Read one cell to see where it comes from. Northeast in March is $9,900 from Brightwell Inc. less the $1,200 credit memo for Harlow Foods: $8,700. The credit memo belongs there; credit notes and returns in revenue measures explains why a sales total is net of them.

Empty cells mean no invoice in that month. To show 0 instead, right-click the pivot, choose PivotTable Options, and on the Layout & Format tab type 0 in "For empty cells show". The default layout, Compact Form, labels the headers "Row Labels" and "Column Labels". Design > Report Layout > Show in Tabular Form puts the field names there instead, which reads better when the table is copied into a report.

Show values as % of grand total

Right-click any number in the pivot and choose Show Values As > % of Grand Total. (The same setting is on the Show Values As tab of Value Field Settings.) Every cell becomes a share of $56,260:

Northeast is 37,060 / 56,260 = 65.9%. Inside the grid, Northeast in January reads 37.6%. For each month's split between regions, choose % of Column Total instead. The sums underneath are unchanged; only the display is. To see dollars and shares side by side, drag Net amount into Values a second time and apply the percentage to that copy only. Set the display back to No Calculation before the check below.

Filter with slicers

Click in the pivot and choose PivotTable Analyze > Insert Slicer (the tab is called Analyze in Excel 2016 and 2019). Tick Product line and click OK. Click Chilled in the slicer:

West drops out because its one line is Ambient. Northeast is $12,400 + $9,900 − $1,200 = $21,100. Click the clear-filter icon on the slicer to return to all lines. For dates, PivotTable Analyze > Insert Timeline adds a sliding filter by month, quarter or year.

The check: the pivot total equals the ledger total

A pivot that does not tie to its source is not ready to show anyone. In a cell beside the pivot:

=SUM(Ledger[Net amount])

It returns 56,260, the same as the pivot's grand total. Turn that into a formula that must be zero:

=GETPIVOTDATA("Net amount",Pivot!$A$3)-SUM(Ledger[Net amount])

With the Chilled slicer still on, the difference is −25,210: the five Ambient lines, filtered out. That is the check doing its job, so clear the slicers before you read it. It is a control total: a figure taken from the source and compared with the report.

For a single figure from the pivot, do not point at a cell such as =B5, which returns whatever sits in B5 after the layout changes. GETPIVOTDATA looks the value up by field and item instead:

=GETPIVOTDATA("Net amount",Pivot!$A$3,"Region","Northeast")

It returns 37,060 wherever the Northeast row moves. Excel writes these formulas for you when you type = and click a cell in the pivot; the setting is PivotTable Analyze > Options > Generate GetPivotData.

Where it goes wrong

Not refreshing. A pivot does not update when the data changes. Right-click it and choose Refresh, or Data > Refresh All. To refresh every time the file opens, tick "Refresh data when opening the file" on the Data tab of PivotTable Options.

A fixed source range. A pivot built on A1:E11 stops at row 11 forever, and next month's lines are left out without warning. Build it on an Excel Table.

Dates stored as text. Text cannot be grouped, so Group is grayed out or Excel says it cannot group that selection. Convert the column to real dates, then refresh.

Count instead of Sum. One blank or text value in Net amount makes Excel summarize the field as Count, so the pivot shows a number of lines, not dollars. Fix the cell, then set Value Field Settings back to Sum.

Credit memos filtered out, or subtotal rows left in. Leave out the credit memo and Northeast reads $38,260 and the total $57,460. Leave an export's subtotal rows in and every subtotal is counted a second time. The check above catches a filter in the pivot, but not subtotal rows in the source, because both sides of it include them; compare the ledger total with the total printed on the export as well.

Beyond the pivot table

A pivot table answers "how much, by what". It does not answer why a number moved. It shows Northeast falling from $21,160 in January to $8,700 in March; it does not say which customers bought less, which stopped buying altogether, or which lines were credited. Insert > PivotChart draws the same table as a chart, and ticking "Add this data to the Data Model" when you create the pivot lets one pivot combine related tables. For the same grid built with formulas that sit inside a report, see SUMIF and SUMIFS in Excel; for the wider set of sales measures, see sales analytics in Excel.

Covirage produces the same region-by-month table from the uploaded ledger and then the bridge behind each change, with deterministic tools computing every figure and checking the total against the ledger; the external AI model writes the explanation and never does the arithmetic. A pivot shows that Northeast fell in March. Upload the ledger, ask why, and get the customers and lines behind it, with no pivot to build: see Excel analysis without the formulas.

title: "How to make a pivot table in Excel from sales data" description: "A step-by-step guide to building a pivot table from an invoice-level sales ledger: preparing the data as an Excel Table, sales by region and month, each region's share of the total, slicers by product line, and the check that the pivot ties to the ledger. Every figure comes from a ten-line sample ledger you can rebuild." seoTitle: "Pivot table in Excel: build one from sales data" metaDescription: "A pivot table summarizes rows into totals by any field you drag in. Build one from sales data: sales by region and month, % of total, the check." date: 2026-09-30 category: guides solution: revenue-driver-analysis keywords: ["pivot table", "creating a pivot table in excel", "create pivot table", "pivot table example", "define pivot table", "pivot table percentage of total", "group dates in pivot table"] answer: "A pivot table summarizes a list of rows into totals by the fields you choose, without formulas. To create one, format the data as a Table, select a cell in it, choose Insert > PivotTable, then drag fields into Rows, Columns and Values. From invoice-level sales data that gives sales by region and month in about a minute. Refresh it when the data changes." faq:

• q: "What is a pivot table used for?" a: "Summarizing a long list of rows, such as invoice lines, into totals by any combination of fields: sales by region and month, margin by customer, count of orders by product. It answers 'how much, by what' without writing formulas."
• q: "Why is my pivot table not updating?" a: "Pivot tables do not recalculate automatically. Right-click and choose Refresh, or Data > Refresh All. If new rows are still missing, the source is a fixed range; change it to an Excel Table so it grows with the data."
• q: "How do I group dates by month in a pivot table?" a: "Right-click a date in the pivot, choose Group, and select Months (and Years if the data spans more than one year). If Group is grayed out, some date cells are text or blank."
• q: "How do I show percentage of total in a pivot table?" a: "Right-click a value, choose Show Values As, then % of Grand Total, % of Column Total or % of Row Total. The underlying sums are unchanged; only the display changes."

A pivot table is Excel's tool for turning a list of rows into totals by the fields you choose, with no formulas. This guide builds one from a ten-line sales ledger: sales by region and month, each region's share of the total, a slicer by product line, and the check that proves the pivot ties to the ledger.

What a pivot table is

A pivot table reads a list with one record per row and adds up a number column by the categories in other columns. The source is never changed; the pivot is a separate report built on top of it. You lay it out by dragging column names into four areas:

Prepare the ledger

The sample is ten invoice lines for Q1 2026, in USD, including one credit memo:

Before you build anything, the data has to meet four conditions, which Microsoft's PivotTable guide also lists:

• One header row, every column named, no two names the same.
• One row per invoice line. No blank rows, and no subtotal or total rows carried over from the export.
• Dates as real dates. =ISNUMBER(A2) returns TRUE for a date and FALSE for text that only looks like one.
• Amounts as numbers, with the credit memo as a negative.

Then click any cell in the data, press Ctrl+T (Insert > Table), confirm that the table has headers, and on the Table Design tab set Table Name to Ledger. A Table grows when rows are added, so a pivot built on it picks up next month's lines when you refresh. If the export arrives with title rows and text amounts, clean it first with Power Query, which records the steps so you can rerun them.

Create the pivot table

1. Click any cell in the Ledger table.
2. Choose Insert > PivotTable. In Microsoft 365 the button opens a short menu; choose From Table/Range. The Table/Range box shows Ledger.
3. Choose New Worksheet, then OK.

Excel adds a sheet with an empty pivot starting at A3 and opens the PivotTable Fields pane: the five column names at the top and the four areas below. Rename the sheet Pivot; the check formula later refers to it. Excel for Mac and Excel for the web have the same Insert > PivotTable command, with a slightly different pane.

Sales by region and month

1. Drag Region to Rows.
2. Drag Date to Columns. Recent versions of Excel for Windows may group dates automatically as you drop them. If you see one column per day instead, right-click any date in the column headers, choose Group, select Months and click OK. Select Years as well when the data spans more than one year, or January 2026 and January 2027 land in the same column. Microsoft's grouping page covers the Grouping dialog.
3. Drag Net amount to Values. It appears as Sum of Net amount.

The result:

Read one cell to see where it comes from. Northeast in March is $9,900 from Brightwell Inc. less the $1,200 credit memo for Harlow Foods: $8,700. The credit memo belongs there; credit notes and returns in revenue measures explains why a sales total is net of them.

Empty cells mean no invoice in that month. To show 0 instead, right-click the pivot, choose PivotTable Options, and on the Layout & Format tab type 0 in "For empty cells show". The default layout, Compact Form, labels the headers "Row Labels" and "Column Labels". Design > Report Layout > Show in Tabular Form puts the field names there instead, which reads better when the table is copied into a report.

Show values as % of grand total

Right-click any number in the pivot and choose Show Values As > % of Grand Total. (The same setting is on the Show Values As tab of Value Field Settings.) Every cell becomes a share of $56,260:

Northeast is 37,060 / 56,260 = 65.9%. Inside the grid, Northeast in January reads 37.6%. For each month's split between regions, choose % of Column Total instead. The sums underneath are unchanged; only the display is. To see dollars and shares side by side, drag Net amount into Values a second time and apply the percentage to that copy only. Set the display back to No Calculation before the check below.

Filter with slicers

Click in the pivot and choose PivotTable Analyze > Insert Slicer (the tab is called Analyze in Excel 2016 and 2019). Tick Product line and click OK. Click Chilled in the slicer:

West drops out because its one line is Ambient. Northeast is $12,400 + $9,900 − $1,200 = $21,100. Click the clear-filter icon on the slicer to return to all lines. For dates, PivotTable Analyze > Insert Timeline adds a sliding filter by month, quarter or year.

The check: the pivot total equals the ledger total

A pivot that does not tie to its source is not ready to show anyone. In a cell beside the pivot:

=SUM(Ledger[Net amount])

It returns 56,260, the same as the pivot's grand total. Turn that into a formula that must be zero:

=GETPIVOTDATA("Net amount",Pivot!$A$3)-SUM(Ledger[Net amount])

With the Chilled slicer still on, the difference is −25,210: the five Ambient lines, filtered out. That is the check doing its job, so clear the slicers before you read it. It is a control total: a figure taken from the source and compared with the report.

For a single figure from the pivot, do not point at a cell such as =B5, which returns whatever sits in B5 after the layout changes. GETPIVOTDATA looks the value up by field and item instead:

=GETPIVOTDATA("Net amount",Pivot!$A$3,"Region","Northeast")

It returns 37,060 wherever the Northeast row moves. Excel writes these formulas for you when you type = and click a cell in the pivot; the setting is PivotTable Analyze > Options > Generate GetPivotData.

Where it goes wrong

Not refreshing. A pivot does not update when the data changes. Right-click it and choose Refresh, or Data > Refresh All. To refresh every time the file opens, tick "Refresh data when opening the file" on the Data tab of PivotTable Options.

A fixed source range. A pivot built on A1:E11 stops at row 11 forever, and next month's lines are left out without warning. Build it on an Excel Table.

Dates stored as text. Text cannot be grouped, so Group is grayed out or Excel says it cannot group that selection. Convert the column to real dates, then refresh.

Count instead of Sum. One blank or text value in Net amount makes Excel summarize the field as Count, so the pivot shows a number of lines, not dollars. Fix the cell, then set Value Field Settings back to Sum.

Credit memos filtered out, or subtotal rows left in. Leave out the credit memo and Northeast reads $38,260 and the total $57,460. Leave an export's subtotal rows in and every subtotal is counted a second time. The check above catches a filter in the pivot, but not subtotal rows in the source, because both sides of it include them; compare the ledger total with the total printed on the export as well.

Beyond the pivot table

A pivot table answers "how much, by what". It does not answer why a number moved. It shows Northeast falling from $21,160 in January to $8,700 in March; it does not say which customers bought less, which stopped buying altogether, or which lines were credited. Insert > PivotChart draws the same table as a chart, and ticking "Add this data to the Data Model" when you create the pivot lets one pivot combine related tables. For the same grid built with formulas that sit inside a report, see SUMIF and SUMIFS in Excel; for the wider set of sales measures, see sales analytics in Excel.

Covirage produces the same region-by-month table from the uploaded ledger and then the bridge behind each change, with deterministic tools computing every figure and checking the total against the ledger; the external AI model writes the explanation and never does the arithmetic. A pivot shows that Northeast fell in March. Upload the ledger, ask why, and get the customers and lines behind it, with no pivot to build: see Excel analysis without the formulas. To lay the pivot out with charts and tiles on one page, see how to build an Excel dashboard.