You are on the Home/Excel/Tutorials/Worksheet as Chart page

Using Worksheet Cells to simulate a graph

One overlooked capability is the ability to format Excel worksheet cells so that the final result is that they convey information visually rather than through numbers. There are many applications of this technique such as creating a Gantt chart, or managing conditional formatting for more than 3 conditions.

Conditional Formatting for more than 3 conditions

Conditional Formatting works well for up to three conditions. But even when the number of conditions exceeds that limit, it is possible to do without any programming support. For example, one possible way to show twelve possible rankings through color is shown in Figure 1.

Figure 1

The effect requires no programming and relies on just a set of conditional formats.

To get going, set the column width for columns C:F to 1.

Set up Cell C1 (Format | Conditional Formatting...) as shown in Figure 2. In the formula specified, note the use of both a relative-absolute address for cell B1 and the change in the constant (it increments by one in each subsequent condition). Also, the color of the cell's pattern is different for each condition (click on the Format... button).

Figure 2

Set up cells D1:F1 similarly. In each condition, the value differs by 1 and the cell pattern has a different color. So, D1 handled values 4 through 6, E1 values 7 through 9, and F1 value 10 through 12.

Now, copy C1:F1 down to rows 2:12.

As the values in B1:B12 change so do the colors of the cells in columns C:F.