Excel Annoyances | O'Reilly Media

BUY THIS BOOK

This print book is out of stock, with no immediate plans to reprint.

Safari Books Online

What is this?

Looking to Reprint this content?

Tell a friend

Excel Annoyances How to Fix the Most Annoying Things about Your Favorite Spreadsheet By Curtis Frye
December 2004
Pages: 256

Cover | Table of Contents | Colophon

Chapter 1: Entering Data Annoyances

Content preview·Buy reprint rights for this chapter

Data entry is the heart of Excel. If you can't get data into your worksheet quickly and accurately, you can't use the nifty tools at your disposal to analyze them. Excel does a lot of things right when it comes to data entry, but some things are downright peculiar. If you've ever had Excel correct your typing when you know what you entered was right, or turn a six-digit integer into a date, you know what I'm talking about.

The first part of this chapter shows you how to blow away Excel's everyday data annoyances, from it's habit of deleting leading zeros to correcting you obsessively. You'll also encounter (and solve) annoyances when creating forms, importing data, cutting and pasting, navigating in and among worksheets, and more. Finally, we'll focus on Excel's handy data validation feature. With validation rules, you can limit the kind of data users can enter in cells. The rules can require values to fall into a specific range, or force the user to pick values from a list. This is a boon to managing data entry, but it can be a major annoyance to set up.

The Annoyance:

I can't tell you how many times I've been happily entering data, only to have that blasted paperclip "helper" thing elbow its way onto the screen. By the time I get rid of it, I've lost track of what I'm doing and it takes me forever to get back in the groove. If that Office Assistant really were a live office assistant, I'd have had fired him years ago! How do I make it go away?

The Fix:

Clippy is one of the most-hated Office "innovations" in history. Microsoft had the good sense to turn off Clippy and his companion Office Assistant characters by default in Excel 2002 (the Office XP version) and in later versions.

In Excel 97, you can terminate Clippy permanently by opening the \Program Files\Microsoft Office\Office folderand renaming the Actors subfolder to something such as Old_Actors or Ha_ha_ha. Once Excel (and your other Office programs) can no longer find the Actors subfolder, Clippy will be unable to appear out of nowhere like a $1,000 bar tab.

If you're using Office 2000 or XP, you can turn off Clippy by going to the Control Panel and using either the Add/Remove Programs applet or (depending on your operating system) the Add or Remove Programs applet. In Windows Me, 2000, or XP, click Microsoft Office in the Currently Installed Programs list and then click Change. Click the Add or Remove button, click the Office Tools item, click Office Assistant, and then click Not Available. Confirm your choices and you're done. If you're running Windows 98, click the Microsoft Office entry in the Install/Uninstall tab, click Add/Remove Program, and step through the wizard until you can change the Office Assistant's setting to Not Available.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

GENERAL DATA ENTRY ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

The Fix:

If you're using Office 2003, you can turn off Clippy by going through the Add or Remove Programs control panel. In the Currently Installed Programs list, click Microsoft Office 2003 and then click Change. Select the Add or Remove option button on the first page of the wizard and click Next. Check the "Choose advanced customization of applications" box and click Next. Expand the Shared Office Features item, click Office Assistant, click Not Available, and then click Update.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

IMPORTING DATA ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

When I copy a table from a Word document into an Excel 97 worksheet, Excel insists on assigning each cell in the Word table to two cells in the worksheet (shown in Figure 1-7). Worse yet, it merges some of the cells. Please tell me there's some way I can stop Excel from merging the cells! If not, can I at least undo the merges and delete the resulting blank rows after I paste my table?

Figure 1-7: For some reason, you get two rows for one when you bring a Word table into Excel 97.

The Fix:

David and Raina Hawley, the authors of Excel Hacks: 100 Industrial-Strength Tips and Tools (O'Reilly), wrote a great macro that removes blank rows from a selection. I added a section at the top of the procedure to remove text wrapping and cell merges from the imported list so that each row will be separate, allowing Excel to remove the blank rows. This macro assumes the data you imported is selected. If the list isn't selected, click any cell in the list, press Ctrl-∗ to select the list, and run this macro to clean up your data:

   Sub FixWordTableInExcel97()
   'Removes all cell merges and text wrapping from
   'the pasted table and then deletes all blank
   'rows added by the paste.

    With Selection
    .WrapText = False
    .MergeCells = False
    End With

   Dim Rw As Range
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False

    Selection.SpecialCells(xlCellTypeBlanks).Select

    For Each Rw In Selection.Rows
    If WorksheetFunction.CountA(Selection. _
    EntireRow) = 0 Then
    Selection.EntireRow.Delete
    End If
    Next Rw

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
   End Sub

David and Raina Hawley originally published the body of this macro (everything after Dim Rw as Range) at http://www.ozgrid.com/VBA/VBACode.htm. It is used here with permission.

The Annoyance:

I saved a file with comma-separated values in a Microsoft Word (.doc) file that I'd like to import into Excel, but Word files don't show up when you select File → Open and open the "Files of type" list. Why don't they? And what can I do about it?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CUT-AND-PASTE ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I frequently want to copy items from one workbook into another—or even into another application—and it's tedious having to copy each cell, object, or image one by one, open the other document, paste what I copied, and then go back to my workbook and repeat. Can't I collect what I cut or copy into a single intermediate location and then paste whatever I like?

The Fix:

It is possible to collect items you cut and copy in Excel into a single location, but how you do it changes drastically from version to version. In Excel 97, you'll have to create a separate workbook or worksheet, and paste your collection in there. It's a pain, but it works. Be sure to keep an eye on your workbook's size so that it doesn't slow down your system. I use 1MB as a guideline for when it's time to create a new "holding tank," but if you're running an older system with less memory, you might want to set this limit at 500KB.

Microsoft introduced the Office Clipboard in Office 2000, and it's been part of the suite ever since. The Office Clipboard keeps track of the last set of items you cut or copied (12 in Office 2000, 24 in XP and 2003) and makes them available to paste in any Office application, individually or all together. What's even more interesting is that any items you cut or copy in one version of Office are available in other versions of Office that are open. At one point I had Word 2003 and Excel 2000, 2002, and 2003 running at the same time—and each program had the same three cut-and-copied items available in its Office Clipboard.

To use the Clipboard in Excel 2000, follow these steps:

Choose View → Toolbars → Clipboard to display the Office 2000 Clipboard (shown in Figure 1-8).

Figure 1-8: Paste-o-rama. With Office's enhanced clipboard, you can cut and paste multiple selections at once.
Follow any of these steps to use the Clipboard:
- Click an item and click Paste to paste that item into your worksheet.
- Click Paste All to paste every item in the Clipboard into your worksheet.
- Click Clear All to empty the Clipboard.

In Excel 2002 and Excel 2003, follow these steps to use the Clipboard:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

NAVIGATION AND DISPLAY ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I switch around a lot between fairly similar worksheets, and it would save me hours of hassle if I could move from one worksheet to another and keep my cursor in the same position in the new sheet as the one I just left.

The Fix:

Excel is designed to remember the last active cell on each worksheet, and to reactivate that cell when you open that sheet again. However, here's a macro you can use that will move you to the next worksheet in your workbook and place your cursor in the same active cell (it works in every version of Excel since Excel 97):

   Sub NextSheetSameCell()

   Dim rngCurrentCell As Range
   Dim shtMySheet As Worksheet
   Dim strCellAddress As String

   'strCellAddress = ActiveCell.Address

   'Comment out the line above to record the
   'selected range's address, or comment out the
   'line below to record the active cell's address.

   strCellAddress = _
    ActiveWindow.RangeSelection.Address

   Set shtMySheet = ActiveWindow.ActiveSheet

   If Worksheets.Count > shtMySheet.Index Then

    shtMySheet.Next.Activate
    Range(strCellAddress).Activate

    Else

    Worksheets(1).Activate
    Range(strCellAddress).Activate

   End If

   Set shtMySheet = Nothing

   End Sub

For good measure, I wrote the macro so that you can move to either the same active cell or the same range of selected cells as you move from worksheet to worksheet. In either case, the macro records the address of the selected cell or cells, activates the next worksheet in your workbook (or returns to the first worksheet if you're currently on the last one), and applies the recorded selection to the new worksheet. To edit the macro so that you transfer only to the active cell (even if more than one cell is selected), put an apostrophe in front of the line that reads strCellAddress = ActiveCell.Address and remove the apostrophe from in front of the line that reads strCellAddress = ActiveWindow.RangeSelection.Address.

If you find the macro useful, you can attach it to a custom button on the toolbar. To find out how, see "Run a Macro by Clicking a Toolbar Button or Menu Item" in Chapter 8.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

DATA VALIDATION ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I'm a manager at an engineering company, and I hired an out-of-work political science major to do data entry and other clerical stuff. We don't pay him a lot, but our deal is that he gets to learn about computers on our dime when he's doing data entry. There's only one problem: he types so badly that he makes a ton of mistakes—adding extra numbers, leaving some out, even hitting letters instead of numbers. Isn't there some way Excel can flag a mistake before he enters the data?

The Fix:

The secret is using data validation. Click a cell (or a group of selected cells), and then turn on the validation feature by selecting Data → Validation and clicking the Settings tab. From the Allow drop-down menu, pick the type of validation criteria you want to use, and then specify its parameters from the drop-down menus that appear below. The type of validation you select in the Allow drop down determines what other options appear below. If you select Whole number, for example, you can specify whether the entered values should be between two other values, not between two other values, or less than or greater than a value. You can hardcode the value by picking "equal to" from the Data drop-down menu and typing the value into the Value field. Figure 1-19shows the types of criteria you can create.

Figure 1-19: Limiting your colleague's actions was never so easy.

For instance, if he's supposed to be typing in Zip Codes, and you want to make sure he doesn't leave out digits or add extra ones, set the Text length validation rule to require a number between 5 and 5. If he's supposed to enter 94607 and he types 9460 by mistake, he'll receive an error message reading "The value you entered is not valid."

If you want to make sure he doesn't forget to enter data in a critical cell, uncheck the "Ignore blank" box in the Data Validation dialog. That forces him to enter

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Format Annoyances

Content preview·Buy reprint rights for this chapter

When all is said and done, formatting objects in Excel is reasonably straightforward

When all is said and done, formatting objects in Excel is reasonably straightforward. The Formatting toolbar and the Format Cells dialog box make most common tasks pretty easy. But if you try to push Excel past the formatting basics, things can go wrong in a hurry. For example, for the longest time I didn't know how to work with Excel's color palette, that collection of mysterious and mysteriously repeating colors that appear in charts, graphs, and cell backgrounds.

Another aspect of Excel that took me a while to get a handle on was custom formats. No, not conditional formats, which change the appearance of a cell and its contents based on the value in the cell, but custom formats that control how Excel displays dates, times, and special information such as Social Security numbers and Zip Codes. You can use custom formats to establish some rules on how a cell's contents will be displayed, but you don't have nearly the variety of formatting options that are available to you through the Conditional Formatting dialog box.

This chapter includes more than 30 annoyance-fixes you can use to control the appearance of your workbooks, from a one-click method of wrapping text within a cell, to a macro that can find out exactly which colors are available in your workbook.

The Annoyance:

OK, I'll come clean: I've been using Excel for only a couple of days, and I'm not that great with computers anyway. But I did finally figure out how to format a cell: I click it and do whatever I want with the buttons on the Formatting toolbar, or sometimes I choose Format → Cells and work with the Format Cells dialog box. But how do I format part of a cell's contents? There has to be a way to make a single word bold!

The Fix:

This is so easy you'll kick yourself when I tell you. To format part of a cell's contents, click 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 might seem basic, but you'd be surprised how many folks miss this.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CELL FORMATTING ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

The Fix:

The Annoyance:

I create a lot of worksheets with longish text labels and explanations about the assumptions behind the data, so to wrap the text I end up choosing Format → Cells, clicking the Alignment tab, and then checking the "Wrap text" box...again and again and again. Isn't there some way to put the "Wrap text" checkbox on the toolbar? Four mouse clicks seems like a lot of work for such a frequent task.

The Fix:

There really ought to be a "Wrap text" button in the Excel command bar system, but Microsoft hasn't provided one. In the meantime, you can attach the following macro to a custom toolbar button:

    Sub WrapTextMacro()
     Selection.WrapText = True
    End Sub

And, of course, to unwrap text, use this matching macro:

    Sub UnWrapTextMacro()
     Selection.WrapText = False
    End Sub

To find out how to attach the macro to a custom toolbar button, see "Run a Macro by Clicking a Toolbar Button or Menu Item" in Chapter 8.

The Annoyance:

I work for a manufacturing company, and one aspect of my job is figuring out how much a new machine will cost to build. I keep each product on a separate worksheet in an Excel 2000 workbook. But because I get new (or updated) prices for many of the parts almost every day, it's a pain to remember which worksheets I've updated and which ones I haven't since the last time my project leader reviewed our progress. Is there any way I can change the color of a worksheet tab to flag which ones have been changed and which ones need updating?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CONDITIONAL FORMATTING ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I'm a commercial gardener and I use Excel to track the temperature in my greenhouses. I want to automatically flag any hours during which the temperature is less than 75 degrees by displaying that cell's value in red text. How do I do it?

The Fix:

Conditional formatting is one of Excel's handiest features. To change a cell's formatting based on its contents, follow these steps:

Select the cells you want to format and choose Format → Conditional Formatting to display the Conditional Formatting dialog box, shown in Figure 2-1.

Figure 2-1: Use the Conditional Formatting dialog box to change how your data looks based on its value.
Open the second drop-down menu and choose the comparison operator (between, less than, greater than, or equal to) you want to use to evaluate your data. Then type the associated values (the values the data is between, less than, greater than, or equal to) in the third and fourth boxes. (The fourth box doesn't always appear; it depends on the Boolean operator you pick.) For this example, select "Less than" from the second drop-down menu, and in the box to the right, type in the value 75.
Click the Format button to tell Excel how it should format cells with values of less than 75. You can specify color, font style, underline, and strikethrough. Click OK when you're done.
Click OK to apply the format, or click the Add button to create up to two more rules for the cell. (You could, for instance, apply another Boolean operator, such as greater than, to flag numbers less than 75 but greater than, say, 25.)

The Annoyance:

I'm a casino host with thousands of players who want to pit their rabbits' feet against my employer's statistical advantage. I like to display my customers' credit limits in different colors, based on how much they're good for, but the conditional format I created (shown in Figure 2-2) doesn't get the colors right. My formula checks whether the player's credit limit fits the three listed criteria and should display the limit amount in gold for players with limits of at least $1 million, blue for players with limits of at least $500,000, and green for players with limits of at least $100,000. What am I doing wrong?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

TEMPLATE ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I create worksheets for many different projects, but they all have the same basic layout (shown in Figure 2-4) and formulas. I'd like to save the general layout of the workbook (one worksheet for each month, the color scheme, the formulas, etc.) so that I don't have to go through the rigmarole of saving the base workbook under a new name. It gets even worse when I press Ctrl-S as a reflex after I delete the data but before I save the workbook under its new name. Isn't there some way to create a template I can call up like the ones that come with Excel?

Figure 2-4: This could be a template—but how do you call it up when you need it?

The Fix:

To create a workbook template, follow these steps:

Create the workbook with all headings, labels, and such, but without data.
Choose File → Save As, and in the "Save as type" box, select Template (*.xlt).
Verify at the top of the dialog box that in the "Save in" dropdown, the Templates folder is selected. Type a name for the template in the "File name" box below and click the Save button.

After you save a workbook template, you can create a new workbook based on that template. The precise steps you follow depend on the version of Excel you're using:

In Excel 97 or 2000, choose File → New and in the General tab, double-click the desired template.
In Excel 2002, choose File → New to display the New Workbook task pane, shown in Figure 2-5. Click General Templates and then double-click the template you want to use.

Figure 2-5: In Excel 2002, you go through the New Workbook task pane to get to the list of available templates.
In Excel 2003, choose File → New to display the New Workbook task pane and then click On My Computer to open the Templates dialog box.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

COLOR MANAGEMENT ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I'm the vice president of sales and marketing of a 5,000-person corporation, and I want my assistant to create a pie chart that shows the relative sales percentages of our 70 products. I've finally gotten over the fact that Excel can display only 56 colors at a time. Really, I have. But what I truly hate is that the color palette has some repeat colors in it. I need every color I can get! Can I replace those repeat colors in the Excel color palette with new ones?

The Fix:

You can, indeed. The repeat colors in the Excel palette, listed by ColorIndex value, are:

Color 32 (repeats Color 5, Blue)
Color 27 (repeats Color 6, Yellow)
Color 26 (repeats Color 7, Magenta)
Color 28 (repeats Color 8, Cyan/Aqua)
Color 30 (repeats Color 9, Dark Red)
Color 29 (repeats Color 13, Violet)
Color 31 (repeats Color 14, Teal)
Color 54 (repeats Color 18, Plum)
Color 34 (repeats Color 20, Light Turquoise)

To change colors in the Excel color palette, follow these steps:

Choose Tools → Options and click the Color tab to see the current workbook's color palette. Figure 2-6 shows the positions of the repeat colors in the default Excel palette (the repeats are crossed out).
Click the color you want to replace and click the Modify button.
In the Colors dialog box, click the desired replacement color. If you don't see the color you want or if you want to enter a precise RGB value, click the Custom tab (see Figure 2-7). (RGB is an abbreviation for "Red, Green, Blue," which are the three primary colors of light.)

Figure 2-6: "X" marks the spot of a repeat color in Excel's palette.

Figure 2-7: When you know the exact color you want, use the Custom tab to define it.
To define your color, go to the Custom tab and click near the color you want in the Colors box. Then go to the vertical color bar to the right and pick the exact color you want. Another approach: type the values for the red, green, and blue components of the color in the appropriate boxes at the bottom of the Custom tab.

The Annoyance:

I'm an Excel VBA programmer, and I just found out the colors in the default Excel color palette are

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

WORKBOOK FORMATTING ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

Arial, the font Excel uses by default, is great for reading on my computer screen, but it doesn't look that good on paper when I print budget reports. How do I change the default font?

The Fix:

Microsoft apps generally default to Arial because the text is easy to read onscreen—there's lots of space in the font's characters and the lines are thin. To change Excel's default font, choose Tools → Options, click the General tab, and in the "Standard font" drop down pick a new font from the list (see Figure 2-12). If you're dreaming about characters that look good in print, try Times New Roman or Courier. Other fonts that look good on screen and on paper include Verdana and Tahoma. Unfortunately, there's no way to set one font as your on-screen default and another font as your printout default.

Figure 2-12: You can change the standard font when you print a workbook, and change back when you're ready to continue working on your computer.

If you send a workbook to other users who don't have your new font installed on their computers, Excel displays the name of your font in the Font drop-down menu on the toolbar, but displays the workbook using the user's standard font.

The Annoyance:

I use the currency format for cross-tabular worksheets because I like to have the dollar sign right next to the numbers. Then, for lists, I use the accounting format, which puts the dollar sign at the left edge of the cell, out of the way of the numbers. However, when I click the Currency Style button on the Formatting toolbar, Excel applies the accounting format. Is this a bug? How do I get Excel to apply the currency format like it promised?

The Fix:

It's easy to be confused by the Currency Style button and how it operates, but in fact, the button acts correctly. The problem is that Excel uses the very similar terms style and format to mean different things. Clicking the Currency Style button doesn't actually apply the Currency format you find in the Format Cells dialog box: it applies the Currency style. Excel has a limited range of built-in styles, which you can see by choosing Format

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CUSTOM FORMAT ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I keep the statistics for my rec-league hockey team. One of the statistics I'm always asked about is "plus/minus," which is the number of times you're on the ice when your team scores a goal (a plus) minus the number of times you're on the ice when the other team scores (a minus). I want to display the negative numbers in red, as usual, but our team color is green and I'd like to display the positive numbers in green. Oh, and I'd like to display text values, such as a note that someone hasn't played yet, in blue. How do I do it?

The Fix:

To define a custom format, choose Format → Cells, select Custom in the Category list, and enter your custom codes in the Type box. You can specify up to four format codes in a custom format. The codes apply (in order) to positive numbers, negative numbers, zero values, and text. In your case, the format to display positive numbers in green, negative numbers in red, and text in blue is [Green](###);[Red](###);;[Blue]"Has not played".

As you can see, a semicolon separates each format. Because you don't require special handling for zero values, I left that element empty (that's why there's nothing between the second and third semicolons). If you specify only two codes, Excel assumes the first is for values of zero or greater and the second is for negative numbers. If you specify only one code, Excel uses it for any value in the cell.

The available number codes are:

#, which tells Excel to display only significant digits and not to display insignificant zeros (e.g., a cell with the code ### would display 035 as 35).
0 (zero), which tells Excel to display insignificant zeros if a number has fewer digits than there are zeros in the format (e.g., a cell with the code 000 would display 035 as 035).
?, which adds spaces for insignificant zeros on either side of the decimal point (such as 0.035) so that decimal points align when formatted with a fixed-width font, such as Courier New.

You can use the following eight colors in a custom format: white, black, red, blue, yellow, green, cyan, and magenta. But don't try to specify more than three sets of custom formats for a given cell; Excel simply won't accept them. For that you'll have to use VBA.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Formula Annoyances

Content preview·Buy reprint rights for this chapter

Transforming the data in your worksheets using formulas is what Excel does best. What could be annoying about that? Lots! Take one horrible example: consider what happens when you create a formula in one cell and copy it to another cell. If you don't build the formula just right, the cell references in the formula might change based on where you pasted the copied formula! I'll show you how to prevent this and other annoyances from happening.

Dates and times can present significant challenges in formulas. The arithmetic isn't particularly hard—it just doesn't always work the way you'd expect it to. Excel uses an exotic numerical system to refer to dates that seems totally arbitrary. But don't worry: you'll learn how to deal with this and other Excel quirks by the time you finish this chapter.

Finally, you'll learn how to wrestle array formulas to the ground. This is one of Excel's most powerful tools and one of the most annoying, because array formulas can be way confusing at first. But once you understand them, they end up saving you a lot of time.

The Annoyance:

I track sales for a department store using the worksheet shown in Figure 3-1. My boss asked me to highlight sales for the jewelry department, so I copied the formula from cell F15 to cell F18—but the formula changed from =SUM(F3:F14) to =SUM(F6:F17). I'm screwed! How do I keep that formula from changing?

Figure 3-1: When you copy this formula from cell F15 to cell F18, it changes to include the wrong cells.

The Fix:

The formula changed when you copied it because you used relative references in the original formula instead of absolute references. In the worksheet shown in Figure 3-1, if you copied the formula from cell F15 to E15, Excel would change it to =SUM(E3:E14), which is correct. If, however, you wrote the formula 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 the reference is an absolute reference, which should not change when the formula is copied. Thus, to ensure the formula in cell F15 copies correctly, you should write it as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

FORMULA ENTRY AND EDITING ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

Figure 3-1: When you copy this formula from cell F15 to cell F18, it changes to include the wrong cells.

The Fix:

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.

The Annoyance:

A friend just taught me about using absolute references so that cell designations won't change when I copy the formula somewhere. The only trouble is that last week I created a worksheet including several dozen complicated formulas using relative references because I didn't know any better. Is there any way to change relative references to absolute references without typing a dollar sign in front of each column and row designator?

The Fix:

To change formula references from relative to absolute, click the cell that contains the formula, select the entire formula on the Formula Bar, and press F4. This even works if you want to change a range's reference type. Just highlight the range on the Formula Bar and press F4. Keep pressing it to see different variations.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

FORMULA ERROR AND AUDITING ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I've figured out how to convince Excel to treat numbers I type into cells as text so that it doesn't delete leading zeros. But since I started using Excel 2002, when I copy those cells to another set of text-formatted cells the program marks the cells with an annoying green flag telling me there's some sort of nonfatal error or inconsistency. This problem never came up in Excel 97 or 2000, so I assume this is some new method Excel's programmers came up with to torture me. Help!

The Fix:

To prevent Excel 2002 or 2003 from marking cells that store numbers as text with an error flag, select Tools → Options, select the Error Checking tab (shown in Figure 3-11), and uncheck the "Number stored as text" box.

Figure 3-11: Use the Error Checking tab's controls to avoid distracting cell markers in cells that don't really contain errors.

The Error Checking tab contains other checkboxes that prevent Excel from marking cells that exhibit other characteristics. The two you should consider unchecking immediately are "Inconsistent formula in region" and "Formula omits cells in region." These types of error flags are helpful for worksheets that summarize lists of data (e.g., sales by hour), but they aren't very helpful on summary worksheets that use data from all over a workbook.

The Annoyance:

I've got financial data flying at me from all sides, and I have to work fast to keep up. As a result, I've built formulas with so many inputs I can't always remember which part of a formula refers to which cells. When things get crazy, any number of cells start trying to divide by zero because I'm off by one column or row in a cell name. Please tell me there is an easy way to see which cells and ranges are referenced in a given formula without going through it element by element!

The Fix:

In Excel 2002 or 2003, to view the cells that contribute values to the formula in the selected cell, choose Tools → Formula Auditing → Trace Precedents. Excel will draw pointers from the selected cell to the cells used in the formula, as shown in Figure 3-12. The formula in cell C5 uses values from cells A1, A2, and A3 in the current worksheet, plus a value from a cell outside of the worksheet. To show pointers in Excel 97 or 2000, choose Tools

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

ARRAY FORMULA ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I inherited an inventory-tracking workbook (shown in Figure 3-14) from an engineer in my custom computer-building company. Somehow, she created what appears to be a formula that calculates the total value of our inventory by multiplying the number of each part on hand by that part's price, and then adding the individual results. The formula, which is stored in cell D7, is {=SUM(B2:B6*C2:C6)}. Why are there curly braces around the formula, and why do I get a VALUE! error when I try to edit the formula?

Figure 3-14: The formula in cell D7 lets you skip a lot of partial calculations in cells D2:D6.

The Fix:

The formula in question is an array formula, which means it operates on ranges of cells instead of on individual cells. To see how an array formula works, break the formula {=SUM(B2:B6*C2:C6)} into its component parts. The innermost operation, B2:B6*C2:C6, multiplies the range B2:B6 by the range C2:C6, which means cell B2 is multiplied by cell C2, B3 by C3, and so on. Instead of writing the individual results to cells in the worksheet, Excel maintains the results in an array in program memory. The second operation, SUM, tells Excel to add the values in the results in memory and display the result in the cell that contains the formula (D7 in this case).

To turn a formula into an array formula, type it as a normal formula, such as =SUM(B2:B6*C2:C6), and then press Ctrl-Shift-Enter. Excel will recognize the formula as an array formula and add the curly braces for you. Don't try to type in the curly braces by hand! If you do, Excel will think the formula is text and will just display {=SUM(B2:B6*C2:C6)} instead of performing the calculation. Also, if you don't press Ctrl-Shift-Enter, the formula will generate a #VALUE! error because you can only multiply one range by another in an array formula.

Back in the day, a slide rule was a badge of honor for every engineer, but these days rocket scientists can't get by without a really good scientific calculator. Making the transition to Excel can be tough after you've spent the majority of your adult life using a calculator to create your equations and formulas, but there's an add-in application called Abacus that lets you enter your Excel formulas through a scientific calculator interface. Best of all, Abacus lets you change the button layout. The program fills in parentheses automatically, supports keyboard shortcuts, and lets you type directly into the formula if you want. You can download a 30-day trial version of Abacus from

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

RECALCULATION ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

The formulas in my workbooks use many values from other sources—and because the source data changes frequently, Excel has to recalculate the formulas constantly. This takes up so much of my computer's processing power that much of the time it's impossible to get anything done. Can I control when and how Excel recalculates my formulas?

The Fix:

To control when Excel recalculates the formulas in your workbook, select Tools → Options, and click the Calculation tab (shown in Figure 3-15).

Figure 3-15: Prevent unnecessary recalculations using the Calculation tab of the Options dialog box.

The default is Automatic, which means Excel will recalculate your workbook whenever the value in a cell used in any of the workbook's formulas changes. If you click the "Automatic except tables" radio button, Excel only recalculates formulas that aren't part of a data table (a data table is a range of cells that shows how changing certain values in your formulas affects the results of the formulas). If you select the Manual radio button, Excel will recalculate your formulas only when you press F9, or click the Recalculate Now button on the Calculation tab. Furthermore, unchecking the "Recalculate before save" box means Excel won't update the formula results every time you save the workbook—it will do so only when you press F9, or click the Recalculate Now button on the Calculation tab. To recalculate just those formulas on the active worksheet, click the Calc Sheet button on the Calculation tab.

Table 3-1 describes the keyboard shortcuts that relate to recalculations.

Table 3-1: Choose which formulas to recalculate, even if the formulas are in other open workbooks
Key(s)	Result
F9	Calculates all formulas that have changed since the last calculation, and formulas that are dependent on them, in all open workbooks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

DATE AND TIME ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I'm a private consultant, and I would like to create a time sheet for myself using Excel. But I'm having a devil of a time getting Excel to subtract time and output total hours to one decimal place. For instance, I formatted all the cells used in the calculation with the hh:mm:ss time format, which means that when it subtracts 7:00:00 from 12:00:00, it shows I worked 5:00:00 hours in the morning (see Figure 3-16) instead of 5.0. When I put in the afternoon time (start 12:30:00, stop 17:00:00, or 4.5 hours worth) it shows the total time I worked as 9:30:00. How do I make Excel display the time worked as decimal values—in this case, 5.0, 4.5, and 9.5 hours?

Figure 3-16: You shortchange yourself when you multiply a time serial number by an hourly rate.

The Fix:

To display a time as a decimal value, such as 9.5 hours, follow these steps:

Click the cell where the result is to appear and select Format → Cells, click the Number tab, and select Number from the Category pull-down list.
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. In the worksheet shown in Figure 3-16, you would use the formula =HOUR(E4)+(MINUTE(E4)/60).

The Annoyance:

I bill my clients by the tenth of an hour, rounding up (of course). How can I get my worksheet to round up the time I spend on a project (recorded in hundredths of an hour) to the next tenth of an hour?

The Fix:

To round a time value to the next tenth of an hour, first make sure the cell is formatted as General by selecting Format → Cells, clicking the Number tab, and selecting General from the Category list. Then type a formula such as =ROUNDUP(C13,1) into the cell where you want the rounded value to appear. This formula will take its raw data from cell C13, but you can use any cell reference you want. The ROUNDUP function, which rounds any value up to the specified number of decimal places, expects a cell reference or range as its first argument and a number of digits as its second argument. The number of digits indicates how many decimal places should be displayed in the formula's result. For example, if cell C13 contained the value 4.335, the formula

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

NAMED RANGE ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I'm a volunteer track coach at the local high school, and I record my runners' times, by event, in an Excel worksheet (shown in Figure 3-17). I can create formulas that use the cell addresses for each runner's times, but I'd have to look in the worksheet to see which runner's times I was working with in a particular formula. I'd rather create a shortcut reference I can use to refer to each runner's times, especially if I could use those labels as names in my formulas. Is this what people mean when they talk about "named ranges?" If so, how can I create named ranges from the existing worksheet?

The Fix:

To create named ranges from existing data labels, first select the entire range of cells, including the labels you want to use as the names of your ranges, which is the range A3:G22 in the worksheet shown in Figure 3-17. (In this case, the ranges represent each runner's times, so you wouldn't select the race distances in row 2.) After you select the data range, choose Insert → Name → Create to display the Create Names dialog box shown in Figure 3-18. Check the box that identifies the location of the rows or columns to which you want to give names ("Left column" in the current example) and click OK to create the names. From now on, instead of referring to row numbers or column letters in formulas and cell references, you can use the label names.

Figure 3-17: Existing data labels are sufficient to create named ranges.

Figure 3-18: The Create Names dialog box lets you pick where the names should come from.

If you want to create two sets of names from the same worksheet, such as the Internet café usage tracking worksheet in Figure 3-5, select the "Top row" and "Left column" boxes. What you need to watch out for, though, are instances in which you have a label in the cell at the top left corner of your worksheet, such as cell B5 in Figure 3-5. In that case, Excel can't determine where one set of labels ends and the other begins. It's better to put labels that aren't used for a row or column a few cells away from your data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

TEXT FORMULA ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I work for a car dealership, so I work with a lot of Vehicle Identification Numbers (VINs). A VIN is 17 characters long and contains information about the car maker, the car model, the year it was made, and so on. The dealership's VIN tracking software writes the VIN of every car we have on the lot to a text file. What I want to do is break down each car's VIN (a representative sample is shown in Figure 3-22) into meaningful chunks. Can Excel do that?

Figure 3-22: You can use this data's pattern to separate it into its component parts.

The Fix:

The trick is to import the data and use an Excel wizard to parse the string. The following works when each field in the string is of a known length:

Choose File → Open, click the down arrow at the right of the "Files of type" drop down, and select All Files (*.*). Select the file you want to import, and click the Open button. This starts the Text Import Wizard.
Select the Fixed Width option and click the Next button.
On the second page of the wizard, click the ruler above the data preview area to set where each break line goes. Figure 3-23 shows an import with four break lines—the string will be broken into five chunks.

Figure 3-23: The lines mark the breaks between substrings in data that isn't delimited by spaces or other characters.
To move a break line, drag it to the desired location. To delete a break line, double-click it. Click Next when you're done.
If you want to define a format for any of the fields you've just defined, click the field and use the Column data format controls at the top right of the wizard page to define the format. When you're done, click the Finish button. The string of VIN data—now neatly sliced up—is popped into separate cells in your worksheet.

The Annoyance:

The manufacturing firm I work for labels the storage location of its parts using a system that generates part codes of anywhere from 10 to 12 characters. Each element of the part code starts with a known letter (T for type, S for section of the shop, and B for bin). If I have a list of product codes such as T301S40B280 and T20S497B123, can I use Excel to separate the type, section, and bin numbers of the parts and store them in separate cells?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

COUNTING AND CONDITIONAL SUM ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

I own a computer-building company, and although most of my orders are less than $5,000, I do get a few that total more than $10,000. I'd like to know how much of my revenue comes from the big orders—which I guess means figuring out what percentage of my orders are for $10,000 or more. Is there some way to find the sum of all my orders for amounts of $10,000 or more, and then use that sum to determine the percentage they contribute to my revenue?

The Fix:

You can find the sum of cells that meet any given criterion by using a SUMIF formula. To create a SUMIF formula, choose Insert → Function, type SUMIF in the "Search for a function" pull down, and click OK to display the Function Arguments dialog box for the SUMIF function (shown in Figure 3-25). With Excel 2000 and 97, select All in the "Function category" list and then SUMIF in the "Function name" list, and click OK to get the Function Arguments dialog box.

Figure 3-25: The SUMIF Function Arguments dialog box lets you define the rules that your data must meet to be considered in the sum.

In the Range field, type the name of the range you want to evaluate in the formula, type the range's cell addresses, or click the Collapse Dialog button at the far right in the field and select the cells. Then, in the Criteria field, type in the evaluation rule Excel should use to determine whether the cell it's looking at should be included in the conditional sum. For example, you could create a rule such as >10000, or <500. Finally, in the Sum_range field, type the address of the cell that will hold the result, and click the OK button.

The Annoyance:

Thanks a lot, but I think I'd be just as happy if I knew the number of orders in my worksheet for amounts of $10,000 or more. How do I count the number of orders that meet a criterion without creating a VBA procedure to do it?

The Fix:

You can use the COUNTIF function to count the number of cells in a range that contain a value that meets a criterion. The COUNTIF function expects two arguments: the range being examined and the criterion a cell must meet to be counted (the Function Arguments dialog box for the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 4: Manipulating Data Annoyances

Content preview·Buy reprint rights for this chapter

Chapters 1 through 3 were all about getting data into Excel, summarizing it, and making it look pretty. In this chapter, you'll make your data tell you what you need to know—to run a business, to manage a sports team, or to figure out how much money you'll need to make those mortgage payments.

I'll start out by showing you how to make your life easy by sorting and filtering your worksheet data. For example, you'll learn how to create a formula that operates on visible cells only, master multilevel filters, and run sorts that whittle down your data to a manageable size.

Although Excel isn't a database, you can use it to find values in data lists thanks to the lookup family of formulas. Finally, I'll dig into PivotTables, the most versatile and useful Excel tool in the book, but one that's tricky for beginners and annoying for pros. You'll learn how to avoid the pitfalls when setting up your data, creating PivotTables, analyzing your data, and more.

The Annoyance:

Sorting the data in a single column is so easy, even I figured it out. I just click any cell in the column I want to sort, and then click the Sort Ascending or Sort Descending buttons on the toolbar. But look at my worksheet (Figure 4-1). I recorded my sales data by department (Accessories, Cars, Service), which is a handy enough view. But it doesn't help me see how I'm doing on a daily basis overall. How can I view my sales data by day?

Figure 4-1: Your sales data is recorded by department—but you need to see sales by day. That means sorting the worksheet by more than one column.

The Fix:

What you need to do is sort the data list by the values in more than one column. Select Data → Sort and use the controls in the Sort dialog box—notably the "Then by" drop downs—to determine the order of the fields and whether to sort each field in ascending or descending order. Sorting by the Day column (in ascending order) and then the Department column (in ascending order) would result in the sorted data list shown in Figure 4-2.

Figure 4-2: In this result, you see all sales, by type, per day.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SORTING AND FILTERING ANNOYANCES

Content preview·Buy reprint rights for this chapter

The Annoyance:

Figure 4-1: Your sales data is recorded by department—but you need to see sales by day. That means sorting the worksheet by more than one column.

The Fix:

Figure 4-2: In this result, you see all sales, by type, per day.

A data list is a set of data in an Excel worksheet in which each column has a unique name and each row represents a unique datum (for example, a salesperson's total sales on a given day). There can be no blank rows and no blank columns in the data list, no blank rows between column titles and the data, and no extraneous data in cells neighboring the list. That means you need at least one blank column to the right, at least one blank column (or the edge of the worksheet) to the left, and one blank row at the bottom of the data list. In essence, a data list looks and acts very much like a table in a database. See Figure 4-2 for a typical data list.

The Annoyance:

I hate the fact that Excel limits me to sorting data in alphabetical or numerical order! My boss at the car dealership insists the order of importance of our three departments is Cars, Service, and Accessories. He expects the data in my worksheets to reflect that priority, with Cars at the top of the list, Service a close second (if we don't sell cars, we won't have anything to fix), and Accessories third. The problem is that Excel insists on sorting the departments only in ascending alpha order (Accessories, Cars, Service), or descending alpha order (Service, Cars, Accessories). I spend a lot of time reordering the data to meet my boss's requirement