Odd or Even Row Formulas in Excel

Add to Favorites
Author: | Edits: don

Formulas to determine if the current cell is odd or even; this allows you to perform specific calculations, value incrementing, row shading using conditional formatting, and more.

Sections:

Odd & Even Row Formula (Newer Excel Versions)

Odd & Even Row Formula (Excel 2002 and Earlier)

Additional Examples

Notes

Odd & Even Row Formula (Newer Excel Versions)

Check if current row is odd:

=ISODD(ROW())

ISODD is the function that checks if a number is odd or even. Returns TRUE for odd rows and FALSE for even rows.

ROW returns the row number of a cell. If you leave the ROW function empty, without an argument, like in this example, it references the row where you put the function; however, you can also include a range reference in the function and it will then return the number of the row for that range reference.

Check if current row is even:

=ISEVEN(ROW())

ISEVEN is the function that checks if a number is odd or even. Returns FALSE for odd rows and TRUE for even rows.

ROW returns the row number of a cell. If you leave the ROW function empty, without an argument, like in this example, it references the row where you put the function; however, you can also include a range reference in the function and it will then return the number of the row for that range reference.

One Formula to Check Even/Odd Rows

It doesn't matter which one of the above formulas you use, both of them will return alternating TRUE/FALSE values for a range.

If you input them into Excel and copy them down, it will look like this:

f226e50a2dda0e72f1f522b6b68b4443.png

Add Some Logic

If you want to do something specific with odd/even rows, you can wrap the above functions inside of an IF statement like this:

=IF(ISEVEN(ROW()),"Even","Odd")

This example outputs Even for even rows and Odd for odd rows, but you can make it do whatever you want.

bef269595292c2e01eafd061a7977899.png

Odd & Even Row Formula (Excel 2002 and Earlier)

In earlier versions of Excel, you didn't have the ISODD and ISEVEN functions and you had to use the MOD() function along with a little programming type logic.

=IF(MOD(ROW(),2)=0,"Even","Odd")

ROW function returns the row of a cell. If you leave the ROW function empty, without an argument, it references the row where you put the function; however, you can also include a range reference in the function and it will then return the number of the row for that range reference.

MOD is the function that really does the magic here and, though it looks scary, it's pretty simple. The MOD function returns the remainder after a number is divided by another number. In this case it will divide the current row number by 2; if it is able to divide the number by 2 without having a remainder, then you know it is an even number, and, in this case, an even row. 

The formula logic knows that the row is even because, it takes the result of the MOD function, the remainder after division, and then checks if it is equal to 0 or not. If there is no remaineder, then that is the same as 0, which is checked for in the =0 part of the formula, and Excel will output the TRUE argument from the IF statement.

a37a27eee890f337b9b4ae73056f559a.png

Output True/False Simple Formula

You don't have to use an IF statement if you just want to get TRUE and FALSE values in Excel.

=MOD(ROW(),2)=0

Change the 0 to a 1 if you want it to return false for even rows.

ea3da3c2004d3f383ff55f1a70061c43.png

Additional Examples

Increment a Value Every X Number of Rows in Excel

Shade Every Other Row in Excel Quickly

Sum Values from Every X Number of Rows in Excel

Notes

These are simple examples that will allow you to further build-out formulas that can do powerful things in Excel, such as incrementing numbers based on the row, shading rows using conditional formatting, and more.

Download the attached workbook to get these examples in Excel.


Excel Function: IF(), ISEVEN(), ISODD(), MOD(), ROW()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Complex Structured References (Table Formulas) in Excel
Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...
Convert Column Number to Letter Using a Formula in Excel
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Calculate Hours Worked - Day or Night Shift & Breaks in Excel
Tutorial: Single simple formula to calculate the hours worked for a day shift or night shift and inc...
Regular Expression Search Formula in Excel - Regex
Tutorial: Part 1 - Regular Expression Match Formula in Excel (No VBA) Part 2 - Complete Excel Reg...
5 Tips for Evaluating Complex Formulas in Excel
Tutorial: 5 simple tips to evaluate any complex formula in Excel! These tips combine to give you th...
Hide Formulas in Excel
Tutorial: How to hide a formula in Excel so that a user cannot see it, select it, or change it - th...
Tutorial Details
Excel Function: IF(), ISEVEN(), ISODD(), MOD(), ROW()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course