|
|
Peltier Tech
Books at Amazon.com
Buy me a coffee If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee. |
Simple Conditional Chart Example.
It's relatively easy to apply conditional formatting in an Excel worksheet. It's a built-in feature on the Format menu, and there many resources on the web to get help (see for example what Debra Dalgleish and Chip Pearson have to say). Charts are a different story. Many people ask how to make a conditional chart, that is, how to change the formatting of a chart's plotted points (markers, bar fill color, etc.) based on the values of the points. This can be done using VBA to change the individual chart elements, but if there are more than a few points, VBA is noticeably slow about making the changes. Also, Excel is designed to plot points and series very quickly from the worksheet. So the following technique works very well without resorting to macros, with the added advantage that you don't have to muck about in VBA. I took the following sample data and charts, and used this technique to apply different formats to each group of 3 (0-3, 3-6, etc.).
I fixed up the spreadsheet range as shown below. In B1:B2, I added labels "min" and "max", and in C1:F2 I entered numbers to serve as limits on the different colored points. In C3:F3 I put my headers "Between X and Y"; I put this formula into C3, then dragged it across to include F3: ="Between "&C1&" and "&C2 Finally, I selected C4:F14, entered this formula into C4: =IF($B4>C$1,IF($B4<=C$2,$B4,NA()),NA()) and pressed Ctrl-Enter so all cells within the selected range will contain the formula. Notice the careful placement of dollar signs to assure that certain absolute rows and columns were maintained in these formulas.
Finally the table looks like this. Each column contains either the value from column B if the value fits within min and max, or the #N/A error if it doesn't fit. If you use zero or any string (even ""), Excel will plot a zero. Using NA() assures that Excel will skip that point without plotting it.
Now simply add the new series to the chart and format the the way you want them to appear. The easiest way is to select the range (C3:F14 to include the headers as legend entries), copy, then select the chart, paste special, by columns, series names in first row. For a Line or XY Scatter chart, I kept the original series, leaving the connecting lines but removing the original markers. The other series have various colors and shapes.
For a Radar (Spider) chart, I followed the same protocol as with the Line/XY chart.
For a Column (or Bar) chart, I removed the original data, then made sure the chart was a Stacked Column type, not a Clustered Column type. Select the chart, and from the Chart menu, select Chart type, and pick the second column chart option, not the first. Then I filled the columns with different colors.
To download a zipped Excel file with this example, click on this link. Other examples of this technique include: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Peltier Technical Services, Inc.Commercial Utilities | Custom Solutions | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2010. All rights reserved. |
