November 25, 2008

Frederique Klitgaard

Good worksheet design is more important than you might think, especially when you plan to share your worksheet data with other people. In this month's column, Frederique goes over some basic practices you may want to implement whenever you create a new worksheet.

Applies to
Microsoft Office Excel 2007

---

Read more Office Hours columns
Get Office Hours columns via RSS

---

Ever had to work with a poorly designed Excel worksheet that someone else created? It probably had lots of text and numbers in various fonts and formats, numerous columns and rows without labels, multiple sheet tabs displaying default names such as Sheet 1, and large comments that obscured the data underneath. And most likely, getting it all to print correctly wasn't quick and easy. Not a fun experience, for sure!

When we create a worksheet, we often forget that it has to be easy for other people to read, scan, and understand. Taking a little extra time to apply a few simple formatting and worksheet design practices will pay off in the long run.

For example, I'd prefer to work with the following worksheet, because I immediately understand what it's about. Wouldn't you?

Labels, labels, labels

To start, I make sure that the data in my worksheet is arranged in rows and columns that are clearly labeled. Without labels, it's hard for anyone to make heads or tails of the data.

To create labels, I simply enter text that refers to the data in a row or column, and then I'll format it so that it stands out. For ease or reading, I try to keep labels short and to the point.

Formatting doesn't just look nice — it's functional!

Yes, formatting does make a worksheet look more attractive, but that isn't the only reason why you should apply it. Formatting is very useful for organizing worksheet data and drawing attention to important details.

I always format labels to make them stand out. If I have lots of data, I may change the font size to fit more data on one page, and I wrap text in columns to keep their column widths as small as possible. I also frequently use cell borders for clarity. All of these options are available on the Home tab.

For a consistent look and feel, I typically stick to a few formatting styles that I apply to specific types of data, and I make sure that related numbers are using the same number format. When it comes to formatting, a little goes a long way — keeping it simple prevents clutter!

Although I often just use the buttons on the Home tab to apply a custom format, Excel 2007 also provides many built-in cell styles. Some of the cell styles are based on the theme (theme: A combination of theme colors, theme fonts, and theme effects. A theme may be applied to a file as a single selection.) that is applied to the worksheet, which helps create a well-coordinated look.

If my data is in a table, I can apply table styles. Tables already have column labels by default — no need to add them manually!

The right name can make a difference

Giving something the right name is important because it will help you quickly locate the data you need. I make a habit of changing the default sheet tab names to something useful. By right-clicking the sheet tab, I can easily rename it. If a worksheet is empty, I often delete it — no need for anyone to click the sheet, only to find out that it does not contain any data.

Similarly, without a workbook name that indicates what the whole workbook is about, a workbook may become really hard to find when you need it again. Therefore, I highly recommend using a descriptive name when saving a workbook.

To help me remember the workbook and worksheet names, I can refer to them in the header or footer of my worksheet. The names will then appear on the pages when I print the worksheet.

Comments that are helpful

Comments can be helpful when they add necessary information about your worksheet data. However, adding too many large comments and then leaving them displayed is not recommended — it can distract attention and obscure the worksheet data. When it's absolutely necessary to include a brief explanation or other helpful information about the contents of a cell, I add a comment to that cell. I then also make sure it's displayed only when I hover over the cell.

And because I don't want comments to be printed on top of the worksheet data, I make sure that they are included as end notes instead, when the worksheet is printed.

Prepping for printing

Before I share the workbook with anyone else, I optimize it for printing. I go to Page Layout view by clicking its button at the bottom of the worksheet. In this view, I can easily verify what will be printed on each page and make some quick adjustments. For example, I might change the page orientation to landscape to include more columns per page, adjust column widths, and wrap text as needed for a better fit.

Then I go to the Page Layout tab to establish some other page settings. Although this tab provides many buttons, I go directly to the Page Setup dialog box by clicking the Dialog Box Launcher on the lower-right corner of the Page Setup group.

In this dialog box, I make sure that:

• Rows that contain labels are printed on each page
• Gridlines are printed, together with the row and column headings
• Comments are printed at the end of the sheet

All of these print settings are stored with the workbook when I save it. All anyone will have to do is click the Microsoft Office Button , and then Print, and voilà…a good-looking printout is on its way to the printer!

About the author

Frederique is a writer in the Office User Assistance group at Microsoft. She has written Excel Help content for several years.

Read more Office Hours columns
Get Office Hours columns via RSS