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 TableExcel TableIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more
  2. Using Conditional Formatting.
  3. Custom Formatting

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

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.

Below are the steps used for highlighting row using excel table –

  1. Select the data.


    highlight rows step 1

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


    highlight rows step 2

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


    highlight rows step 3

    This will automatically highlight every other row.

  4. 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 ExcelROW Function ExcelThe row function in Excel is a worksheet function that displays the current row index number of the selected or target cell. The syntax to use this function is as follows: =ROW( Value ).read more 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>