## Conditional Formatting for Creating Patterns

In my earlier post on conditional formatting, I mentioned that it can also be used to generate or display patterns based on the content of certain cells. In this post I will illustrate this by generating a seven-segment display of a digit entered in a particular cell, say cell A1. This is an interesting example as it integrates conditional calculations and formatting. The seven-segment display is common to household appliances such as microwaves, clock radios, and calculators, and is used to display numeric information such as time and temperature. A seven-segment display for a single digit is made of seven segments arranged in a particular way as shown in Figure 1. By selectively turning on and off different segments, all ten digits from 0–9 can be displayed as shown in Figure 2. By linearly arranging multiple seven-segment displays, we can show numbers consisting of multiple digits.

**Figure 1:** A seven-segment display with numbered segments.

**Figure 2:** By selectively turning on and off different segments, a seven-segment display can create all ten digits from 0–9.

To display the digit present in cell A1, we first shape a set of worksheet cells that will function as segments. To do this, let us merge adjacent cells to form segments of proper shape and orientation. For example, by merging cells C2:E4 we form Segment 1 of the display and by merging cells F4:F11 we shape Segment 2, and so on. The result of cell merging to form seven segments is shown in Figure 3 where I have highlighted the borders of merged cells to show clearly the seven segments of the display.

**Figure 3:** Merged cells to form seven segments.

Let us name cell A1 as DIGIT to describe the logic for each segment as shown below. Since segments are highlighted based upon the digit to be displayed, we can set conditional formatting for each segment to control when the segment should be highlighted. For example, we see from Figure 2 that Segment 1 comes on whenever the digit to be displayed is neither 1 nor 4; thus the logic for Segment 1 can be written as follows:

=NOT(OR(DIGIT=1,DIGIT=4))

This formula will return TRUE whenever DIGIT, the digit entered in cell A1, is any digit other than 1 and 4. Similarly, we can see from Figure 4.17 that Segment 2 comes on whenever the number in cell A1 is neither 5 nor 6. Thus, the logic for Segment 2 can be written as:

=NOT(OR(DIGIT=5,DIGIT=6))

Similarly, the logic for rest of the segments can be expressed through the following formulas:

=NOT(DIGIT=2) for Segment 3

=OR(DIGIT=0, DIGIT=2, DIGIT=3, DIGIT=5, DIGIT=6, DIGIT=8, DIGIT=9) for Segment 4

=OR(DIGIT=0, DIGIT=2, DIGIT=6, DIGIT=8)for Segment 5

=OR(DIGIT=0, DIGIT=4, DIGIT=5, DIGIT=6, DIGIT=8, DIGIT=9)for Segment 6, and

=NOT(OR(DIGIT=0, DIGIT=1, DIGIT=7))for Segment 7

Once we have the logic for each segment, the next step is to enter the logic for each segment for conditional formatting. For Segment 1, we select cell C2 which represents this segment in our worksheet, and click the Conditional Formatting button to select the New Rule option. Since we are using a formula to specify the condition, we use the formula option and enter the formula conveying the logic for Segment 1 in the formula box, and select red color to show Segment 1. At this stage the New Formatting Rule dialog box should appear similar to that shown in Figure 4.

**Figure 4:** Conditional Formatting for Segment 1.

In a similar fashion, we select the cells corresponding to the rest of the segments, and assign conditional formatting through their respective formulas. Once completed, we can check our display by entering any digit from 0–9 in cell A1. We will, for example, see the display in Figure 5 if we enter 3 in A1.

**Figure 5:** The seven-segment display when a value of 3 is entered in cell A1.

In developing the above logic, we have assumed that a single digit number will be entered in cell A1. Let us see what will happen if something else, for example a text string or a number greater than 9, is entered in cell A1. You will notice that segments numbered 1, 2, 3, and 7 come on whenever we enter anything other than a digit. This is caused by the logic we are using for these segments. For example, segment 1 logic is given by the formula =NOT(OR(DIGIT=1, DIGIT=4)). This logic returns TRUE whenever cell A1 does not contain 1 or 4. As long as we are working on good faith that only a digit between 0 and 9 will be entered in cell A1, the formula works fine because if the digit entered is not 1 or 4, it must be one of the remaining digits. A better way of defining the logic for segment 1 would be to check for the presence of digits for which the segment should come on and not infer for their presence by the absence of digits 1 and 4. Such a way of writing the logic would ensure that the segment displays come on only when a valid digit is entered. Thus, we should rewrite the formulas for segments numbered 1, 2, 3, and 7 as follows:

=OR(DIGIT=0,DIGIT=2,DIGIT=3,DIGIT=5,DIGIT=6,DIGIT=7,DIGIT=8, DIGIT=9)for Segment 1

=OR(DIGIT=0,DIGIT=1,DIGIT=2,DIGIT=3,DIGIT=4,DIGIT=7,DIGIT=8, DIGIT=9)for Segment 2

=OR(DIGIT=0,DIGIT=1,DIGIT=3,DIGIT=4,DIGIT=5,DIGIT=6,DIGIT=7, DIGIT=8, DIGIT=9)for Segment 3, and

=OR(DIGIT=2,DIGIT=3,DIGIT=4,DIGIT=5,DIGIT=6,DIGIT=8,DIGIT=9)for Segment 4.

Compared to our earlier formulas for these segments, the current formulas have more arguments; however, these formulas return TRUE only when one of the specified digits is present and they do not work under the good faith assumption that only a valid input will be entered in cell A1. These or similar formulas should always be preferred over the formulas that draw inferences from negativity.

I hope you like my example of using conditional formatting to display patterns. If you can think of another example, please let me know.

## Recent Comments