Excel Video 106 reviews Error Bars and shows how to use custom Error Bars to show more data on the same chart. Hats off for the idea in this video to John Walkenbach, whose book on Excel 2007 Charts has given me several good charting ideas.
Typically Excel uses Error Bars to chart a margin for error in data, such as survey that has an error margin of plus or minus 3%. I hope your financial data doesn’t have a big margin for error we need to chart, but can use custom error bars to show the range of a set of data on a chart. In this example, we charted the average number of prescriptions filled each day during each of five consecutive weeks. Charting the average number of prescriptions per day in a week masks some pretty wide day-to-day variations in the number of prescriptions.
To generate the Error Bars, first I calculate the maximum and minimum number of prescriptions in a week using Excel’s MAX and MIN functions. The difference between the maximum value and my weekly average is in a row I call Plus. The difference between the weekly average and the minimum value is in a row I call Minus. By putting Plus and Minus in as the values for my custom Error Bars, I can show the range of data (highs and lows) on the same chart I show the average data.
This is my last Excel Video using Excel 2007. Excel 2010 is out and has some cool new features. I’ll start by demonstrating Sparklines, a new way to create small charts that fit in a single cell. The jump from Excel 2007 to Excel 2010 is nothing like the learning curve from Excel 2003 to Excel 2007. Even if you don’t upgrade to Excel 2010 for a while, you’ll be able to follow these Excel Videos in Excel 2010 just fine. Once you see some of the cool features in Excel 2010 like Sparklines and Slicers, you’ll start thinking about Excel 2010.
