DataBrighten

    Excel PivotTable practice

    5 questionswith checked answers ยท sample sales data

    Build PivotTables from 1,000 order lines and check your totals against ours. Updated 3 November 2026.

    textnumberdateyes/no

    Steps

    1. Download sales-orders-1000.xlsx and click any cell in the table.
    2. Insert > PivotTable > New worksheet.
    3. Drag region to Rows and sales to Values (Sum of sales).
    4. Add category to Columns, then swap sales for profit.
    5. Group order_date by Months and Years to find the best month.
    6. Add discount to Filters and choose 0.3, 0.4 and 0.5 to see what heavy discounts do to profit.

    Questions

    try first, then open the answer

    What are total sales and total profit?

    Sales $955,613.76, profit $214,946.97

    Excel

    =SUM(Q2:Q1001) and =SUM(S2:S1001)

    File: sales-orders-1000

    Which region has the highest sales?

    South, $299,380.41 (Midwest $234,801.89, West $217,262.68, Northeast $204,168.78)

    Excel

    =SUMIFS(Q:Q,H:H,"South")  (one region per cell), or a PivotTable: region in Rows, Sum of sales in Values

    File: sales-orders-1000

    Profit margin (profit / sales) by category?

    Office Supplies 26.98%; Technology 22.32%; Furniture 21.42%

    Excel

    =SUMIFS(S:S,K:K,"Technology")/SUMIFS(Q:Q,K:K,"Technology")

    File: sales-orders-1000

    How many orders had a discount of 30% or more, and what was their combined profit?

    114 orders, combined profit $-8,060.65

    Excel

    =COUNTIFS(P:P,">=0.3") and =SUMIFS(S:S,P:P,">=0.3")

    File: sales-orders-1000

    Which month had the highest sales?

    2023-02, $69,236.92

    Add a month column (=TEXT(B2,"yyyy-mm") in Excel, or FORMAT in Power BI), then sum sales by it.

    File: sales-orders-1000