WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Highlight Every Other Row in Excel

Highlight Every Other Row in Excel

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.

  1. Using Excel Table
  2. Using Conditional Formatting.
  3. Custom Formatting

Follow this article to learn and explore each one of them.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
You can download this Highlight Row Excel Template here – Highlight Row Excel Template

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.

highlight rows

In this data, we need to highlight every other row in excel. Using this method, we can highlight every other row. We do not need any special skills.

  • Step 1: Select the data.

highlight rows step 1

  • Step 2: Press Ctrl + T (shortcut to create table). This will open up the below box.

highlight rows step 2

  • Step 3: Click on the OK. This will create a table like this.

highlight rows step 3

This will automatically highlight every other row.

Go to Design > Table Styles.

highlight rows step 5

Here we have many different types of highlighting every other row by default.

If you uncheck the Banded Rows box, it will remove the highlighting option.

Method 2 – Highlight Rows Using Conditional Formatting

If you are aware of what conditional formatting is, it is easy for you to understand. Let me give you a simple condition formatting example.

I have a number list from A1 to A10. I want to highlight the number 5 in this range with a yellow colour.

highlight rows step 6

  • Step 1: Select the data range from A1 to A10.

highlight rows step 7

  • Step 2: Go to Home tab > Conditional Formatting > New Rule >

highlight rows step 9

  • Step 3: Click on New Rule it will open a separate dialogue box. Select Use a formula to determine which cell to format.

highlight rows step 10

  • Step 4: In the formula section mention = $A1 = 5.

highlight rows step 11

  • Step 5: Once the formula is entered, click on Format.

highlight rows step 11

  • Step 6: Go to Fill and select the color you want.

highlight rows step 12

  • Step 7: Click, OK. This will highlight all the cells containing number 5 from A1 to A10.

highlight rows step 13

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 the formula will highlight that row as well.

step 14

  • Step 2: Go to Home tab > Conditional Formatting > New Rule >

step 9

  • Step 3: Click on New Rule it will open a separate dialogue box. Select Use a formula to determine which cell to format.

Example 15

  • Step 4: In the formula section mention = MOD (ROW (), 2) =1

Example 16

  • Step 5: Once the formula is entered, click on Format.

Example 16

  • Step 6: Go to Fill and select the color you want.

example 17

  • Step 7: Click on the OK. This will highlight every alternate row.

excel - 18

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 a result. When we divide the number 3 by 2, we will get 1 as the remainder. Similarly, the ROW function Excel will return the row number, and whatever the number returned by the ROW function will be divided by 2. If the remaining number returned by the 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 Other Row 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 –

  • Highlight Duplicates in Excel
  • Add Rows in Excel Shortcut
  • Group Rows in Excel
  • Shade Alternate Rows in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Highlight Row Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More