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. "

[@WheeliePete]

Alternately, you can use these formulas in conditional formatting if you specifically want to shade even numbered rows on color and odd numbered rows another color:
=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)

[@robertweekley5926]

I find that saving a Spreadsheet with those tricky Formula's in it, is a useful tool, besides saving a Favorites folder for Excel Tricks I need!

[@allisonjoyalali2504]

@@robertweekley5926 please would you help me know how to save formulas in spreadsheet.

[@robertkajita1531]

Awesome, thank you. This tutorial was precisely how I needed it presented; simple, easy to mirror/follow, and verbally clear.

[@1Joyceala]

I tried several videos and this is by far the best.
Thank you so very much.

[@almaveronicagutierrezdevel7973]

Love the narration and explanation. Thank you for not making it so complicated.

[@MsCritiq]

I appreciate the simplicity of your explanation and practical application of the function

[@mobayguy]

Excellent Video Pete! So simple but incredibly useful. You've just saved me a whole lot of time and effort. Thanks !

[@SafwanHashmi]

We can take a screenshot of the formula and get it whenever we need or we thank you for uploading this video and whenever we need it we will come to your video again and follow your instructions.
Well, Thanks a lot for uploading this video.

[@GrandRiserOfficial]

Hey man, thanks a lot. You saved my work! Clearly demonstrated, clearly spoken and clearly defined step by step. 100% worked.

[@jshea938]

super helpful and easy to understand - thank you so much! A better, more versatile option than if I was in the "Format Table" mode.

[@faustmaglinao1331]

WOW!! The best and clearest instruction so far. Thank you! :)

[@bimbola6903]

I have been looking everywhere for this. Thanks for posting!

[@IstasPumaNevada]

"MOD" is short for "modulus", which means "tell me what the remainder is when I do division". So it's saying take the row number and divide it by two. If the remainder is equal to zero (that is, if there's no remainder), highlight the row. This will highlight the even rows (as it does in the video). I believe if you change the zero to a one, it would highlight the odd rows.
Thanks for the info, this will come in quite handy.

[@weetzybat]

You're a wizard Harry

[@lloydchristmas4547]

Thanks.

[@shellyd1m]

Thank you. I was wondering why the opposite row for me was being formatted. Changing the zero to a one resolved my issue.

[@USAAnto]

Thank you for this quick and easy solution. Excellent teaching approach. - Appreciated.

[@karicarter5078]

Fabulous tip and detailed demonstration of how to apply. Thanks Wheelie Pete!

[@icecubejdi]

Very simple, I really appreciate you taking the time!

[@viewtech2984]

Dear WheeliePete Thank you so very much. Its working and its perfect. Thanks again for helping all of us.

[@es4205]

Sweet and to the point, everything needed to know in less than 4 minutes

[@arazick]

Thank you WheeliePete for a useful video. Since I wanted my first row and every other row after that to be shaded, I found out that I had to move my data one row down. This made my first row to be "even row" which makes the formula return a true value.

[@brownj0002]

Or you could alter the formula to say =1 instead of =0... it is really only checking "even" or "odd" so easy to adjust.

[@yasr.6205]

YOU ARE FANTASTIC. Thank you so much, so clear and easy to follow.

[@emilyk5920]

Thank you! Works GREAT! And if you want to skip the first line you can enter 1 instead of 0 for ex., =MOD(ROW(),2)=1

[@TedWoodYourMentor]

Thanks WheeliePete this video nails it big time in a short time.

[@daviddavila7443]

Thanks so much for this video, I always wondered how to do this without bothering my expert co-worker in Montana via email/video chat. Easy explanation, I took notes to remember next time.

[@ElenaAcevedo]

Thank you so much. short and straight to the point. I appreciate that especially when time is so precious. I will subscribe to your videos. Stay safe and thank you again ;)

[@andreabothma3253]

Thanks for the description, it helped a lot

[@lovehope3753]

TFS...I needed every other column, and this video pointed me in the right direction. =MOD(COLUMN(),2)=0

[@Conbriocoaching]

This was awesome and easy to follow. Thank you so much.

[@maryr0410]

Thank you 👏
You explained very well and it was easy to follow

[@annlesleysteyn]

Thank you so much for this. 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.

[@WheeliePete]

Are you in the U.S. or another country? I've come to learn that other countries have different standards for the separators so the program changes from region to region. This is the first comment regarding Microsoft 365 though. Thank you for posting.

[@annlesleysteyn]

Hi WheeliePete. I am in South Africa. I have noticed that if you search for help on Chrome they use a space in place of the comma or semicolon. This can be most frustrating until you figure it out. A big shout out for your tutorial video - best one I have come across. I rarely comment (stalker not a talker), so this shows how great your video was!!!

[@YO3HJV]

Oh, this did the trick for me. Romania here...

[@roberto_amorim]

Thanks you so much for this tip. I was facing the same problem.

[@StephenIsaacson-fe2qm]

Awesome tutorial! Thanks for sharing the tip.

[@mattreinecke6260]

So very easy. Thanks for making this nice and uncomplicated

[@velona509]

Excellent. Thank you very much, this just what I have been looking for

[@rmhutchins7]

I enjoyed your video. It was very helpful. Thank you!

[@Ducs4life]

Find myself coming back to this video a lot. Thank you.

[@WheeliePete]

Honestly, it was one of the reasons I made the video; so I would know where I could find the code when I needed it. 😄

[@genegraves3076]

Excellent and simple, not often seen with techies.

[@mikeb6824]

I've come here more than once, having failed to remember how this is done. Thank you for posting this! I've hit the subscribe button - AND taken notes this time!

[@WheeliePete]

Truth be told, making the video was a way for me to be able to remember where I could find the information on how to do this too...lol

[@robertweekley5926]

@@WheeliePete Like - Sharing your Secret discoveries! Nice!

[@AuntDenise411]

Worked beautifully! Thanks!

[@samitmandal8778]

So many thanks for sharing the format.

[@maynardmg-photography8670]

Excellent. Easy does it. =MOD(ROW(),2)=0

[@robertthoresonjr.7462]

Thanks Pete...that was great and worked very well.

[@qingfengxue4779]

thanks for the video, it helps me a lot, save my time.

[@baronosong8569]

Thank you very much for this. Very helpful.

[@cleveoh]

Very helpful and easy to follow! thank you!

[@hmathenge25]

Many thanks man! very well explained....

[@ibakunbetaa]

This is helpful! Thanks much!

[@elianaseidl1501]

VERY CLEAR AND EASY. THANK YOU.

[@juanm.aguayo-leal9795]

Excellent!!! Worked very well !!!!!

[@jodelg0101]

Thank you, thank you, thank you, now I can move on and finish my assignment. I spent way too much time trying to figure this out.

[@WheeliePete]

Jode Gabriel I made this video because of the ridiculous amount of time I spent one day trying to figure this out at work. Figured I couldn't be the only one wanting to do this... :-)

[@alextom6769]

Thank you.... Nicely detailed...

[@rhondawray2151]

works for me... Thank you for the easy tip

[@allisonjoyalali2504]

Thank you so much for this video

[@elsonkhoza9615]

I like this Sir,this is so incredible mmmmh i have learn a lot form this video.......... Thumbs UP

[@twyztedsteel81]

Very helpful! Thanks!

[@samtaufao]

Thanks, this solved a quick issue for me.

[@thegrumpybusinessman3400]

Perfect. Thank you.

[@osirise.infanteh.8347]

Thanks bro, you saved my life.

[@jamescohen8817]

Tremendously helpful. Thanks WhelliePete

[@TN.WoodsandWater]

Great video thanks for the help

[@kimcrabtree6724]

Great tip thanks and very clear.

[@LuisGonzalez-ie9qd]

Thank you so much! really helpful!

[@consultantmarshall4626]

Thank you Sir, this was great.

[@lloydchristmas4547]

Clean and easy. Thanks so much.

[@willbollman813]

That worked out perfect man, Thank You!!!!!!!!!!!!!!!!!!!!

[@lukemeandel3409]

Thanks. Very helpful!

[@thomasdavidsin5700]

Thank you video was very helpful

[@sindhpunjab778]

Good tip and well demonstration

[@laurabishop4209]

Thank you so much!!

[@BeHappyNoMatterWhat]

You are a hero, thank you

[@TheRealSmithFamily]

Thank you! ❤

[@andreailles9736]

Thanks :-) The Spanish formula is: =RESIDUO(FILA();2)=0

[@angelabarth6432]

Thankyou so much!!

[@kayumahammedprogrammer7622]

thanks for your video

[@belindaolvera3501]

Great video, thank you!!!!!

[@sayeed110575]

THANK YOU , IT VERY HELPFUL TO ME ( SAYEED , UAE )

[@justhelping1988]

Brilliant. Thank you.

[@mariafriend644]

Thank you. You can also create a table out of the data and it will highlight every other row.. Thanks for sharing.

[@merks000]

Thanks for this!

[@SoulRedeemer77]

Thank you it worked

[@1stp4ward]

That is awesome, thank you

[@kamalboro366]

Thanks Bro. Very helpful..🫡

[@DogCbone]

Thank You !!!

[@renegade2137]

In case it doesn't work for you, this is what I had to do,
Check your system's regional settings to see what your "List Separator" is set to:
In Control Panel, search for Region then select Change date, time, or number formats, click the Additional settings... button, then look for List separator under the Numbers tab. In my case, I had it set to a pipe '|' because I was messing with some script that would change an XLS to a CSV and needed to end up with a pipe-separated file instead of a comma-separated one.
Either use that character to separate the values in your formulas or just change it back to comma and the formula finally worked for me

[@elisasegal8153]

THANK YOU!!!!!

[@myoneblackfriend3151]

Thank you, sir!!

[@griffin280]

This was great

[@lisaviolet_video]

Thank you!

[@hollabacknowall]

Thank god for this video because my current spreadsheet is almost 400 lines long and I did NOT want to do that by hand 🤣

[@robertweekley5926]

Absolutely Not! Same if it was 400 Columns Wide, I suppose!

[@TheeMcDonger]

you are the man!

[@everything_ste]

Thanks!

[@robertbendinelli9843]

THANK YOU,

[@brownj0002]

Good tip, well demonstrated and many thanks!
Here are a couple alternatives if it helps anyone (based on questions below)
*=MOD(ROW(),2)=1* .... for ODD numbered rows instead of even
*=MOD(ROW(),5)=0* ... for all the "fives & tens"
*=MOD(ROW(),12)=0* ... for anything divisible by 12 with no remainder (e.g. 12, 24, 36...)
*=MOD(COLUMN(),2)=0* ... for even numbered COLUMNS
etc. see how that works?
->> you can apply multiple rules to the same areas! So enter one color for EVEN rows, another color for ODD rows
->> if you forget to apply formatting colors or try multiple times then you are layering on multiple rules, so you may be able to select Conditional Formatting, Manage Rules (below New Rule)... and edit them.
->> you can TEST it: type it into a cell and see the result, such as enter =MOD(ROW(),2)=0 anywhere in ROW 2,4,6 etc. and it will show "TRUE". You CAN paste the formula into the rule, but you have to copy the TEXT not the CELL. :D

[@devilpuppy1969]

Incredible information, thank you so much.

[@KarK1ttyy]

Thanks :) works

[@dridribelo]

Thank you so much.

[@Mamununic]

Thank you.

[@suntek8390]

Thanks a lot

[@Khalish-lp1tz]

Thanks sir

[@transformation2479]

Thank you!!!

[@ralphvon7462]

short video and very clear.

[@amastershock]

Thank you so much

[@denniswilliams9092]

Thanks

[@marketingcalendars3036]

You can also convert the range into a table, it will prompt which table format you'd like, then select one with every 2nd band coloured