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 Auto-Fill recognizes. For example, Excel doesn’t 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:
-
Choose Office button
Excel Options.The familiar Excel Options window appears.
-
Choose the Popular section, and then click Edit Custom Lists.
Here, you can take a gander at Excel’s predefined lists, and add your own (Figure 2-9).
-
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.
Figure 2-9. Here, a new custom list of colors is being added.
-
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-9 shows a series of color names separated by commas.
If you’ve already typed your list into your worksheet, you can save some work. Instead of retyping the list, 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.
-
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 Custom Lists dialog box, and OK again to close the Excel Options window.
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.2 (Figure 1-5) 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).
| GEM IN THE ROUGH 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, right-click the column header at the top of the column, and then choose 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.) You can also adjust multiple column widths at the same time. Just select multiple columns (click the first column header, and then drag to the left or to the right to select more columns). Now, when you apply a new width, Excel uses it for all the selected columns. 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 Home |
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 Home
Cells Format AutoFit Selection, Excel automatically expands the column to fit the content in the active cell. This feature is helpful if you have a column that’s 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: When 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. When 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.
| NOSTALGIA CORNER Do More with Undo |
|
Long-time Excel fans will realize Excel 2007’s Undo feature is vastly improved. Previous versions of Excel were limited to a paltry 16 levels of Undo. Excel 2007 goes far better with 100 levels, which lets you travel farther back into the history of your spreadsheet. Another welcome improvement that you may not have noticed is the fact that Excel 2007 doesn’t clear the Undo history when you save your spreadsheet. In previous versions of Excel, this was an exasperating quirkas soon as you decided to save your work, you lost out on any chance to undo an action. Now, the Undo history remains until you close your workbook. |
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. In the Quick Access toolbar, simply click the Undo button (Figure 2-10), 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 pressing Ctrl+Y.
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 100 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. 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, then 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.
Figure 2-10. Top: When you hover over the Undo button, you see a text description for the most recent action, which is what you’ll undo if you click away. Here, the text Hello has just been typed into a cell, as Excel explains.
Bottom: Click the down-pointing arrow on the edge of the Undo button to see a history of all your recent actions, from most recent (top) to oldest (bottom). If you click an item that’s down the list, you’ll perform a mega-undo operation that undoes all the selected actions. In this example, three actions are about to be rolled backthe text entry in cell B2, and two format operations (which changed the number format and the background fill of cell A2).
Tip: The Undo feature means you don’t need to be afraid of performing a change that may 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.
The Undo feature raises an interesting dilemma. When you can go back 100 levels into the history of your document, how do you know exactly what changes you’re reversing? Most people don’t remember the previous 100 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. You don’t see this description if you use the Ctrl+Z and Ctrl+Y shortcuts. However, when you hover over the button in the Quick Access toolbar, you’ll see the action you’re undoing listed there.
For example, if you type hello into cell A1, and then delete it, then when you hover over the Undo button in the Quick Access toolbar, it says “Undo Clear (Ctrl+Z)”. When you choose this option, the word hello returns. And if you hover over the Undo button again, it now says, “Undo Typing ‘hello’ in A2 (Ctrl+Z)”, as shown in Figure 2-10, top.
Note: Occasionally, when 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. When 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.
Technorati Tags: add data quickly in excel
Popularity: 13% [?]
Recent Comments