Which data should be in rows, and which in columns?

Sometimes this is rather obvious, but generally speaking, you'll want the data that will be most abundant to fill rows rather than columns. Consider the readability of your data when you make this decision.

For example, a month-oriented worksheet like the one in the following picture can work well with the month labels either across the top or down the left side of the worksheet. But in this case, having the month labels down the side makes it is easier to view the worksheet on-screen and easier to fit it on a printed page. You may also prefer the worksheet to be long rather than wide so you can use the Page Up and Page Down keys to navigate on-screen.

The worksheet in the picture contains only four columns of detail data, but if your worksheet has more categories of detail data than the number of months, you may want to run the months in columns instead. When oriented horizontally, the data shown in the previous worksheet would still work, but you would have to scroll to the right to view all the data, as shown in following picture.

Usually the detail you accumulate in a worksheet best fits into rows from top to bottom—relatively speaking, a deep and narrow worksheet. It is not unheard of to build a spreadsheet that is shallow and wide (only a few rows deep, with lots of columns), but you might regret it later. A shallow and wide sheet can be annoying to deal with if you must continually scroll to the right to find information and deal with odd column breaks when printing.

Will you need to print the worksheet?

Before you start work on a worksheet, you also need to ask yourself if the worksheet will need to be printed. You may realize that you don't need to worry about printing at all, if, for example, the worksheet is to be used for information storage or reference purposes only.

If you want to print the sheet, consider how your data will look and how the sheet will work on paper. This will make a huge difference to your overall worksheet design. For example, the worksheet in horizontal orientation shown in the previous picture will require two pages to print, even if you use Landscape mode in the Page Setup dialog box (File menu, Page Setup command). The second page of the printout will contain some of the monthly totals, but you won't see the names of the regions unless you use the Print titles options on the Sheet tab of this dialog box to repeat the headings on each page.

How will the data be used?

You also need to consider what the printout will be used for. If it's going to be used in a management report, you'll want to try getting the salient information to fit on one page. If it's for a presentation, you may need to distill it further, or create smaller, more digestible chunks of data that can be summarized in a small grid of a dozen cells or so, so it will fit onto a transparency or a slide. If you have massive amounts of data to start with, you can create summary pages for various purposes, as shown in the following picture.

If the worksheet is for auditing or reference purposes, you'll probably want to see all the information it contains. Orientation is a big issue here. You can print either in landscape (horizontal) or portrait (vertical) format, so design your worksheet accordingly. Sometimes using a landscape orientation helps if you have lots of columns. If you have an inordinate number of columns, you may want to try and segment your data into an overall system of worksheets—chunks that can be realistically printed without losing context or readability. For example, the sheet tabs at the bottom of the workbook shown in the previous picture give evidence that the displayed summary sheet actually consolidates the data from six other sheets in the same workbook.

If you don't need to print everything on your worksheet, you can use outlining to collapse the detail in large worksheets. For example, you can use outlining to display and print only the totals, as shown in the following picture.

Who is the audience?

Are you building a worksheet for your own use, or will you be sharing it with others online or in printed form? In other words, does the worksheet need to look marvelous, or is fancy formatting optional? Do you need to create a big-picture summary or overview for others? It's definitely important to consider audience when deciding how your worksheet is going to look.

If you're close to the data in your worksheet—that is, if the data is your job—you probably think that the details are a lot more interesting than others might. You need to think like the people you will be presenting this information to, and tell them what they need to know—no more, and certainly no less. If your worksheet contains a lot of data that your audience doesn't really need to see, which is almost always the case, you can create a summary sheet specifically for the purpose of mass consumption.

Would your worksheet survive without you?

If you are creating worksheets that might at some point be used by others, make sure they are understandable and well documented. Most of us don't think about documentation, but every worksheet you create for business or personal use should be created with the possibility in mind that others will need to figure it out some day—possibly without your help. If you change jobs, you will be leaving a good legacy behind for the next person, which reflects well on you. A little documentation goes a long way, as shown in the following picture.

You also need to prepare worksheets containing important personal records with survivability in mind. Not to sound too morbid, but if you were to unexpectedly shed this mortal coil, you wouldn't want to leave your family in the lurch because your financial worksheets are undecipherable.

Does the worksheet rely on imported data?

Many people work with data that is compiled elsewhere as the basis for their worksheet analyses. For example, a database located either on your computer or somewhere on a network is often the repository for specific information that you extract and analyze. If this is the case, try to make it easy on yourself.

Often, we use the ad hoc approach to working—that is, we do it quickly, when it's needed, with no particular attention paid to repeatability. If you gather information from a database, you might be able to construct queries that you can execute again and again, on whatever schedule you need, rather than starting from scratch each time. This way, you can ensure that the imported data will be structured in exactly the same way each time.

You might use the structure of the imported data as the basis for your worksheet design. Or, it might make sense to keep the imported data on a separate sheet that no one will see, and to construct nicely formatted sheets you can use to extract only the pertinent information. For example, the following picture shows just such a worksheet. You can see that the raw data is on a separate sheet behind the information sheet.

Note  Sometimes when you say the word "database," people's eyes glaze over in anticipation of a barrage of incomprehensible terminology. While using a database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) program can be complex, consider that many of the worksheets you'll create in Excel are actually rudimentary databases. The telephone directory is an example of a database in printed form. In database terminology, each phone listing in the directory is a record of the database, while each item of information in a listing (first name, last name, address, and telephone number) is a field of the record.

Do you need more than one worksheet?

Spreadsheet programs began as a better way to store, present, and interpret information that previously had been kept on paper and calculated by hand — probably using a 10-key calculator. Often the first worksheets we created when we were climbing the old Excel learning curve were little more than clean two-dimensional reproductions of what we used to do on paper.

One way to step up from the old paper paradigm is to use modular design. Modular design is a sort of "structured programming" or "object oriented" approach, where you carve your data into logical chunks that make sense as standalone elements. (The other design approach, called "hierarchical," is organized for error identification and maximum readability.) If there's no need to keep detail data in any kind of presentable format, why bother? Instead, concentrate your worksheet beautification program on the summary sheets and charts that you will share with others.

Have you allowed room to insert new rows and columns?

It's critical to allow for expansion and editing after your worksheet is assembled. It's generally a good idea to add a few extra rows and columns to the detail area and to keep totals separated from the detail data by a row or column or two, if possible. One of the most common editing actions you'll perform is inserting new rows and columns.

Excel has gotten a lot smarter about this over the years, making obsolete some of the rules of thumb that we old-timers have collected. But it's still possible to mess up. There's a rather famous folkloric tale about an accounting person who inserted a row at the bottom of a range of cells but forgot to adjust the totals formulas and was fired because his numbers were $200,000 off.

The moral? Edit worksheets carefully and audit yourself ruthlessly — especially if your job is on the line.