Microsoft Office Tutorials and References

In Depth Information

**Plotting with ASCII characters**

Plotting with ASCII characters

Figure 8-64 shows a chart that consists of text characters that comprise horizontal

bars. Columns E and G contain formulas that graphically depict monthly budget

variances by displaying a series of characters in the Wingdings font. The number of

characters displayed is determined by an IF function.

Figure 8-64: This chart is made up of text characters displayed in cells.

The data used in this chart are in columns A:C. Formulas in column D calculate

the percent difference between the Budget and Actual amounts. Columns E and G

contain formulas that use the value in column D. The formulas for columns E and

G follow. I copied these formulas down to accommodate the 12 rows of data.

E2: =IF(D2<0,REPT(“n”,-ROUND(D2*100,0)),””)

G2: =IF(D2>0,REPT(“n”,-ROUND(D2*-100,0)),””)

The key to this technique is the use of the REPT function, which displays a

character (specified in the function’s first argument) the number of times specified in

the function’s second argument. The cells that display the bars use the Wingdings

font — the letter
n
in this font produces a square block. In column E, the text is

aligned to the right. In column G, the text is aligned to the left.

Depending on the numerical range of your data, you may need to change the

scaling. Experiment by replacing the “100” value in the formulas. You can, of

course, substitute any character you like for the
n
in the formulas to produce a

different character in the chart.

Plotting with conditional formatting

Excel’s conditional formatting feature is a handy tool that enables you to format

cells based on their content. Many users don’t realize it, but conditional formatting

can also be used to format cells based on types of conditions rather than just on the

contents of the cells.