Skip to content
  • Official
  • Beginner
  • English
  • 10 steps · 2 quizzes

Pivot Table Exercises: A Hands-On Excel Lesson

Pivot table exercises in Excel: drag rows, columns, values and filters on a sales sheet, then answer real questions with Sum, Count, slicers and Top 3.

  • Free
  • No account needed
  • Stop any time, pick up where you left off
Workshopy.io

WorkshopyOfficial

78 lessons · used by 2 teachers

For teachers

Teaching this? Run it live with your class.

  • Students join with a code — no accounts
  • See who’s done and who’s stuck, step by step
  • It’s your copy — change anything before class

Sign in to copy it to your workspace. It’s free.

Your path

10 steps · 2 quizzes · open any step, or read it here

  1. Pivot table exercises: a hands-on Excel lessonStart hereIn this lesson you practise pivot tables in Excel on one small sales sheet: you drag fields into Rows, Columns, Values and Filters and answer real questions with the pivot, such as "which region sold the most in March?".InteractiveHands-on
    Read this step

    In this lesson you practise pivot tables in Excel on one small sales sheet: you drag fields into Rows, Columns, Values and Filters and answer real questions with the pivot, such as "which region sold the most in March?". Every exercise runs in a live pivot table inside the lesson, and each one ticks as soon as the pivot shows the answer.

    Watch the sheet below. Its 36 orders each have a date, a region, a rep, a product, units and revenue. A pivot table groups them and adds them up.

    Interactive · 36 orders become one row per region — try it in the lesson

    Press Product and Month too: the same 36 rows, grouped another way, give a different summary. Nothing in the source changes.

    In this lesson you'll:

    • build a pivot table by dragging fields into Rows, Columns, Values and Filters
    • switch between Sum, Count and Average, and show values as a share of the total
    • filter with a slicer, group dates by month, sort, and keep only the top 3
    • avoid the usual mistakes: Count instead of Sum, a pivot that wasn't refreshed, and new rows the source range misses

    It takes about 60 minutes: nine short steps, eight pivot exercises you do in the lesson, a practice step and two quizzes.

    Takeaway: a pivot table groups rows that share a value and adds up their numbers, without a single formula.

  2. Rows and Values: your first pivot tableA pivot table needs a list with a header row and one record per row, like the sales sheet: no empty rows, no merged cells, no totals mixed in. To make one in Excel: Click any cell in the list. Insert > PivotTable.InteractiveHands-on
    Read this step

    A pivot table needs a list with a header row and one record per row, like the sales sheet: no empty rows, no merged cells, no totals mixed in.

    To make one in Excel:

    1. Click any cell in the list.
    2. Insert > PivotTable. Excel selects the whole list for you; leave New Worksheet chosen and press OK.
    3. An empty pivot table appears, and the PivotTable Fields pane opens on the right: the list's column headers at the top, four areas below them, Filters, Columns, Rows and Values.

    Then you drag fields into the areas:

    • A field in Rows gives one row per value: one row per region.
    • A field in Values is added up for each of those rows: Sum of Revenue.

    Ticking a field's box in the pane does the same: text fields go to Rows, number fields to Values.

    The pivot table never changes the sheet. It works on its own copy of the data, which is why step 10 is about refreshing it.

    Try it: drag the fields below (or press a field and choose an area), then do the four tasks. Every number in the pivot can be pressed to show the rows behind it.

    Interactive · Your first pivot: Region in Rows, Revenue in Values — try it in the lesson

    North and South are close: 15,000 against 14,550. Reading 36 rows by eye, you might never have seen that.

    Takeaway: Rows says how to group, Values says what to add up.

  3. Columns: a cross-tab of two fieldsA second field in Columns splits every row again. With Region in Rows and Product in Columns, each cell is one region's revenue from one product: a cross-tab. Two totals appear: Grand Total at the end of each row: that region, all products.InteractiveHands-on
    Read this step

    A second field in Columns splits every row again. With Region in Rows and Product in Columns, each cell is one region's revenue from one product: a cross-tab.

    Two totals appear:

    • Grand Total at the end of each row: that region, all products.
    • Grand Total at the foot of each column: that product, all regions.

    A cell left empty means that region sold none of that product. Rows and columns are interchangeable: putting Product in Rows and Region in Columns shows the same numbers turned on their side. That turning is the "pivot" in pivot table.

    Try it: add Product to Columns, answer the two questions, then swap the fields.

    Interactive · Rows and Columns: Region by Product — try it in the lesson

    Laptops bring in the most revenue overall (20,400), but not in the East, where Monitors lead with 3,000. A total hides what a cross-tab shows.

    Takeaway: Rows and Columns are two ways to split the same numbers; drag a field across to pivot the view.

  4. Sum, Count, Average, and why Excel picks CountA field in Values is summarised by one function. You change it in Value Field Settings: click the field in the Values area, choose Value Field Settings, and pick on the Summarize Values By tab.InteractiveHands-on
    Read this step

    A field in Values is summarised by one function. You change it in Value Field Settings: click the field in the Values area, choose Value Field Settings, and pick on the Summarize Values By tab. Right-clicking a number in the pivot and choosing Summarize Values By does the same.

    Summarize by What each cell shows
    Sum the numbers added up
    Count how many cells have something in them: here, how many orders
    Average the sum divided by how many numbers there are

    Excel chooses for you when you drop the field: Sum if every cell of the column is a number, Count if even one cell is empty or holds text. That is the most common pivot surprise. One order below has the text pending instead of its revenue, so Revenue arrives as Count of Revenue: the West shows 9, not a sum of money.

    Fix it by switching to Sum, and fix the cause in the data too: type the order's revenue as a number once it is known. Numbers stored as text (pasted from another system, often with a small green triangle) do the same. A Rows field with an empty cell gets a row labelled (blank).

    Try it: do the four tasks. The pending order is in sheet row 13; open the source data under the pivot to find it.

    Interactive · Count of Revenue? A text cell in a number column — try it in the lesson

    With Sum, the total is 46,980: the pending order isn't in it, because Sum skips text. Count still counted it, because Count counts any cell that isn't empty. And the South took only 9 orders but has the biggest average order, 1,616.67, thanks to two laptop orders of 2,400 and 4,800.

    To show the numbers with a thousands separator in Excel, use Number Format in the same Value Field Settings window.

    Takeaway: when Values says "Count of" a money column, look for text or empty cells in the source before you switch to Sum.

  5. Quick check: rows, columns and valuesFour questions on the steps so far.Quiz · 4 questions
    Read the questions

    Four questions on the steps so far.

    Question 1. You put Region in Rows and Revenue in Values. What does each row of the pivot table show?

    • One order from that region
    • The revenue of every order from that region, added up
    • The first order's revenue for that region
    • How many products that region sells

    Question 2. Revenue arrives in Values as "Count of Revenue". What is the most likely reason?

    • The pivot table has too many rows
    • Revenue is in the wrong area
    • At least one Revenue cell in the source is empty or holds text
    • Excel always counts money columns

    Question 3. Region is in Rows, Product in Columns. Where do you read the East's revenue from all products?

    • The Grand Total row at the bottom
    • The Grand Total column at the end of the East row
    • The first cell of the East row
    • The Laptop column

    Question 4. Where do you change Sum of Revenue to Average of Revenue?

    • Insert > PivotTable
    • Value Field Settings, Summarize Values By
    • Home > Format as Table
    • By dragging Revenue into Rows
  6. Filters and slicers: answer for one productOften the question is about part of the data: "who sold the most laptops?". Two tools narrow the pivot without touching the source. Filters area: drag a field there and a drop-down appears above the pivot table.InteractiveHands-on
    Read this step

    Often the question is about part of the data: "who sold the most laptops?". Two tools narrow the pivot without touching the source.

    • Filters area: drag a field there and a drop-down appears above the pivot table. Pick one value (Laptop) and every number counts only those rows. It shows (All) until you choose.
    • Slicer: a panel of buttons for a field. Insert one with PivotTable Analyze > Insert Slicer and tick the field. Click a button to show only that value; turn on Multi-Select (the button at the slicer's top) or hold Ctrl while you click to add more; Clear Filter shows everything again.

    A slicer shows at a glance what is filtered, which a drop-down hides, so slicers are the usual choice on a report other people read. Filters and slicers work together: each one narrows what the other leaves.

    Try it: filter to laptops, then use the Region slicer above the pivot table.

    Interactive · Filters and a slicer: laptops by rep — try it in the lesson

    Ana leads the reps overall, but Dev sold the most laptops: 7,200. Filter first, then read.

    Takeaway: a filter or slicer changes which rows count, not how they're grouped.

  7. Group dates by month: which region sold the most in March?A date in Rows gives one row per day: 36 rows for 36 orders, which answers nothing. Grouping turns days into months (or quarters, or years). In Excel: Right-click any date in the pivot table and choose Group….InteractiveHands-on
    Read this step

    A date in Rows gives one row per day: 36 rows for 36 orders, which answers nothing. Grouping turns days into months (or quarters, or years).

    In Excel:

    1. Right-click any date in the pivot table and choose Group….
    2. In the Grouping window select Months (click Days off if it is selected) and press OK.

    Excel for Microsoft 365 often groups dates for you as soon as you drag a date field into Rows, adding a Months field to the list. When it doesn't, the menu above does it; and when Group says "Cannot group that selection", some cells in the date column are text or empty, not dates.

    Here the grouping is in the pivot table's Row Labels menu.

    Try it: put Date in Rows, group it, and answer the two questions.

    Interactive · Group dates by month: which region won March? — try it in the lesson

    The North is the top region over the three months, yet in March the South sold the most: 8,040 against the East's 4,800. "Most overall" and "most in March" are different questions, and the pivot answers both.

    Takeaway: group dates before you read them; a month, quarter or year is a row you can compare.

  8. Show Values As: shares instead of sums"The East brought in 7,920" is less useful than "the East brought in 16% of revenue". Show Values As turns each number into a share without a formula. It is the second tab of Value Field Settings, or right-click a number and choose Show Values As.InteractiveHands-on
    Read this step

    "The East brought in 7,920" is less useful than "the East brought in 16% of revenue". Show Values As turns each number into a share without a formula. It is the second tab of Value Field Settings, or right-click a number and choose Show Values As.

    Show Values As Each cell divided by
    % of Grand Total the total of the whole pivot table
    % of Column Total the total of its own column
    % of Row Total the total of its own row

    With % of Column Total, each column adds up to 100%: with months in Columns, you read each region's share of that month. A share of the grand total and a share of the column answer different questions, so pick the one your question asks.

    Try it: show the region shares, then put Date in Columns and switch to % of Column Total.

    Interactive · Show Values As: shares instead of sums — try it in the lesson

    The South had 30.22% of all revenue but 43.23% of March's. Shares make two different sized months comparable.

    Takeaway: % of Grand Total answers "how big a part of everything?", % of Column Total answers "how big a part of this column?".

  9. Sort and Top 3: the best repsA pivot table lists its rows A to Z. To rank them, sort by the values: Right-click a number in the Values column, Sort > Sort Largest to Smallest. Or open the Row Labels drop-down, More Sort Options, Descending by Sum of Revenue.InteractiveHands-on
    Read this step

    A pivot table lists its rows A to Z. To rank them, sort by the values:

    • Right-click a number in the Values column, Sort > Sort Largest to Smallest. Or open the Row Labels drop-down, More Sort Options, Descending by Sum of Revenue.

    To keep only the best few, use a value filter: open the Row Labels drop-down, choose Value Filters > Top 10…, change 10 to 3 and press OK. The dialog reads "Show Top 3 Items by Sum of Revenue".

    With a value filter on, the Grand Total adds up only the rows you can see, so it becomes "the top 3 together", not the whole company.

    Try it: rank the reps, keep the top 3 and answer the two questions.

    Interactive · Sort and Top 3: the best reps — try it in the lesson

    The top 3 brought in 27,360 of the 48,150: more than half, from three of the eight reps.

    Takeaway: sort by the value to rank; a Top 10 value filter set to 3 keeps the leaders, and the Grand Total follows the filter.

  10. Refresh, and a Table that grows: the two mistakes that hide new dataA pivot table reads its source once, into its own copy, and keeps that copy. Two things follow. 1. A change in the source doesn't show until you refresh. Fix a number on the sheet and the pivot keeps the old one.InteractiveHands-on
    Read this step

    A pivot table reads its source once, into its own copy, and keeps that copy. Two things follow.

    1. A change in the source doesn't show until you refresh. Fix a number on the sheet and the pivot keeps the old one. Refresh with PivotTable Analyze > Refresh, right-click the pivot and Refresh, or press Alt+F5; Ctrl+Alt+F5 refreshes every pivot table in the workbook.

    2. A fixed range misses new rows, even after a refresh. A pivot table made from A1:F37 reads rows 1 to 37 for ever. Orders typed in row 38 and below never count, and nothing warns you.

    The fix for the second is a Table. Click in the data and press Ctrl+T (or Home > Format as Table). A Table grows by itself when you type a row under it. Give it a name in Table Design > Table Name, such as SalesTable, then point the pivot table at it: PivotTable Analyze > Change Data Source, type SalesTable, OK. From then on, Refresh reads every row the Table has. Build pivot tables from a Table from the start and you never meet this problem.

    Google Sheets pivot tables update by themselves when the data changes.

    Try it: fix the order, refresh, then make the data a Table and get April in. Try adding April before the Table first, and refresh: watch what happens.

    Interactive · Refresh, and a Table that grows — try it in the lesson

    After the fix March reads 19,800, and with April in the Table the pivot table has a fourth month: 5,580.

    Takeaway: refresh after every change to the source, and build the pivot table on a Table so new rows count.

Comments

You’ll sign in with Google before it’s posted.Comments are public — don’t name your students.
0 / 1,500

No comments yet. Be the first to say how it went.

Using it

Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.

Versions

  1. 1

    Version 1Current

    · First published

Copies are made from the current version. A copy you already have doesn’t change.

More from Workshopy

All 78 lessons
All 21 Data & AI lessons