How to create a floating column chart in Excel

While I love Excel, I am the first to admit the standard charts are not always the most effective way to represent your data.  Floating column charts for example are not a standard chart in Excel 2010 and IMHO should be. In this type of chart, a single column seemingly floats between a minimum and maximum value instead of being anchored to the axis. This is really useful when you want to display a range of values (High to Low or Minimum to Maximum) for a series of data.

Let’s imagine, for example, in January the temperature was between 6 and 39 degrees fahrenheit.  Rather than having two series of lines showing the low values and the high values by month, you can use a floating column chart to show the same thing much more visually as seen below.  How is it done? You can download the example spreadsheet here.

floating column

Step 1 Create your table

Set up your original data table showing the labels for the X-axis, the low values and the high values.

data table 1

Then in the fourth column, calculate the difference between the two values.  So for example, if your titles are in row 1, your Low values are in column B and High values are in column C, the calculation in cell D2 would be:


Copy and paste the calculation down the rows of your table until it looks something like this:

data table 2

Step 2: Create the chart

Now highlight columns A and B of your table (your X-axis labels and the Low values).  While holding down the Ctrl key, highlight column D (the calculations).  You should now have three columns highlighted.  With them highlighted, click on Insert > Chart > Stacked Column.  This is the second chart in the 2-D column list as shown below.

stacked column

Once you’ve clicked on the chart type, Excel should create a chart for you.

stacked column chart

Step 3: Format the chart

Left click on the Low series (shown in blue in the chart above).  Then press Ctrl+1 to bring up the formatting menu box.  Click on the Fill tab and change the fill to No Fill

format chart fill

Click on the Border Color tab and change the Border Color to No Line

format chart border

Then press the OK button.  Your chart is done!  Don’t hesitate to let me know what you think!  You can download the sample spreadsheet here.

+Alesandra Blakeston

2 thoughts on “How to create a floating column chart in Excel

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s