LearnLive

Excel reporting guide

Excel PivotTables for Fast Reporting

Turn transaction rows into a useful PivotTable, choose the right fields, group dates, show percentages, and avoid misleading totals.

How this page is maintained

Written for learners, checked against the sources below, and reviewed every year. Last reviewed July 22, 2026.

Short answer

Use a PivotTable when you need to summarize many rows by categories such as region, product, or month. Start with clean tabular source data, put dimensions in Rows or Columns, put numeric measures in Values, and inspect whether Excel chose Sum, Count, or Average.

  • A PivotTable is only as trustworthy as its source range and field types.
  • Check the Value Field Settings instead of accepting Excel's default summary.
  • Refresh after source data changes and use an Excel Table so new rows are included.

Place fields by the role they play

Rows and Columns define groups. Values perform calculations. Filters restrict the report without changing the underlying records. A simple sales report might place Region in Rows, Month in Columns, and Revenue in Values.

When a numeric field appears as Count instead of Sum, Excel may have found text or blanks in the source column. Fix the source type rather than forcing a calculation that hides the inconsistency.

Make the result answer one question

A compact PivotTable is easier to read than a report with every available field. Add a field only when it helps answer the stated question. Use Show Values As for share of total, running total, or difference from a comparison period.

  • Group real dates into months or quarters.
  • Use distinct labels and number formats that match the measure.
  • Double-click a total to inspect the records behind it when a number looks wrong.

Compare monthly revenue by region

A Sales table has one row per order with Order date, Region, and Revenue.

  1. Insert a PivotTable from the Sales table.
  2. Put Region in Rows, Order date in Columns, and Revenue in Values.
  3. Group Order date by Months and Years, then verify that Revenue uses Sum.
Result: The report shows monthly regional totals and can be refreshed when new orders are added.

Common mistakes

  • Using a source range that excludes new records.
  • Leaving dates stored as text and being unable to group them.
  • Reporting Count of Revenue when the intended measure is Sum of Revenue.
  • Adding many row fields until the report is harder to read than the source data.

Try one

Where should a field go if it defines the groups down the left side of a PivotTable?

The Rows area. Measures to calculate belong in Values.

Sources

Excel data quality guideData cleaningExcel visual analysis guideConditional formattingExcel chart guideCharts for KPIs

Learn this with a tutor

Tell LearnLive what you already know and what you need to do with pivottables.

Build this course