How to Make Alternate Row Color in 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)

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