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.