Excel shading - understanding the logic

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

An explanation of the logic behind alternate shading rows or columns in Excel.

If you’re content with this result and don’t need to know the nuts and bolts, class dismissed. If, on the other hand, you’d like to understand how we end up with a checkerboard pattern from this expression, stick with me. Unless you have some programming or a philosophy degree under your belt, trying to get your head around logical expressions can be a little confusing at first.

 

Note that to produce the checkerboard pattern, we’ve used two formulas one after the other; we’ve also dispensed with the final =0 we used in the earlier formulas.

 


Why is this so?

 

Well, instead of comparing the result of an expression to 0, we compare the result of the first expression to the result of the second expression, and determine whether they are equivalent. So, Excel evaluates the first formula and determines whether it is TRUE or FALSE. It then evaluates the second formula and determines whether it is TRUE or FALSE. Finally, it equates the two results and determines whether the end result is TRUE or FALSE.

 

In other words, you end up with four possible intermediate results:

 

1. Both expressions are TRUE.

2. The first is TRUE; the second is FALSE.

3. Both expressions are FALSE.

4. The first is FALSE, the second is TRUE.

 

Now, take the final step and evaluate these four intermediate results. The first and third evaluate to TRUE (that is, TRUE = TRUE is clearly TRUE, just as FALSE = FALSE is clearly TRUE); the second and fourth evaluate to FALSE (TRUE = FALSE is FALSE; FALSE = TRUE is FALSE).

 

So, if you apply the conditional expression =MOD(ROW(),2) =MOD(COLUMN(),2) to cell A1 (where both the row and column number are equal to 1) you get:

 

=MOD(1,2)  =MOD(1,2)

1 = 1 which is TRUE

 

For cell A2: =MOD(2,2) =MOD(1,2)

0 = 1 which is FALSE

 

For cell A3: =MOD(3,2) =MOD(1,2)

1 = 1 which is TRUE

 

For cell A4: =MOD(4,2) =MOD(1,2)

0 = 1 which is FALSE

 

For cell B1: =MOD(1,2) =MOD(2,2)

1 = 0 which is FALSE

 

For B2: =MOD(2,2) =MOD(2,2)

0 = 0 which is TRUE

 

For B3: =MOD(3,2) =MOD(2,2)

1 = 0 which is FALSE

 

For B4: =MOD(4,2) =MOD(2,2)

0 = 0 which is TRUE

 

And so on, with the alternating TRUE/FALSE/TRUE/FALSE in one column and FALSE/TRUE/FALSE/TRUE in the next building up the checkerboard shading.

subs profile e1563205311409 - Excel shading - understanding the logic
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address