How to Create Waterfall Charts in Excel?

Waterfall Chart

Amongst the wide range of data interpretation tools available in Excel, the newest addition to its inventory is the Waterfall Chart and this time it is about handling finances. It is not a common thing to come across a visual interpretation of financial statements these days and MS Excel is said to have taken a step to fill this gap back in 2016.

Yes! Only MS Office versions on or after 2016 will be having this charting option. So, for those of you handling financial data using older versions of MS Office, day in & day out, it is high time to opt for an update. Rather than becoming too technical with the stream of finance, the creation of this chart shall be demonstrated in this article using a monthly budget of an individual.

Also read: How to Create Column Charts in Excel?

Consider this article as QuickExcel’s dedication toward the rising number of personal finance write-ups. Following is the dataset that we shall use for demonstration throughout this article.

Monthly Income Expenses of an Individual
Monthly Income & Expenses of an Individual

In order to get started with creating a Waterfall Chart, one shall search for the Insert tab among the list of available tabs. Hover the cursor over the Tabs section & click on the Insert tab as shown below.

Locating the Insert Tab
Locating the Insert Tab

Once within the Insert tab, get into the Charts section & click on the Waterfall Chart icon.

Selecting the Waterfall Chart Icon
Selecting the Waterfall Chart Icon

A drop-down menu shall now appear with a list of charts to choose from. Select the waterfall chart for plotting the income & expenses in the given dataset. One can also see a concise description appearing when the cursor hovers over the corresponding icon as shown below.

Selecting the Waterfall Chart Option
Selecting the Waterfall Chart Option

A chart now appears plotting the values within the selected range. But something doesn’t quite add up here!

Chart with Default Selection Appears
Chart with Default Selection Appears!

Having a glance at the legend, one can tell that all the entries given in the charts are taken as increases, whereas all the items except for Income & Balance are expenses. So, to convey that to MS Excel, one ought to include a negation within all the values except for Income & Balance as shown below.

Including Negation within the Dataset
Including Negation within the Dataset

Now values updated in the dataset instantly reflect in the chart too by the appearance of the orange bars within the plot area indicating the expenses.

Chart Gets Updated with
Chart Gets Updated with Expenses

Still, one could see that there is no bar with the colour that indicates Total in the legend. The values under the ‘income’ & ‘balance’ can be categorized under Total since these are the amount that remains in the hand of the individual at the start & end of the month respectively. So, to change that left click on the blue bar for income. This selects all the data bars.

All Data Bars gets Selected
All Data Bars get Selected

Now click again on the income bar, so that only that & nothing else get selected as shown below.

Only Income Bar Selected
Only Income Bar Selected

It is time that one right-clicks to choose the Format Data Point option from the menu that appears as shown below.

Selecting Format Data Point Option
Selecting Format Data Point Option

Once done, a panel shall appear on the right of the worksheet. Tick the Set as total option as indicated below.

Ticking Set as total Option
Ticking Set as total Option

Once done the colour of the ‘income’ bar immediately gets updated from blue to grey as shown below.

Income Bar Set as Total
Income Bar Set as Total

The same sequence is to be repeated for the ‘balance’ bar too, to change it into a Total.

Balance Bar Updated
Balance Bar Updated!

The Chart Title can also be changed by double-clicking it & replacing the default title with the desired one. So, now we have a waterfall chart plotting one’s monthly income & expenses.

Updated Waterfall Chart
Updated Waterfall Chart


Hope this article helped you understand how to create a waterfall chart in MS Excel & has given you what you came looking for! Have a look at this article to know how to create a Contour Plot in MS Excel. There are numerous equally interesting & informative articles in QuickExcel that serve to be of great help for your path to mastering MS Excel. Until then, cheers!