Highlight Every Other Line In Excel – Excel & Google Sheets

This tutorial will demonstrate how to highlight every other row using Conditional Formatting and Table formatting in Excel and Google Sheets.

 

highlight every other line master

 

Highlight Every Other Line

To highlight every other row with Conditional Formatting, use the ISEVEN or MOD Functions within a Conditional Formatting rule.

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

highlight every other line menu

 

  1. Select “Use a formula to determine which cells to format‘, and enter the following formula (with the ROW Function):

 

highlight every other line iseven formula

 

OR

 

highlight every other line mod formula

 

  1. Click on the Format button and select your desired formatting.

 

Conditional formatting based on another cell formatting

 

  1. Click OK, and then OK again to view the result.

 

highlight every other line final

 

You can apply a similar rule using the ISODD formula =ISODD(ROW()) or =MOD(ROW(),2)=0 to highlight all the odd rows in the table.

 

highlight every other line rule manager iseven

 

OR

 

highlight every other line rule manager mod

 

The result will then be:

highlight every other line final odd

 

Where all the even rows are green and all the odd rows are orange.

Excel Tables

You can also highlight every other row in Excel by using the table formatting option.

  1. Select the range you wish to highlight.
  2. Click on Home>Format as Table.

 

highlight every other line format as table

 

  1. Select the type of formatting you require.

 

highlight every other line format table range

 

  1. Check if your table has a header row, and if so, keep the check box ticked, and click OK.

 

highlight every other line format table header

 

  1. The table is formatted including a filter row at the top. To remove the filter row and convert your table to a standard range, click in your table, and then click Convert to Range in the Tools section of the Design tab of your toolbar.

 

highlight every other line rule format table convert

 

  1. A warning box will appear. Select Yes to convert the table to a normal range.

 

highlight every other line rule format table convert message

 

  1. Your format will then be applied but the Design toolbar will no longer appear when you click in your table.

 

highlight every other line format table final

 

Highlight Every Other Line in Google Sheets

The process to highlight odd and even rows in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.

 

google sheets begin end with menu

 

  1. The Apply to Range section will already be filled in.

 

google sheets highlight every other line range

 

  1. From the Format Rules section, select Custom Formula from the drop-down list and type in the following formula:

 

google sheets highlight every other line formula

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets highlight every other line format

 

  1. Click Done to apply the rule.

 

google sheets highlight every other line final

 

  1. Click Add another rule to add a rule for the odd rows.

 

google sheets highlight every other line second rule

 

  1. Select Custom Function and type in the formula.

 

google sheets highlight every other line second formula

 

  1. Click Done to apply the rule.

 

google sheets highlight every other line second final

 

You can also use the =MOD(ROW(),2)= 1 and =MOD(ROW(),2=0 to achieve the same result.

 

google sheets highlight every other line mod final