Excel Highlight Every Nth Row with Conditional Formatting - Every 3rd, 4th, or 5th row.

Conditional Formatting in Excel allows you to highlight every 2nd, 3rd, 4th, or 5th row. Writing a formula using the MOD function and ROW function together allows us to do this. Conditional Formatting is one of my favorite Excel features.

MOD Function has two required arguments and shows the remainder after dividing two numbers. For example, =MOD(7,3) will return 1. Three goes into 7 two times with a remainder of 1. Another example of MOD is =MOD(5,3) will return 2.

The ROW function specifics what row you are in. =ROW(), if you are in row 5, it will return 5. =ROW() will return 9 if you are in row 9.

Chapters:
0:00 Intro
0:28 Select quickly
0:47 Examples of MOD and ROW with Conditional Formatting
2:16 MOD and ROW Explained

#chrismenardtraining #exceltraining #microsoftexcel #msexcel #conditionalformatting

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[sundukchi]

Thank you for the fantastic video on explaining conditional formatting. Your presentation skills are excellent. Following your guidance, I implemented conditional formatting for every 14th row, beginning with row 2. Here's the format I used:

=MOD(ROW()-2, 14)=0

Thanks again

[gotcomps]

Big Thanks!! This is exactly what I was looking for. Works perfectly!

[thecarriebradshaw]

You saved me so much time. Thanks for sharing!

[MA]

It was amazing to learn with you. Thanks for helping us learn! I have a scenario where I have data in Column B2 to H2 and Row 2 to Row 10. I want to highlight every 4th row, but I don't want to highlight the entire 4th row, only from Column D2 to F2. Is it possible to highlight in this way? The thing to remember is that the data is in Excel worksheet not in table

[mikevnb]

Chris, Thanks for this hard to find info... Can you also combine a second argument?
Like if you want the formatting to apply only to every Nth column ?
E.g., in the case of every third column and every 2nd row, it would be : A2, A4, A6, ..., D2, D4, D6, ... G2, G4, G6.... J2, J4, J6... etc...

[themisstte]

Hi, can you help me? I'm trying to highlight every1- 7th cel, 7-14 cel etc in a row from a specific date on column A, for example, 06/17 plus 6 days (yellow), then from 8th to 14th (green) cel until the end of the month. The columns are the days of the month until the 31st

[allisonbabb]

Is there a way to do this that stays with the line when sorted? It doesn't with conditional formatting and it takes forever to do it manually

[DLeeeet]

Thanks, what if I wanted to highlight the first seven rows, then have 7 unhighlighted rows, then 7 highlighted rows again?

[mario-t]

Tx, but border are gone after this formatting, even if you select neutral color

[JuanSanchez-ikwx]

Something that has always bugged me in my 25 years of using excel on and off is that the column headers disappear once you have enough rows to fill your screen. Isnt there a way to always have the column headers float at the top?

[HappyBeezerStudios]

How to apply it if Excel is not in english?
Formulas are sadly language specific.

[Wirtschaftsinformatiker]

I get an error: There's a problem with this formula. What am I doing wrong?