Skip to main content

Highlight every other row or column in Excel - Step by step guide

Author: Xiaoyang Last Modified: 2023-11-24

In a large worksheet, highlighting or filling every other or every nth row or column improves data visibility and readability. It not only makes the worksheet look neater but also helps you understand the data faster. In this article, we'll guide you through various methods to shade every other or nth row or column, helping you present your data in a more appealing and straightforward manner.


Video: Highlight every other row or column


 Highlight every other row or column  

In this section, we'll show you three simple ways to shade every other row or column in Excel. This will help make your data look better and be easier to read.

Highlight every other row or column by applying Table style

Table styles offer a convenient and swift tool to easily highlight every other row or column in your data. Please do with the following steps:

Step 1: Select the data range that you want to shade

Step 2: Apply the Table style

  1. Click Home > Format as Table, and then choose a table style that has alternate row shading, see screenshot:
  2. Then, in a prompt Create Table dialog bob, click OK, see screenshot:
    Note: If there is no headers in the selected range, untick My table has headers checkbox.

Result:

Now, the selected data has been shaded alternately as shown in the following screenshot:

Tips:
  1. Once you use a table style, your table's odd and even rows will automatically be shaded in alternating colors. The great part is, this color banding adjusts on its own even if you sort, remove, or insert new rows.
  2. To shade every other column, select the table, under the Table Design tab, uncheck the Banded Rows option, and check the Banded Columns option, see screenshot:
  3. If you want to convert the table format to normal data range, select one cell in your table, right click and choose Table > Convert to Range from the context menu. After changing the table back to a normal list, new rows won't auto-shade. Also, if you rearrange the data, the striped pattern gets mixed up because the colors stay with the old rows.

Highlight every other or nth row or column by using a quick feature - Kutools for Excel

Do you want to easily highlight every other or specific row/column in Excel? Kutools for Excel’s Alternate Row / Column Shading feature can make your data stand out and organized. No complicated steps required, in just a few simple clicks, make your spreadsheet look more professional and clearer!

Note: If you want to use this Alternate Row / Column Shading feature, please download and install Kutools for Excel first.

Select the data range, and then click Kutools > Format > Alternate Row / Column Shading to enable this feature. In the Alternate Row / Column Shading dialog box, please do the following operations:

  1. Select Rows or Columns you wish to shade from the Apply shading to section;
  2. Choose Conditional formatting or Standard formatting from the Shading method;
  3. Specify a shade color for highlighting the rows from the Shade color drop down;
  4. Specify the interval at which you want to shade rows from the Shade every scroll box, such as every other row, every third row, every fourth row, and so on;
  5. At last, click OK button.

Result:

Shade every other row
Shade every other column
Tips:
  1. In the Alternate Row / Column Shading dialog box:
    • Conditional formatting: if you select this option, the shading will be adjusted automatically if you insert or delete rows;
    • Standard formatting: if you choose this option, the shading will not be adjusted automatically as you insert or delete rows;
    • Remove existing alternate row shading: to remove the exiting shading, please select his option.
  2. To apply this feature, please download and install Kutools for Excel first.

Highlight every other or nth row or column by using Conditional Formatting

In Excel, Conditional Formatting stands out as an invaluable feature, enabling users to dynamically alternate colors for rows or columns. In this section, we'll dive into some formula examples to help you alternate row colors in different ways:

Tips: Conditional Formatting is a dynamic feature, the shading will be adjusted automatically if you insert or delete rows in the specified data range.
Shade every other or nth row / column

To highlight every other or nth row or column, you can create a formula within the Conditional Formatting, please follow these steps:

Step 1: Select the data range that you want to shade

Step 2: Apply the Conditional Formatting feature

  1. Click Home > Conditional Formatting > New Rule, see screenshot:
  2. In the New Formatting Rule dialog box:
    • 2.1 Click Use a formula to determine which cells to format from the Select a Rule Type list box;
    • 2.2 Type any one of the below formulas into the Format values where this formula is true textbox you need:
      Shade every odd row: =MOD(ROW(),2)=1
      Shade every even row: =MOD(ROW(),2)=0
    • 2.3 Then, click Format button.
  3. In the Format Cells dialog box, under the Fill tab, specify one color you want to fill the rows, and then, click OK.
  4. When it returns to the New Formatting Rule dialog box, click OK.

Result:

Shade every odd row
Shade every even column
Tips:
  1. If you want to highlight alternate columns, please apply the following formulas:
    Shade every odd column: =MOD(COLUMN(),2)=1
    Shade every even column: =MOD(COLUMN(),2)=0
  2. To highlight every third row or column, please apply the formulas below:
    Note: When using the following formulas, the data in your sheet must start from the first row; otherwise, errors will occur. To shade every fourth or nth row or column, just change the number 3 to 4 or n as you need.
    Shade every third row: =MOD(ROW(),3)=0
    Shade every third column: =MOD(COLUMN(),3)=0
Shade alternating groups of n rows / columns

If you want to shade every n rows or columns in Excel as following screenshot shown, a combination of the ROW, CEILING, and ISEVEN or ISODD functions paired with conditional formatting is your go-to solution. Here, we'll walk you through a step-by-step process to easily highlight alternating groups of n rows or columns.

Step 1: Select the data range that you want to shade

Step 2: Apply the Conditional Formatting feature

  1. Click Home > Conditional Formatting > New Rule to go to the New Formatting Rule dialog box, in the popped-out dialog box, please do the following operations:
    • 1.1 Click Use a formula to determine which cells to format from the Select a Rule Type list box;
    • 1.2 Type any one of the below formulas into the Format values where this formula is true textbox you need:
      Shade alternate n rows from the first group: =ISODD(CEILING(ROW(),3)/3)
      Shade alternate n rows from the second group: =ISEVEN(CEILING(ROW(),3)/3)
    • 1.3 Then, click Format button.
      Note: in the above formulas, the number 3 indicates the group of rows that you want to shade alternately. You can change it to any other number you need.
  2. In the Format Cells dialog box, under the Fill tab, specify one color you want to fill the rows, and then, click OK.
  3. When it returns to the New Formatting Rule dialog box, click OK.

Result:

Shade alternate 3 rows from the first group
Shade alternate 3 rows from the second group
Tips:

If you want to shade alternating groups of n columns, please apply the following formulas:
Shade alternate n columns from the first group: =ISODD(CEILING(COLUMN(),3)/3)
Shade alternate n columns from the second group: =ISEVEN(CEILING(COLUMN(),3)/3)


Alternate row color based on value changes

Sometimes, you may need to change row colors based on different cell values to make the data visually easier to analyze. For instance, if you have a range of data and you want to highlight rows where values in a specific column (column B) change, doing so allows for quicker identification of where the data shifts. This article will discuss two practical methods to accomplish this task in Excel.

Alternate row color based on value changes with Conditional Formatting

In Excel, using Conditional Formatting with a logical formula is a useful way to highlight rows where values change. This ensures that every adjustment in value is clearly and distinctly marked.

Step 1: Select the data range that you want to shade (exclude the header row)
Note: When using the following formulas, the data range must have header row, otherwise, an error will occur.

Step 2: Apply the Conditional Formatting feature

  1. Click Home > Conditional Formatting > New Rule to go to the New Formatting Rule dialog box, in the popped-out dialog box, please do the following operations:
    • 1.1 Click Use a formula to determine which cells to format from the Select a Rule Type list box;
    • 1.2 Type any one of the below formulas into the Format values where this formula is true textbox you need:
      Shade rows based on value changes from the first group:
      =ISODD(MOD(SUMPRODUCT(--($B$1:$B1<>$B$2:$B2)),2))
      Shade rows based on value changes from the second group:
      =ISEVEN(MOD(SUMPRODUCT(--($B$1:$B1<>$B$2:$B2)),2))
    • 1.3 Then, click Format button.
      Note: in the above formulas, B1 is the header row of the column that you want to shade rows based on, B2 is the first cell in your data range.
  2. In the Format Cells dialog box, under the Fill tab, specify one color you want to fill the rows, and then, click OK.
  3. When it returns to the New Formatting Rule dialog box, click OK.

Result:

Shade rows when value changes from the first group
Shade rows when value changes from the second group

Alternate row color based on value changes with a powerful feature-Kutools for Excel

If the previous method seems a bit tough, there’s an easier way! You can use Kutools for Excel. Its Distinguish Differences feature makes coloring rows by group really easy and fast. Not only can you change the colors of rows when values change, but you can also add borders, page breaks, or blank rows as needed, making your Excel data more organized and easier to understand.

Note: If you want to use this Distinguish Differences feature, please download and install Kutools for Excel first.

Click Kutools > Format > Distinguish Differences to enable this feature. In the Distinguish differences by key column dialog box, please do the following operations:

  1. In the Range box, specify the selection that you want to shade color;
  2. In the Key column box, select the column that you want to shade color based on;
  3. In the Options section, check the Fill Color option, and specify one color;
  4. In the Scope section, choose Selection from the drop down;
  5. At last, click OK.

Result:

Tips:
  1. In the Distinguish differences by key column dialog box, you can also:
    • Insert page break when cell value changes
    • Insert blank row when cell value changes
    • Add bottom border when cell value changes
  2. To apply this feature, please download and install Kutools for Excel first.

Whether you choose to use table style or Kutoosl for Excel or Conditional Formatting, you can easily add highlighting effects to your Excel data. Based on your specific needs and preferences, choose the method that suits you best for the task. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!


Related Articles:

  • Auto-highlight row and column of active cell
  • When you view a large worksheet with numerous data, you may want to highlight the selected cell’ row and column so that you can easily and intuitively read the data to avoid misreading them. Here, I can introduce you some interesting tricks to highlight the row and column of the current cell, when the cell is changed, the column and row of the new cell are highlighted automatically.
  • Highlight row if cell contains text/value/blank
  • For example we have a purchase table in Excel, now we want to find out the purchase orders of apple and then highlight the entire rows where the orders of apple are in as the left screenshot shown. We can get it done easily in Excel with Conditional Formatting command or Kutools for Excel's features, please read on to find out how.
  • Highlight approximate match lookup
  • In Excel, we can use the Vlookup function to get the approximate matched value quickly and easily. But, have you ever tried to get the approximate match based on row and column data and highlight the approximate match from the original data range as below screenshot shown? This article will talk about how to solve this task in Excel.
  • Highlight rows based on drop down list
  • This article will talk about how to highlight rows based on drop down list, take the following screenshot for example, when I select “In Progress” from the drop down list in column E, I need to highlight this row with red color, when I select “Completed” from the drop down list, I need to highlight this row with blue color, and when I select “Not Started”, a green color will be used to highlight the row.
Comments (2)
Rated 0.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
first option did not work
Rated 0.5 out of 5
This comment was minimized by the moderator on the site
Hello, john
The first method works well in my worksheet, could you upload your workbook here, so that we can check where the problem is?
Or you can explain your problem more detailed.
Thank you!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations