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.

51 comments
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...

ssharkins
ssharkins

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.

TobiF
TobiF

You wrote your question as a comment to a 3 weeks old article. I'd recommend you post your question in Q&A section instead. That would give you more visibility.

TobiF
TobiF

No, that's not a stoopid question. My feeling about skins and ribbons is that software vendors are concerned that their products should look flashy and that a totally new user should, more or less, be able to do something (without touching the keyboard.) Usability and productivity for experienced users is far down on the list. This is the only way I can explain the trends of: + hiding (or even ditching) normal menues (replacing them with flashy screen buttons in weird places) + inventing a new user interface for every main release (One of the most infamous being the change between MS Office 2003 and 2007) + putting so much "smart guessing" into applications that it becomes very hard to do something the application wouldn't guess. That's it. No more soap-boxing today. See ya!

TobiF
TobiF

Whenever I'm about to start playing with some data, I often do exactly this as a first step, in order to be able to easily return back to the initial order. (Spin-off: When I want to sort a list in RANDOM order, I add a column, which I fill with =rand(). Next I do copy-paste special(values) onto the same location. Next I sort the list with this column as argument. Finally, I delete the column and enjoy the result)

Glenn from Iowa
Glenn from Iowa

Unless all the order numbers were stored as a number. E.g., 145523 < 3145523 < 13145523 and also '000145523 < '003145523 < '013145523. As long as you can be consistent, you can store them as numbers or text. BTW to format numbers to appear as a string of digits, as shown above, use a Custom Format of "000000000" The problem I always have is that we get .csv files, pull them into Excel to manipulate them, then export them back out to a .csv file. You tend to lose leading zeroes that way. One function that helps is TEXT, e.g., =TEXT(B4,"000000000"), but it would be nice if Excel had a setting to automatically format or otherwise retain the leading zeroes.

dogknees
dogknees

When I learned Maths, which admittedly is a long time ago, numbers didn't have leading zeros. So, by definition, "003145523" is not a number but a string of digits. It's like serial "numbers" like "AUD012323". This is not a number of any kind. Serial or otherwise.

ssharkins
ssharkins

Numeric values sort different when stored as text -- absolutely correct!

linda.wilkins
linda.wilkins

You can do math on a pivot table if you copy, then paste the table. This can be done in the same location it was inserted, but after copying, the raw values are now available to use for calculations, etc.

TobiF
TobiF

I always feel strange, if a receipt or similar, doesn't give me subtotals and explain things. Say, for simplicity, that you buy one piece of something for 100 NewDollars. If the next line says "Total due (after discount&taxes) = 117 NewDollars", then you don't have any clue. Instead, put it like this: Total value: 100 Discount (10%): -10 Subtotal: 90 Regional tax (11%) +10 Governmental tax (17%) +17 Total due: 117

Glenn from Iowa
Glenn from Iowa

A little elaboration on #7: What she is actually using are named ranges. There are named ranges Price, Quantity, Discount, and Tax that are then referenced by the formula =(Price*Quantity)-(Price*Quantity)*Discount+(Price*Quantity)*Tax. You can name a cell simply by selecting the cell, clicking on the cell reference box to the upper left of the worksheet, and typing the name in the box. The nice thing about named ranges, is that they don't have to refer to a cell. For example, you can create a "named range" Discount that equals -0.05 by going into the Name Manager (Data tab or Ctrl-F3) and making a new name of Discount with the Refers to value of 0.05. If you want a different Discount for each worksheet, you can also limit the scope to the current worksheet. It hides things a little, which goes against the tone of this article, so if you're aiming for user transparency, create a cell named Discount instead, but occasionally it makes sense to "hide" it.

Shadeburst
Shadeburst

@TobiF: Because the sales manager didn't understand the $ operator he gave away $$$ and $$$ :-)

CharlieSpencer
CharlieSpencer

That key to the left of your 'Enter' key. That will force the cell contents to be formatted as plain text. Incidentally, I would use a different tool for passwords. There are several free ones that will allow you to use a flash drive. The problem with your method is it assumes Excel will be installed on all computers you may use to access password-secured content. That rules out most hotels and my parents.

ssharkins
ssharkins

You enter =NOW() in cell A1. Then, when a formula needs a NOW() function, you reference A1 instead of entering NOW(). Make sense?

ssharkins
ssharkins

I find that most numeric values stored incorrectly either as text or numbers is usually just a flat mistake -- often it results from imported data and people just don't know any different.

ssharkins
ssharkins

Good developers always document their work.

amitlohia1982
amitlohia1982

you can easily unhide row 1 or col a, (of any column/row) just type the column/row address at the name box and hit enter then to unhide column use Alt->O->C->U or to unhide row use ALT->O->R->U. hope this will help Amit Lohiya

Shadeburst
Shadeburst

HI @seanferd, please clarify? Can't see the difference between adjusting row height and column width? Thanks.

don.brandt
don.brandt

You can find the Merge commands by changing the Choose commands from: from the defalt Popular Commands to the Home Tab via the drop down menu. I like the Quick Access Toolbar as it is like the older versions without the Ribbon Toolbar. I use it for all the commands I use frequently and do not want to chose a different Ribbon Tab to do one action.

ssharkins
ssharkins

Yes, it is there, you're right! Thanks for pointing that out. I totally missed it!

CharlieSpencer
CharlieSpencer

If you can find it on the Ribbon, you can right-click it to select Add to QAT. How about 'Merge and Center', in the Alignment group on the Home ribbon?

TobiF
TobiF

Changed my mind, here's another smiley! :)

Shadeburst
Shadeburst

@ ssharkins: Yeah, it happens, especially when you import from text or CSV. I try to incorporate some sort of reality check. The file you're importing from may have a total at the bottom which you can compare. With financial modeling, I like to model not only sales and profit but the balance sheet items as well. Then all the debits and credits must nett out to nil. But from what you said, it sounds like you are designing workbooks to be used by someone else. I've mostly been in the fortunate position of creating workbooks for my own use.

Kent Lion
Kent Lion

seanferd's comment applies to rows and columns. If you manually adjust them, they stop autoadjusting and stay whatever width.and height you've manually set them at. I find it a bigger problem when I've manually set column width and insert cells in a way that pushes text set to wrap into a narrow column where the row hasn't been fixed, forcing it's height to change. Once a height has changed automatically, it won't necessarily change back when you fix the column width. Also, if you manually change a row height and then change something in the row so it needs more height, it won't automatically change, and you may not notice that some of the text is now hidden below the bottom of the cell. Unfortunately, everything having to do with manually changing width and height isn't always predictable, so I wouldn't have said "never adjust ... manually". I might have said that if the default widths and heights aren't what you want, you'd better pay attention to how your sheet looks before you publish/print/distribute it.

seanferd
seanferd

For a sheet in which you are still making entries, if you manually adjust the row or column sized, it tends to bork the height or appearance of cell contents in the following cells. Since the auto-sizing feature does not always work as intended, it can be tempting to drag to enlarge, but this can result in (more) unexpected behavior. Advice against this is (or was) actually in the Excel help documentation.

ssharkins
ssharkins

When developing a workbook for others, you really have to be mindful of all the possible gotchas. You might remember with your own sheets, but users won't know why something's not working as expected. Worse, they won't even notice that it's not working as expected.

Editor's Picks