Errors in Excel spreadsheets are very common. I make them too! We’ve been hearing more and more about how much it’s costing companies here, here, here, and here.

I’ve written about using purpose specific commercial software products here, but what if you just HAVE to use Excel? Well, today, I’m going to give you a few tips on avoiding the most common Excel errors.

Design / Build / Test

Just like ‘real’ software, an Excel spreadsheet will be better designed if you take some time to do some minimal design work up front. At least for a moment, think about what you’re trying to accomplish. The data you’ll need, the data you have, the formulas you need, etc. Thinking in advance will prevent you from building something that isn’t going to suit your purpose. Or isn’t going to do it easily.

Step by Step

One technique I particularly love it building the model step by step. This means you may have some extra columns. Here’s a very simple example. If I was building something to calculate (A + B) / 3, I’d create a column that was (A + B) and then another column that takes the result of that and divides by 3. You can always combine the formulas later (be sure to keep your step by step model and check the results along the way) or hide the intermediate calculations. See below for an example.

Row by Row

Build your model for one row of data first. Test it, proof it, and then, and only then copy it for all data. Test it again as your data varies, but start with one row to make things simpler.

Check your Totals

One error I see commonly (and have made more than once myself!) is forgetting to extend the totals at the bottom when new rows are added. Just double click on the total, to see the range of cells included. Check this after making any major edits or adding new data. This works for any formula – you can make sure you’re using the cells you expect, by double clicking.

Another Set of Eyes

The single best way to catch errors in your spread sheet (or even in your thought process) is to explain it to someone else. This means you need a willing victim – oops – I mean collaborator, but if you can find one that will go through all the details with you do it. And then buy them lunch!

Lock it up

Another very common cause of spreadsheet errors is overwriting formulas. Before you share your spreadsheet (and even if you don’t) protect the calculated cells. It’s also a good idea to provide a visual indicator of either areas where users can and should enter data and where they should not. This is relevant even if you’re the only one using the spreadsheet.

Use your sanity

If a result seems surprising, or unexpected, question it. That’s probably good advice in any circumstance, but especially here. If you can, try to make the calculation by hand and make sure that unexpected result isn’t the result of an unexpected error.

With these tips, you’ll be much less likely to make a big Excel error, but still – be careful! And please – share your favorite tip in the comments!