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.
Using literal values, you can try to create the chart, as follows:
- Select the data to be charted: A2:C23. (Figure B)
- Click the Insert tab. In Excel 2003, click the Chart Wizard on the Standard toolbar.
- From the Columns dropdown in the Charts group, select Stacked Column (the second option in the first line).
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:
- Enter the formula =C2-B2 into cell D2.
- 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:
- Select A2:B23.
- Hold down [Ctrl] and select D2:D23. Doing so creates a non-contiguous selection. (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.
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:
- Double-click any column in the low series to select it and open the Format Data Series dialog.
- Click Fill in the left pane.
- Click the No Fill option.
- Click Border Color in the left pane.
- Click the No Line option.
- Click Close.
- Click anywhere to see the results.
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:
- Select any blank cell.
- Click the Insert tab. (In Excel 2003, choose Symbol from the Insert menu and skip to step 4.)
- Click the Symbols option in the Symbols group.
- Highlight the degree symbol and click Insert and then Close.
- Highlight the symbol and press [Ctrl]+C to copy it to the Clipboard.
- Double-click the axis in the chart to open the Format Axis dialog.
- Choose Number in the left pane.
- Click Custom in the Category list.
- Position the cursor after the first 0 in the actual code.
- Enter a double quotation mark character (").
- Press [Ctrl]+V to copy the degree symbol from the Clipboard into the code.
- Enter F and a closing quotation mark.
- Click Add and then Close.
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!