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.

**=ISEVEN(CEILING(ROW()-offset,n)/n)**

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 **=ISEVEN(CEILING(ROW()-4,3)/3)**

**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

the box**=ISEVEN(CEILING(ROW()-4,3)/3)****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**.

## Leave a Comment