- With conditional formatting and a single formula, you can automatically change odd or even rows background color: difficulty level is "beginner", because you can just copy-paste the code.
- Select the columns and/or rows you want highlighted (quick selection tips).
- Click "Conditional Formatting" (Home tab), and select Highlight Cells Rules > More Rules:
- Choose "Use a formula to determine which cells to format" (last option).
- Right-click and copy the code below, then paste it inside the formula text box in Excel:
To match odd rows (first, third...), use this formula:
To match even rows (second, fourth...), use that one:
Tech Note: the formula is pretty straightforward, and uses two built-in functions. • The
mod()function performs a modulo operation, whose result is the remainder of a division.
• Therow()function returns the current row index (first row =1, second row =2, etc.)
• We pass to themod()function two values (called "arguments") - the current row index (given to us by "row()"), to be divided by the second argument, which is "2".
• The first equal sign just tells Excel that the text is a formula to evaluate, not a value.
• The second equal sign tests if the remainder is equal to "1". - Click on the Format button, and choose a background color; you can also pick a pattern style and color, and explore the other tabs to add borders or different text effects on matched cells.
- Click OK to save your formatting, and OK once more to apply the new coloring scheme.
- Excel leaves the unaffected rows' background as-is: to use two separate colors, apply the formula for odd rows with one color, then apply the other to highlight even rows in another color!
- To remove conditional formatting without deleting actual cell values, click on the Clear button (it's the eraser icon, on the right of the ribbon), and choose "Clear Formats":
Email AOL Mail Gmail Hotmail Yahoo Mail MS Office Excel 2010 OneNote 2010 Outlook 2010 Word 2010 OS & Devices Kindle Fire Windows 7 Web Dev C# and .NET JavaScript jQuery