Conditional formatting can be used to add dynamic row shading to rows of data in an Excel worksheet.
The number of shaded rows and their frequency is easily changed because the shading is set using the following formula:
= MOD ( ROW(), 5 ) <2
The above formula was used to add bands of shading in the image above.
Interpreting the Formula
How this formula is read by Excel is:
- the number 5 in the formula determines that the shading pattern repeats every five rows in the selected range
- the condition of < 2 in the formula determines that two rows out of five will be shaded
In the above image, there are ten rows in the range, shading is therefore applied to two rows in each group of five.
Changing the number of rows shaded or the frequency with which the shading pattern repeats will change the shading pattern in the worksheet.
Another advantage to using the formula to add row shading is that the shading is dynamic which means it changes if the number of rows change.
If rows are inserted or deleted the row shading adjust itself in order to maintain the pattern.
Shading Rows Example
- Open an Excel worksheet - a blank worksheet will work for this example
- Select cells A15 to H24 to highlight them
- Click on the Home tab of the ribbon
- Click on the Conditional Formatting icon to open the drop down menu
- Choose the New Rule option to open the New Formatting Rule dialog box
- Click on the Use a Formula to determine which cells to format option from the list at the top of the dialog box
- Enter the following formula in to the box below the Format values where this value is true option in the bottom half of the dialog box:
- Click the Format button to open the Format Cells dialog box
- Click the Fill tab to see the background color options
- Choose a color to use for shading the rows of the selected range
- Click OK twice to close the dialog box and return to the worksheet
- Two rows out of every five should now be shading in the selected range
Note: Depending on the rows selected, the shading pattern changes.
For example, if rows 1 to 10 are selected, the shading pattern will start with a single shaded row (row 1) followed by three unshaded rows, then two shaded rows.
This occurs because the formula checks to see if the remainder of the MOD function is less than 2 - and the remainder changes as the row number changes.
If the remainder is less than 2, the row is shaded. If is not the row is left unshaded.