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

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Sum the Smallest N Values in Excel
How to Sum the Smallest N Values in Excel 15

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel
How to Create Dynamical Drop-Down List 14

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...

How to Count Duplicate Values Only Once in A Range in Excel?
How to Count Duplicate Values Only Once in A Range in Excel5

When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in ...

How to Create Dynamic Drop Down List without Blank in Excel
create dynamic drop down list with blank5

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...

How to Stack Data from Multiple Columns into One Column in Excel
Stack Data 12

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...

How to Count Only Unique Values Excluding Duplicates in Excel
Count Only Unique Values Excluding Duplicates 6

We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the ...

How To Transpose Every N Rows of Data into Muliptle Columns in Excel
How to Transpose every N rows from one column to multiple2

This post will guide you how to transpose data from rows to column with a formula in Excel. How do I transpose every N rows from one column to multiple columns in Excel. Assuming that you have a list of ...

Sidebar