Create quick mini charts with fonts and formulas

by Jonathan Rabson

Application: Microsoft Excel 2000/2002/2003/2004/2007/2010/2013

Operating Systems: Microsoft Windows, Macintosh

Download: http://download.elijournals.com/excel/201803/pictfont.zip

Excel’s charts are quite powerful. But sometimes you may actually find Excel’s native charts limiting. For example, suppose you want a succession of little charts, like those shown in Figure A, to provide an overview of your data. If you used Excel charts for this task — sized and positioned just so — you’d have to manually make each chart refer to the appropriate data for the corresponding row. We’ll show you how to create just one chart and paste the mini charts into all the rows at once so each copy of the chart reflects the correct information based on its position.

To programmatically add mini charts to our data table, we’ll:

Demonstrate how you can use picture fonts to make charts.

Cover the main techniques you’ll need to know to reproduce our example: the REPT function and text orientation.

Build our example and point out a few caveats you may want to consider.

Raw data doesn’t always get your message across, but pictures can make data easy to absorb at a glance. Our technique will let you add row-specific mini charts to your data table without manually placing and resizing Excel charts.

A: You can create your own quick mini charts right on the spreadsheet to view the data for each row at a glance.

A chart made out of formulas

If the description of our mini charts reminded you of formulas, you’re on to something. Essentially, the charts in Figure A are nothing more than formulas, and that’s why you can paste them into different rows and have them reflect the data for each row. There are many applications of this general technique, but this article will focus mainly on charts.

Use characters as graphics

When you hear about fonts, you likely think of different styles of lettering for text, such as Arial and Times New Roman. However, some fonts map characters to graphic designs, instead of to the more familiar letters, numbers, and other symbols you see on your keyboard. These are called picture fonts. Windows has long come with Wingdings as a standard font, and Windows machines generally also have Webdings, since that font set comes with Internet Explorer. The standard picture font for Macintosh is Zapf Dingbats, but Macs are likely to have both Wingdings and Webdings as well.

Create charts using picture fonts

Once you decide to use picture fonts to create charts, the mechanics are quite simple. You just select the range of cells you want for the chart and set that range to the picture font you want to use. You then create a formula that repeats a picture character a given number of times in one cell, so the number is represented by how many times you repeat the character.

For example, our sample charts shown in Figure A repeat the letter g using the Webdings font, which appears as γ. In our sample chart, we add one g character for each increment representing 10 percent of the respondents who answered our survey a certain way. That means we represent up to 10 percent as g and we represent 20 percent as gg to create a column that’s twice as long.

As usual with Excel, there’s more than one way to automatically display a string of letters. Before replicating our miniature column chart, let’s look at some basic formula techniques you can use to repeat characters a certain number of times.

Convert a number into a string of repeated letters

We’ll create some simple formula examples that should help get you thinking of the possibilities regarding the kinds of charts you can create. For example, suppose you want to string together a number of smiley face characters to represent how many people have contributed to your special charity.

Let’s say that if 1-5 people contributed, you want one smiley face, if 6-10 people contributed, you want two smiley faces, and so on. Using the Wingdings font, when you type J you get ☺. All you need now is a formula that displays the appropriate number of characters.

Use IF functions to control the number of characters

You might be tempted to use the very popular IF function. To do this, you could nest your IF functions as in the formula:

=IF(B2=0,"",IF(B2<=5,"J",IF(B2<=10,"JJ","JJJ")))

This formula produces up to three smiley faces in a cell that has had the Wingdings font applied to it. The formula result accounts for up to 15 people — each smiley face representing up to five people, as shown in Figure B.

The problem with this method is that nested functions can prove difficult to manage. Worse, Excel can only handle a certain number of nested IF functions. If you nest too many in one cell, Excel eventually tells you The formula you typed contains an error, even if your syntax looks perfect.

B: You can create a smiley face pictograph with nested IF functions.

Use REPT to display picture characters

Because of the nested IF function limitations, we recommend that you instead use the REPT function. REPT accepts two arguments — a string and a number. It returns a string produced by concatenating the string in the first argument as many times as the number in the second argument, rounded down to the nearest integer. For example, =REPT("Hi",3.9) returns HiHiHi.

So let’s say you want one smiley face for each individual person, using data from cell B2. You could enter =REPT("J",B2). Notice that, unlike with nested IF functions, the REPT function automatically scales; you don’t have to create separate cases to account for larger numbers in your data. As long as the number of characters generated isn’t greater than the limit of 32,767, =REPT("J",B2) returns the number of smiley faces specified in B2.

Combine REPT and ROUNDUP

There’s one more thing you have to deal with concerning REPT: It rounds the second argument down. If you want a single character in a picture font to represent a range of numbers — say, one smiley face for persons 1-5, and two smiley faces for 6-10 — you probably want to round up rather than down. For example, to determine increments of five, rounding down the result of 4 divided by five would make 4 people look like 0.

ROUNDUP takes two arguments and returns the number you’d get if you rounded the first argument up to the number of decimal places specified in the second argumen[...]