Excel TipsUsing Worksheet Cells to simulate a graphOne 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 conditionsConditional 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 both the use of 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 was different for each condition (click on the Format… button).
Figure 2 Cells D1:F1 were set up similarly. In each condition, the value changed by 1 and a different color was selected for the cell Pattern. So, D1 handled values 4 through 6, E1 7 through 9, and F1 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.
|
