How to Highlight Every Other Row or Every Nth Row in Excel?

Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight rows by excel ‘Conditional Formatting’ function. Actually, ‘Conditional Formatting’ has a powerful capability to highlight cells per some rules, it already contains some rules like ‘Duplicate Values’ in itself, we can also define new rule by ourselves. In this free tutorial, we will introduce you the way to highlight every Nth row through ‘Conditional Formatting->New Rule’ function. Follow steps with screenshots below, we can make sure you can learn it well.

Precondition:

Create a table with several rows. Now we want highlight rows every other row or every Nth row.

How to Highlight Every Other Row or Every Nth Row in Excel 1

Method 1: Highlight Every Other Row by Conditional Formatting Function


Step 1: Select the range to highlight rows, you can ignore the header part. Click Home in ribbon, then click Conditional Formatting in Styles group, select New Rule in menu.

How to Highlight Every Other Row or Every Nth Row in Excel 2

Step 2: On New Formatting Rule dialog, select the last rule type: Use a formula to determine which cells to format.

How to Highlight Every Other Row or Every Nth Row in Excel 3

Step 3: In Edit the Rule Description section, in Format values where this formula is true textbox enter the formula =ISEVEN(ROW()).

How to Highlight Every Other Row or Every Nth Row in Excel 4

Step 4: Click Format button in Preview section. Verify that Format Cells window is displayed.

How to Highlight Every Other Row or Every Nth Row in Excel 5

Step 5: Click Fill tab, then select a color as background color. For example, select yellow, then click OK.

How to Highlight Every Other Row or Every Nth Row in Excel 6

Step 6: After above step, we go back to New Formatting Rule window. You can see that in Preview section, cell is filled with yellow. Click OK.

How to Highlight Every Other Row or Every Nth Row in Excel 7

Step 7: Check the result. See screenshot below. Rows are highlighted every other row.

How to Highlight Every Other Row or Every Nth Row in Excel 8

Method 2: Highlight Every Nth Row by Conditional Formatting Function


Just repeat above steps, only a little difference in some steps. See simple steps below.

Step 1: In above step#3, in Edit the Rule Description section, in Format values where this formula is true textbox enter the formula =MOD(ROW(),3)=1. In this formula, highlight rows every third row.

How to Highlight Every Other Row or Every Nth Row in Excel 9

Step 2: Select yellow to highlight rows. See preview part.

How to Highlight Every Other Row or Every Nth Row in Excel 10

Step 3: Check the result, see screenshot below. It works well. If you want the header part is also included in counting, you can update the formula as =MOD(ROW(),3)=0.

How to Highlight Every Other Row or Every Nth Row in Excel 11

Related Functions


  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

 

Related Posts

Creating a Table with Automatic Row Numbering

When creating an Excel Table, you may use a calculation relying on the ROW function to insert row numbers automatically. The formula in A2, which has been copied down, is as follows: =ROW()-ROW(Table1[#Headers]) Note: the default table name is Table1. ...

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table's value. For the DATA table, we have ...

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Filter or Extract with a Partial Match

Assume you have a table consisting of a few cells having few values, and you want to filter out the set of records with the partial match. You might take it easy and would prefer to manually filter out the ...

Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Sidebar