Top Ten Excel Annoyances

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.

Related Reading Excel Annoyances How to Fix the Most Annoying Things about Your Favorite Spreadsheet By Curtis Frye

Create a Named Range from Multiple Sheets

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.

Copy Charts as Pictures

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:

1. Select the chart.
2. Hold down the Shift key and choose Edit → Copy Picture, then select the "As shown on screen" and Picture radio buttons. Click on OK.
3. Go to your Word document and select Edit → Paste.

Speed Up Recalculations

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:

• Where possible, create links only to workbooks that are on your computer, thus reducing the possibility that network slowdowns will affect your recalculations.
• If you use a web query or database query to bring external data into your workbook, Excel attempts to update the query results every time you recalculate the worksheet that contains the query. If you don't need to update the query's results, highlight the cells that contain the results, choose Edit → Copy, and then, with the cells still selected, choose Edit → Paste Special → Values to break the link to the query.
• Reduce your use of array formulas. Array formulas use RAM, so if you're running a lot of applications at once, your system resources might be so low that Windows is using your hard disk as extra memory--which is much slower than RAM access (or try adding more RAM; it couldn't hurt).
• If you're using Excel 2000 or later, save the workbook as an HTML web page file, and then resave it as an Excel workbook. When you save a workbook as a web page, Excel cleans up internal and external pointers that can become garbled as workbooks get more complex.
• Optimize your database queries so that they return the minimum data set you need for your answer. In other words, don't download a huge table if you need only a few rows. If possible, have your database administrator write a query that creates a table with exactly the data you need.

Summing Up

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.