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.
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.
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:
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.
Once you’ve clicked on the chart type, Excel should create a chart for you.
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
Click on the Border Color tab and change the Border Color to No Line
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.