Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel ROWS Function
  • How to use Excel OFFSET function
  • Vlookup Examples in Excel
  • Multi-criteria lookup and transpose in Excel
  • Approximate match with multiple criteria in Excel

Data Analysis Examples

  • How to calculate correlation coefficient Correlation in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Conditional Formatting Data bars Examples in Excel
  • Get column name from index in Excel Table
  • How to create Checklist in Excel

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only
  • Excel Data validation unique values only
  • Excel Data validation allow uppercase only

Highlight every other row in Excel

by

This tutorial shows how to Highlight every other row in Excel using the example below;

Formula

=ISEVEN(ROW())

Explanation

If you want to highlight every other row in a table (sometimes called zebra striping) with conditional formatting you can do so with a simple formula that uses either the ISEVEN or ISODD function. For example, if you want to highlight every other row in the range B3:F102 select the entire range then create a conditional formatting rule that uses this formula:

Worked Example:   Highlight dates greater than in Excel
=ISEVEN(ROW())

How this formula works

When you use a formula to apply conditional formatting, the formula is evaluated for every cell in the selection. In this case, there are no addresses in the formula, so, for every cell in the data, the ROW and ISEVEN functions are run. ROW returns the row number of the cell, and ISEVEN returns TRUE if the row number is even and FALSE if the row number is odd. The rule will trigger on TRUE, so even rows will be shaded.

Worked Example:   Highlight row and column intersection exact match in Excel

To shade odd rows, just use ISODD instead:

=ISODD(ROW())

A MOD alternative

If you’re using an older version of Excel (before 2007) you may not have access to ISEVEN and ISODD. In that case, you can use the “classic” formula for shading even or odd rows, which depends on the MOD function:

=MOD(ROW(),2) = 0 // returns true for even rows
=MOD(ROW(),2) = 1 // returns true for odd rows

MOD takes a number and a divisor as arguments, and returns the remainder. The ROW function provides the number, which is divided by 2, so MOD  returns zero when the row is an even number and 1 if not.

Worked Example:   How to create Checklist in Excel

Post navigation

Previous Post:

Highlight multiples of specific value in Excel

Next Post:

Highlight entire rows in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to Check If A Cell Contains Specific Text in Excel
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to display conditional message with REPT function in Excel
  • Get project midpoint in Excel
  • Convert date to text in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • How to get same date next year or previous year in Excel
  • Convert Unix time stamp to Excel date
  • Bond valuation example in Excel
  • How to calculate principal for given period in Excel
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • DURATION function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles