Highlight every other row
Here is how to highlight every other row using conditional formatting.
Conditional formatting formula:
Alternative CF formula:
This formula colors every second row if any cell in the row is populated. If you know you will add more records later to the list, select and conditional format a range larger than the list. Empty rows won´t be formatted.
How to apply the conditional formatting formula in excel 2007:
- Select the range, example cell range A:C
- Press with left mouse button on "Home" tab on the ribbon
- Press with left mouse button on "Conditional formatting"
- Press with left mouse button on "New rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Press with left mouse button on "Format values where this formula is true" window.
- Type =ISEVEN(ROW())*OR($B3:$D3<>"")
- Press with left mouse button on Format button
- Press with left mouse button on "Fill" tab
- Select a color
- Press with left mouse button on OK!
- Press with left mouse button on OK!
Explaining the contional formatting formula in cell A2
=ISEVEN(ROW())*OR($A2:$C2<>"")
Step 1 - Check if row number is even
=ISEVEN(ROW())*OR($A2:$C2<>"")
ISEVEN(ROW())
becomes
ISEVEN(2)
returns TRUE in cell A2.
Step 2 - Check if any of the cells in row are not empty
=ISEVEN(ROW())*OR($A2:$C2<>"")
OR($A2:$C2<>"")
becomes
OR({"Federer", " Roger ", 25}<>"")
becomes
OR({TRUE, TRUE, TRUE})
and returns TRUE
Step 3 - Multiply functions (AND logic)
Both functions need to return TRUE in order to return TRUE
=ISEVEN(ROW())*OR($A2:$C2<>"")
becomes
TRUE*TRUE
and returns TRUE. Row 2 is conditional formatted.
Get excel sample file for this tutorial
color-every-second-row-using-dynamic-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)
Functions used in this article
OR(logical1, logical2, …)
Checks whether any of the arguments are TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE
ROW(reference)
Returns the row number of a reference
ISEVEN(number)
Returns TRUE if the number is even
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Extract a list of alphabetically sorted duplicates from a column
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
How to use the EVALUATE method
The Evaluate method converts an Excel name to an object or value. The picture above demonstrates a macro that uses […]
How to use the EXPONDIST function
The EXPONDIST function calculates the exponential distribution representing an outcome in the form of probability. This function is in the compatibility […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
Partial match with two conditions and return multiple results
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
Partial match for multiple strings – AND logic
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
6 Responses to “Highlight every other row”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
This is helpful, thanks, but I've been looking for the formatting to take place AS I fill new cells.
So my scenario is: A clear-formatted excel sheet, and ONCE you insert data into each row, the color of the row changes, e.g. row 1 becomes light blue, row 2 becomes dark blue, BUT row 3 is still clear-formatted because I haven't inserted data in it yet.
Do you know how I can do that?
*Thanks!!!*
@Sawsan,
Select all the cells (starting from upper left corner of the range so the the upper left cell ends up the active cell) that you think you will ever need to put your alternating row colors in (I would refrain from selecting entire columns), then follow the blog's instructions to get to the Conditional Formatting dialog box and then use this formula...
=AND(ISEVEN(ROW()),COUNTIF($A1:$C1,"*")>0)
Change the range as indicated, but note that your formula should only specify the first row's cells range even though you will have many more rows selected.
@Sawsan,
I guess I missed your request for a second color. That will require a second condition for the second color... use the identical formula I gave you in my previous message for this condition, but change the ISEVEN function to ISODD.
hi,
i'm interested to know on the conditional formatting.
how do i set the condition to evaluate a dynamic range?
my example is this:
Rule: =MAX($G$5:$G$20)=$G5
Applied to: =$AG$5:$AG$20
based on the value in G5:G20, the rule will determine the max value and highlight it on AG5:AG20
If i add new rows of data to G21 onwards, the rules does not "extend" to the newly added rows.
david,
I got this working in excel 2007:
Rule: =MAX(OFFSET($G$5;0;0;COUNTA($G5:$G1000))=$G5
Applied to: =OFFSET($AG$5;0;0;COUNTA($G5:$G1000)
thanks oscar,
i was using a dynamic named range as the rule in conditional formatting.
will try ur solutions ;)
thanks!!!