The river of Excel annoyances runs deep and treacherous, populated by numerous species of sharp-toothed predators ready to chew up your data. How deep and treacherous? When we queried user groups across this fair land, we got 150 emails in a single week! Emails from newbies and Excel masters, homemakers and NASA engineers, all at their wit's end because of some Excel feature, bug, quirk, flaw, or just-plain-dumb design decision. My job, in consonance with my editors, Robert Luhn and Michael Oliver-Goodwin, was to figure out what bugged people the most, determine how to fix the problems, and also point out useful utilities, web sites, and other resources that might make their lives easier.
In this article, I'd like to offer fixes for ten of the most common Excel annoyances:
This one is so easy, you'll kick yourself when I tell you. To format part of a cell's contents, click on the cell to display its contents in the Formula Bar just above the worksheet and below Excel's toolbar. Select the characters you want to format in the Formula Bar, and use the buttons on the Formatting toolbar to change the characters' appearance. This solution might seem basic, but you'd be surprised how many folks think it's impossible to edit part of a cell's contents. The program sure doesn't make it obvious.
You can add a line break inside a cell by pressing Alt-Enter. Yep, that's all there is to it.
Have you ever typed in a data list, only to discover that you left a value out of the middle? Sure, you could just cut and paste the data below the item you missed and type it into the blank cell, but here's a quick way to add a new cell in the middle of list without cutting and pasting.
To replace a formula with its result, click on the cell with the formula, choose Edit → Copy and then Edit → Paste Special, and select the Values radio button to paste the value in the cell where the formula was. This procedure works for multiple cells, too.
If you're a restaurant manager and you check a workbook to see how much ketchup you have left, wouldn't it be helpful to know whether the value 16 in the Ketchup Inventory Level cell represents bottle cases or two-gallon cans? You can add text to the value of a cell by following these steps:
" cases"(including the quotes and the space after the first quote). The entry should read
The format you just created will put the word
cases behind any value you enter into the cell, but Excel will still treat the value you entered as a number, not text. Of
course, you can substitute any word you want, such as
You also can create a format that changes depending on the value typed into the cell. To create such a format, you add conditions enclosed in square brackets in front of each partial format, and separate the segments with a semicolon. For example, the format
[<>1]General" units";[=1]General" unit" will cause Excel to follow the value
1 with the word
unit and values other than
1 with the word
Consultants often bill their clients by the tenth of an hour, so it doesn't do much good to bill someone for 9:30 when you worked for nine and a half hours. To display a time as a decimal value, such as 9.5 hours, follow these steps:
)/60), replacing value with the address of the cell that contains the time or the formula that generates the time you want to convert into a decimal value. If the time were in cell E4, you would use the formula
One usually useful but occasionally annoying aspect of Excel is that the program changes will sometimes change a formula's cell references when you copy the formula to another cell. Here's the secret: the formula changed when you copied it because you used relative references (which can change) in the original formula instead of absolute references (which can't change).
For example, if you copied the formula
=SUM(F3:F14) from cell F15 to E15, Excel would change the formula to
=SUM(E3:E14). If, however, you wrote the formula in cell F15 as
=SUM($F$3:$F$14), Excel would copy the formula as
=SUM($F$3:$F$14) no matter where you moved it. The dollar sign in front of a row or column designator indicates that the reference is an absolute reference, which should not change when the formula is copied. Thus, to ensure the formula in cell F15 stays the same when you copy it, you should write it as
You can mix absolute and relative references in a cell designation, so (for example) the rows referenced could change but the columns couldn't. Some of the possibilities:
$A$1keeps both the row and column constant.
$A1keeps the column constant but allows the row to vary.
A$1keeps the row constant but allows the column to vary.
A1allows both the row and column to vary.
A named range is an abbreviation for a group of cells you can use to shorten your formulas. For example, if you summarize the results in cells C4, C8, C12, C16, and C20 in a bunch of formulas, you could define a named range called
DeptTotals and create the formula
=SUM(DeptTotals) in place of the formula
=SUM(C4, C8, C12, C16, C20). The problem is that there's no obvious way to create named ranges that contain cells on more than one worksheet. There's no obvious way, it's true--but there is a way.
Creating a named range is all about selecting the cells you want to include in the range at the right time. That time is after you select Insert → Name → Define, when the Define Name dialog box is open and the "Refers to" field is empty. To create a named range that refers to cells on more than one worksheet, type a name in the "Names in workbook" field, and then click on the "Refers to" field. First, select the sheet tab of the first worksheet that contains cells to be included in the range, select the cells on the active sheet you want to use, and then type a comma at the end of the code that appears in the "Refers to" field.
To add cells from another sheet, click on the sheet tab of the next worksheet with cells to include, and select the cells. If you want to add cells from more sheets, type another comma and repeat the selection process. Otherwise, remove the final comma (if there is one), click on the Add button, and then click on OK.
It's usually easy to copy Excel charts into documents created using other Microsoft Office programs, but how do you paste an Excel chart into Corel Draw? You can't paste the chart, but you can copy the chart as a picture and paste the picture into another application. Unfortunately, you won't find the command you need in any of the standard Excel menus. The secret, which not many people know, is that you can get to some alternative menu commands if you hold down the Shift key when you open a menu. That's what you'll do here:
Excel recalculates your workbook's formulas when you open the workbook and when you make significant changes, which is good--but sometimes those recalculations can take a long time, which is not so good. There are a number of ways to speed up workbook calculations:
Don't let Excel get you down! There's usually a way around the program's limitations if you know where to look for the solution. Excel Annoyances helps you find solutions from within Excel and points you to third-party solution providers who work hard to extend the program's capabilities. If you can't figure out how to make Excel do what you want it to do, perhaps someone else can.
Curtis Frye is the author of numerous books on Excel and other Office products, most recently Excel Annoyances.
Return to the Windows DevCenter.
Copyright © 2009 O'Reilly Media, Inc.