• Subcribe to Our RSS Feed

How to Make Alternate Row Color in Excel

Aug 10, 2011 by     2 Comments    Posted under: microsoft excel

Just want to share a trick that we are doing in order to make an automatic alternate color in Microsoft Excel.

It can be done using Conditional Formatting , which we can use to create an alternate row color for a table of data. This small trick will let your second row filled with a specified color.

How to: Alternate Row Colors/Color Banding

Let’s say you have a table of data Starting in A1 and ending in D6. All you need to do is select the range A1:D100, Starting from A1, then go to Format>Conditional Formatting and choose "Formula is:" and then in the box to the right, type the formula as shown below;

=MOD(ROW(),2)

How to Make Alternate Row Color in Excel

Now click the "Format" button and choose your desired cell shading under "Patterns". Then "Ok" and "Ok" again.

This is being used if you are working on an excel sheet which have lots of columns as it’s data. You don’t have to use a ruler or an straight edge in order to see the data of that row.

Above instruction is for Microsoft Excel 2003 to 2007 however in Microsoft Excel 2010 it might be a slight different.

In Microsoft Excel 2010  You need to go to Home >> Conditional Formatting >> New Rules >>Use a formula to determine which cell to format. and then same formula shall be used.

Other Excel Trick that we have shared before :

How to Print Row and Column Heading in Excel
Learn to Print the row and column heading

Excel Trick : What is the use of $ sign ?
Learn How to use the $ sign in Excel

How to unlock BOQ without Password ?
Hack your excel protected sheet and remove the password

2 Comments + Add Comment

  • Thanks for sharing this trick. Sana may screenshot ng result ng trick.

    • @Nonoy,

      HHmm.. Sige update ko mamaya.. at lagyan ko ng screen shot.. Salamat po sa comment:)

Got anything to say? Go ahead and leave a comment!

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>