How to Highlight Every Other Row in Excel?
When we are working in excel we often look at the ways of designing the data and make that data look beautiful to the viewers or for ourselves. There are many ways of designing the data and shading or highlighting every other row in excel or every Nth row is one of the ways.
In this article, I am going to demonstrate the techniques of highlighting every Nth row by using formatting.
There are a couple of ways we can highlight every other row in excel.
- Using Excel Table
- Using Conditional Formatting.
- Custom Formatting
Follow this article to learn and explore each one of them.
Method 1 – Highlight Rows Using Excel Table
In excel by default, we have a tool called “Excel Table”. This is the quick way to highlight every other row in excel.
Now take a look at the raw data.
In this data, we need to highlight every other row in excel. Using this method we can highlight every other row in excel. We do not need any special skills.
Step 1: Select the data.
Step 2: Press Ctrl + T (shortcut to create table). This will open up the below box.
Step 3: Click on OK. This will create the table like this.
This will automatically highlight every other row in Excel.
Go to Design > Table Styles
Here we have many different types of highlighting every other row in Excel by default.
If you uncheck Banded Rows box it will remove the highlighting option.
Method 2 – Highlight Rows Using Conditional Formatting
If you aware what is conditional formatting it is easy for you to understand. Let me give you a simple condition formatting example.
I have number list from A1 to A10. I want to highlight the number 5 in this range with a yellow colour.
Step 1: Select the data range from A1 to A10.
Step 2: Go to Home tab > Conditional Formatting > New Rule >
Step 3: Click on New Rule it will open a separate dialogue box. Select Use a formula to determine which cell to format.
Step 4: In the formula section mention = $A1 = 5.
Step 5: Once the formula is entered Click on Format.
Step 6: Go to Fill and select the colour you want.
Step 7: Click OK. This will highlight all the cells containing number 5 from A1 to A10.
In this way based on the user condition excel to format the particular cells for us.
Method 3 – Highlight Every other Row in Excel Using Custom Format
Similarly, we can highlight every alternative row by using Conditional Formatting.
Step 1: Select our data (data that we have used in example 1). Do not select the heading because formula will highlight that row as well.
Step 2: Go to Home tab > Conditional Formatting > New Rule >
Step 3: Click on New Rule it will open a separate dialogue box. Select Use a formula to determine which cell to format.
Step 4: In the formula section mention = MOD (ROW (), 2) =1
Step 5: Once the formula is entered Click on Format.
Step 6: Go to Fill and select the colour you want.
Step 7: Click OK. This will highlight every alternate row.
Breaking Down the Formula
Ok, let me break down the formula.
The formula reads =Mod (Row (), 2) = 1
MOD function returns the remainder of the division calculation. For example, =MOD (3, 2) returns 1 as the result. When we divide the number 3 by 2 we will get 1 as remainder. Similarly, ROW function will return the row number and whatever the number returned by ROW function will be divided by 2. If the remaining number returned by MOD function is equal to number 1 then excel will highlight the row by the mentioned colour.
- If the row number is divisible by 2 then the remainder will be zero. If the row number is not divisible by 2 then we will get the remainder that is equal to 1.
- Similarly, If we want to highlight every 3rd row we just need to change the formula to =MOD (ROW (), 3) =1.
- If we want to highlight every 2nd column we can use =MOD (COLUMN (), 2) = 0.
- If we want to highlight every 2nd column starting from the first column then we need to use this formula. =MOD (COLUMN (), 2) =1
Things to Remember
- If the data needs to be printed you need to use light colors to highlight. Because dark colors will not show the fonts after printing.
- If the header is selected while applying conditional formatting then it will treat the header as the first row as well.
- If we want to highlight every 3rd row we need to divide the row by 3.
- Similarly, we can apply this formatting for columns using the same formula.
- You cannot change the color of the row once the conditional formatting is applied.
Recommended Articles
This has been a guide to Highlight Every OtherRows in Excel. Here we discuss how to highlight every other row in excel using excel table and conditional formatting along with practical examples and downloadable excel templates. You can learn more about Excel from the following articles –
- Explanation of 3D Cell Reference in Excel
- Excel Table Styles & Formats
- Merge and Center in Excel
- Shortcut to Merge Cells in Excel
- Excel Print
- Convert Columns to Row in Excel
- Auto Format in Excel
- Remove Blank Rows in Excel
- Show Formula in Excel
- Merge Cells in Excel
- Absolute Reference in Excel
- Track Changes in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
