Home | Articles

Waterfall Chart Additions – Working with Negative Numbers - Part 5

By: Taylor Croonquist

Explore Waterfall Chart Basics, learn how to work with positive numbers.

Add Your Ratings!:

...Continued from Page 4

Now that you know how to build a Waterfall Chart with positive numbers, the question is how to you handle negative numbers? Although this requires a bit more work, it’s no problem if you keep your spreadsheet organized.

Negative Waterfall Scenario: Continuing with the same example, if we change the handling fee above from a positive 10 to a negative 10 (a handling discount), the Excel spreadsheet updates to a total cost of 80 (I’ve marked the row in yellow below).

Note: With the handling discount of 10, the Waterfall Chart in PowerPoint is no longer correct as the Handling column disappears.

Excel Sheet Handling Discount of 10
Figure 1: Excel Sheet Handling Discount of 10

PowerPoint  Resulting Waterfall Chart
Figure 2: PowerPoint Resulting Waterfall Chart

  1. Fix the Base: First you'll need to navigate back to the Excel spreadsheet. Right-click your chart and select the Edit in Excel option to return to the Excel spreadsheet.

    For rows with negative numbers, marked in yellow in the spreadsheet, the Base will need to adjust due to the negative number. So in cell D13, change the formula to "=F12 + B13", which takes the Base of the Handling column down to 80.

    Adjusting the Base Formula in Excel
    Figure 3: Adjusting the Base Formula in Excel

    Resulting Waterfall Chart in PowerPoint
    Figure 4: Resulting Waterfall Chart in PowerPoint

  2. Fix the Value: To bring the Value column back into our PowerPoint chart, we need to change the negative value of 10, to a positive value, while still keeping our data dynamic. So instead of hard-coding it in, in cell E13 simply change the formula to "=-B13". Reversing the sign creates a positive value, which brings the column back into our PowerPoint chart, as you can see on the right of Figure 5. Now we’ll just need to manually adjust the label.

    Adjusting the Label Formula in Excel
    Figure 5: Adjusting the Label Formula in Excel

    Resulting Waterfall Chart in PowerPoint
    Figure 6: Resulting Waterfall Chart in PowerPoint

  3. Fixing the Data Label in PowerPoint: To close Excel, hit Ctrl + W on your keyboard to return to PowerPoint. Now that we have manually adjusted the formulas in Excel, we need to manually adjust the Data Label for the negative value within the Waterfall Chart itself.

    To do so, double-click into the Handling Data Label, and wrap parentheses around the number to indicate that it is negative. If you want, you can also fill the Handling column's Value column with another color, to further indicate that it’s a negative number.

    Resulting Waterfall Chart in PowerPoint

    Note: If at any point your number changes back to positive value, you will need to go back into Excel to adjust the formulas, and then correct the data label manually back in PowerPoint.

  4. Spot Checking: Even though all the numbers are correct, it’s a best practice to visually spot check your Waterfall Chart for accuracy to make sure the items are adding up and subtracting correctly.

Ending Excel Spreadsheet Formulas for your Negative Waterfall Chart

Just as a reference, below is what the formulas inside the Excel spreadsheet should look like for your negative Waterfall Chart.

Excel Spreadsheet Formulas for Waterfall Chart

Waterfall Chart Additions – Adding Step Lines | Continued on Page 6

comments powered by Disqus

Subscribe to Indezine
Follow Indezine

Follow Indezine on Pinterest
Share This Page
Bookmark and Share
Translate Page

Like This Page
Like This Site

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Home | PowerPoint | Photoshop | PowerPoint Templates | PowerPoint Tutorials | Blog | Notes | Ezine | Advertise | Feedback | Site Map | About Us | Contact Us

Link to Us | Privacy | Testimonials

PowerPoint Backgrounds | Christian PowerPoint Backgrounds | Business PowerPoint Presentation Templates

Plagiarism will be detected by Copyscape

©2000-2019, Geetesh Bajaj. All rights reserved.

since November 02, 2000