This post was published 5 years 7 months 18 days ago. Therefore, it may well be out of date. Do not reply on the contents of this post being accurate. I keep having to look up Excel’s Conditional Formatting feature to automatically shade alternate rows in a spreadsheet.
Therefore, I’m including it here so that I know where to look it up next time I need to use the function.
- Select the range that you want to format
- Choose Format, Conditional Formatting
- In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula:
=MOD(ROW(),2)=0. - Click the Format button, select the Patterns tab, and specify a color for the shaded rows
- Click OK twice to return to your worksheet
Great tip i also found that If you want to take the rule off the Top/Heading row use this formula:
=IF(ROW()>1,MOD(ROW(),2)=1)
Which means apply the rule to every row bigger than 1.
Which will apply the formatting to everyrow apart from the top row.
Hi Spencer, thanks for the useful tip.