About the author

Image of Philipp Kowalski

Philipp Kowalski is a self-taught Excel-lover, project manager and blog-owner. Besides developing "beautiful" solutions in MS' premier spreadsheet tool, he loves his fiance, his cats and surfing the net. He is CEO and owner of Inservo IT, the company behind Excel-King. He's also available on Twitter and YouTube. If you like this website, please leave a comment. If you want to know more visit the forums or look into the about section of this website.

Error
  • Error loading Modules:
Do you remember this old two-colored paper that was used in the old dase when there were large printouts from the good old matrix printers? That has to be inserted with the little perforated hole stripes on both sides? If you are - like me - a child of the 80s you will remember those very well.

We had them until early to mid 90s. The effect was used, to assure a good readability of great amounts of tabular data. This reason is still valid for larger printouts of rows with information. Instead of searching the flea market or asking your VBA programmer to achieve this outcome, you can reproduce it by using conditional formating. Imagine you have a table like the following one:

 

Tabular Data in Excel

Okay, that is not a huge amount of information as mentioned before, but it will work for our purposes. Simply highlight the cells you want to format by clicking and dragging over their row numbers (be careful not to touch the borders, as this will change line height). Then go to the menu and select Format > Conditional Formatting

Conditional Formatting in Excel

In the upcoming dialog box under Condition 1 select Fomula is and in the text field right beside the dropdown enter the formula

=MOD(ROW(),2)=0

Then click the Formula button and choose the Patterns tab. Here select a color like grey or light green from the color picker. Of course you can select any color you like, but take care of the contrast between background pattern and text color. It should look like this:

Conditional Formatting Dialog Box

Click OK and voilà, there you have it, that old fashioned look:

Conditional Formatting Example 1

What this formula does is detecting the actual row number with the table function ROW. Through the function MOD this value is divided by 2. If there is no rest, it is a an even row number which then will be colored by the format mentioned in condition 1.

If you are really retro  you can have a two colored table sheet. Simply add another condition by clicking the Add button and this time, divide the number of rows by 1 and detect the odd row numbers, which then will have a different color applied. The formula for this second condition is

=MOD(ROW(),1)=0

as shown in the image below:

Conditional Formatting with 2 conditions

The outcome should be something like this:

Conditional Formatting Example 2

Again, please take care not to use too flashy colors and take care of the contrast. I also suggest not to use more than two colors in one sheet.

This easy to apply formatting comes in very handy, if you are asked to print out a greater amount of tabular data.

Do you like it? Then feel free to comment on this thread and / or share your opinion, if this was useful for you.

Discuss this blog post in the forums (click the blue button)


Administrator
Written on Tuesday, 12 May 2009 00:00 by Administrator

Viewed 618 times so far.
Like this? Tweet it to your followers!

Rate this article

(0 votes)

Latest 'tweets' from Phil Kowalski

  • dashboard reporting http://bit.ly/eSiM68 . Dashboard Financial Reporting for Board Members Link Wednesday, 30 March 2011 16:00
  • ! http://bit.ly/fZZSOI Display hidden data in an Excel chart chart excel Link Wednesday, 30 March 2011 06:00
  • Calculate the Coefficient of Variation in Excel statistics excel http://bit.ly/h6JrOe :P Link Wednesday, 30 March 2011 01:00
  • Excel - Combine Data From 2 Different Spreadsheets With a VLookup vlookup :O http://bit.ly/gFK5YH Link Tuesday, 29 March 2011 15:40
  • Undo and Redo in Microsoft Excel 2007 windows excel :) http://bit.ly/eM064Y Link Tuesday, 29 March 2011 14:00

Comments  

 
0 #1 Art Johnson 2009-05-12 21:11
For some reason the semi-colon doesn’t work for me. Replace with a comma and it works fine. Also, delete the =0 and it changes the white rows to shaded and vice versa.
Quote
 
 
0 #2 Administrator 2009-05-12 22:00
hmmm… strange. I rechecked and it still works for me. What Excel version are you using?

Skipping the “=0″ is correct, that works as well. But with comma, it didn’t work out.
Quote
 
 
0 #3 Administrator 2010-01-18 14:06
Woohoo, newbie alarm

This was because of the different language versions of MS Excel. When you use a semicolon ";" in European version of Excel, you will have to use a comma "," in english and american versions.
Quote
 

Add comment


Security code
Refresh

Sharing is caring

Facebook Twitter Digg Delicious Stumbleupon Google Bookmarks RSS Feed 

Tweet, Tweet...

Click the little blue friend to follow Phil on Twitter!

Advertisement

Create Excel dashboards quickly with Plug-N-Play reports.