• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint



The Annoyance:

I copy and paste lengthy tables of sports schedules and stats from web pages into Word, mostly without success. Word can't seem to import tables correctly and things just never look quite right.

The Fix:

Don't even consider using Word. Excel is the premier tool for handling tables. Here's how:

  1. Highlight and copy info on the web page (see Figure 4-27).

  2. In Excel, right-click the cell where you want to start the table.

  3. Select Paste Special and-here's the important part-select HTML format.

  4. The data pours beautifully into the cells to form a perfect table. While the data's still selected, choose Format → AutoFormat, and you can fiddle for countless hours choosing a style to display the data (see Figure 4-28).

Figure 4-27. If you're into thoroughbred racing, you'll recognize this table. Copy the section of the table you want to export into Excel.

Figure 4-28. Top: The table from the Web pours smoothly into Excel using the "Paste Special"' HTML format. Bottom: Use Excel's AutoFormat tool to tweak the table so it looks just right.


The Annoyance:

I often need to enter a bunch of repetitive data in a worksheet. Sometimes it's a column of numbers that are all the same; other times it's a row of numbers or dates that increment. I often need to do this across a row, such as when I have a series of dates at the top of each column.

The Fix:

You're probably vaguely aware of the Auto Fill feature in Excel, which is designed to relieve the tedium of entering repetitive data. But I'll bet you haven't used many of the capabilities that make it a truly useful and convenient tool. Here are some of the things you can do with Auto Fill:

  • Select a single cell that has a numeric value. Place the mouse pointer on the fill handle-the tiny black square in the lower-right corner of the active cell indicator (see Figure 4-29). Drag in the direction you want to repeat the data.

    Figure 4-29. Dragging the fill handle (the black square in the lower-right corner of the active cell) downward repeats a value or increases it, depending on whether or not you hold down Ctrl as you drag.

    As you drag, a tooltip shows the value that will be placed in each cell as you pass over it. Excel tries to guess whether you want to repeat the data (put the same value in each cell) or enter a series (increase the value in each cell by one if you're dragging to the right or down, or decreasing it if you're dragging up or to the left)-and it often guesses wrong. If that's the case, hold down the Ctrl key before you release the mouse button to finish dragging; if Excel thought you wanted to repeat data, it changes to a series, and vice versa.

    If you're using Excel 2002 or later, you can switch between repeated data and a series even after you finish dragging: click the tiny Auto Fill Options box that appears by the cell where you ended and choose either Copy Cells or Fill Series.

  • You can enter a series that increases or decreases by amounts other than one. For example, if you want to create a series that increases by five, enter the first two values in the series. Select both cells, and then drag the fill handle.

  • Excel can enter a series of month names or day names. Simply enter the first value you want (for example, February or Monday) and drag. Excel fills the cells you drag over with successive months or days. When doing so, Excel maintains the style you used in the first cell; if you abbreviate the month or day name, Excel abbreviates the ones it creates.

    By default, the only text series that Excel generates are for months and days and their three-letter abbreviations. But you can create your own custom series, whether it's the order of planets in the solar system, a list of your store locations, whatever. To do this, choose Tools → Options and click the Custom Lists tab. In the "Custom lists" box, select NEW LIST, and then type the values in your new series in the "List entries" space to the right, as shown in Figure 4-30. Click the Add button when you're done. If you've already typed the series into a worksheet, you can import it to the "Custom lists" box without retyping; select the cells, select Tools → Options, and click the Import button.

    Figure 4-30. Excel comes with only a few predefined lists, but you can create a custom series just by typing in the values-like a sequence of planets.

  • Don't like dragging? Then you'll love this one: double-click the fill handle. If there are values in an adjacent column, Excel fills cells downward until it reaches a cell without a value in the adjacent column.

  • If you haven't had your fill of Fill, there's more: select a cell with a value and choose Edit → Fill → Series. You'll find all kinds of options for creating series with much more complex progressions.


To insert a line break in a cell, press Alt-Enter.


The Annoyance:

I know you can do date calculations in Excel, whether it's to find how many days late I am on a car payment or to see how long it's been since my last haircut. It's pretty easy to determine the number of days between two dates; just subtract one from the other. But when I do that, the result is another date! Huh?

The Fix:

In a blank worksheet, try this little exercise, which should show your age in days:

  1. In cell A1, enter your birth date in MM/DD/YYYY format.

  2. In cell B1, enter the formula =today() to display the current date.

  3. In cell C1, enter the formula =b1-a1.

You'll notice that the result of the formula in C1 is some other date, which appears to have no correlation to either of the first dates. How come?

When you enter a formula, Excel matches the formatting of the formula's inputs. This works well, when you're doing calculations on dollar amounts or percentages; the result comes out formatted just the way you'd want. But in our example, Excel formatted the formula result-a number of days-as a date.

The solution is simple: select the formatted result cell (C1 in the example) and choose Format → Cells or press Ctrl-1. On the Number tab, select General in the Category box, and click OK. Now your age (in days) is displayed as a huge integer. Hmmm, I think I liked the result better before I fixed the problem.


The Annoyance:

A carefully crafted worksheet might be full of formulas that calculate all kinds of important things. But if the result of the formula is zero or blank (depending on how the cell is formatted), the cell that contains the formula appears to be blank, unused, empty, devoid of data-and ready for me to put something else in its place. And too often, I do just that; I type something or copy into a cell, wiping out the formula that was there. Even when the formula results are visible, it's all too easy to assume that it's just a normal, typed-in value, and to inadvertently overwrite it.

The Fix:

Excel has several features that prevent you or someone else from messing up your worksheet. The first method has been available in just about every spreadsheet program since VisiCalc (for you youngsters, that early spreadsheet was the "killer app" that led to the widespread acceptance of personal computers), but lots of spreadsheet users still don't know about it. Here's how you use it:

  1. Select the cells that do not have formulas or fixed information that you want to keep. Hold down Ctrl to make multiple selections.

  2. Choose Format → Cells, click the Protection tab, uncheck the Locked box, and click OK. This tells Excel that these cells are okay to edit. But Excel enforces this restriction only when you protect the worksheet, which you do in the next step.

  3. Choose Tools → Protection → Protect Sheet. You can enter a password if you like, but it's not required. If you enter a password, you (or anyone else using the worksheet) won't need it to enter data in the unlocked cells. But you will need it if you want to unprotect the worksheet so you can make changes in the locked cells. Click OK when you're done.

You can now enter anything in the unlocked cells, but if you try to change the content of a locked cell, Excel pops up an error message. If you need to change those cells, choose Tools → Protection → Unprotect Sheet; you'll need to provide the password if you originally specified one.

If you're using Excel 2002 or 2003, you have another, more powerful option for protecting parts of your worksheet from accidental or malicious changes. You can even enforce varying restrictions for different users on each part of the worksheet:

  1. Choose Tools → Protection → Allow Users to Edit Ranges, then click New.

  2. In the Title box, type a descriptive name for a cell or range of cells in which you want to allow data entry (that is, the cells that do not have formulas or fixed information you want to protect).

  3. In the "Refers to cells" field, type a cell range or click the range button at the right end, which lets you select the range on the worksheet. When you're done selecting the range, click the box again. (If you select the cells first, as in the first fix, those cell names will already be in the "Refers to cells" box.)

  4. In the Range password box, enter a password, click OK, confirm the password, and click OK again.

  5. Back in the Allow Users to Edit Ranges dialog box, click the Protect Sheet button. Then click OK.

When you attempt to enter data outside of the range (or ranges) you specified, Excel displays an error message and prevents you from making changes. When you attempt to enter data within the range you specified, Excel asks for the password; provide it once and you can keep making entries. You can avoid the password prompt in two ways:

  • Don't specify a password.

  • In the Allow Users to Edit Ranges dialog box, select a range, click the Permissions button, and specify which users can edit the range. When one of these users logs on and opens the worksheet, Excel will allow the user to edit cells in the specified range without asking for a password.

To disable or modify this type of protection, choose Tools → Protection → Unprotect Sheet.


The Annoyance:

I've protected my worksheet so that I don't accidentally wipe out formulas and other entries. Unfortunately, when I enter data I'm not always paying attention, and I end up typing the wrong values into cells. Excel then gives me bogus answers, which leads to my making stupid decisions that result in financial ruin. How do you prevent that?


Specifying a password with Tools → Protection won't prevent snoops from viewing your worksheet and learning your super-secret information; the purpose of this feature is to keep someone from changing the data. To keep a file from prying eyes, turn to the File→Save As command, which has a well-hidden feature that can require a user to provide a password before he or she can open the file. In the Save As dialog box, click Tools → General Options and create your passwords for opening and modifying a worksheet. Even with the advanced encryption options available in Excel 2002 and 2003, don't rely on this method to provide absolute protection. With enough time and some password-cracking tools readily available on the Internet, anyone who has access to your file can eventually get into it. Don't believe me? One day you'll password-protect an Excel file and forget the password. Annoying, yes, but for the cost of a download-and if it strikes you, a donation to the author-there's a way to crack it. Try the Excel Password Remover at http://www.oreilly.com/pcannoyances.

The Fix:

Excel can force you to enter only appropriate data in certain cells. To enable these restrictions:

  1. Select the cell (or cells) where only one type of input should be accepted.

  2. Choose Data → Validation. On the Settings tab, you can choose from Whole Number (integers), Decimal (real numbers), List (a defined list of allowable entries, which you can display to users as a drop-down list), Date, Time, Text Length, or Custom (allows you to use formulas to calculate acceptable values).

  3. Enter values in the remaining boxes, which change depending on your choice in the Allow pull-down menu. See Figure 4-31.

  4. (Optional) On the Input Message tab, you can create a text message that appears when you select a cell with data validation restrictions.

  5. On the Error Alert tab, enter a message that appears when you make an entry that doesn't match the criteria specified for the cell. (If you don't enter text, Excel issues a default message.)

Figure 4-31. You can enter absolute values (as shown in the Minimum box), cell references (as shown in Maximum), or formulas.

The Style menu offers three icons. Your selection here affects more than just the icon that appears in the error message box:

  • If you choose Stop, the message box includes Retry and Cancel buttons. You can retry all day if you like, but Excel won't accept a value outside the prescribed range.

  • If you choose Warning, the message box includes Yes, No, and Cancel buttons. Clicking No is the same as clicking Retry in the Stop box; it gives you another chance to enter a valid value. Clicking Yes, however, tells Excel to accept the out-of-range value you've given it.

  • If you choose Information, the message box includes OK and Cancel buttons. Clicking OK is like clicking Yes in the Warn box: it accepts the invalid value.

If you uncheck the "Show error alert" box on the Error Alert tab, you might expect Excel to reject any nonconforming data entry. Instead, Excel ignores the condition and accepts any entry, effectively disabling this command. Oy!


The Annoyance:

Often when I'm in a worksheet, I need a quick total. I sometimes want to sum all the numbers in a column, but often I want to sum just a few values. Sure, with Excel, I could create a formula to calculate just about anything, but there's got to be an easier way!

The Fix:

Excel can add selected numbers and perform certain other common functions on the spot, and you don't have to mess with formulas. First, make sure the status bar is showing-that's where the answers will appear. Choose Tools → Options, and on the View tab, be sure "Status bar" is selected.

Next, select the numbers you want to add. You can select a single range of adjacent cells, or you can select nonadjacent cells by holding down the Ctrl key as you click each one. (You can also extend a range by holding down Shift as you click. Practice a bit with Ctrl, Shift, and click, and you'll be able to select any combination of cells you like.)

The sum of the selected cells now appears in the status bar below the worksheet, next to "Sum=". If it doesn't, right-click the status bar and choose Sum. The right-click also displays the other results you can show in the status bar:


AutoFilter is one of Excel's slicker features. If you have a bunch of data in a worksheet set up like a database-that is, you have column headings with your data below, one record per row-try choosing Data → Filter → AutoFilter. It adds a drop-down arrow next to each column heading. You can then filter your database so that only certain records (rows) are shown, as in the figure. It's a lot easier than trying to figure out Access, right?

But here's the rub: you can't use AutoFilter on a protected worksheet. If you protect the worksheet and then try to use the AutoFilter command, you'll find that the command is unavailable. If you set up the AutoFilters and then protect the worksheet, the drop-down arrows no longer work. Although they still appear on the worksheet, clicking them does nothing. If the list is already filtered, it stays filtered; if it's not filtered, you can't filter it.

If you're using Excel 2000 or earlier, you're out of luck. You have to choose either protection or filtering. But if you're using Excel 2002, you can employ AutoFilter in a protected worksheet. Here's what you do:

  1. Choose Tools→Protection→Protect Sheet. (If the sheet is already protected, you must first choose Tools → Protection → Unprotect Sheet.)

  2. In the Protect Sheet dialog box, scroll down until you find the Use AutoFilter checkbox and select it. Then click OK.


Displays the mean (average) of the selected cells


Displays the number of nonblank cells in the selection

Count Nums

Displays the number of numeric values in the selection


Displays the highest (maximum) numeric value in the selection


Displays the lowest (minimum) numeric value in the selection


Displays the sum of the numeric values in the selection


The Annoyance:

Occasionally, after I've set up column headings and row names, and started entering data, I realize the arrangement isn't going to work out. The column headings should be row titles, and the row titles should be column headings. The prospect of retyping or cutting and pasting or dragging to rearrange everything is daunting.

The Fix:

Fret not. Excel has this cool ability to copy a vertical set of cells and then paste them horizontally, and vice versa. And it's easy to do if you know where to find this feature.

  1. Select the cells that you want to turn on their ear. Don't select cells across more than 256 rows; turned on its side, your selection has to fit within Excel's 256-column limit (A through IV). Also, you can't paste the range you're copying, so you may want to paste into a new sheet. To quickly transpose an entire worksheet, press Ctrl-End and then Ctrl-Shift-Home to select all the cells that have been used.

  2. Copy the selected data by pressing Ctrl-C, clicking the Copy button, or right-clicking the selection and choosing Copy.

  3. Right-click the destination cell-the cell in the upper-left corner of the range where you want the data to end up-and choose Paste Special.

  4. Select Transpose and click OK.

Each row from the selected area becomes a column in the new area, with the first (top) row becoming the first (leftmost) column of the destination area, as shown in Figure 4-32.

Figure 4-32. Selecting cells A1:F4 and using Paste Special with the Transpose option in cell A6 turns rows into columns with just a few clicks.


The Annoyance:

Storing hyperlinks in Excel worksheets is a pain-I'm always clicking one by accident and launching my browser. How can I select the contents of a cell holding a hyperlink?

The Fix:

The quick and dirty way to select a cell in Excel 2000, 2002, and 2003 without triggering the link it contains is to click the cell and hold the mouse button until the cursor changes to a plus sign, and then let go. The cell is highlighted and your browser stays closed. Now you can do the usual cell tasks, such as moving the cell's contents to another location.

On the other hand, if you want to remove a link, right-click it and choose Hyperlink → Remove Hyperlink. If you never, ever want to see a hyperlink created in Excel 2002 and 2003 when you enter a web address in a cell, stop the program from automatically converting them: choose Tools → AutoCorrect Options, click the AutoFormat As You Type tab, and uncheck the "Internet and network paths with hyperlinks" box.


The Annoyance:

When I type text into an Excel cell, say, for a label or heading, the text goes in as one long line. I'd really like to type in a short list, with numbered entries on separate lines. Is this possible?

The Fix:

What? You're not happy with a mile-long line of text? Try this trick: click the cell where you want the text to appear, enter the desired text, and then press Alt-Enter whenever you want a sentence to start on a new line. (Typing Alt-Enter essentially enters a carriage return in the cell.) When you're done typing, press the Enter key to close the cell (see Figure 4-33).

Figure 4-33. Want to type a short list in a cell? Alt-Enter is the magic keyboard combo that lets you do it.


The Annoyance:

The way the Enter key works in Excel drives me nuts. I want it to take me one cell to the right, but it always takes me down one cell in the column instead.

The Fix:

After doing a little digging, I discovered you can make the Enter key move the cursor to any adjacent cell-up, down, left, right, or even not at all. To change the Enter key's setting, click Tools → Options, click the Edit tab, and go to the Direction drop-down menu. Or uncheck "Move selection after Enter" to keep the current cell active when you use Enter.


The Annoyance:

When I create a new, blank workbook, Excel creates three blank worksheets, labeled Sheet 1, Sheet 2, and Sheet 3. (Don't confuse this phenomenon with "three sheets to the wind," which is something else altogether.) Most of my quick-and-dirty workbooks use only a single worksheet, and if I need more worksheets, it's easy to add them. So why does Microsoft always clutter my screen and hard drive with worksheets I don't need?

The Fix:

I can't answer that last question, but I can offer a solution. Open Tools → Options and click the General tab. In the "Sheets in new workbook" field, pick the number of sheets you want in each new workbook you create. I set mine to 1, but this box accepts values up to 255.


Tired of using the mouse to switch to another worksheet? Use Ctrl-PageUp and Ctrl-PageDown to move through them.

Since I've called your attention to those tabs at the bottom of the workbook, here are a few things you can do with them:

Finally, here's a really cool trick that can speed data entry and impress your friends:

Enter data or apply formatting to multiple worksheets simultaneously. Select multiple tabs. Entries you make and formatting you apply affect all selected (active) worksheets. This trick makes it a snap to reformat all the worksheets in a workbook if you want to change their appearance, for example.
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint