Subtotals in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing total sales by each product category and the overall total sales.

📊 Data: You have a sales data table with columns: Date, Product Category, Product Name, Units Sold, and Sales Amount.

🎯 Deliverable: Create a subtotal report that groups sales by Product Category and shows the sum of Sales Amount for each category and the grand total.

Sample Data

Date	Product Category	Product Name	Units Sold	Sales Amount
2024-06-01	Electronics	Headphones	10	500
2024-06-02	Electronics	Smartphone	5	2500
2024-06-03	Home Appliances	Blender	7	350
2024-06-04	Home Appliances	Microwave	3	600
2024-06-05	Furniture	Desk	2	400
2024-06-06	Furniture	Chair	4	200
2024-06-07	Electronics	Tablet	6	1800
2024-06-08	Home Appliances	Toaster	8	240

1

Step 1: Sort the data by the 'Product Category' column to group similar categories together.

Select all data rows and columns, then go to Data tab > Sort > Sort by 'Product Category' ascending.

Expected Result

Data rows are ordered so that all Electronics rows come first, then Furniture, then Home Appliances.

2

Step 2: Insert subtotals to sum 'Sales Amount' for each 'Product Category'.

Select the data range including headers, then go to Data tab > Subtotal. Use 'At each change in': Product Category, 'Use function': Sum, 'Add subtotal to': Sales Amount.

Expected Result

Excel inserts subtotal rows after each product category showing the sum of Sales Amount for that category.

3

Step 3: Verify the subtotal sums for each category.

Check the subtotal rows under Sales Amount column.

Expected Result

Electronics subtotal: 4800, Furniture subtotal: 600, Home Appliances subtotal: 1190.

4

Step 4: View the grand total of all sales at the bottom of the data.

Look at the last subtotal row which shows the grand total sum of Sales Amount.

Expected Result

Grand total sales amount is 6590.

Final Result

Product Category   Sales Amount
--------------------------------
Electronics        4800
  Headphones       500
  Smartphone       2500
  Tablet           1800
Furniture          600
  Desk             400
  Chair            200
Home Appliances    1190
  Blender          350
  Microwave        600
  Toaster          240
--------------------------------
Grand Total        6590

✓Electronics is the highest sales category with $4800 in sales.

✓Home Appliances have moderate sales totaling $1190.

✓Furniture has the lowest sales at $600.

✓Overall, total sales amount to $6590.

Bonus Challenge

Create a subtotal report that also shows the total units sold per product category along with sales amount.

Show Hint

In the Subtotal dialog, select both 'Units Sold' and 'Sales Amount' columns to add subtotals for both.