Welcome Excellers to another blog post in my 2019 series of #Excel tips for #FormulaFriday. This post was prompted by a question over email by one of my subscribers. So, I thought this would be a good tip to share. They wanted to highlight every fifth row in their Excel data set. They sent me an expected outcome which looked like the screenshot below. This meant that every 5th row needed to be highlighted. We do this using the MOD and ROW functions.
This pattern of highlighting can be done by using conditional formatting. But this is #FormulaFruday isn’t it? Where are the formulas I hear you say!. By combining it with the MOD and the ROW functions we can create a really handy conditional formatting tool.
Writing The Excel Formula.
Let’s go ahead and write the formula, see the results then walk through it step by step. Follow the steps below.
- Highlight the data to which you want to apply conditional formatting
- Home | Conditional Formatting | New Rule
- Select Rule Type | Use A Formula To Determine Which Cells To Format
- Enter The Following Formula Into ‘Format value where this formula is true’ text box
=MOD(ROW(),5)=0
Your formula should look like my example in the screenshot below.
What Is The Excel Function?
So, we used the MOD Function to solve this query from one of my subscribers. Ever used MOD in Excel?. If not then here is a bit more information about it to make it a bit clearer.
The Excel MOD function returns the remainder of two numbers after division.
Syntax
=MOD (number, divisor)
Where
number – The number to be divided.
divisor – The number to divide with.
For Example, MOD(3,2) returns 1, because 2 goes into 3 once, with a remainder of 1. So, MOD is often seen in formulas that deal with “every nth” value.
This is exactly what we needed. To find every 5th row and colour it.
- MOD will take the current ROW number as the number argument. The ROW function simply returns the row number for a reference.
- The second argument is 5 as the divisor. This is the number representing the ‘nth’ rows to format. So MOD takes the ROW number and divides by 5 and gives us the remainder.
- =MOD(ROW(),5
- The final part of the formula is where we set what the divisor which should equal. In this case, we set it as zero. This gives us our TRUE or FALSE test to enable Excel to decide to format our cells or not.
- =MOD(ROW(),5)=0
So, have you ever highlighted rows in Excel?.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
A Full list Of Formula Friday Blog Posts
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
