How To Shade Every Other Line in Excel with Conditional Formatting
***Read the comments section for tweaks to the formula that are needed for different regions (and I posted a recent comment about Microsoft 365 (from a viewer in South Africa) down below in these video details.)***
Different versions of the program require different characters (commas vs. semi-colon etc.)
How to shade every other line on an Excel spreadsheet using conditional formatting.
First, select the area of the spreadsheet you wish to have alternating lines shaded.
Then From the Home tab --Conditional Formatting --New Rule --Use a formula to determine which cells to format --Format values where this formula is true:
=MOD(ROW(),2)=0
Select the Format button -- select a color for your alternating line shade --OK --OK
Alternately you can use these formulas:
=ISEVEN(ROW())
Select the Format button -- select a color for your alternating line shade --OK --OK (This will shade EVEN numbered rows)
=ISODD(ROW())
Select the Format button -- select a color for your alternating line shade --OK --OK (This will shade ODD numbered rows)
(October 2018) comment from a viewer regarding Microsoft 365: "On my version of Excel (Microsoft 365) the formula uses a semicolon in place of the comma. This took me a while to figure out. If the formula doesn't work for you try =MOD(ROW();2)=0. "
Watch video How To Shade Every Other Line in Excel with Conditional Formatting online without registration, duration hours minute second in high quality. This video was added by user WheeliePete 15 June 2014, don't forget to share it with your friends and acquaintances, it has been viewed on our site 293,70 once and liked it 3.2 thousand people.