In this tutorial, you will learn to highlight every other row (or 2nd,3rd, or even nth) or column in Excel.
Here we will use the below dataset with the evergreen feature “Conditional Formatting” of Excel.
See the image below:-
Highlight Every Other Row in Excel
First of all, select the whole dataset where you want to shade or highlight other rows.
Make sure not to select the headlines of the dataset.
See the image below:-
Then go to the “Home” tab and go to “Condition Formatting”. (Keyboard Shortcut key – Alt + H + L+N)
Click the “Conditional Formatting” drop-down and select “New Rule” from the options.
See the image below:-
A “New Formatting Rule” box will open up with multiple formatting options
Go to the last option ‘Use a formula to determine which cells to format’ from the ‘Select a Rule Type’ option list.
Then enter the below-given formula in the provided field.
=MOD(ROW(),2)=1
After entering the formula, select the color from the “Format” button to shade the rows (by which color we want to highlight or shade our rows).
After clicking on the “Format” button, the below image will appear.
Go to the “Fill” tab and find multiple colors here to use for our shaded rows.
Select the color and press the “Ok” button below.
Your image will appear as below:-
Press the “OK” button, and see that every other row of the dataset is shaded with the selected color.
See the image below:-
Now let’s learn about the MOD and ROW function works with “Conditional Formatting” to get it done.
How MOD and ROW Function Works
First, see the MOD function and its parameters.
MOD function has two parameters.
- Number
- Divisor
This function always returns a remainder if we divide the number parameter with the divisor.
For example: – if we use ‘5’ in place of the ‘Number’ parameter and use ‘2’ as ‘Divisor’, then the MOD function will returns ‘1’ as remainder.
Simply if we divide ‘5’ by ‘2’, our remainder will be there as ‘1’.
To fill the ‘number’ parameter in the MOD function, use the Excel ROW function.
And in place of the ‘divisor’ parameter in the MOD function, we will use ‘2’ by default.
In the above image, the MOD function returns ‘1’ as a remainder on every ‘odd number’ row.
And returns ‘0’ for even number by using “2” as “Divisor’ by default.
If we divide any even numbers (2, 4, 6, and 8) by ‘2’ then our remainder returns as ‘0’.
And if we divide an odd number (3, 5, 7, and 9) by ‘2’ then our remainder returns as ‘1’.
Now we will apply this condition in our “Conditional Formatting”.
Now see the formula in the “Conditional Formatting”, which we have applied earlier.
=MOD(ROW(),2)=1
If the MOD function returns its result for any row as ‘1’ that row will be highlighted and the rest rows will remain the same or will not highlight.
Now if we use the above formula as below
=MOD(ROW(),2)=0
Here we have used ‘0’ instead of ‘1’.
If MOD function returns remainder as ‘0’ then every even number row is highlighted. (i. e. even row returns its remainder as ‘0’ if divide by ‘2’.
See the result below:-
How to Highlight Every 3rd, 4th, or nth Row
Now if we want to shade every 3rd, 4th, or nth row in the dataset.
First of all, select the whole dataset where we have applied conditional formatting.
Then go to the “Home” tab go to conditional formatting (keyboard shortcut: – ALT + H+L+R).
And go to Manage Rules. See the image below:-
Click on the Manage Rule option here.
If you have applied multiple conditional formatting, then you will find here multiple applied rules.
In this case, you need to find which you want to modify and then click on the “Edit Rule” button that appears just above.
Here you will find the conditional formatting rule box which you want to modify.
Here you can see the applied formula to highlight the other rows.
Formula: – =MOD(ROW(),2)=1
Here we will change the divisor number, which is ‘2’ here.
If you want to shade or highlight every 3rd row in the dataset, change it with ‘3’.
And we have to change the divisor by ‘3’ which will highlight every 3rd row in the dataset.
See the image below:-
Similarly, if you want to shade every 4th row in the dataset, change it with ‘4’.
And so on if we want to shade the nth row in the dataset, change this divisor number with the nth number.
So this way we can shade any number of rows in the dataset.
Just try to apply that and see the magic.
Highlight Every Other Column
Till now we shaded the row numbers but if we want to shade the columns then how to do it.
If you want to shade other columns then use COLUMN instead of the ROW function.
Use the following procedure to highlighted COLUMNS instead of ROW.
Go to the “Home” tab then the “Conditional Formatting” drop-down and click “Manage Rules” (Keyboard Shortcut: – “ALT+H L+R”).
Use the “Manage Rules” option to edit or modify the applied rules in Conditional Formatting.
After clicking on this option, you will get the following “Conditional Formatting Rule Manager”.
Click on the “Edit Rule” button.
And use COLUMN in place of ROW in the formula box.
And press “OK”.
You will again go back to the “Rule Manager” option box and press apply button.
And finally, you will get your dataset highlighted by every other column.
See the image below:-
So this way we can shade or highlight the other columns.
How to Highlight Every 3rd, 4th, or Nth Column
If you want to shade or highlight every 3rd. 4th or nth columns then use the same method applied to shade 3rd or 4th or nth row number.
If you want to learn more about this, please visit Microsoft Office Support.
I hope you find this tutorial useful.
Please feel free to put your comments or suggestion in the comment box below.
Thanks
Narendra
Related Post
How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel
5 Ways to Delete Blank Rows in Excel (With VBA Macro)
How to Wrap Text in Excel – 4 Easiest Method
How to use Excel SUMIFS Function – With Multiple Example
How to use Excel SUMIF Function – Multiple Condition (with examples)