Want to shade groups comprising a constant number of rows in an alternating format (that is, highlighting every other 4 rows, every other 5 rows and so on)? Conditional Formatting can be
used alongside a formula in the CEILING, ISEVEN and ROW functions.
To begin with, you should prepare a table on which you wish to highlight groups containing a specific number of rows. It may look like the table shown below:
Figure 1: Prepared table ahead of shading alternative groups of n rows
Having prepared a table like the one above, you can use the formula below to carry out the operation.
The details of the formula are explained below including how to vary it to suit various situations.
Explanation of the Formula
In the formula above, ISEVEN, CEILING, and ROW represent the respective functions, while offset is used to make the numbering of the cells to start from 1 irrespective of how many rows
were used before the actual first item in the table. In the table above, the first item in the table is the pupil named “Fred” and it is in row number 5. However, in reality, this should be item
number 1. The offset tool is used to ignore the first 4 rows so that the 5 th row now counts as row number 1. In this case, since it is the first 4 rows that were used, offset is “4”.
The element “n” is the number of rows to be highlighted as a group. In our examples, “n” is 3. This means the rows in the table will be shaded in alternating groups of 3.
In that case, the more definite formula will look like this
Application of the Formula
After writing this formula, you need to use it together with Conditional Formatting, a function found under Home on the Menu Bar. However, before going to Conditional Formatting, highlight the table as shown in Figure 2 below.
Figure 2: Highlighting the table
Figure 3: Accessing the tool of Conditional Formatting in Excel
- Click on Conditional Formatting.
- On the menu that displays, a point on Highlight Cells Rules.
- Follow the list that displays down and clicks on More Rules.
The dialogue box will appear as shown below:
Figure 4: Setting a new rule for Conditional Formatting
- Enter this formula
=ISEVEN(CEILING(ROW()-4,3)/3)the box Format values where this formula is true.
- Next, click on Format and then click on the Fill tab in the dialogue box that appears.
- Select the color with which you wish to shade the selected cells and click on OK.
Figure 5: Selecting the color for selective shading
- Finally, click on OK in the New Formatting Rule dialogue box.
The color you have selected will be applied to those cells that fall into the range determined by your formula. See Figure 6 below:
Figure 6: Results displayed by Conditional Formatting in conjunction with the ISEVEN, CEILING, and ROLE functions.
Note that you can alter this formula to suit various other conditions. For example, you can decide to shade alternating groups of 2, 4, 5 or 6.