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

Lookup and Reference Examples

  • Left Lookup in Excel
  • How to use Excel OFFSET function
  • How to use Excel INDIRECT Function
  • How to use Excel MATCH Function
  • Complete List of Excel Lookup and Reference Functions, References and Examples

Data Analysis Examples

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How To Insert and Customize Sparklines in Excel
  • Data Series in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel

Data Validation Examples

  • Excel Data validation must contain specific text
  • Prevent invalid data entering in specific cells
  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total
  • Excel Data validation number multiple 100

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:

=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 rows with blank cells 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 get address of last cell in range 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
  • Remove unwanted characters in Excel
  • How to count specific characters in a cell in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to split text with delimiter in Excel
  • How to create email address from name in Excel
  • Get work hours between dates in Excel
  • Display Days until expiration date in Excel
  • Get fiscal year from date in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Excel Date & Time Functions Example
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate simple interest in Excel
  • DISC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles