#226 Excel Readability

So you just finished that big Excel report, double-checked the data and you’re feeling pretty good about yourself. What do you do next? Kick back, put your feet up, and imagine all the praise that will follow? Umm..not so fast my friend. Have you given any thought to those poor souls who have to actually read it???

Trust me when I say that spending a few minutes making those endless rows of data easier to read and less likely to result in an industrial case of eye strain will win you much more appreciation. After all the best data in the world is useless if it’s not readable.

For any big spreadsheet, shading alternate rows is very helpful and you can do it in less than 2 minutes. There are two options for this: automatic formatting and conditional formatting.s.

Automatic Formatting:

The simplest approach is to use one of the following pre-defined autoformat, which can be found from the Format menu under AutoFormat….

» List1 shades every other row by using the Gray-25% fill color.

» List2 shades every other two rows by using the Light Green fill color.

Note: Don’t apply this method until after you complete your worksheet. If you update, filter, or sort the data later, you will need to re-apply the autoformat.

Conditional Formatting:

Conditional formatting is a more flexible, dynamic way to achieve the same result. Unlike the AutoFormat option, this method preserves the alternating format if you update, filter, or sort the data.

How it works:

  1. Select the range you want to format
  2. From the Format menu, select Conditional Formatting
  3. Under Condition 1, select Formula Is. then in the data entry box, type =MOD(ROW(),2)=1
  4. Click the Format button, then click the Patterns tab.
  5. Select a light-blue color (or any other color you want), and then click OK.
  6. In the Conditional Formatting dialog box, click OK.

While we’re on the topic of spreadsheet readability, you should probably freeze the top row and first column or so too so when readers scroll they can still see the labels, etc.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s