Analyze with pivot tables

Build a summary you can check

A pivot table groups rows and calculates a summary for each group. Start with one question: how much did each category earn from paid orders?

Open a blank spreadsheet and name its sheet Orders. Copy the table below, click A1 once, and paste. Each header belongs in its own column. Prices are in USD; enter the numbers without typing a dollar sign.

1. 1. Prepare the values to summarize

   Type Total in F1. Enter =D2*E2 in F2, then copy F2 into F3:F7. Select A1:F7, including the headers and all six orders. Leave any other summaries out of this selection.

   You should see: The Total column is 24, 30, 12, 36, 60, 18. It includes the canceled order for now.

2. 2. Create and set up the first pivot

   Use the instructions for your spreadsheet app below. Put the summary on a new sheet.

   I’m usingMicrosoft ExcelGoogle Sheets

   1. With A1:F7 selected, choose Insert → PivotTable. Choose New Worksheet, then OK. In Excel for the web, the pane may say New sheet.
   2. In PivotTable Fields, drag Category to Rows, Total to Values, and Status to Filters. If Values says Count of Total, open Value Field Settings and select Sum.
   3. Open the Status filter above the pivot, select Paid, and apply it. If the field pane closes, click inside the pivot to reopen it.
   4. After changing an order, right-click inside each pivot and choose Refresh. If you add orders beyond row 7, update the pivot’s data source first.

   You should see: The new sheet shows a summary of paid sales by category. Check the setup and totals below.

3. 3. Check the fields and result

   Confirm that Rows is Category, Values is SUM of Total, and the Status filter includes only Paid.

   You should see: Books: 78. Games: 90. Grand total: 168.

   Rows names the groups; Values chooses what to calculate. SUM adds money. COUNT would show 3 Books orders and 2 Games orders, which answers a different question.

4. 4. Make a second view

   Keep the first summary. Create another pivot from Orders!A1:F7 on a new sheet. Use Status for Rows and SUM of Total for Values. Include both statuses.

   You should see: Canceled: 12. Paid: 168. Grand total: 180.

   The summaries agree: all orders (180) minus canceled orders (12) equals paid orders (168). Write those filters beside the summaries so someone else can compare them.