Section 2.2. Quick Ways to Add Data

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 different 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.

UP TO SPEED
Regional Dating

Windows has regional settings for your computer, which affect the way Microsoft programs understand things like dates and currency. You can change the settings, and they don't have to correspond to where you liveyou can set them for your company headquarters on another continent, for instance. But keep in mind that these affect all the programs on your computer.

To make a switch, go to the Start menu in Windows and choose Settings Control Panel, then double-click Regional and Language Options, which brings up a dialog box. The Regional Options tab has the settings you want. The most important setting is in the first box, which has a drop-down list you can use to pick the region you want, like English (United States) or Swedish (Finland).

You can fine-tune the settings in your region, too. Fine tuning makes sense only if you have particular preferences about how dates should be formatted that don't match the standard options. Click the Customize button next to the region box to bring up a new dialog box, and then click the Date tab (shown here at left)

Use the pull-down menus to specify the date separator; order of month, day, and year components in a date; and how Excel should interpret two-digit years. You can mix and match these settings freely, although you may wind up with a computer that's completely counterintuitive to other people.

No matter what the regional settings are, you can always use the international date standard, which is Year/Month/Day, though you must supply a four-digit year (as in 2004/7/4). If you use a two-digit year, Excel will assume you're trying to use the Month-Day-Year or Day-Month-Year pattern.

2.2.1. AutoComplete

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:

If you want to accept the AutoComplete text, move to another cell. For example, if you hit the right arrow key or press Enter to move down, Excel leaves the word Kermit behind.
If you want to blow off Excel's suggestion, just keep typing. Because Excel automatically selects the AutoComplete portion of the word (ermit), your next keystrokes overtype that text. Or, if you find the AutoComplete text is distracting, press Delete to remove it right away.

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 takes a stab at providing you with a suggestion only if the text you've typed in matches another column entry unambiguously. For example, 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."

2.2.2. AutoCorrect

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 may not even realize it's monitoring your every move. To get a taste of its magic, look for behaviors like these:

If you type HEllo, AutoCorrect changes it to Hello.
If you type friday, AutoCorrect changes it to Friday.
If you start a sentence with a lowercase letter, AutoCorrect uppercases it.
If you scramble the letters of a common word (for example, typing thsi instead of this, or teh instead of the), AutoCorrect replaces the word with the proper spelling.
If you accidentally hit the Caps Lock key and then type jOHN sMITH when you really wanted to type John Smith, Excel not only fixes the mistake, it also switches off the Caps Lock key.

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 youre currently using.) Most of the actions are self explanatory, as you can see in Figure 2-6, and you can turn them off by unchecking them.

As you'll notice when you look at Figure 2-6, near the middle of the AutoCorrect dialog boxunder "Replace text as you type"is a long list of symbols and commonly misspelled words (the column on the left) that Excel automatically replaces with something else (the column on the right). But what if you want something different? For example, what if you want the copyright symbol to appear as a C in parentheses, instead of the c-in-a-circle that Excel suggests? You have two options: you can remove individual corrections (select one and then click Delete), or you can change the replacement text (click in the With box, make your edits, and click Replace).

And you can add your own rules, too. For example, you may want to be able to type PESDS and have Excel insert Patented Electronic Seltzer Delivery System, so you don't have to type the whole thing out every time. To create your own rule, simply type in the Replace and With text as shown in Figure 2-6, and then click OK.

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).

2.2.3. AutoFill

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 may want to add. People commonly use AutoFill for sequential numbers, months, or days.

Figure 2-6. Under "Replace text as you type" you find a long list of symbols and commonly misspelled words (the column on the left) that Excel automatically replaces with something else (the column on the right). But the AutoCorrect dialog box doesn't just correct errors; you can also use it to create your own shorthand text rules. Simply type in the Replace and "With" text as shown here, and then click OK.

Here are a few examples of lists that AutoFill can and can't work with:

The series 1, 2, 3, 4 is easy for Excel to interpretit's a list of steadily increasing numbers. The series 5, 10, 15 (numbers increasing by five) is just as easy. Both of these are great AutoFill candidates.
The series of part numbers CMP-40-0001, CMP-40-0002, CMP-40-0003 may seem more complicated because it mingles text and numbers. But clever Excel can spot the pattern easily.
Excel readily recognizes series of months (January, February, March) and days (Sun, Mon, Tue), either written out or abbreviated.
A list of numbers like 47, 345, 6 doesn't seem to follow a regular pattern. But by doing some analysis, Excel can guess at a relationship and generate more numbers that fit the pattern. There's a good chance, however, that these won't be the numbers you want, so take a close look at whatever Excel adds in cases like these.

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 Christmas card sent, 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:

Fill in a couple of cells in either a row or a column to start off the series.
Technically, you can use AutoFill if you fill in only one cell, although this approach gives Excel more room to make a mistake if you're trying to generate a series. Of course, if you just want to copy a single cell several times, one cell is a sufficient start.
Select the cells you've entered so far. Then click the small black square at the bottom-right corner of the selected box.
You can tell that your mouse is in the right place when the mouse pointer changes to a plus symbol.
Drag the border down (if you're filling a column of items) or to the right (if you're filling a row of items).
As you drag, a tooltip appears, showing the text that Excel is generating for each cell.

While you're dragging, you can hold down Ctrl to affect the way that Excel fills a list. If you've already filled in at least two cells, Ctrl tells Excel to just copy the list multiple times, rather than look for a pattern. If you want to expand a range based on just one cell, Ctrl does the opposite: it tells Excel to try to predict a pattern, rather than just copy it.

When you release the mouse, Excel automatically fills in the additional cells, and a special AutoFill icon appears next to the last cell in the series. Clicking the AutoFill icon (as shown in Figure 2-7) displays a menu that lets you fill the series without copying the formatting, or you can copy the formatting without filling the series. You can also choose to copy values instead of generating a list. For example, if you choose to copy valuesor Copy Cells, as Excel calls itin the two-item series Jan, Feb, you end up with Jan, Feb, Jan, Feb, rather than Jan, Feb, Mar, Apr.

Figure 2-7. After AutoFill does its magic, Excel displays a menu that lets you fine-tune the automatically generated data.

2.2.3.1. 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 doesn't come set to understand the vegetables you planted this season or your grandkids' names as a series, but you can add them 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 re-create it as often as you'd like. For example, if you create a spreadsheet each year to track the new heirloom varieties of veggies you planted, you can create the list once and use it in spreadsheet after spreadsheet.

To create a custom list, follow these steps:

Choose Tools Options and then click the Custom Lists tab, shown in Figure 2-8.
In the "Custom lists" box on the left side of the dialog box, select NEW LIST.
This action tells Excel that you're ready to create a new list.
In the "List entries" box on the right side of the dialog box, type in your list.
Separate each item with a comma or by pressing Enter. The list in Figure 2-8 shows a series of vegetable names separated by commas.
If you've already typed your list into your worksheet, you can save some work. Instead of retyping the list in the Options dialog box, click inside the text box labeled "Import list from cells." Then, click the worksheet and drag to select the cells that contain the list. (Each item in the list must be in a separate cell, and the whole list should be in a series of adjacent cells in a single column or a single row). When you're finished, click Import, and Excel copies the cell entries into the new list you're creating.

Figure 2-8. Here, the new custom list is veggies (useful if you're a gardener using Excel to track how each new variety fares in your plot).
Click Add to store your list.
At any later point in time, you can return to this dialog box, select the saved list, and modify it in the window on the right. Just click Add to commit your changes after making a change, or click Delete to remove the list entirely.
Click OK to close the Options dialog box.
You can now start using the list with the current worksheet or in a new worksheet. Just type the first item in your list and then follow the AutoFill steps outlined in the previous section.

2.2.4. AutoFit

Section 1.1.3 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:

When you type a number or date that's too wide to fit into a cell, Excel automatically widens the column to accommodate the new content. (Excel doesn't automatically expand columns when you type in text, however.)
If you double-click the right edge of a column header, Excel automatically expands the column to fit the widest entry it contains. This trick works for all types of data, including dates, numbers, and text.
If you select Format Column AutoFit Selection, Excel automatically expands the column to fit the content in the active cell. This feature is helpful if you have a column thats made up of relatively narrow entries, but which also has a long column title. In this situation, you may not want to expand the column to the full width of the title. Instead, you may wish to size the column to fit a typical entry and allow the title to spill over to the next column.

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, bad things could happen. 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.)

TRICKS OF THE TRADE
A Few More Ways to Adjust Column Width

Excel gives you the ability to precisely control column widths. To change the width of a column, select the column (by clicking the column header at the top of the column), and then choose Format Column Width. The standard unadjusted column size is a compact 8.43 characters, but you can change that to any number of characters. (Remember that because different fonts use different size letters, the number of characters you specify here may not correspond directly to the number of characters in your column.)

Finally, you can customize the standard width for columns, which is the width that Excel assigns to columns in every new worksheet that you create. To set the standard width, choose Format Columns Standard Width from the menu, and change the number.

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.

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 the changes you made in your last editing session (in other words, you can't undo any change you made prior to saving). If you open the Edit menu, Can't Undo appears as a dimmed, inaccessible option.

Things get interesting when you want to go further 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.

GEM IN THE ROUGH
Using Redo to Automate Repetitive Tasks

Redo is commonly used to reverse an Undo. In other words, if you cancel an action and then change your mind, you can use Redo to quickly reapply the change. But Redo also has a much more interesting ability: it lets you repeat any action multiple times. The neat thing is that you can repeat this action on other cells.

For example, imagine you hit Ctrl+B to change a cell to bold. If you open the Edit menu, you'll see that the Redo item now says Repeat Font. If you move to another cell and hit Ctrl+Y, Excel applies the bold formatting to the new cell. In this case, you're not saving much effort, because it's just as easy to use Ctrl+B or Ctrl+Y. However, imagine you finish an operation that applies a set of sophisticated formatting changes to a cell.

For example, say you increase the font size, bold the text, and apply a border around the cell (Chapter 4 tells you how to do these things). If you press Ctrl+Y, Excel applies all the changes at oncewhich is much easier than calling up the Formatting dialog box and then reselecting all those options.

The trick when using Redo is to make sure you don't perform another action until you've finished repeating your changes. For example, if you make some formatting changes and then stop to delete an incorrect cell value, you can no longer use Redo to apply your formatting because Excel applies the last change that you madein this case, clearing the cell. (Of course, if you mistakenly apply Redo, you can always call on Undo to get out of the mess.)

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 work as they'd hoped.

2.2.5.1. 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 youre 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.

Figure 2-9. As you work, Excel updates the Edit menu to reflect your last action. Here, the text "Hello" has just been typed into a cell, as Excel explains.

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 may want to cancel the change, save your worksheet as a backup, and then continue.