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 making that data look beautiful to the viewers or for ourselves. There are many ways of creating the data, and shading or highlighting every other row in Excel or every Nth row is one of the ways.

This article will demonstrate the techniques of highlighting every Nth row by formatting.

There are several ways to highlight every other row in Excel:

  • 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 moreUsing Conditional Formatting.Using Conditional Formatting.Conditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read moreCustom 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.” It is a 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. We do not need any special skills.

Method 2 – Highlight Rows Using Conditional Formatting

It is easy for you to understand if you know conditional formatting. Let us give you a simple conditional formatting example.

  • First, we must select the data. Press “Ctrl + T” (shortcut to create table). It will open up the below box. Click on the “OK.” It will create a table like this. It will automatically highlight every other row. Go to “Design” > “Table Styles.” Here, we have many different types of highlighting every other row by default.

It will automatically highlight every other row.

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

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

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

  • Step 2: Go to the “Home” tab > “Conditional Formatting” > “New Rule.”

  • Step 3: Now, click on “New Rule.” It will open a separate dialog box. Select “Use a formula to determine which cell to format.”

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

  • Step 5: Once the formula is inserted, click on “Format.”

  • Step 6: Go to the “Fill” option and select the color as per the wish.

  • Step 7: Click “OK.” As a result, now, it will highlight all the cells containing the number 5 from A1 to A10.

This way, Excel formats the particular cells based on the user’s condition.

Method 3 – Highlight Every other Row in Excel Using Custom Format

Similarly, we can highlight every alternative row using “Conditional Formatting.”

  • Step 1: Select the data (data that we have used in example 1). Do not select the heading because the formula will also highlight that row.

  • Step 3: Click on the “New Rule.” It will open a separate dialog box. Then, select “Use a formula to determine which cell to format.”

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

  • Step 6: Go to the “Fill” option and select the color as per choice.

  • Step 7: Click on the “OK.” It will highlight every alternate row.

Breaking Down the Formula

Let us break down the formula.

The formula reads =Mod (Row (), 2) = 1

The MOD function returns the remainder of the division calculation. For example, =MOD (3, 2) returns 1. 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 the number returned by the ROW function will be divided by 2. If the remaining number returned by the MOD function equals number 1, Excel may highlight the row by the mentioned color.

  • If the row number is divisible by 2, the remainder will be zero. On the other hand, if the row number is not divisible by 2, we will get the remainder equal to 1.Similarly, If we want to highlight every third row, we 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.We must use this formula if we want to highlight every second column starting from the first column. =MOD (COLUMN (), 2) =1

Things to Remember

  • If the data needs to be printed, we must use light colors to highlight it because dark colors may not show the fonts after printing.If the header is selected while applying conditional formatting, it will be treated as the first row.If we want to highlight every third row, we need to divide the row by 3.Similarly, we can apply this formatting for columns using the same formula.We cannot change the color of the row once the conditional formatting is applied.

This article has been a guide to Highlight Every Other Row in Excel. Here, we discuss highlighting every other row in Excel using an Excel table, conditional formatting, practical examples, and downloadable Excel templates. You can learn more about Excel from the following articles: –

  • Highlight Duplicates in ExcelAdd Rows in Excel ShortcutGroup Rows in ExcelShade Alternate Rows in Excel