Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out.

Note: this technique only works for Excel 2016. You would need to hack a clustered column chart in other versions.

The waterfall, or bridge chart, is normally used to show how you get from one figure to another eg last year’s profit to this year’s profit. You can read an article I wrote on it last year here.

You can hack the waterfall chart to show what makes up a total when there are negatives.

Let’s say we have a list of state profits. One of the states makes a loss. How can we show the make-up of the total profit across all the states?

We can’t use a pie chart or a stacked column chart, but we can use a Waterfall chart.

Select the range A2:B7.

Click the Insert ribbon and click the Recommended Charts icon.

Then click the All charts tab and then the Waterfall option near the bottom of the list.

Click the last column in the chart and then click it again, then right click it and choose Set as Total.

Job done. – see below.

I removed the grid lines and entered the Chart title.

You could tweak it further as shown below. I think it looks cleaner and less cluttered.

Changes made

• changed to the format on the source table to currency
• deleted the left axis
• centered the data labels and made the font white and bold
• removed the line from the bottom axis