Selecting data for charts in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a clear chart showing monthly sales totals for each product category to understand trends.

📊 Data: You have a table with sales data including columns for Month, Product Category, and Sales Amount.

🎯 Deliverable: Create a chart that displays total sales per month for each product category.

Sample Data

Month	Product Category	Sales Amount
January	Electronics	1200
January	Clothing	800
January	Home Goods	600
February	Electronics	1500
February	Clothing	700
February	Home Goods	650
March	Electronics	1300
March	Clothing	900
March	Home Goods	700

1

Step 1: Create a pivot table to summarize total sales by Month and Product Category.

Insert > PivotTable > Select the sales data range > Place 'Month' in Rows, 'Product Category' in Columns, and 'Sales Amount' in Values with SUM aggregation.

Expected Result

Pivot table shows total sales for each product category per month.

2

Step 2: Select the entire pivot table data including headers.

Click and drag to highlight the pivot table area with months, categories, and sales totals.

Expected Result

The pivot table data is selected and ready for chart creation.

3

Step 3: Insert a clustered column chart based on the selected pivot table data.

Insert > Charts > Clustered Column Chart.

Expected Result

A chart appears showing sales totals per month for each product category.

4

Step 4: Add chart title and axis labels for clarity.

Click on chart title and type 'Monthly Sales by Product Category'. Add axis titles: Horizontal axis as 'Month', Vertical axis as 'Sales Amount'.

Expected Result

Chart has clear title and axis labels for easy understanding.

Final Result

Monthly Sales by Product Category

Month      Electronics   Clothing   Home Goods
------------------------------------------------
January       1200          800          600
February      1500          700          650
March         1300          900          700

[Clustered Column Chart showing bars for each category per month]

✓Electronics consistently have the highest sales each month.

✓Clothing sales are steady but lower than Electronics.

✓Home Goods have the lowest sales but show a slight increase over months.

Bonus Challenge

Create a line chart instead of a column chart to show sales trends over months for each product category.

Show Hint

Use the same pivot table data but choose Insert > Line Chart to better visualize trends over time.