After Hours

10 ways to keep Excel from biting you in the butt

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

Excel warms you about text and numbers

Excel warns you when you store numeric values as text. You can leave them as text or convert them.

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

AutoSum references the header cell

AutoSum references the header cell because it contains a numeric value.

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.

Other gotchas?

What other actions or decisions have you run into that caused worksheet problems down the road? Share your experiences with the TechRepublic community.

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.

55 comments
dakra137
dakra137

Save big spreadsheets as .xlsb. That is a compressed binary format that loads and saves more quickly and takes up much less disk space (25%..45% of the original size.)  This is especially valuable for transfers to and from a file server whether on a LAN or across a WAN via VPN.

dakra137
dakra137

When I have more than about 60 rows, I add a row at the bottom where every cell has the value "caboose". As I add columns, I make sure the caboose value is added at the bottom. If there are blank rows, I add a column of caboose values on the right.  Why? that way I can always hit <end><down_arrow> or <end><right_arrow> and get to the bottom or right edge of the data rather than the 16 Millionth row or the 16384th column . 

Q: Why "caboose"? 

A: It comes after the last car at the end of the train.

stacey5089
stacey5089

has anyone ever had excel change the alignment of random cells?

nannak51
nannak51

Using excel spreadsheet for e tides, I copy and paste info to add and calculate is it possible for this excel to delete rows that I have no idea its doing that? I copy and paste information and when I go back to compare later there are items missing?

yuriy_babaev
yuriy_babaev

Can Excel functions automatially restore missed links with inserted pictures (originally it is about 200 photos placed in different directories)? Links broken with copying directory to other disk.

david.widener
david.widener

I have a user with a spreadsheet that changes NUMBER cells to DATE and DATE cells to RANDOM NUMBERS. This spreadsheet also will change the FORMAT of ALL cells to DATE regardless of what the original format was. When it does this it does not change the content of the cell, only the FORMAT. I have had her build the spreadsheet from scratch and it ends up doing the same thing. I have changed her computer and had her create it from scratch and it does the same thing. I have not been able to figure this one out. Any help would be appreciated.

Zookz1
Zookz1

Why does M$ keep insisting on using that flawed merging formatting option? Using the Center Across Selection option would be far better. However, M$ doesn't even have a button to add the Center Across Selection button to the QAT in Excel 2007 or 2010. Does M$ prefer to frustrate users instead of making the product better? Guess that is a stooopid question! :)

linda.wilkins
linda.wilkins

I use tabs to store raw data, source report name, qualifications, and run date. I create new tabs for pivot tables & add???l summarization/analysis. For distribution, I create a new copy with just final summary & include the name & location of the document showing all of my work. This is a dept. standard and we are tasked with a lot of analysis and reporting.

peter.tees
peter.tees

For list management where a list is distributed to a number of people, it can be a nightmare having subsequent discussion where you try to refer to a specific row only to find that various people have sorted/filtered/etc the data in a different way and what's in your Row26 is somewhere else for them. So what I do for these lists is immediately insert a column in Column A, put in column header of "Seq" and create a Series which numbers each row sequentially. So I can always go back and re-sort by the Seq column to see the data in its original format. Saves confusion IMO

ggalvan
ggalvan

... for example, handling sales/purchase orders where order number is like '003145523', storing as number would disrupt sorting by order number.

KNOWLEDGE464
KNOWLEDGE464

Never ask your pivot table to do any kind of math ;-) #REF will be your answer. But really when finding the difference in time to days remember to format the time [h]:mm-[h]:mm+24:00:00 Note: 24:00:00 formula must be '=DAY360' . this thing drove me nuts because even if you uses military time you will get the #REF in a pivot table. Just something about time excel needs the [h]:mm as a time format for any kind of calculations. Well unless someone has found a better way. I would like to hear it.

VirtualPro
VirtualPro

Never give Management numbers where displayed hides the stored decimal used in a computation? For example, Sum stored: 2.4 + 2.3 + 2.4 = 7.1 and present: 2 + 2 + 2 = 7. You will hear from a senior manager: "When I went to school they taught us that equals 6"... or better yet: "You need to go back and recheck all of your numbers, they don't add up"!

Kent Lion
Kent Lion

Don't assume that when you protect a sheet you're designing as a form, only unprotected cells can be changed. It is very easy to destroy any formula in a protected cell that refers to an unprotected cell on a protected sheet, by cutting and pasting over the referenced cell. This behavior has existed since Excel 5, and although it makes absolutely no sense from the point-of-view of what anyone would ever want to use a protected form for, Microsoft doesn't seem to intend to fix it. Sorry, if you want formulas in protected cells to refer to unprotected cells in a spreadsheet, you will have to use indirect addressing of some kind, or sooner or later a user will destroy a formula. If you're lucky, a #REF error will tell the user something is wrong, if you're not, it'll depend on how serious the resulting calculation error is...

susan_harkins
susan_harkins

Instead of a formula that contains nested functions, consider breaking it out into meaningful pieces. This isn't always possible.

kewingewingk
kewingewingk

Can you clarify #7? For instance, in the example you give, what *should* one put instead? Thanks!

Shadeburst
Shadeburst

When displayed values are not the same as stored values: I'm an accountant by training, and I want my spreadsheets to balance to the cent. I use the INT() and ROUND() functions to make sure there are no hidden "insignificant" digits. This was critical in older versions of Excel, including 2007, which could multiply 2*2 and get 3.9987654321 for example. I don't know if this has been fixed yet.

Daddy Tadpole
Daddy Tadpole

Despite the title, the sophisticated functions of spreadsheets do have their uses, specially in those many enterprises where it takes 5 years for the hierarchy to transmit a request for an application to IT, which then waits another 5 years to turn it down. I wonder sometimes how many companies have crashed because they got their sums wrong. The trouble is that (as mentioned), errors creep in more easily than you might think. You need a clear head and plenty of patience to write a worthwhile validation protocol for a spreadsheet application. M$ and others appear to have made a great effort to maximise the risk of getting things wrong; on the simplest level, how many times have you had an entry reformatted automatically and badly as a date? Among the most obvious congenital defects of spreadsheets is the use of ordinary rows and columns for headers/titles. These items should be totally distinct from others. See SigmaPlot (still current), and the old 4GL RS/1. RS/1 reminds us that ordinary cell arrays should be layered structures, with the layers being visible or not depending on user and developer requirements. There should be separate viewable layers for at least: the data that are input, the data formatted for presentation, data typing information, formatting information, conditional formatting, links between merged cells (yes you do need to merge sometimes), and formulae. I could go on, but hope that someone more expert than me will at long last pick up the the thread.

TobiF
TobiF

I know a company I worked for gave away several thousande of dollars to a customer just because the sales manager didn't want to admit he made this mistake.

foss.paul
foss.paul

Having passwords on Excel will bite you, since many passwords might start with = sign or similar that has a function in Excel. I found the article very good personally. Thank you.

sir.ptl
sir.ptl

One of my uses for Excel is for a password list that I keep on a flash drive as a portable backup. However, sometimes Excel will not let me use non-text or non numeric characters. It doesn't happen all the time, but when it does it is very frustrating. When it acts up it acts like it trying to create some sort of formula. Is there a way to get Excel to accept what I enter and simply leave it alone?

sh10453
sh10453

Hey Susan, On the positive part, thank you for this article. Good overall advice. Excel articles always grab my attention. On the other hand, #6 is not well written or explained, or at least a bit confusing. For example, "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.", may not be clear enough to some. I think if #6 had a good example, it would be clearer, and more beneficial. Just my 2 cents. Thanks.

mstabile
mstabile

If I know that a number will be used as text (like in a heading) I always precede it with an apostrophe which will define it as text and exclude it in functions such as autosum..

lipl1
lipl1

I'm at best a reasonable user but what has saved my butt many times is that I create an empty sheet and everytime I do a formula or calculation I explain it to myself. It's extra time spent but .....

Robert.Shaw
Robert.Shaw

I make it a practice to never use row 1 or col a. This way I can hide any data row,cols and easily unhide them. For some reason I never remember how to unhide row1 or col a. I guess this is one of my personal quirks. :)

seanferd
seanferd

Unless this has changed, it can throw everything out of whack. I know it's tempting when Excel adds mystery blank line space above entries, or if you are going beyond the capacity of the visible space.

dogknees
dogknees

Is available in the Ribbon versions of Excel. Go into the Cell Format dialogue and it's right where it's always been. The strange thing is that I can't find it in the Customise lists to add it to the QAT or Ribbon. Checked All Commands and it's not in the list as far as I can see.

Editor's Picks