Peltier Technical Services, Inc.
- Custom Excel Solutions
Jon's Excel and Charting Pages
- Jon's Charts
- Tutorials
- Excel
- Site Index and Search
- RSS
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages |
|
Horizontally Banded Chart Background.A chart could be made more informative by selectively shading regions of the background with different colors. For example, a run chart may show colored bands to indicate standard deviations of a process value from the mean. Excel only provides the ability to add one color to the background (the Plot Area), but multiple colors can be added by creating a combination chart with added area chart series colored as desired. This tutorial shows how to construct such a chart. The Basic Data and ChartThe table below shows a simple table of data pairs made up of a date and the value at that date. Above the table, the average is shown in cell B1 and the standard deviation in B2. A simple line chart of this data is shown next to the table. (The Y axis scale parameters in this chart have been manually set so they don't vary during construction of the bands. This is done by double clicking on the axis, and on the Scale tab, unchecking the Auto boxes and entering the desired values where appropriate.)
Data and Construction of the BandsThe table below shows the data used in the area chart series which comprise the bands. The first series, "Below 3 SD", plots the value of the mean minus 3 standard deviations; the formulas in E2 and E3 are =$B$1-3*$B$2. Since this chart uses stacked area series, the rest of the data is simply the standard deviation; the formulas in F2:K3 are =$B$2. No series is needed for "Above 3 SD", since shading of the plot area indicates this range.
Copy the range D1:K3, select the chart, and from the Edit menu, choose Paste Special; select the options New Series, Series in Columns, Categories in First Column, Series Names in First Row. The series are added as new line chart series.
The series need to be plotted on the secondary axis. Double click one of the new line chart series, and on the Axis tab, choose Secondary. In turn, select each of the other added series, and press the F4 key to repeat the last action. The series disappear from view, but a secondary Y axis is added on the right edge of the chart.
The disappearance of the added series is caused by Excel adding a secondary Y axis but no secondary X axis. Add the secondary X axis as follows: On the Chart menu, choose Chart Options, click on the Axes tab, check the box for Secondary Category (X) Axis and choose the Category Option. The new axis is added at the top edge of the chart.
The secondary X axis has two categories, which are offset inwards from the edges of the chart. Extend them outward by double clicking on the secondary category axis, selecting the Scale tab, and unchecking Value Axis Crosses Between Categories.
The new line chart series can now be converted to area chart series. Select any one of the added series, and on the Chart menu choose Chart Type. Select the Area Chart type, and note that the default subtype is Stacked Area, which is what the chart requires. Click OK, then in turn select each of the other added series, and press the F4 key to repeat the last action.
Hide the secondary X axis by double clicking on it, and on the Patterns tab, choose None for Line, Major and Minor Axis Ticks, and Axis Tick Labels. Double click the secondary Y axis, and on the Scale tab uncheck the Category Axis Crosses at Maximum Value checkbox.
Remove the secondary Y axis: On the Chart menu, choose Chart Options, click on the Axes tab, and uncheck the Secondary Y Axis checkbox.
Format the area chart series appropriately. In this chart, All area series are formatted with no line. The plot area is filled with light red and the "Below 3 SD" series is transparent so the plot area shows through. "Below 2 SD" and "Above 2 SD" are filled with light orange, "Below 1 SD" and "Above 1 SD" are filled with light yellow, and "Below Mean" and "Above Mean" are filled with light green and light blue respectively, to provide a slight contrast at the mean.
|
|
Peltier Technical Services, Inc. Jon's Excel and Charting Pages Peltier Technical Services, Inc., Copyright © 2008. All rights reserved. |
Waterfall Chart
Box and Whisker
