Excel Tip... Alternate Row Shading

More tech stuff… God I need to get a life.

There are a lot of powerful computer programs that can be very frustrating to use. It seems that software companies expect everyone to become an expert with their program. Don’t they know that there are a lot of people that just want to be able to use the program without having to get a degree in technology? Here is an Excel tip that may make life a little easier.

Have you ever been working with a sheet in Excel and thought how nice it would be if the lines in the sheet could alternate shading between and white and gray? You know, just to make it a little easier to read. Sure you can fill the cells in yourself, but when you add a line, everything is out of whack and you have to redo the whole thing. And if the sheet is more than a page, well… just forget about it.

There is an easier way to do this using the conditional formatting option. Let’s go through a simple example…

Start by getting your data entered into the spreadsheet and formatted…

Next, select the area where you want the alternating lines to appear…

By the way, if you want the entire sheet to have this alternating effect, click the empty cell heading between Row 1 and Column A. This will select all of the cells in the entire sheet…

Now, click on the Format Menu pull down, and select Conditional Formatting…

Set Condition 1 so that Formula Is = MOD(ROW(),2)=1

This basically tells Excel to get the row number and divide it by 2… If there is a remainder after doing the division, that is an odd numbered row and it is shaded. Otherwise Excel leaves it unshaded.

To actually set the shading color, click the Format… Button

Pick the Patterns tab, and then select your fill color (I usually just select Gray)

Your conditional formatting box will look like this…

For this example I decided to highlight only the rows in the table, not the entire sheet. The final result will look like this…

Now you can add rows within the table, and everything will automatically highlight.

By the way, once you have used conditional formatting in a cell, you can copy and paste that format anywhere you want. Select a formatted cell, then copy. Next select the cell needing the format applied to it, and select Paste Special… from the Edit pull down menu (you HAVE to do it this way… the Enter key or Control-V won’t work). In the Paste Special box, click the dot next to Formats

Then click OK. The conditional formatting (and any other formatting applied to the original cell) is now applied to the destination cell. You can even do that to a whole range of cells.

See... didn't Microsoft make that nice and easy?