Use conditional formatting to change cell or font colors for the data you want to highlight. Quickly apply conditional formatting with Excel’s built-in formatting rules. Or customize the formatting by adding a formula to a conditional formatting rule.
Remark † The instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365 and Excel for Mac.
- Shading rows and columns in Excel
- Shading worksheet rows in Excel
- Interpretation of the MOD Formula
- Shaded columns instead of rows
- Change shadow pattern
Shading rows and columns in Excel
The advantage of using a formula to add row shading is that the shading is dynamic, meaning it changes as the number of rows changes. When lines are inserted or deleted, the shading of the lines is adjusted to maintain the pattern.
Alternative strings are not the only option. By slightly changing the conditional formatting rule, the formula overshadows any string pattern. It also obscures columns instead of rows.
Shading worksheet rows in Excel
The first step is to select the range of cells you want to shade, as the formula will only affect those selected cells. The statement to shade rows with conditional formatting uses the formula:
= MOD (СТРОКА (), 2) = 0
To apply conditional formatting with a formula:
Open an Excel sheet. Use a blank slate to follow this tutorial.
Select a range of cells on a sheet.
Select At home †
Select Conditional Formatting †
Select new rule to open the dialog † †
Select Use a formula to determine which cells to format †
In the Format value where this formula is true text boxes, enter the formula: =MOD(ROW(),2)=0 †
Select Format to open the Format Cells dialog box. Except Mac wherever you want Format with †
Open a tab To fill and choose a color for the alternate strings. When you’re done, click Okay to return to the New Formatting Rule dialog box.
Select Okay to close the New Formatting Rule dialog box and return to the worksheet.
A conditional formatting rule that contains a formula is applied to the worksheet.
Alternate rows in the selected range are shaded with the selected fill background color.
Interpretation of the MOD Formula
The designed template depends on the MOD function in the formula. MOD divides the row number (determined by the ROW function) by the second number in parentheses (2) and returns the remainder or modulo.
= MOD (СТРОКА (), 2) = 0
At this point, the conditional formatting takes over and compares the modulus to the number after the equals sign. If there is a match (when the condition is TRUE), the row is shaded. If the numbers on either side of the equal sign do not match, the condition is FALSE and no hatching occurs for that row.
The condition = 0 in the formula specifies that the first row in the range is not shaded. This is because this line often contains headers that have their own formatting.
Shaded columns instead of rows
To shade alternate columns, change the formula used to shade alternate rows. Use the COLUMN function instead of the ROW function in a formula. Column shading formula:
= MOD (колонка (), 2) = 0
And the result looks like this:
Change shadow pattern
To change the hatch pattern, change one of the two numbers in the formula.
To display a row from the first row instead of the second, at the end of the formula, change the value from 0 to = 1 †
If you want to shade every third or fourth row instead of alternating rows, change the 2 in the formula to 3 or 4 †
The number in the parentheses is called the divisor because it is the division in the MOD function. Division by zero is also not allowed in Excel. If you enter 0 in parentheses instead of 2, no hatching will appear in the range.
You can also change the pattern by changing the conditional or comparison operator (=) used in the formula to a less-than sign ( † For example: = change 0 to (less than 2), the two lines are shaded together. Change = 0 in and the hatching is done in groups of three.
The only caveat to using the less operator is to make sure the number in parentheses is greater than the number at the end of the formula. If not, each row in the range is shaded.