Excel Sales & PivotTable Practice — Tasks & Answers

Open this HTML file in your browser, then choose Print or Save as PDF. No sign-in, script or upload is needed. This file includes the worked answers.

1. Inspect the source table

   Check that the source has eight columns and one record per order. Identify the two inputs needed for Revenue.

   200 records. G is Units and H is Unit Price. No source Revenue column exists yet.

2. Calculate revenue

   Add Revenue in I and fill a per-order formula down.

   =ROUND(G2*H2,2)

   First order revenue: 30477.65. Sum I2:I201 to obtain 3051575.50.

3. Create a region/category PivotTable

   Summarize revenue by region and category.

   Select A1:I201. Choose Insert > PivotTable > New Worksheet. Put Region in Rows, Category in Columns and Revenue in Values. In Value Field Settings choose Sum, not Count. Compare every cell with Region by category answers.

4. Cross-check one pivot cell

   Independently reproduce the first region/category value with SUMIFS.

   =SUMIFS($I$2:$I$201,$C$2:$C$201,"Central",$F$2:$F$201,"Accessories")

   Central / Accessories: 198347.70. SUMIFS and the corresponding PivotTable cell should agree.

5. Prepare a month key

   Add Month in J from the real Order Date.

   =TEXT(B2,"yyyy-mm")

   Fill down and use Month as a row field. Alternatively group numeric Order Date values by Years and Months in a PivotTable. Date-format tokens can differ in localized Excel.

6. Build monthly revenue totals

   Create a second PivotTable with Month in Rows and Revenue in Values, summarized by Sum.

   There are 12 months present. The Monthly revenue answers sheet provides every expected total. Monthly totals must add to 3051575.50.

7. Calculate revenue per unit

   Calculate the aggregate revenue per unit, not the unweighted average of order prices.

   =SUM(I2:I201)/SUM(G2:G201)

   685.44 displayed to two decimals. Total units: 4452. This ratio weights prices by units.

8. Refresh and reconcile

   Change a Units value in your working copy, refresh your PivotTables and check both totals.

   The original answer file is a fixed reference for the unchanged source. After edits, both PivotTables must agree with your new Revenue total, not the old reference. Clear filters before comparing grand totals.

Matching these checks does not prove every row is correct. Use the reference workbook or CSV to compare the complete result. The tables below are fixed expected values, not interactive PivotTables.