Software

An easy technique for creating a floating column chart in Excel

This quick Excel technique turns an otherwise complicated charting requirement into a simple task.

Standard charts don't always represent data in its best light. Floating column charts are a good example. In this type of chart, a single column seemingly floats between a minimum and maximum value instead of being anchored to the axis. I've seen some convoluted solutions for creating such a chart, but it doesn't require a complex solution.

Fortunately, there's a quick method: create a stacked column chart where one of the values is literal and one is the difference between the high and low values. The chart in Figure A illustrates this idea perfectly.

Figure A

Using literal values, you can try to create the chart, as follows:

  1. Select the data to be charted: A2:C23. (Figure B)
  2. Click the Insert tab. In Excel 2003, click the Chart Wizard on the Standard toolbar.
  3. From the Columns dropdown in the Charts group, select Stacked Column (the second option in the first line).

Figure B

There's nothing wrong with the resulting chart, but if you're trying to represent the range from the lowest to the highest temperature, it doesn't really work. What you're really after is a single column that begins at the lowest temperature and extends to the highest temperature. Even if you delete the low series, the chart doesn't work because of the axis values. For example, the high series column for November 1 starts at 17, so far so good. But, it adds the high temperature of 41, it doesn't stop at 41. Do you see where I'm heading?

The solution is as simple as adding a third column that returns the difference between the two temperatures, as follows:

  1. Enter the formula =C2-B2 into cell D2.
  2. Copy the formula in D2 to the remaining range, D3:D23.

Now, instead of charting the low and high series or even all three value columns, chart the low values and the new values in column D.  You might be wondering how to chart a non-contiguous range, but it's easy. To do so:

  1. Select A2:B23.
  2. Hold down [Ctrl] and select D2:D23. Doing so creates a non-contiguous selection. (Figure C)

Figure C

Now, repeat the earlier instructions for creating the first stacked column chart. (Figure D)

So far, so good - the top column now starts at the low temperature and stops at the high temperature. All you need to do now is delete the low series.

Figure D

You can't really delete it though; if you do, the high series will drop down to the 0. Instead, you have to hide it (Figure E), as follows:

  1. Double-click any column in the low series to select it and open the Format Data Series dialog.
  2. Click Fill in the left pane.
  3. Click the No Fill option.
  4. Click Border Color in the left pane.
  5. Click the No Line option.
  6. Click Close.
  7. Click anywhere to see the results.

Figure E

Looks are deceiving, but the result is exactly what we want. There's only one visible column and it extends from the low temperature to the high temperature.

At this point, you can (probably) delete the legend. In addition, you might be wondering how to display the degree symbol and the letter F (Fahrenheit) so the axis values better represent the data. (Figure F) Fortunately, that too is easy:

  1. Select any blank cell.
  2. Click the Insert tab. (In Excel 2003, choose Symbol from the Insert menu and skip to step 4.)
  3. Click the Symbols option in the Symbols group.
  4. Highlight the degree symbol and click Insert and then Close.
  5. Highlight the symbol and press [Ctrl]+C to copy it to the Clipboard.
  6. Double-click the axis in the chart to open the Format Axis dialog.
  7. Choose Number in the left pane.
  8. Click Custom in the Category list.
  9. Position the cursor after the first 0 in the actual code.
  10. Enter a double quotation mark character (").
  11. Press [Ctrl]+V to copy the degree symbol from the Clipboard into the code.
  12. Enter F and a closing quotation mark.
  13. Click Add and then Close.

Figure F

If you anticipate reporting temperatures that fall below 0, be sure to add the custom code text to the second section of the code.

Although the problem sounds complicated, the solution is simple. We used a stacked column chart to represent the low series and a formula to represent the high series. Then, we hid the low series. It works great!

Example Excel worksheets to help explain this technique are available for download.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

11 comments
gobrannan
gobrannan

Elegant solution for many uses, thanks for the tip about the axis formatting, BUT... the chart doesn't work for negative numbers. If the low was -10 and the high was 45, the column shows 0 to 55. If the low was -10 and the high was -5, the column shows 0 to 5.

hineses@hotmail.com
hineses@hotmail.com

To enter the degree symbol (°), press and hold the key while entering the digits "0176" on the numeric keypad.

guy
guy

"you might be wondering how to display the degree symbol " There is a much simpler method than finding, copying and pasting a symbol. Simply put your typing-curosr where you want the degrees symbol to appear, hold down the ALT key and type 248 on the numberic keypad. This is useful for other symbols too. ALT248= ° , ALT234=?, ALT230=µ , ALT234=?, ALT241=±.

dahveedmeir
dahveedmeir

Susan, love your column; I've been following it for a couple of years. Is there a reason why you cannot change the fill and border of the chart in figure B? I am not clear of the benefits of a third column, noting the difference between column B and column D.

jody.burton
jody.burton

This could be done with a High-Low-Close chart, using an Average value and label as the close, if that was info you wanted to show.

lekopperud
lekopperud

You can hid the lower bar simply by selecting the lower bar and setting the fill color to "no fill" and the border color to "no line." The lower bar is now hidden. Almost no effort! Sometimes the simple solution is not obvious.

sparent
sparent

Thanks for the great tip. This is very kuhl!

cherieat
cherieat

This is great and a straightforward way to get to the outcome. Thanks!

adgarg
adgarg

Hello, Excellent article. The best part was adding the °F sign in the Axis. ppriciate your efforts in documenting the steps.

dahveedmeir
dahveedmeir

I just went back and completed the chart according to your instructions. When you see the charts side-by-side the reason for the third column becomes clear. What I wasn't seeing was that the red column in figure B was nowhere near to being correct. Although it starts in the right place it ends somewhere around 60° instead of at 41° (as it says in the chart). Once again, great column, great tip! Thank you for being here with all your help.

Editor's Picks