How-To

Microsoft Excel: How To Alternate the Color Between Rows

Color Banding

Sometimes you need to make large spreadsheets easier to read. Alternating colors between rows is a good way to do it. Here’s how.

In Excel, assigning an alternating color scheme to rows is known as color banding.

Start by selecting the cells you want to apply color banding to. Or press [Ctrl][A] to select the entire sheet.

microsoft excel spreadhseet cells selected

Next, select the Home tab on the Ribbon, select Styles and click Conditional Formatting.

microsoft excel conditional formatting

A drop-down menu will display, click New Rule.

conditional formatting new rule

The New Formatting Rule dialog appears. Click the bottom option labeled Use a Formula to Determine Which Cells to Format.

In the empty format value field, copy and paste in the following formula:

=MOD(ROW()/2,1)>0

The New Formatting Rule window will look like this. Click Format.

excel formula to create banded rows

Pick a fill color and click OK.

excel fill pattern

The New Formatting Rule windows will display the color Preview. Click OK.

 excel-banded-rows-ok

The section of your spreadsheet will now be color banded.

rows of alternating colors achieved

If you want to change the color or add more cells, highlight the cells on the spreadsheet. Then on the Ribbon go to Conditional Formatting > > Manage Rules.

manage excel 2007 or 2010 rules

The Conditional Formatting Rules Manager window comes up. Click Edit Rule.

edit rules in microsoft excel

You’ll be taken back to the Edit Formatting Screen and can adjust accordingly.

format rules


15 Comments

15 Comments

  1. Dave  

    There is an obvious typo in two places where the number 9 is inserted in place of the left parent (shift 9). Who hasn’t done that!

    I question the formula. Use =mod(row()/2)>0. It works equally well and is less convoluted.

    • ChuckZilla  

      I used your formula and it didn’t work.

  2. Dave  

    Oops, should be =mod(row(),2)>0

    • Peter Devos  

      both formulas
      =mod(row(),2)>0
      =mod(row()/2,1)>0
      are giving error message in MS office Pro 2010 ?????

  3. James M Singleton  

    I have been looking for a Windows application to automatically do it like an Excel addon.

  4. Michael  

    Why not use the Format as Table button beside it to do it automaticaly, then turn the header off and select your banding style on the Design tab that appears?

    • I was thinking the same thing…there are much easier ways to do this, however conditional formatting is an amazing tool everyone using excel for presentation data should learn. Makes accounting and notable figures much more easy to find and work with.

  5. Peter Devos  

    Here is the correct formula ( separator is instead of )!
    =MOD(ROW()/2;1)>0

  6. nury  

    thank you i just tried it and it worked thanx again

  7. This really helped me a lot. I struggled with this for 1 hr could you be leave it.

  8. Techno Sage  

    Very nice tip!

  9. don  

    way too complex, =iseven(row()) or =isodd(row())
    does the same thing

  10. Scott  

    Prior to XL2007, ISEVEN and ISODD required loading the Analysis Toolpak.

    In the mod formula, the >0 part isn’t necessary. 0 will evaluate to FALSE and any non-zero number will evaluate to TRUE, therefore;

    =MOD(ROW(),2)

    will suffice.

    If you want to start with a blank row, instead of a filled row, just subtract 1 in the formula:

    =MOD(ROW(),2)-1

  11. laune  

    if error occurs
    should be a “;” instead of a “,” in the formula between row() & 2….
    so. =mod(row();2)=0

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top