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.

Practice

1. Why do advanced charts in Excel help tell better stories with data?

easy

A. They remove the need for any data analysis.

B. They make the spreadsheet file size smaller.

C. They automatically fix errors in data.

D. They highlight key trends and make data easier to understand.

Solution

1. Step 1: Understand the purpose of advanced charts

   Advanced charts are designed to visually emphasize important data points and trends.

2. Step 2: Compare options to the purpose

   Only They highlight key trends and make data easier to understand. correctly states that advanced charts highlight key trends and improve understanding.

3. Final Answer:

   They highlight key trends and make data easier to understand. -> Option D

4. Quick Check:

   Advanced charts = highlight trends [OK]

Hint: Remember: charts show trends clearly, not fix data [OK]

Common Mistakes:

• Thinking charts reduce file size
• Believing charts fix data errors automatically
• Assuming charts replace data analysis

2. Which of these is the correct way to insert a chart in Excel?

easy

A. Right-click a cell and choose 'Delete Chart'.

B. Type =CHART(A1:B5) in a cell.

C. Select data, then go to Insert > Chart and choose a chart type.

D. Use the formula bar to draw a chart.

Solution

1. Step 1: Recall Excel chart insertion steps

   Charts are inserted by selecting data and using the Insert tab's Chart options.

2. Step 2: Evaluate each option

   Only Select data, then go to Insert > Chart and choose a chart type. correctly describes this process; others are invalid or incorrect.

3. Final Answer:

   Select data, then go to Insert > Chart and choose a chart type. -> Option C

4. Quick Check:

   Insert tab > Chart = correct method [OK]

Hint: Insert charts via Insert tab, not formulas [OK]

Common Mistakes:

• Trying to create charts with formulas
• Using right-click to insert charts
• Expecting charts to be created in formula bar

3. Given this data in Excel:
Month: Jan, Feb, Mar
Sales: 100, 150, 120
If you create a line chart, what will it show?

medium

A. A line connecting points 100, 150, and 120 over Jan, Feb, Mar.

B. A bar chart with three bars of heights 100, 150, and 120.

C. A pie chart dividing the total sales into three slices.

D. An error message because line charts need more data.

Solution

1. Step 1: Understand line chart behavior

   Line charts plot data points connected by lines over categories (months).

2. Step 2: Match data to chart type

   Data points 100, 150, 120 will be connected over Jan, Feb, Mar on the line chart.

3. Final Answer:

   A line connecting points 100, 150, and 120 over Jan, Feb, Mar. -> Option A

4. Quick Check:

   Line chart = connected points [OK]

Hint: Line charts connect data points over categories [OK]

Common Mistakes:

• Confusing line chart with bar or pie chart
• Expecting error with small data
• Thinking line charts show slices like pie charts

4. You created a chart but it shows wrong data. What is the most likely fix?

medium

A. Restart Excel to refresh the chart automatically.

B. Check and correct the data range selected for the chart.

C. Delete the chart and create a new one without selecting data.

D. Change the chart type to pie chart regardless of data.

Solution

1. Step 1: Identify common chart errors

   Wrong data in charts usually comes from incorrect data range selection.

2. Step 2: Choose the best fix

   Correcting the data range fixes the chart data; other options do not address the root cause.

3. Final Answer:

   Check and correct the data range selected for the chart. -> Option B

4. Quick Check:

   Fix data range = correct chart data [OK]

Hint: Always verify chart data range first [OK]

Common Mistakes:

• Restarting Excel expecting auto-fix
• Deleting chart without fixing data
• Changing chart type without checking data

5. You want to show sales trends and compare product categories over time. Which advanced chart type should you choose?

hard

A. A combo chart with lines for trends and bars for categories.

B. A simple pie chart showing total sales only.

C. A scatter plot with unrelated data points.

D. A table with raw sales numbers only.

Solution

1. Step 1: Understand chart needs for trends and comparisons

   Showing trends and category comparisons requires combining chart types.

2. Step 2: Identify suitable advanced chart

   Combo charts combine lines (for trends) and bars (for categories) effectively.

3. Final Answer:

   A combo chart with lines for trends and bars for categories. -> Option A

4. Quick Check:

   Combo chart = trends + categories [OK]

Hint: Use combo charts for trends plus category comparison [OK]

Common Mistakes:

• Using pie charts for trends over time
• Choosing scatter plots without related data
• Displaying raw data instead of visual charts