Waterfall charts in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the intermediate subtotal for a waterfall chart

You have a list of monthly revenue changes in cells B2:B6. The starting revenue is in cell B1. Which formula in cell B7 correctly calculates the subtotal after all changes?

Excel

Starting revenue in B1: 1000
Changes in B2:B6: 200, -150, 300, -100, 50
Formula in B7 to calculate subtotal:

A =B1 - SUM(B2:B6)

B =SUM(B1:B6)

C =B1 + AVERAGE(B2:B6)

D =B1 + SUM(B2:B6)

💡 Hint

Add all the changes to the starting revenue to get the subtotal.

The subtotal after all changes is the starting revenue plus the sum of all changes. Option D correctly adds B1 to the sum of B2:B6.

❓ Function Choice

intermediate

Choose the correct Excel function to calculate cumulative totals for a waterfall chart

You want to create a column that shows the running total of revenue changes for a waterfall chart. Which Excel function is best to use in cell C2 and drag down?

Excel

Starting revenue in B1: 1000
Changes in B2:B6: 200, -150, 300, -100, 50
Running total formula in C2:

A =SUM($B$1:B2)

B =SUM(B$1:B2)

C =SUM(B$1:B$2)

D =SUM(B1:B2)

💡 Hint

Use absolute reference for the start cell and relative for the end cell.

Option A uses an absolute reference for the starting cell B1 and a relative reference for the current row B2, so dragging down will correctly calculate running totals.

🎯 Scenario

advanced

Identify the correct data layout for a waterfall chart in Excel

You want to create a waterfall chart showing starting value, increases, decreases, and ending value. Which data layout is correct for Excel to build the chart?

Excel

Data options:
A) Column A: Labels (Start, Increase1, Decrease1, End)
    Column B: Values (1000, 200, -150, 1050)
B) Column A: Labels
    Column B: Positive values only
    Column C: Negative values only
C) Column A: Labels
    Column B: Cumulative totals
D) Column A: Labels
    Column B: Values with all positive numbers

A Labels in one column and values (positive and negative) in the next column

B Separate columns for positive and negative values with labels in first column

C Labels with cumulative totals only in the next column

D Labels with only positive values in the next column

💡 Hint

Waterfall charts need both positive and negative values in one column to show increases and decreases.

Excel waterfall charts require labels and a single column of values that include positive and negative numbers to show increases and decreases properly.

❓ data_analysis

advanced

Determine the final value displayed in a waterfall chart

Given the starting value 500 in cell A1 and changes in cells A2:A5 as 100, -50, 200, -150, what is the final value shown at the end of the waterfall chart?

Excel

Starting value: 500
Changes: 100, -50, 200, -150

A 550

B 500

C 600

D 700

💡 Hint

Add all changes to the starting value.

Sum of changes: 100 - 50 + 200 - 150 = 100. Add to starting value 500 + 100 = 600.

🧠 Conceptual

expert

Why use helper columns in waterfall charts?

In Excel waterfall charts, why do we often use helper columns for 'Increase', 'Decrease', and 'Subtotal' instead of a single column with positive and negative values?

A To make the chart look colorful by using multiple columns

B To separate positive and negative values for Excel to correctly display the bars as increases or decreases

C Because Excel does not support negative numbers in charts

D To avoid using formulas and make data entry easier

💡 Hint

Think about how Excel knows which bars go up or down in a waterfall chart.

Helper columns separate increases and decreases so Excel can color and position bars correctly in the waterfall chart.

Practice

1. What is the main purpose of a Waterfall chart in Excel?

easy

A. To show how an initial value changes step-by-step to a final value

B. To display data in a pie format

C. To compare values across categories side-by-side

D. To create a 3D visual effect for data

Solution

1. Step 1: Understand Waterfall chart purpose

   Waterfall charts visualize how an initial value changes through a series of positive and negative values to reach a final total.

2. Step 2: Compare with other chart types

   Pie charts show parts of a whole, bar charts compare categories, and 3D effects are visual styles, not purposes.

3. Final Answer:

   To show how an initial value changes step-by-step to a final value -> Option A

4. Quick Check:

   Waterfall charts = step-by-step changes [OK]

Hint: Waterfall = track increases and decreases stepwise [OK]

Common Mistakes:

• Confusing waterfall with pie or bar charts
• Thinking waterfall shows only totals, not changes
• Assuming waterfall charts use formulas directly

2. Which of these is the correct way to prepare data for a Waterfall chart in Excel?

easy

A. Use only positive values for all changes

B. Enter formulas directly into the chart

C. Use text labels only without numbers

D. Include both positive and negative values to show increases and decreases

Solution

1. Step 1: Identify data requirements for Waterfall charts

   Waterfall charts need positive values for increases and negative values for decreases to show step changes clearly.

2. Step 2: Eliminate incorrect options

   Using only positive values hides decreases, text labels alone don't create the chart, and formulas are not entered directly into charts.

3. Final Answer:

   Include both positive and negative values to show increases and decreases -> Option D

4. Quick Check:

   Positive and negative values = correct data setup [OK]

Hint: Use positive & negative numbers for changes [OK]

Common Mistakes:

• Using only positive numbers hides decreases
• Trying to put formulas inside the chart
• Using text without numbers for values

3. Given this data for a Waterfall chart:
Start: 100
Increase: 30
Decrease: -20
Increase: 10
What is the final total shown in the chart?

medium

A. 130

B. 120

C. 140

D. 150

Solution

1. Step 1: Add increases and decreases to start value

   Start with 100, add 30 (increase) = 130, subtract 20 (decrease) = 110, add 10 (increase) = 120.

2. Step 2: Calculate final total

   Final total after all changes is 120.

3. Final Answer:

   120 -> Option B

4. Quick Check:

   100 + 30 - 20 + 10 = 120 [OK]

Hint: Add positive and subtract negative values stepwise [OK]

Common Mistakes:

• Adding negative values instead of subtracting
• Ignoring decreases in calculation
• Mixing up order of operations

4. You created a Waterfall chart but it shows all bars starting from zero instead of connecting. What is the likely cause?

medium

A. Data does not include negative values

B. The data range is not formatted as a table

C. The 'Set as total' option is missing for subtotal rows

D. Chart type selected is not Waterfall

Solution

1. Step 1: Understand Waterfall chart bar connections

   Bars connect when Excel knows which rows are totals or subtotals, set by 'Set as total' option.

2. Step 2: Identify missing 'Set as total' setting

   If subtotal rows are not marked as totals, bars start from zero instead of continuing from previous values.

3. Final Answer:

   The 'Set as total' option is missing for subtotal rows -> Option C

4. Quick Check:

   Missing 'Set as total' = disconnected bars [OK]

Hint: Mark subtotal rows as totals to connect bars [OK]

Common Mistakes:

• Assuming negative values fix bar connections
• Thinking table format affects bar start points
• Choosing wrong chart type without checking data

5. You want to create a Waterfall chart showing monthly sales changes, but some months have zero change. How should you handle zero values to keep the chart clear?

hard

A. Leave zero values as is; Excel will show no bar for those months

B. Remove rows with zero values from the data

C. Replace zero values with small positive numbers

D. Convert zero values to negative numbers

Solution

1. Step 1: Understand zero values in Waterfall charts

   Zero values represent no change, so Excel shows no bar for those points, keeping the chart accurate and clear.

2. Step 2: Avoid altering zero values

   Replacing zeros with small positives or negatives misrepresents data; removing rows loses timeline context.

3. Final Answer:

   Leave zero values as is; Excel will show no bar for those months -> Option A

4. Quick Check:

   Zero values = no bar, keep data accurate [OK]

Hint: Keep zeros; chart shows no bar for no change [OK]

Common Mistakes:

• Changing zeros to small numbers distorts data
• Deleting zero rows breaks timeline
• Using negative numbers for zero changes