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.
Search JabSto ::

Custom Search