Software

10 ways to screw up your spreadsheet design

How you set up a spreadsheet determines its efficiency, usability, and reliability. Avoiding these pitfalls during the design phase will save you a million headaches.

Wrong references, missing values, and invalid data aren't the only things that will ruin a spreadsheet. The development process starts before you do a thing, while you're planning the design. These types of mistakes are worse than bugs because you can't troubleshoot them. All you can do is start over. Here are 10 mistakes to avoid early in the process, when you're still in the decision-making phase.

1: That sheet has a million rows and I'm going to fill them all!

An Excel 2007 sheet suggests that you can have up to a million rows of data — but you really can't. If you have that much data, you need more power than Excel has to offer. Consider a new strategy, such as using SQL Server Express. Excel simply can't handle that much data even if it gives the impression that it can.

2: I use Excel as my database

"Hi! I'm working on a database project and I'm using Excel." Um... not with my help, you're not. An Excel sheet and a database table may look similar. Excel displays data in rows and columns, but that's as close as an Excel sheet and a database table get. If you must use an Excel workbook to store data in database-like fashion, think in terms of a flat file database (which repeats values from record to record). If you need a relational database system, use Access or SQL Server. Having multiple sheets doesn't give a workbook relational capabilities. You'll just break your heart trying to use Excel that way.

3: I know a few blank cells won't matter

Empty cells aren't wrong, but they can interfere with the way some functions work. For instance, the AVERAGE() function evaluates the value 0 and an empty cell differently. In addition, many features interpret a blank cell as the end of your data. For these reasons, avoid a design that accommodates blank cells when possible. If empty cells are valid, define those values within the context of the tools and functions you'll be using, so you can avoid unexpected errors and bad data.

4: I always start at A1

Many developers anchor their spreadsheets at A1 — it's a habit. That means summarizing functions and expressions are at the bottom or the right of all the data. In a small sheet, it doesn't matter, but a large sheet is another story. Users will waste a lot of time scrolling between the data and summary values. You can build a custom view or show users how to use [F5], but there's a more efficient alternative. Leave a few blank rows at the top and a few blank columns to the left of the data range. That way, you can insert summarizing functions in the first screen the users see. Those top blank rows are also a good spot for advanced filter criteria.

5: I put as much as possible in each cell

An Excel sheet isn't a trash compactor. The more data you put into a single cell, the fewer opportunities you'll have to use and extract the data you need. Instead, atomize! Within the context of designing a spreadsheet, atomize means to break down your data into the smallest possible elements. For instance, if you're storing names, split the first and last names into two columns instead of one. If you store the names in firstname lastname format, you can't sort by last name. If you think the easy solution to that dilemma is to use lastname firstname format, you might get a surprise later on — someone is sure to ask for that type by first names. Always break your data into the smallest reasonable pieces. You can concatenate values later, but it's difficult to reverse-engineer data.

6: I set all my sheets to manual to speed things up

By default, Excel automatically recalculates formulas when a dependent cell changes. In a poorly designed or large workbook, all that calculating can slow things down. A common reaction to a slow workbook is to set the calculation mode to manual. The sheet will then recalculate only when you explicitly tell it to by pressing [F9]. There's an unseen consequence to this madness, though: Eventually, you will refer to a value that hasn't been updated. Manual calculation isn't bad; it just isn't the right fix for a slow workbook.

7: I did it myself!

This is one of those tips that won't benefit the folks who need it the most — they won't recognize themselves. Most of us (and I include myself in this group) don't have all the necessary skills to build from scratch every spreadsheet we need. We're not stupid. We just lack a particular skill set. When you don't feel quite up to the task, rely on someone else. It's okay to start with a prebuilt template and make changes to meet your needs. Your end product will be more dependable and you'll save time.

8: I love to jazz up my spreadsheets with WordArt

"Word on the street is, he uses WordArt. Amateur!" I don't understand why, but WordArt is definitely taboo. Professionals absolutely hate it. If you use it, no one will notice your efficient formulas or helpful macros. All they'll see is your snazzy WordArt headings. They'll shake their heads and call another developer. WordArt produces some interesting effects, but never use it in a professional application.

9: I put everything on Sheet1 so it's easy to find

By default, Excel provides three two-dimensional sheets, and you can add more. The inclination is to put everything on the first sheet and to ignore the rest. It's an extreme you should avoid. If you cram everything onto that first sheet, you'll end up with a mess that's hard to use. When designing sheets, think in terms of major functions or tasks. Everything doesn't have to fit on one screen — it's about function, not display. Each sheet's task should be specific and autonomous. Dedicated sheets are easy to use and maintain.

10: I distribute everything across a whole bunch of sheets

The opposite of #9 is using dozens of sheets, each containing just a small amount of data. This design mistake won't make data easier to find. In fact, the more sheets your users have to browse, the more difficult it will be to find what they need. And once you spread your data across too many sheets, mistakes start creeping in. You reference the wrong sheets, the wrong cells — it's difficult to keep track of all the pieces when they're spread out like that.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks