Category Archives: 10 ways to keep Excel from biting you in the butt

10 ways to keep Excel from biting you in the butt

Takeaway: Little decisions you make when you first build a worksheet can lead to big problems down the road. Here are some things to watch out for.

It’s easy to create a sheet that works perfectly — at first. Later, when you make modifications, you run into problems. Things just don’t work the way you expected. Usually, it’s because you’ve forgotten about decisions that made sense at the time but that don’t accommodate your changes. You can reduce future problems by avoiding the following features and behaviors. There’s nothing inherently wrong with them, but they can have far-reaching repercussions.

1: Avoid merging cells

Merged cells can help you arrange values in a meaningful way, but they come with problems — numerous problems. For instance, Excel won’t apply column formats to a merged cell unless you select all the columns that comprise the merge. In addition, not all cell formats stick once you unmerge a cell. You can’t sort a column with merged cells. You can’t even select a single-column range if there’s a merged cell in it — go ahead, try!

Don’t hesitate to use merged cells if you really need them, but they will limit what you can do to the cells and even the columns involved. In non-Ribbon versions, Center Across Selection is a reasonable alternative to merging. [UPDATE: Unfortunately, this option isn’t available in the Ribbon versions of Excel. To apply this format in the Ribbon versions, you’ll need to launch the Alignment group dialog and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.]

2: Avoid hidden rows and columns

Hidden rows and columns create problems because, well, they’re hidden! You can’t consider what you can’t see, but Excel can still evaluate all that data. Hidden rows and columns confuse users, cause functions and formulas to seemingly return erroneous data, and can even play havoc with macros and import tasks. Avoid hiding rows and columns unless you must.

3: Don’t store numeric values as text

Some numeric values, such as street addresses, ZIP codes, phone numbers, and part numbers, can be stored as text because you won’t evaluate them in mathematical equations. Unfortunately, it’s easy to import numeric values as text and not know it. This problem is easily resolved, but some users don’t realize that Excel isn’t evaluating all their data. They just know that their data isn’t adding up right.

Mistaking numeric values for text is harder to do in recent versions because Excel tags them, as shown in Figure A. When you do run into numeric data stored as text, you can quickly convert it by choosing Convert To Number. In this case, the solution is adequate training.

Figure A



4: Use descriptive text in headers, not numbers

Avoid using just numbers in header cells. You might think they’re harmless, but they can generate errors you might miss. For instance, Figure B shows a simple sheet with the years used as headers. But look what happens when you use AutoSum to total the regional data. It includes the header value 2008 in cell B2. That’s a mistake you might not catch. The solution is simple: Always include alpha characters in header labels. In this case, you might use the headers FY2008, FY2009, FY2010, and so on, where FY stands for fiscal year.

Figure B



5: Avoid blank cells, rows, and columns in a data range

You won’t always have a value for every cell, but blanks can play havoc. Many built-in features, such as AutoSum and filtering, interpret a blank cell as the end of your data range. If blank cells aren’t in your sights when using these features, you might not realize that the evaluated range isn’t the one you expected. When blank cells are acceptable, consider filling them with 0s or some other descriptive value, such as NA.

This rule holds for blank rows and columns. Adding a blank row to separate your January and February values might seem like a good idea. After all, it looks nice, right? It’s still a bad idea. Use borders to separate sections, if users need a visual clue.

6: Avoid multiple volatile functions

A volatile function recalculates every time there’s a change in the worksheet, not just when a referenced cell changes. Examples of volatile functions are NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), ROWS(), and COLUMNS(). The gotcha is that all those recalculations will eventually slow down a workbook. The alternative is to enter the function somewhere in the sheet and then reference that cell instead of dropping the actual function into multiple expressions.

7: Don’t use unnecessarily complex formulas

Sometimes, formulas truly are complex. But more often than not, you can break them down into a few meaningful components. Why bother? Because eventually, someone will ask for them anyway. A good example is a total sales formula: =(Price*Quantity)-(Price*Quantity)*Discount+(Price*Quantity)*Tax. If you turn that in, someone’s sure to ask for the subtotal, the discounted subtotal, the total tax, and so on. They’ll want to see all the steps that result in the final total, just like your algebra teacher.

8: Avoid array formulas

I’m probably committing professional suicide, but I don’t recommend the use of array formulas in a production file unless they’re absolutely necessary. Yes, arrays are super cool, and if your purpose is to amaze and dazzle folks, array away. On the other hand, array formulas are difficult to understand, so they’re difficult to troubleshoot. They’re memory hogs, they can be impossible to maintain, and they break easily. They’re a booby-trap just waiting to spring. When preparing files for others, consider an alternative, such as helper formulas (see #7).

9: Do use comments

Many spreadsheet developers are loath to use comments — they seem almost offended by the idea. But comments are a great way to attach information to a cell. You can share information with users or add a note to yourself explaining a decision or special detail that you might forget. They make your sheets easier to use, for your users and for yourself.

10: Don’t confuse displayed values with stored values

Depending on the format you use to display numeric values, Excel might not display the actual number you’ve stored. That means formulas often return unexpected results. For instance, you might multiply the values 1 and 2, expecting the result to be 2. If the numbers stored are actually .6 and 2.1, but formatted to display integers, you’ll probably be surprised when the expression returns 1 or 1.26 (depending on the formula cell’s format).

Excel’s doing exactly what it’s supposed to do. It evaluates stored values, not the displayed values. It helps to be familiar with your data and reporting needs so you can accommodate this behavior accordingly.