Microsoft Office Tutorials and References
In Depth Information
Plotting with ASCII characters
Figure 8-65 shows an example that takes advantage of conditional formatting to
produce a crude bar chart, displayed directly in worksheet cells. Note that the
column widths in this worksheet are very narrow, producing square cells (except for
columns C and D, which store data used by the chart).
Figure 8-65: This bar chart is produced by conditional formatting in cells.
The worksheet uses two named cells: D1 (named ScaleMin ) and D2 (named
ScaleMax ). These cells control the upper and lower limits for the “axis” scale, in row
4. Cell E4 simply contains a reference to the ScaleMin cell. Cell F5 contains the
following formula, which is copied across to cell AJ4:
=((ScaleMax-ScaleMin)/COLUMNS(F$1:AJ$1))*(COLUMN()-5)+ScaleMin
This formula generates the intervals displayed in row 4. It uses the COLUMNS
function (rather than a literal value) to return the number of columns used in the
chart. Using this function enables the user to insert additional columns without the
need to modify the formula.
The conditional formatting is applied to range F5:AJ30. Figure 8-66 shows
the Conditional Formatting dialog box. As you can see, the condition is based on a
formula:
=$D5>=F$4
Search JabSto ::




Custom Search