Bird
Raised Fist0
Excelspreadsheet~15 mins

Waterfall charts in Excel - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a financial analyst at a retail company.
📋 Request: Your manager wants a clear visual report showing how different factors contributed to the change in net profit from last year to this year.
📊 Data: You have data showing last year's net profit, various increases and decreases in revenue and expenses, and this year's net profit.
🎯 Deliverable: Create a waterfall chart in Excel that visually breaks down the changes from last year's net profit to this year's net profit.
Progress0 / 5 steps
Sample Data
CategoryAmount
Last Year Net Profit50000
Increase in Sales15000
Decrease in Cost of Goods Sold7000
Increase in Marketing Expenses-4000
Increase in Administrative Expenses-3000
Other Income2000
This Year Net Profit67000
1
Step 1: Organize your data in Excel with two columns: 'Category' and 'Amount' as shown in the sample data.
No formula needed for this step.
Expected Result
Data is clearly listed with categories and their corresponding amounts.
2
Step 2: Select the entire data range including headers (A1:B8).
No formula needed.
Expected Result
Data range is selected for chart creation.
3
Step 3: Go to the Insert tab, click on 'Insert Waterfall or Stock Chart', then choose 'Waterfall'.
No formula needed.
Expected Result
A basic waterfall chart appears showing the changes from last year to this year.
4
Step 4: Set the first and last bars as totals to represent the starting and ending net profit.
Right-click the first bar (Last Year Net Profit), choose 'Set as Total'. Repeat for the last bar (This Year Net Profit).
Expected Result
The first and last bars are shown as totals, anchoring the waterfall chart.
5
Step 5: Format the chart for clarity: add data labels, adjust colors for increases (green) and decreases (red).
Use Chart Tools to add data labels and change bar colors manually.
Expected Result
Chart clearly shows positive changes in green, negative changes in red, with data labels on each bar.
Final Result
Waterfall Chart

| Last Year Net Profit | +15000 | +7000 | -4000 | -3000 | +2000 | = This Year Net Profit |
|        50000         | 65000  | 72000 | 68000 | 65000 | 67000 |         67000         |
✓Net profit increased from 50,000 last year to 67,000 this year.
✓Sales and cost reductions contributed positively to profit.
✓Marketing and administrative expenses reduced profit.
✓Other income added a small positive amount.
Bonus Challenge

Add a slicer to the waterfall chart to filter changes by category type (e.g., Revenue, Expenses, Other).

Show Hint
Add a new column categorizing each row, then convert data to a table and insert a slicer based on that category.

Practice

(1/5)
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard