98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Create Contrasting Coloured Rows In Excel
Wed 24th August 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsT. Rowe Price
Facilities Team Leader Julie Ramsey Presentation Skills Very satisfied with the training course and the way in which it was delivered. Andrew is a very competent trainer and was very encouraging. I thoroughly enjoyed this course. Frankham Consultancy Group Limited
Trainee Building Surveyor Kayleigh Barnard Excel Intermediate Great course, learnt lots Queen Mary University of London
Assessments & QA Administrator Jack Osborne Excel Intermediate Great facilities, great training. Room temperature got a bit cold but other than that, fantastic. |
PUBLICATION GUIDELINES