"
Print. Print
Email. Email article link
Discuss. Discuss
Trackbacks. Trackbacks
Blog this. Blog this
Add to Project. Add to Project
Top Ten Excel Annoyances
by Curt Frye, author of Excel Annoyances
01/11/2005
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:
1. Format part of a cell's contents
2. Add a carriage return to a cell's contents
3. Insert or delete a single cell
4. Delete a formula and keep the result
5. Add text to a displayed numerical value
6. Display partial hours as a decimal number
7. Prevent copied formulas from changing cell references
8. Create a named range from multiple sheets
9. Copy charts as pictures
10. Speed up recalculations
Format Part of a Cell's Contents
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.
Add a Carriage Return to a Cell's Contents
You can add a line break inside a cell by pressing Alt-Enter. Yep, that's all there is to it.
Insert or Delete a Single Cell
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.
1. Select a cell where you want a new, blank cell inserted.
2. Choose Insert → Cells, which displays the Insert dialog box.
3. Depending on what you want to do, click on the "Shift cells right" or "Shift cells down" radio button, and then click on OK.
Delete a Formula and Keep the Result
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.
Add Text to a Displayed Numerical Value
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:
1. Choose Format → Cells and select Custom in the Category list.
2. In the Type box, you'll see General, which is the default value. Immediately after it, type " cases" (including the quotes and the space after the first quote). The entry should read General cases.
3. Click on OK.
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 bags, cans, or pounds.
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 units.
Display Partial Hours as a Decimal Number
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:
1. Click on the cell where the result is to appear and select Format → Cells, click on the Number tab, and select Number from the Category pull-down list.
2. Type this formula in the cell: =HOUR(value)+(MINUTE(value)/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 =HOUR(E4)+(MINUTE(E4)/60).
Prevent Copied Formulas from Changing Cell References
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 =SUM($F$3:$F$14).
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$1 keeps both the row and column constant.
* $A1 keeps the column constant but allows the row to vary.
* A$1 keeps the row constant but allows the column to vary.
* A1 allows both the row and column to vary."
- http://www.windowsdevcenter.com/pub/a/windows/2005/01/11/xcel_annoy.html
I have a formula that sums several (but not all) values in a column above where the result is displayed. When I type in a value outside of the range of cells to be summed, but in a position between the last value in this range and the cell containing the formula, Excel "helps" me by changing my formula, changing my defined range to include the cell containing the number I've just entered.
How do I get Excel to leave my formulas alone?

Chowhound
Comic Vine
GameFAQs
GameSpot
Giant Bomb
TechRepublic