Automatically Alternate Row Coloring |
+1 This Page
|
Two Ways to Alternate Row Coloring in Excel
If you're using excel 2007 or 2010, probably the quickest and easiest way to get alternating row coloring is through the use of Excel's built in table feature. However, there are several instances when the solution calls for something other than an excel table. In this article we're going to cover a very simple, yet effective way to automatically alternate row coloring within an excel spreadsheet. We'll use 3 concepts:
- Conditional Formatting
- The MOD function
- The ROW function
The Short Answer
For those of you who don't need a detailed step by step how-to, here you go:
- Highlight the area of interest
- Go to the conditional formatting section in the ribbon and select new rule
- Use the formula =MOD(ROW(),2)=1 and then set your preferences
Step-by-Step Instructions with Screen Shots
Step 1
Highlight the area of interest - in this example we're not using any data, but you get the point.
Step 2
Under the 'Home' ribbon, select 'Conditional Formatting' then select 'New Rule'
Step 3
The 'New Formatting Rule' box will open, select 'Use a formula to determine which cells to format' and enter the following formula as shown in the picture below; note, you can copy and paste this formula.
=MOD(ROW(),2)=1
=MOD(ROW(),2)=1
Step 4
Select 'Format' and then pick and choose all of the formatting you're interested in - in this example we're only choosing to fill the rows. After you select your formatting, click 'OK' (you'll have to click this twice).
Your Done!
 |
|
||
How does this work?
The first thing that happens is that the =ROW() formula identifies the row number (i.e., 1 = 1, 2 = 2, etc). Then the MOD function divides the row number by a divisor, in this case 2 and returns either a "0" for even rows or a "1" for odd rows. Because we set the formula = 1, when that's true the conditional formatting is turned on. You could change this formula to be less than 1, =MOD(ROW(),2)=0, if you wanted all even rows highlighted.
If you found this page helpful don't forget to +1 it!
Spreadsheets on this Site that use Alternating Row Coloring
We've used the offset function in some of the templates uploaded on this website. If you would like to download the excel templates and see some more real world uses of this fantastic function please click on the links below:
