Home > Excel > Tutorials > Worksheet as Chart
Google
Web This Site

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

Starting with Excel 2007, Microsoft made several improvements to its implementation of conditional formatting.  One of these improvements was to relax the limit of 3 on the number of conditional formats specified for a cell.  This means that all the color conditions can be specified in a single cell rather than requiring N/3 cells for N conditions (the example below uses 4 cells for 12 conditions).

In Excel 2003 and earlier, 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

Download the sample file.

Recent Comments

From Dan on March 3, 2013:

I rated this page a 7 because I could not follow along with how to create cells with 12 conditions.   I only need to use 4 conditions however, all the solutions I have found will not work with cell containing formulas.  Your solution had promise however,  I could not follow along.   Could you please post and example of a spreadsheet.    I would be so grateful.

Multiple conditional formatting conditions in Excel 2003

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. 

 

Share your comments