Creating a Chart Using Conditional Formatting in Worksheet Cells in Excel 2007:-


Think about the Excel worksheets on your computer. Basically, an Excel worksheet is a very large sheet of graph paper, with 17 billion tiny little boxes.

You can create plenty of charts right on a worksheet, without ever invoking the Excel charting engine. First Figure (given below) shows such a chart. The gray bars in D2;R6 are drawn based on conditional formatting rules in response to data entered in B2;C6. Note how the bars have expanded or contracted in the bottom image when starting or ending years are adjusted.



(The gray bars are created through a series of conditional formatting rules.)

With Excel 2007’s limit of only three rules for prior conditional formatting, it was not quite possible to handle every condition by using Excel’s conditional formatting. However, it is possible to manually create this type of chart. The years stretch from cell D1 and would extend as far right as necessary. To make the chart narrow, you can select Vertical Text from the Orientation drop-down in the Home ribbon. Look into the second Figure (given below). You can then resize the columns to a column width of 2.



(The Vertical Text setting is located behind the diagonalab icon in the Home ribbon.)

The logic for creating the bars is as follows:

1. If the start and end year are equal and they match the year in row 1, color the cell gray, with borders on all four sides.

2. If the start year in column B matches the year in row 1, color the cell gray. Include left, top, and bottom borders.

3. If the end year in column C matches the year in row 1, color the cell gray. Include right, top, and bottom borders.

4. If the year in row 1 is greater than the start year and less than the end year, color the cell gray, with top and bottom borders but no side borders.

You follow these steps to create the conditional formatting rules for this logic:

1. Select the range D2:R6. (Although you have selected many cells, you write the conditional formatting rules as if they applied to the top-left cell, D2.)

2. From the Home ribbon, choose Conditional Formatting, Manage Rules. Excel displays the Conditional Formatting Rules Manager dialog.

3. Click the New Rule button. Excel displays the New Formatting Rule dialog.

4. In the top half of the dialog, choose Use a Formula to Determine Which Cells to Format. The bottom half of the dialog box redraws to show Format Values Where This Formula Is True.

5. Enter the formula =$B2=D$1 for the first condition. This formula checks whether the start year in column B of the current row is equal to row 1 of the current column. It is crucial that you enter dollar signs before the B and 1 but not before the 2 and D.

6. Click the Format button in the dialog. On the Fill tab, choose a fill color for the cell. On the Border tab, click None and then click the Top, Bottom, and Left. Click OK to close the Format Cells dialog. Click OK to close the New Formatting Rule dialog. If you click the Apply button, you should see that the first cell for each bar is drawn in the worksheet.

7. Repeat steps 3 and 4 and then enter the formula =$C2=D$1 for the second rule; this is the formula to format the last cell of the bar. The Format selection is the same color fill as in step 6. On the Border tab, choose None, Top, Bottom, and Right.

8. Repeat step 7 and then enter the formula =AND($B2<D$1,$C2>D$1) for the third rule; this is the formula to format center cells in the bar. The Format selection is the same fill as in step 6. On the Border tab, choose None, Top, and Bottom.

9. Repeat step 7 and then enter the formula =AND($B2=$C2,$B2=D$1) for the last rule; this is the formula to find where the model was only available for a single year. The Format selection is the same fill color as in step 6. On the Border tab, choose Outline. Rules are added to the beginning of the rule list. By entering this rule last, you ensure that it is evaluated first.

At this point, your Conditional Formatting Rules Manager dialog should look similar to the one in the third Figure (given below).



(Four rules create the chart. One drawback: the dot for row 6 appears outside the print area.)

This example is complicated by the fact that you draw borders on the appropriate edges of each cell. If you instead used a solid black fill, you could create the effect with a single rule, using the formula =AND($B2<=D$1,$C2>=D$1), as shown in the fourth Figure (given below).



(If you aren’t concerned with the cell borders, the chart can be reduced to a single conditional formatting rule.)