Updated October 17, 2026

You can learn PivotTables without opening a customer list or changing a real sales report. Start with a blank workbook and a small, completely invented dataset. You'll get totals you can check and a file that never needed confidential source data.

Start with eight invented sales

Enter or paste this table starting in cell A1 of a new workbook. Every row was created for this exercise. Revenue uses arbitrary currency units; none of these figures represents a real business. Month is a text label, so you don't need to configure date grouping.

Month Region Item Units Revenue
Jan East Notebook 3 24
Jan East Pen 5 10
Jan West Notebook 2 16
Jan West Pen 4 8
Feb East Notebook 4 32
Feb East Pen 6 12
Feb West Notebook 5 40
Feb West Pen 3 6

Keep the five headings in one row, with no merged cells or blank rows inside the data. Units and Revenue should be numbers. Microsoft recommends this simple tabular structure for PivotTables. Source-data guidance

Build your first PivotTable

  1. Select A1:E9, including the headings.
  2. Choose Insert → PivotTable and create it on a new worksheet. On the web, choose New sheet under the option to create your own PivotTable.
  3. In the fields pane, put Region in Rows, Item in Columns, and Revenue in Values.
  4. Check that Revenue is summarized by Sum.

Windows, Mac, and web Excel use the same field arrangement for this exercise, though their creation panes and field-settings labels differ. Microsoft's platform-specific steps

Your answer should be:

Region Notebook Pen Grand Total
East 56 22 78
West 56 14 70
Grand Total 112 36 148

Try two changes:

  • Put Month in Filters and select Feb. The revenue total should be 90: East 44 and West 46.
  • Clear the month filter, then replace Revenue with Units in Values. The total should be 32: East 18 and West 14.

Fix the two common surprises

You get a count instead of a sum. Check the summary calculation and whether Revenue or Units was pasted as text. Fix the source values, then refresh the PivotTable. Where Excel shows a number-stored-as-text warning, use Convert to Number. Microsoft also documents a VALUE-function approach. Convert text to numbers

You edit a source value but the result stays the same. Right-click inside the PivotTable (or Control-click on Mac) and choose Refresh. As a test, change the first Revenue value from 24 to 30. With no filters and Revenue back in Values, the grand total should become 154. Refresh instructions

Make a larger practice dataset

Once the small example makes sense, explore the Rad Sheets Fake Data add-in to generate more sample values in a fresh workbook. Its page explains the available data types and installation requirements.

Keep a few repeated regions and products so the PivotTable has useful groups. Decide what each row represents, and keep related numbers consistent. For example, if a notebook costs 8, four notebooks should have revenue of 32. Changing every column independently can produce confusing practice data.

Why masking a real workbook needs more care

Creating these invented rows is different from hiding names or replacing a few values in an existing workbook. A workbook can still contain hidden sheets, comments, links, and cached data. Microsoft specifically warns that PivotTable-related caches may contain information you cannot see, and Document Inspector cannot remove those caches for you. Cached-data warning

For a lesson or public example, build a new workbook from synthetic values and recreate the PivotTable there. If you must share a workbook that previously held real data, follow your organization's review process. Document Inspector can help review a copy where available, but it is not a guarantee that a masked workbook is ready to share. Microsoft's inspection guidance

Ready to create your own practice table? See the Fake Data add-in and examples.

Browse all Excel tutorials