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.
- Select the first two rows of the data table and then create a stacked 2-D column chart with no legend.
- 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.
Other posts you may enjoy:
- Excel – copying and pasting ranges with hidden cells
- Excel – Save time reorganising data lists in Excel
- Excel – Excel shortcut tips using Ctrl, Shift and the arrow keys
- Excel – Paste Values shortcut in Excel