Create Contrasting Coloured Rows In Excel

Wed 24th August 2011

Back in the days of the dot matrix printer, specialist paper was produced that allowed rows to appear in contrasting shades for easier reading. Excel can perform this same function by printing the contrasting shades directly onto paper. This article shows you how.

Back in the early days of computers, before WYSIWYG changed our lives, lumbering dot matrix printers were state of the art. These mechanical monsters screeched like a seagull with toothache as they produced data in a barely readable shade of grey on tractor-feed paper. There was a choice of one font and it came in a single size. To make deciphering data produced on these printers easier on the eye, specialist paper was introduced which had two contrasting shades running horizontally to help guide the eyes along the same line of data.

Back then, bigger was considered better as far as office equipment was concerned and the average printer was the size of a small oven. Yet, even though these have been surpassed by far more efficient, smaller and quieter inkjet and laserjet printers, dot matrix technology is still used to print in cases where quality is not an issue, and that receipt you get from the ATM will probably be produced on a scaled down dot matrix printer.

But while things have moved on considerably since those days of giant office equipment, sometimes printed data can be stressful to the eyes, no matter how crisp the characters. It can be difficult to keep track of data on sheets filled with columns and rows of printed values against a solid white background.

One way to make such data easier on the eye is to emulate the continuous feed paper of the dot matrix age, and have alternate rows appear in contrasting shades. Instead of using specialist paper, Excel will allow you to add a shade to rows directly on your worksheet via our old friend, Conditional Formatting. As these shades will be printed, you need only add a single shade to contrast against the colour of the paper you are printing on. The shade you choose should not be a heavy colour, but the merest pale shade, which will serve the purpose and save on ink, especially if you are printing off a lot of data.

In Excel 2007 and on, select the data you wish to apply the alternate shading to. Display the Home tab of the ribbon and click the Conditional Formatting tool. Click New Rule to open the New Formatting Rule dialog box. In the Select a Rule Type area, choose Use a Formula to Determine Which Cells to Format. Type the following into the formula space that opens:

=MOD(ROW(),2)=0

Click on the Format button to open up the Format Cells dialog box, and select the Fill tab. Choose the colour you wish to apply to your alternate rows and click OK. This will take you back to the Conditional Formatting dialog box. Click OK again and the formatting will be applied to your rows.

Excel 2007 has the facility to apply alternate shading to rows via the Styles group on the Home tab, using the table formatting feature. Using Conditional Formatting to achieve the same effect offers the user more control over the formatting applied, and so is worth the extra effort.

In pre-2007 versions of Excel, select the data you wish to apply the alternate shading to. Click on Format and select Conditional Formatting from the menu. In the dialog box, and click on the down arrow in the Condition 1 area. Select Formula Is and the dialog box will adapt to receive your formula. Enter the following:

=MOD(ROW(),2)=0

Click on Format and then select the Patterns tab. Choose the colour you wish to apply to your alternate rows and click OK. This will take you back to the Conditional Formatting dialog box. Click OK again and the formatting will be applied to your rows.

Conditional Formatting is a very useful feature of Excel that can add a variety of different formatting styles to your worksheets. In learning how to use Excel, you will discover that Conditional Formatting has the capability to perform many useful tasks, and that there are also some pretty cool effects up its sleeve too.

Author is a freelance copywriter. For more information on advanced excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1896-create-contrasting-coloured-rows-in-excel.html