T TestsWorld
← All topic guides

Pivot tables: formulas, shortcuts & solved examples

What it is

A pivot table summarises a large list of records into a compact report without writing formulas. You drag fields into Rows, Columns, Values and Filters, and Excel groups and totals the data. It is one of the fastest ways to answer questions like "total sales by region" or "count of tickets by agent".

Key rules

The four areas

  • Rows: categories listed down the left (for example, Region).
  • Columns: categories listed across the top (for example, Month).
  • Values: the numbers being summarised (Sum of Sales, Count of Orders).
  • Filters: a field used to filter the whole report (for example, Year).

Source data requirements

  • One header row with a unique name for every column
  • No blank rows or merged cells in the data
  • Each row is one record; each column is one field
  • Convert the data to a Table (Ctrl + T) so new rows are included on refresh

Summary options

  • Number fields default to Sum; text fields default to Count.
  • Change via Value Field Settings to Average, Max, Min or Count.
  • Show Values As gives % of Grand Total, % of Column Total, Difference From and Running Total.

Calculated fields and slicers

  • A calculated field adds a new value built from existing fields, for example Profit = Sales − Cost, directly inside the pivot.
  • A slicer is a set of clickable buttons that filters the pivot visually; one slicer can control several pivots built from the same data.
  • A pivot chart draws a chart that changes whenever the pivot is filtered or rearranged.

Layout options

Report Layout lets you choose Compact, Outline or Tabular form. Tabular form repeats labels in separate columns, which is easier to copy into other sheets. Subtotals and grand totals can be switched on or off from the Design tab, and the field list can be reopened by clicking inside the pivot if it has been closed.

Refresh

A pivot table does not update automatically when source data changes. Use Refresh (Alt + F5) or Refresh All (Ctrl + Alt + F5).

Shortcuts

  1. Alt, N, V opens the Insert PivotTable dialog from the keyboard.
  2. Double-click any value cell to open a new sheet listing the records behind that number, useful for checking a total.
  3. Group dates by right-clicking a date in Rows and choosing Group, then Months, Quarters or Years, instead of creating helper columns.

Solved examples

Example 1: Total sales by region

Data:

Region Salesperson Sales (₹)
North Anil 12,000
North Meena 8,500
South Ravi 15,000
North Anil 4,500

Drag Region to Rows and Sales to Values.

Region Sum of Sales (₹)
North 25,000
South 15,000
Grand Total 40,000

North = 12,000 + 8,500 + 4,500 = ₹25,000.

Example 2: Percentage of total

Using the same pivot, set Show Values As to % of Grand Total.

  • North = 25,000 ÷ 40,000 = 62.5%
  • South = 15,000 ÷ 40,000 = 37.5%

Example 3: Count instead of sum

A manager wants the number of sales entries per salesperson. Drag Salesperson to Rows and Salesperson (or any field) to Values, set to Count.

  • Anil: 2, Meena: 1, Ravi: 1, Grand Total: 4

Count tells you how many records exist, not their value.

Example 4: Two-dimensional report with a filter

A dataset has Date, Branch, Product and Amount for a whole year. The finance team wants monthly amounts per branch for one product.

  1. Insert the pivot from the table.
  2. Branch to Rows, Date to Columns, then group Date by Months.
  3. Amount to Values (Sum).
  4. Product to Filters, then select the required product.

The result is a branch-by-month grid with row and column totals.

Common mistakes

  1. Forgetting to refresh after editing the source data, so the report shows old numbers.
  2. Blank cells in number columns, which can make Excel default to Count instead of Sum.
  3. Numbers stored as text, which are counted but not added.
  4. Selecting a fixed range like A1:D500 and then adding rows beyond it. Use a Table so the range grows.
  5. Typing over pivot values to "fix" a total. Pivot cells are calculated; correct the source data instead.

Practice now: https://www.testsworld.com/mocks

Ready to practise Pivot tables?
Take a free mock test

Related guides