PivotTables in Excel: from sales lines to a useful report

A PivotTable lets you answer a question about data without writing a separate formula for every group. From a sales list, I can obtain revenue by region, month or product. Moving fields between areas gives me several views of the same underlying records.

The important decisions come before that first drag-and-drop action. What does one row represent? Is revenue numeric? Does the source include every relevant record? I will work through four synthetic sales lines, keeping the arithmetic small enough to follow on paper.

What a PivotTable does

A PivotTable groups records and calculates summaries. You choose the fields that define the groups and the values to sum, count or average. This changes the report’s presentation without editing the underlying source records.

The Microsoft introduction shows how to create a PivotTable from a range or an Excel table. Basic PivotTables are available on desktop and in Excel for the web, although commands and capabilities vary between platforms. This guide uses one ordinary source, without a Data Model or external connections.

I start with a sentence: “I want revenue by region and month.” That tells me which fields belong in the report. Beginning with random clicking makes it easy to build an attractive summary whose purpose becomes difficult to explain.

Four sales lines as a source

Enter these synthetic records in A1:F5. Row 1 contains headings. Quantity and revenue must be numbers, while Date should contain actual Excel dates.

For readability, the six columns appear in two parts. Enter the first in A1:C5 and the second in D1:F5, preserving the same four-record order. They form one A1:F5 table in the worksheet.

Name A1 “Order,” B1 “Date,” C1 “Region,” D1 “Product,” E1 “Units” and F1 “Revenue.”

Each row represents an order line. Order Z-3 contains two lines. That distinction will matter when we count records and discuss averages. There are four lines, but only three different order identifiers.

Total revenue is 100 + 200 + 50 + 150, which equals 500. Total units are 2 + 4 + 1 + 3, which equals 10. I record these totals before building the report so that I have simple reference values for checking its results.

Prepare the source first

Use one header row, one column for each field and consistent data types. Avoid merged headings and manually inserted subtotal rows. A “Total” row inside the source can be treated as another sale and cause double counting.

Blank rows inside a list make range detection less reliable. A blank region creates a group for missing region values. I do not delete such records without investigating. The missing label might represent an unassigned region, an export problem or a genuinely unknown location.

Select a source cell and use Insert > Table, confirming that the data has headers. You can name the table “Sales.” An Excel table helps the source expand as rows are added, but the PivotTable still needs to reflect those changes through refresh. Converting a range to a table also does not convert numeric-looking text into real numbers.

Create the first PivotTable

Click inside the source and choose Insert > PivotTable. Check the selected table or range. For the first exercise, place the report on a new worksheet. This avoids covering the source and leaves space for a larger summary.

After confirming, you should see the report area and field list. If the field list is hidden, select a cell inside the PivotTable and enable it from the PivotTable tools. The precise interface depends on your Excel version, but the same four field areas are involved.

Move Region to Rows and Revenue to Values. Expect North to show 300, South 200 and the grand total 500. North contains three lines with revenue 100, 50 and 150. South contains one line with revenue 200.

If the numbers are 3 and 1 instead, inspect the aggregation. Excel is probably counting nonempty values rather than adding revenue. Changing the visible heading will not repair an incorrect data type or an inappropriate summary function.

Rows, columns, values and filters

Rows defines the groups shown vertically. Columns adds a second grouping dimension. Values contains calculations such as revenue sums. Filters restricts the records used in the report without making that field another displayed row or column dimension.

Keep Region in Rows and move Product into Columns. Revenue should remain summed in Values. North will show 150 for notebooks and 150 for folders. South will show 200 for notebooks, with no folder line in the source.

The Notebook column totals 350, Folder totals 150, and the overall total remains 500. The underlying records have simply been grouped differently. A blank cell for South and Folder means there are no such lines in this source. It does not establish that the region never sells folders.

You can leave Order outside the report for now. There is no requirement to use every source column. Field selection should follow the question. Adding dimensions without a purpose usually makes the answer harder to read.

Sum, count and average answer different questions

Value Field Settings lets you choose the summary function. For revenue, I normally begin with Sum. Count describes nonempty entries, while Average divides the total of included numeric values by their count.

In this example, average revenue per line is 500 / 4, which equals 125. North averages 300 / 3, or 100. South averages 200 / 1, or 200. Averaging those two regional averages would produce 150, which does not recover the average across all four lines. The regions have different record counts.

I do not call 125 “average order value.” Z-3 appears twice. Calculating an order-level average requires accounting for three different orders rather than four lines. An ordinary Count of the identifier counts occurrences. It does not automatically become a count of unique identifiers.

Microsoft explains the summary functions available for PivotTable values. Distinct Count requires a PivotTable using the Data Model. I am not enabling it here because the exercise concerns an ordinary source. The essential point is that a metric’s label must describe what the report actually calculated.

Why Excel selects Count instead of Sum

When an amount field contains text, Excel may default to Count. A value displayed as 100 can actually be text from an export, use an unsuitable separator or contain additional characters. Applying a numeric display format does not guarantee that the underlying value has been converted.

I correct the source first. I inspect several values, the import method and decimal or thousands separators. Then I refresh the PivotTable and choose the intended summary function. Forcing Sum without repairing text values can leave part of the revenue out of the result.

I also avoid entering a unit as part of the text in the amount cell. Revenue stays numeric, while presentation is handled through the value field’s number format. This keeps the field suitable for arithmetic. The unit belongs in the report heading or its formatting.

Analyze months and years

Remove Product from Columns and put Date there instead. Dates can be grouped into months and years. Depending on settings, Excel may create groups automatically or let you select them with Group. The Microsoft grouping guide explains the operation.

January totals 300 and February totals 200. In a region-by-month view, North has 100 in January and 200 in February. South has 200 in January. With the same four records and no changed filters, every view should still total 500.

When a source covers several years, include the year as well. Grouping only by month can combine January from different years. That may be useful for seasonality, but it answers a different question from monthly revenue within a particular year.

If grouping fails, inspect the source for text dates and missing values. A date display format cannot turn arbitrary text into a valid date. Establish a consistent field before relying on a convenient date hierarchy in the report.

Percentages need a clear denominator

Drag Revenue into Values a second time. Leave the first field as Sum and set the second to Show Values As > % of Grand Total. In the region-only view, North contributes 300 / 500, or 60%, and South contributes 40%.

Revenue of 300 and a share of 60% describe the same group in different ways. Label both columns so that the reader understands what the percentage refers to. In a larger table, percentages of row totals and column totals can answer very different questions.

After filtering, inspect the denominator again. The share may refer to the selected subset rather than the entire source. Filtering to North means the report no longer compares both regions. For every percentage, I want to identify its numerator, denominator and active data scope.

The Show Values As documentation distinguishes percentages of grand, row and column totals. Choosing among them is an analytical decision. The formatting symbol alone does not tell the reader which comparison was intended.

Refresh and newly added records

Add a March line with revenue 100. If the source is an Excel table and the new record belongs to that table, the PivotTable should include it after refresh. The total rises from 500 to 600.

If the source is a fixed A1:F5 range, entering row 6 outside that range does not automatically expand its definition. Inspect and, if needed, change the source before refreshing. Repeatedly pressing Refresh cannot include a record that does not belong to the selected source.

Check both ends of the range. A report can omit trailing records or accidentally include an old control total. The details of refreshing PivotTable data depend on the version and connection. For this exercise, a manual refresh followed by comparison with the expected total is sufficient.

Newer versions can support automatic refresh for local workbook data. That is still a setting to understand, rather than an assumption to make about every file. When reviewing somebody else’s workbook, inspect how and when its displayed data was last updated.

Filters, slicers and a chart

A Product filter restricts the report to the selected product. Notebooks should total 350 and folders 150. A slicer can expose the selection as buttons. Its purpose is to make interaction clearer, but the chosen values still need to remain visible.

A PivotChart can help compare regions or months. I would start with simple columns. When filters change, the chart represents the current view of the PivotTable, so the title should describe the metric and period. An attractive chart with a hidden filter can mislead its audience.

I do not put every product, region and month on one chart merely because Excel allows it. I choose a question first, then the layout. If an existing report only needs one conditional total, SUMIFS may be sufficient without another PivotTable.

Read totals according to the selected calculation

A grand total for an average is calculated from the underlying records. It should not be interpreted as the sum of the regional averages displayed above it. In our example, the regional averages are 100 and 200, while the overall average is 125. That apparent mismatch is expected.

The same care applies when adding subtotals beneath several row fields. A region subtotal already includes its products. Adding the visible product amounts and then adding the region subtotal again counts the same records twice. Read the hierarchy before copying displayed values into another calculation.

If I move results into a presentation, I preserve the field definitions and filter context alongside the numbers. A copied table no longer gives its reader access to the interactive controls that explained its scope inside Excel. A short note about the period, unit and selected categories makes the exported view easier to interpret correctly.

Investigate an unexpected total

If you expect 500 and see another amount, return to the simplest view: Revenue in Values without additional grouping. Inspect active filters, the source definition and the summary function. Changing one thing at a time makes it easier to identify the cause.

In this example, 350 could mean that Notebook is selected. A result of 300 could represent North alone or January alone. Four could be a count of lines. Each number can result from a valid operation on the same source. Whether it belongs in your report depends on the intended definition.

If the total remains 500 after adding another 100, inspect both source membership and refresh. These are separate conditions. A new record can already belong to the source while the displayed summary has not refreshed. Alternatively, it can sit outside the range that the report references.

For a system export, compare the report scope with the export definition. Are the periods, document statuses and regions the same? Excel cannot know that a file contains only approved orders or excludes the last day of a month. The PivotTable summarizes the records it received without evaluating the completeness of the upstream process.

I keep an independent source total and a short definition of which records belong in the report. Comparison is meaningful when both sides measure the same thing. Net versus gross revenue, or order date versus fulfillment date, can explain a discrepancy that formatting options will never fix.

Exercise and handover review

Build three views: revenue by region, by product and by month. Expected regional totals are 300 and 200. Product totals are 350 and 150. January and February total 300 and 200. Each view of the original four lines should have a grand total of 500.

Then change Sum to Average and describe what 125 means. Calling it average revenue per line matches the source grain. Return to the Order column and explain why four occurrences do not mean four different orders.

Before sharing the report, I inspect the source definition, aggregation method, active filters and refresh state. I add the period and unit. When these are explicit, the PivotTable becomes a view that another person can understand and reproduce without guessing how the numbers were obtained.