How to Plan a Great Spreadsheet

Creating a spreadsheet is easy. You just put the column headers in, stick the row titles in as you get data, and then plug in numbers.

However, though the spreadsheet itself is easy to put together, it's a lot harder to create one that can return good information to you. For years, I set up spreadsheets for departments, budgets, and special projects that helped get meaningful information out of raw data, and I learned a lot of common-sense practices that every spreadsheet creator should remember.

The first and most important rule is to use the right data to begin with. Sounds easy? It's not. For instance, if you were putting together a timesheet system, how would you track fractions of hours? How would you ensure you could add and subtract hours to figure out how long someone had worked? The most common error on timesheets involves writing down the wrong number of hours worked - how could you prevent that?

Start by writing down the questions that you and your bosses are going to have about the spreadsheet data. The more information you can extract from raw numbers, the better your spreadsheet will work.

As an example, suppose your boss wanted to create a sales tracking sheet for several stores. You have the raw data: daily sales totals for each store. That's the easy part. Many people might just do a quick-and-dirty sales sheet: dates at the top, store numbers down the side, and plug in the numbers.

Questions you may want to ask about that data include:

* Who was the manager at the highest sales times?

* Which stores do overall best sales?

* Is there a difference between Wednesday sales and Saturday sales?

* Were sales during Memorial Day Weekend higher?

* Is there a store that seems to have a high loss ratio? (this can indicate employee theft)

Now you have other data that you need to complete the sheet: when managers were working, which days of the week match each date, a marking for holidays, and some formulas.

Moreover, for that first question, you will need TWO row headings. The first column will have the store number. The second column will have the managers' names for each store:

Store #34

Fred

Tom

Henry

When you include your data, you'll simply plug in the sales figures for each date next to the appropriate manager. So if Fred always manages weekends and Tuesdays, those blanks next to Fred will be filled in, and the others left empty. Tom does Monday, Wednesday, and Thursday, so those blanks next to him will hold the daily sales figures for Store #34 for those days. Henry gets Friday. The row next to Store #34 will be blank - and that's fine. It makes it easier to read the spreadsheet.

Similarly, at the top you'll need two header rows. The first one should be days of the week: M Tu W Th F Sa Su, repeated ad infinitum. The second row should hold the corresponding sales days.

Basically, for each question you need to stop and think: specifically how can I divide my information in order to answer this question? This will help you set your spreadsheet up properly, and that good start will always help you later when you need to use the data.

Spreadsheets with Addresses

Okay, here's a pet peeve: people who put the citystatezip all in the same column. Or, worse, those who have one blank for the whole name of a person. I always want to alphabetize lists by last name to check for duplicates, which means I have to split columns every time I get a spreadsheet like this. Or with a large mailing, splitting addresses by zip code can give you a sizeable discount if you bulk mail - but if the citystatezip is all in one row, this is a tedious and time-consuming job.

Always separate each separate piece of information into its own column, for spreadsheets with addresses and with every other type of spreadsheet. This will be a blessing in the long run.

Other Tips

Separate out your large tables onto different sheets in a workbook. Never put more than one table on each page - you'll be sorry later!

Put charts and graphs on separate sheets as well. This will keep your work neat and organized. Always label every worksheet page you create with the name of the spreadsheet or its purpose.

Always assume that any spreadsheet you create will have to be understandable to someone besides you, and design it accordingly. Most of the time, you won't be wasting your time.

Learn how cell and spreadsheet protections work. This function allows you to lock users out of header and formula cells, but enables them to enter data in the other cells if necessary. Also, make the header cells distinct by coloring them or bolding them or otherwise setting them apart visually.

Make sure that the formulas you create are stable, work properly, and are in place before anyone else looks at the spreadsheet.