When you have a large database, it can sometimes be difficult to see the data clearly. In cases like this, it can be useful to shade alternate rows to make the data easier to view. There are a couple of handy functions in Excel using conditional formatting that will do this for you quickly and easily. Here’s how:

Format as table

In Excel 2010, the easiest way to do this is using the ‘format as table’ function. Highlight the range of cells that you want to format and select ‘Home’ tab > ‘Styles’ group > Format as table. 

You’ll then see a large drop down box with various pre-set styles in lots of fun colours! Select the colour and style that you want, and ‘hey presto!’ your boring data list is transformed into an exciting, cheerful table! In older version of Excel, you can do the same thing using conditional formatting and an easy formula…


Using Conditional Formatting

To shade alternate rows in your spreadsheet, begin as before by highlighting the range of cells, rows or columns that you want to colour.

Next select ‘Format’ > Conditional Formatting > Add new rule

You’ll then see the conditional formatting dialogue box with a range of options. In the top box, labelled ‘select a rule type’ choose ‘use a formula to determine formatting’.

In the next box under ‘edit the formula’ enter the formula =MOD(ROW(),2)=0

Next, you need to choose your formatting – you can be as creative as you like here, choosing a fill colour for your cells, or perhaps also changing font type and colour if you prefer.

Click ‘Format…‘ and you will then see the Format Cells dialogue box where you can choose your options.

Note: it would be wise to choose a fairly light colour to make sure your database is still legible!

Click ‘OK‘ and ‘OK’ again to go back to your worksheet.

The great thing about this formula is that it will change as your spreadsheet does, so if you add or delete rows, the shading should remain on alternate rows. If you decide that you want to remove the formatting, this is easily done by highlighting the cells again, select Format > Conditional Formatting > Clear rules