Why advanced charts tell better stories in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a clear visual report showing monthly sales trends and product category performance to help guide marketing strategies.

📊 Data: You have monthly sales data for three product categories (Electronics, Clothing, Home Goods) over four months, including total sales and units sold.

🎯 Deliverable: Create a dashboard with advanced charts that show sales trends over time and compare product category performance clearly.

Sample Data

Month	Product Category	Sales ($)	Units Sold
Jan	Electronics	12000	100
Jan	Clothing	8000	150
Jan	Home Goods	6000	80
Feb	Electronics	15000	120
Feb	Clothing	7000	140
Feb	Home Goods	6500	90
Mar	Electronics	13000	110
Mar	Clothing	9000	160
Mar	Home Goods	7000	85
Apr	Electronics	16000	130
Apr	Clothing	8500	155
Apr	Home Goods	7200	95

1

Step 1: Organize the data by creating a pivot table with Rows as Month, Columns as Product Category, and Values as SUM of Sales ($).

Insert > PivotTable > Select data range > Rows: Month > Columns: Product Category > Values: Sum of Sales ($)

Expected Result

Pivot table showing total sales for each product category by month.

2

Step 2: Create a line chart from the pivot table to show sales trends over months for each product category.

Select pivot table > Insert > Line Chart > Choose 'Line with Markers'

Expected Result

Line chart with months on X-axis and sales on Y-axis, lines for Electronics, Clothing, and Home Goods.

3

Step 3: Add data labels to the line chart to show exact sales values on each point.

Click chart > Chart Elements (+) > Data Labels > Show

Expected Result

Sales values displayed on each data point for clarity.

4

Step 4: Create a clustered column chart to compare total units sold by product category across all months.

Create a new pivot table: Rows = Product Category, Values = SUM of Units Sold; Insert > Column Chart > Clustered Column

Expected Result

Column chart showing total units sold per product category.

5

Step 5: Format charts with clear titles, axis labels, and legends for easy understanding.

Select chart > Chart Tools > Add Chart Title: 'Monthly Sales Trends' or 'Total Units Sold by Category'; Add Axis Titles; Ensure legend is visible

Expected Result

Charts have descriptive titles and labels making them easy to read.

6

Step 6: Arrange the charts side by side in a dashboard layout for a clear visual story.

Drag and resize charts on the worksheet to place them next to each other

Expected Result

Dashboard showing sales trends and category comparisons clearly.

Final Result

Monthly Sales Trends (Line Chart)

Month | Electronics | Clothing | Home Goods
------------------------------------------
Jan   | 12000       | 8000     | 6000
Feb   | 15000       | 7000     | 6500
Mar   | 13000       | 9000     | 7000
Apr   | 16000       | 8500     | 7200

Total Units Sold by Category (Column Chart)

Product Category | Units Sold
-----------------------------
Electronics      | 460
Clothing        | 605
Home Goods      | 350

✓Electronics sales show a steady increase from January to April.

✓Clothing sales fluctuate but remain strong overall.

✓Home Goods sales grow slowly but steadily.

✓Clothing has the highest total units sold, indicating strong customer demand.

✓Using line and column charts together helps tell a clear story about sales trends and product performance.

Bonus Challenge

Add a combo chart that shows sales as columns and units sold as a line on the same chart for each product category over months.

Show Hint

Use Insert > Combo Chart > Choose 'Clustered Column - Line' and assign sales to columns and units sold to line.