Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

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

In Cell Bar Charts

The configuration required to make these in-cell bar charts are as follows:

Bar Chart 1 (Column C)
Formula: =REPT("|",B2)
Font: Arial

The formula means "Repeat the character '|' as many times as the value in B2".
Enter the formula in cell C2 and fill it down to C17.

     

Bar Chart 2 (Column D)
Formula: =REPT("n",B2)
Font: Wingdings

The formula means "Repeat the character 'n' (or in Wingdings, 'n') as many times as the value in B2".
Enter the formula in cell D2 and fill it down to D17.

In-Cell Dot Plots

In Cell Dot Plots

The configuration required to make these in-cell dot plots are as follows:

Dot Plot 1 (Column E)
Formula: =REPT(" ",B2-1)&"o"
Font: Courier New

The formula means "Repeat the space character as many times as the value in B2 minus one, then the letter 'o'".
Enter the formula in cell E2 and fill it down to E17.

     

Dot Plot 2 (Column F)
Formula: =REPT(" ",B2-1)&"l"
Font: Wingdings

The formula means "Repeat the space character as many times as the value in B2 minus one, then the letter 'l' (or in Wingdings, 'l').
Enter the formula in cell F2 and fill it down to F17.

In-Cell Charts with Changing Scales

In Cell Bar Charts

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:

Bar Chart 1 (Column C)
    =REPT("|",2*B2)

     

Bar Chart 2 (Column D)
    =REPT("|",INT(B2/2))

Conditionally Formatted In-Cell Charts

In Cell Charts with Conditional Formatting

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.

 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2013. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile