Skip to content
Reading settings
ExcelIntermediate~35 min practiceLesson 20 of 34

Summarize sales with a PivotTable

Compare regional sales and break the totals down by month.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Create a regional PivotTable, format and sort its totals, then add a monthly breakdown.

Practice material

Start with the practice file. Open the worked answer after trying the exercise. CSV and text briefs are also available. All resources and import notes →

Example

Quarterly sales by region and month

The sales lead needs a regional summary and a monthly breakdown of eight sales records. A PivotTable lets you rearrange the same data for both questions and refresh the report when another sale is added.

Team sales Q1.xlsx — Sheet2
Source data (A1:E9)
MonthRegionRepProductSales
JanWestMayaLaptops1200
JanEastJordanMonitors950
JanWestPriyaMonitors1800
FebNorthSamLaptops700
FebWestLeoLaptops1500
FebEastAnaLaptops1100
MarNorthSamMonitors900
MarEastJordanLaptops1300
PivotTable
Row LabelsSum of Sales
West4,500
East3,350
North1,600
Grand Total9,450
Teaching diagram, not a Microsoft screenshot.

How it works

Rows and Columns group the source records; Values calculates a result for each group. Changing the PivotTable layout leaves the transactions in the source unchanged.

A PivotTable needs refreshing after its source changes. Using a table helps the source range grow, then Refresh brings the new records into the summary.

Steps

  1. Use sales-csv or enter these records: Jan West Maya Laptops 1200; Jan East Jordan Monitors 950; Jan West Priya Monitors 1800; Feb North Sam Laptops 700; Feb West Leo Laptops 1500; Feb East Ana Laptops 1100; Mar North Sam Monitors 900; Mar East Jordan Laptops 1300.

  2. Place the headers Month, Region, Rep, Product and Sales in A1:E1 and the records in rows 2–9. Press Ctrl+T on Windows or choose Insert > Table and confirm the headers.

  3. Select a table cell and choose Insert > PivotTable > From Table/Range. Choose New Worksheet and select OK.

  4. In PivotTable Fields, drag Region to Rows. West, East and North should appear as row labels.

  5. Drag Sales to Values. Check that it says Sum of Sales. If it says Count, inspect the source amounts before choosing Sum in Value Field Settings.

  6. Right-click a result and choose Number Format > Number. Use 0 decimal places and turn on Use 1000 Separator.

  7. Right-click a value and choose Sort > Sort Largest to Smallest. Check that West is first with 4500.

  8. After a source edit, use PivotTable Analyze > Refresh or your edition’s Refresh command. Compare the grand total with the source.

  9. If supported, add Sales to Values again and set the new field to Show Values As > Percent of Grand Total. Keep the amount field and check that it still totals 9450.

Try it yourself

  1. Drag Month into the Columns area of the PivotTable Fields pane.
  2. On a copy, append Mar West Maya Laptops 300, refresh and compare regional, monthly and grand totals. Keep the original eight-row result for comparison.
Show expected result
  • Rows before the exercise: West 4,500 · East 3,350 · North 1,600 · Grand Total 9,450.
  • After adding Month: West Jan 3,000, Feb 1,500 · East Jan 950, Feb 1,100, Mar 1,300 · North Feb 700, Mar 900.
  • Column totals read Jan 3,950 · Feb 3,300 · Mar 2,200.
  • After the added training sale, West is 4800, March is 2500 and Grand Total is 9750; January and February remain 3950 and 3300.

If something goes wrong

Values shows Count of Sales instead of Sum of Sales.
Check the source for text amounts and blanks. Convert amounts that should be numeric, refresh, then choose Sum in Value Field Settings.
A new sale is missing from the PivotTable.
Check that the source table includes the new row and refresh. If the source is a fixed range, extend it under Change Data Source.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What is the original grand total?
2. Which region has the largest original total?
3. What should you do after adding a source transaction?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Summarize this sales table by Region and Month. Show the PivotTable layout and compare the amounts for January and March.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…