PROBLEM: Charting data with a starting and ending point. This method of charting is sometimes referred to as floating and is often used to create waterfall charts.
SOLUTION: Quickly create a floating chart.
Excel 2010 & 2013 – Creating a floating (waterfall) chart
Note: If you are using Excel 2016 please see note and link at the bottom of the page
Recently I had the pleasure of working onsite with an Executive Assistant who needed assistance with floating charts. She totally impressed me with how she had painstakingly measured and drawn a waterfall chart in PowerPoint using the drawing tools. Her precision was amazing. I totally impressed her by showing her how she could create the same chart in less than 5 minutes using the Charting tools in Excel. Here’s how we did it.
Creating a floating chart
A floating bar or column chart displays data using a minimum and maximum value, therefore the series do not connect to an axis, giving the appearance of “floating”. To create your own floating charts:
- The first step is to enter the data into a worksheet. Record the lower and upper values for each series. Place a row in between that calculates the difference between the upper and lower values as shown below in row 5.
2. Select the first two rows of the data table and then create a stacked 2-D column chart with no legend.
3. Right-click the bottom data series and then select Format Data Series. Change the format of the bottom data series so that it has no Fill and no Border line. The chart will now display the data as floating columns, depicting the highest and lowest values for the series.
I’ve created the tutorial below to demonstrate. In the tutorial I’ll show you how this process can be used to chart a running total.
If you are using Excel 2016 check out the new Waterfall Charts available in the Charting options. For information on how to use them visit Microsoft’s Office Support page Create a waterfall chart in Excel 2016.
If you found this post helpful please ‘Like’ us!
If you enjoyed this post you may enjoy:
- Learn how to quickly Undo back multiple steps with two clicks
- Fitting your Excel worksheet on to 1 page when it prints
- Save time reorganising data lists in Excel