Why advanced charts tell better stories in Excel - Challenge Your Understanding

Challenge - 5 Problems

🎯 Scenario

intermediate

Choosing the best chart type for sales trends

You have monthly sales data for two products over a year. You want to show how sales changed over time and compare the two products clearly. Which chart type tells the best story?

A A pie chart showing total sales for each product

B A bar chart showing total sales per product

C A scatter plot with months on X-axis and sales on Y-axis

D A line chart with two lines, one for each product, showing sales over months

💡 Hint

Think about showing changes over time and comparing two sets of data.

A line chart with two lines clearly shows trends over time and allows easy comparison between products. Pie and bar charts show totals but not trends. Scatter plots are less intuitive for time series.

📊 Formula Result

intermediate

Calculating percentage change for a chart label

You want to add a label showing the percentage change in sales from January to December in a cell. If January sales are in B2 and December sales in B13, which formula correctly calculates the percentage change?

A =B13/B2+1

B =(B13-B2)/B2

C =(B2-B13)/B13

D =B2/B13-1

💡 Hint

Percentage change is (new - old) divided by old.

The formula (B13-B2)/B2 calculates the change from January (old) to December (new) divided by January, giving the correct percentage change.

❓ Function Choice

advanced

Which function helps create dynamic chart ranges?

You want your chart to update automatically when you add new data rows. Which Excel function helps create a dynamic range for the chart data?

A OFFSET

B VLOOKUP

C SUM

D IF

💡 Hint

Think about a function that can define a range based on a starting point and size.

OFFSET can create a range that changes size dynamically based on data, perfect for charts that update with new rows.

❓ data_analysis

advanced

Identifying misleading chart elements

You see a bar chart where the Y-axis starts at 50 instead of 0. What effect does this have on the story the chart tells?

A It has no effect on how the data is perceived

B It makes the chart more accurate by focusing on relevant data

C It exaggerates differences between bars, making changes look bigger

D It hides the smallest values completely

💡 Hint

Consider how starting the axis higher than zero changes visual bar lengths.

Starting the Y-axis at 50 exaggerates differences, making small changes look large and potentially misleading the viewer.

🧠 Conceptual

expert

Why use combination charts in storytelling?

Combination charts use two or more chart types together (e.g., bars and lines). What is the main advantage of using combination charts in data storytelling?

A They allow showing different types of data with different scales in one chart

B They make charts more colorful and attractive without adding meaning

C They reduce the need for labels and legends

D They simplify data by showing only one data series at a time

💡 Hint

Think about showing data with different units or scales together.

Combination charts let you display different data types or scales together, making complex stories clearer by using appropriate chart types side by side.

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