|
|
Peltier Tech
Excel Dashboards
Books at Amazon.com
|
In Cell Charting with Worksheet Formulas
Lately there has been great interest in lightweight in-cell charting, using worksheet formulas and sometimes a special font, to display values graphically. John Walkenbach showed this technique a long time ago, and the Juice Analytics blog recently had an extensive discussion in Lightweight data exploration in Excel. While reading my recent page on Dot Plots, my colleague Debra Dalgleish suggested I include a demonstration of cell formula dot plots, so I wrote some examples of in-cell bar charts and dot plots described below. Note: Firefox 3 does not render "special" fonts like Symbol, Wingdings, or Webdings in a manner expected by most users. It has something to do with TTF fonts not being Unicode compatible. Bottom line: the Firefox developers have discontinued support for TTF fonts, and there seems to be no workaround. Pretend the lower case Ls below are black circles and the lower case Ns are black squares. In-Cell Bar Charts
The configuration required to make these in-cell bar charts are as follows:
In-Cell Dot Plots
The configuration required to make these in-cell dot plots are as follows:
In-Cell Charts with Changing Scales
There is a lot of flexibility in this approach. You can change the "scale" of the axis by using a multiplier. For example, the Arial pipe symbols in column C are rather thin, so you may want to double their number. Alternatively, you may want to halve the number of heavy wingdings blocks in column D, by using these adjusted formulas:
Conditionally Formatted In-Cell Charts
You can use worksheet conditional formatting to highlight certain values in different colors. For example, in the bar chart in column C, suppose you want values less than 10 to appear red, and those 20 and greater to appear blue. Select C2:C17, then choose Conditional Formatting from the Format menu. For Condition 1, select Formula Is in the dropdown, and enter the formula =$B2<10. Then click the Format button, and on the Font tab, change the color to red. Click the Add>> button, and for Condition 2, select Formula Is in the dropdown, and enter the formula =$B2>=20. Then click the Format button, and on the Font tab, change the color to blue. Press Enter, and you've got a conditional chart. You can produce a conditional chart with different markers with an expanded formula. The dot plot in column D can be changed to show "x" for numbers less than 10 and "+" for numbers at least 20 by using this formula in cell D2 and filling it down: =REPT(" ",B2-1)&IF(B2<10,"x",IF(B2>=20,"+","o")) Naturally you could combine both approaches. |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2010. All rights reserved. |
