Excel 2010 Bible

Chapter 11: Creating Formulas That Manipulate Text

237

You can also use this function to create crude vertical dividers between cells. This example displays

a squiggly line, 20 characters in length:

=REPT(“~”,20)

Creating a text histogram ........................................................................................

A clever use for the REPT function is to create a simple histogram (or frequency-distribution chart)

directly in a worksheet. Figure 11.3 shows an example of such a histogram. You’ll find this type of

graphical display especially useful when you need a visual summary of many values and a standard

chart is unwieldy.

Cross-Reference
The Data Bars conditional formatting feature is a much better way to display a simple histogram directly in
cells. See Chapter 20 for details. n

FIGURE 11.3

Using the REPT function to create a histogram in a worksheet range.

The formulas in column D graphically depict the sales numbers in column B by displaying a series

of characters in the Wingdings font. This example uses character code 61 (an equal sign), which

appears onscreen as a small floppy disc in the Wingdings font. A formula using the REPT function

determines the number of characters displayed. The formula in cell D2 is

=REPT(“=”,B2/100)

Assign the Wingdings font to cells D2, and then copy the formulas down the column to accommo-

date all the data. Depending on the numerical range of your data, you may need to change the scal-

ing. Experiment by replacing the 100 value in the formulas. You can substitute any character you

like for the equal sign character in the formula to produce a different character in the chart.