2.2. Quick Ways to Add Data
Some of Excel's time-saving frills can make your life easier when you're entering data in a worksheet. This section covers four such features: AutoComplete, AutoCorrect, AutoFill, and AutoFit, along with Excel's top candidates for the Lifetime Most Useful Achievement award: Undo and Redo.
Tip: Excel really has two types of automatic features. First off, there are features that do things to your spreadsheets automatically , namely AutoComplete and AutoCorrect. Sometimes that's cool and convenient , but other times it can send you running for the old manual typewriter. Fortunately, you can turn off both. Excel also has "auto" features that really aren't that automatic. These include AutoFill and AutoFit, which never run on their own.
Some worksheets require that you type in the same information row after row. For example, if you're creating a table to track the value of all the Sesame Street collectibles you own, you only can type in Kermit so many times before you start turning green. Excel tries to help you out with its AutoComplete feature, which examines what you type, compares it against previous entries in the same column, and, if it recognizes the beginning of an existing word, fills it in.
For instance, in your Sesame Street worksheet, if you already have Kermit in the Characters column, when you start typing a new entry in that column beginning with the letter K, Excel automatically fills in the whole word Kermit. Excel then selects the letters that it's added (in this case, ermit ). You now have two options:
Tip: If you want to use the AutoComplete text but change it slightly, turn on edit mode for the cell by pressing F2. Once you enter edit mode, you can use the arrow keys to move through the cell and make modifications. Press Enter or F2 to switch out of edit mode when you're finished.
AutoComplete has a few limitations. It works only with text entries, ignoring numbers and dates. It also doesn't pay any attention to the entries you've placed in other columns . And finally, it only takes a stab at providing you with a suggestion if the text you've typed in matches another column entry unambiguously . This means that if your column contains two words that start with K, like Kermit and kerplop, Excel doesn't make any suggestion when you type K into a new cell, because it can't tell which option is the most similar. But if you type Kerm , Excel realizes that kerplop isn't a candidate, and it supplies the AutoComplete suggestion Kermit.
If you find AutoComplete annoying, you can get it out of your face with a mere click of the mouse. Just select Tools Options, click the Edit tab, and then turn off "Enable AutoComplete for cell values."
As you type text in a cell, AutoCorrect cleans up behind youcorrecting things like miscapitalized letters and common misspellings. AutoCorrect is subtle enough that you might not even realize it's monitoring your every move. To get a taste of its magic, look for behaviors like these:
Note: AutoCorrect doesn't correct most misspelled words, just common typos. To correct other mistakes, use the spell checker described on Section 5.3.
For the most part, AutoCorrect is harmless and even occasionally useful, as it can spare you from delivering minor typos in a major report. But if you need to type irregularly capitalized words, or if you have a garden-variety desire to rebel against standard English, you can turn off some or all of the AutoCorrect actions.
To reach the AutoCorrect settings, select Tools AutoCorrect Options. (All AutoCorrect options are language specific, and the title of the dialog box that opens indicates the language you're currently using.) Most of the actions are self-explanatory, and you can turn them off by unchecking them. Figure 2-6 explains the "Replace text as you type" option, which is not just for errors.
Tip: For really advanced AutoCorrect settings, you can use the Exceptions button to define cases where Excel won't use AutoCorrect. When you click this button, the AutoCorrect Exceptions dialog box appears with a list of exceptions. For example, this list includes abbreviations that include the period but shouldn't be capitalized (like pp.) and words where mixed capitalization is allowed (like WordPerfect).
AutoFill is a quirky yet useful feature that lets you create a whole column or row of values based on just one or two cells that Excel can extrapolate into a series. Put another way, AutoFill looks at the cells you've already filled in a column or row, and then makes a reasonable guess about the additional cells you might want to add. People commonly use AutoFill for sequential numbers, months, or days.
Here are a few examples of lists that AutoFill can and can't work with:
Bottom line: AutoFill is a great tool for generating simple lists, but it isn't nearly as useful if you're working with a complex sequence of values.
Tip: AutoFill doubles as a quick way to copy a cell value multiple times. For example, if you select a cell in which you've typed Cookie Monster , you can use the AutoFill technique described below to fill every cell in that row or column with the same text.
To use AutoFill, follow these steps:
220.127.116.11 Custom AutoFill Lists
Excel stores a collection of AutoFill lists that it refers to every time you use the feature. You can add your own lists to the collection, which extends the series AutoFill recognizes. For example, Excel does not come set to understand Kermit, Cookie Monster, Grover, Big Bird, Oscar, and Snuffleupagus as a series, but you can add it to the mix.
But why bother to add custom lists to Excel's collection? After all, if you need to type in the whole list before you use it, is AutoFill really saving you any work? The benefit occurs when you need to create the same list in multiple worksheets, in which case you can type it in just once and then use AutoFill to recreate it as often as you'd like.
To create a custom list, follow these steps:
Page Figure 1-4 explained how you can drag the edge of a column to resize it. For greater convenience, Excel also provides an AutoFit feature that automatically enlarges columns to fit overflowing contents perfectly ( unfortunately , it doesn't include a shrink-to-fit option).
The AutoFit feature springs into action in three situations:
Note: If a column is already large enough for its content, AutoFit has no effect.
While AutoFit automatically widens columns when you type in a number or date in a cell, you can still shrink a column after you've entered your information.
Keep in mind, however, that when your columns are too narrow, Excel displays the cell data differently, depending on the type of information. If your cells contain text , it's entirely possible for one cell to overlap (and thereby obscure) another, a problem first described in Chapter 1. However, if Excel allowed truncated numbers , it could be deceiving. For example, if you squash a cell with the price of espresso makers so that they appear to cost $2 (instead of $200), you might wind up ordering a costly gift for all your coworkers. To prevent this problem, Excel never truncates a number or date. Instead, if you've shrunk a cell's width so that the number can't fit, you'll see a series of number signs (like ##### ) filling in the whole cell. This warning is just Excel's way of telling you that you're out of space. Once you enlarge the column by hand (or by using AutoFit), the original number reappears. (Until then, you can still see the number stored in the cell by moving to the cell and looking in the Formula bar.)
2.2.5. Undo and Redo
While editing a worksheet, an Excel guru can make as many (or more) mistakes as a novice. These mistakes include copying cells to the wrong place, deleting something important, or just making a mess of the cell formatting. Excel masters can recover much more quickly, however, because they rely on Undo and Redo. Get in the habit of calling on these features, and you'll be well on your way to Excel gurudom.
How do they work? As you create your worksheet, Excel records every change you make. Because the modern computer has vast resources of extra memory and computing power (that is, when it's not running the latest three-dimensional real-time action game), Excel can keep this log without slowing your computer down one bit.
If you make a change to your worksheet that you don't like (say you inadvertently delete your company's entire payroll plan), you can use Excel's Undo history to reverse the change. Simply choose Edit Undo, or press the super-useful keyboard shortcut Ctrl+Z. Excel immediately restores your worksheet to its state just before the last change. If you change your mind again, you can revert to the changed state (known to experts as "undoing your undo") by choosing Edit Redo, or by pressing Ctrl+Y.
Note: Storing multiple versions of your data can potentially multiply the size of your files. So Excel automatically clears the change log every time you save or close a workbook. This means that as soon as you save your worksheet, Excel clears the Undo history, and you can't reverse any of your previous changes. If you open the Edit menu, Can't Undo appears as a dimmed, inaccessible option.
Things get interesting when you want to go farther back than just one previous change, because Excel doesn't just store one change in memory. instead, it tracks the last 16 actions you made. And it tracks just about anything you do to a worksheet, including cell edits, cell formatting, cut and paste operations, and much more. Put another way, at any given point in time, Excel is actually holding the previous 16 versions of your worksheet in memory. As a result, if you make a series of changes you don't like, or if you discover a mistake a little later down the road, you can step back through the entire series of changes, one at a time. Every time you press Ctrl+Z, you go back one change in the history. This ability to reverse multiple changes makes Undo one of the most valuable features ever added to any software package.
Tip: The Undo feature means you don't need to be afraid of performing a change that might not be what you want. Excel experts often try out new actions, and then simply reverse them if the actions don't have the desired effect.
18.104.22.168 Undo quirks
The Undo feature raises an interesting problem. If you can go back 16 levels into the history of your document, how do you know exactly what changes you're reversing? Most people don't remember the previous 16 changes they made to a worksheet, which makes it all too easy to reverse a change you actually want to keep. Excel provides the solution by not only keeping track of old worksheet versions, but also by keeping a simple description of each change, too. You don't see this description if you use the Ctrl+Z and Ctrl+Y shortcuts. However, if you use the menu (Edit Undo), the action you're undoing or redoing is listed there.
For example, if you type hello into cell A1, and then delete it, when you open the Edit menu, it says Undo Clear. If you choose this option, the word hello returns. And if you open the Edit menu again, it says, "Undo Typing `hello' in B5," as shown in Figure 2-9.
Tip: Occasionally, if you perform an advanced analysis task with an extremely complex worksheet, Excel may decide it can't afford to keep an old version of your worksheet in memory. If Excel hits this point, it warns you before you make the change, and gives you the chance to either cancel the edit or continue (without the possibility of undoing the change). In this rare situation, you might want to cancel the change, save your worksheet as a backup, and then continue.