Microsoft Office Tutorials and References
In Depth Information
5. Choose Home ⇒ Clipboard ⇒ Copy.
6. Select cell A1.
7. Choose Home ⇒ Clipboard ⇒ Paste ⇒ Values.
8. Press Enter or Esc to cancel paste mode.
9. Delete column B.
When to use AutoFill rather than formulas
Excel's AutoFill feature provides a quick way to copy a cell to adjacent cells. AutoFill also has some other uses
that may even substitute for formulas in some cases. I'm surprised to find that many experienced Excel users
don't take advantage of the AutoFill feature, which can save a lot of time.
For example, if you need a list of values from 1 to 100 to appear in A1:A100, you can do it with formulas. You type
1 in cell A1, type the formula =A1+1 into cell A2, and then copy the formula to the 98 cells below.
You can also use AutoFill to create the series for you without using a formula. To do so, type 1 into cell A1 and 2
into cell A2. Select A1:A2 and drag the fill handle down to cell A100. (The fill handle is the small square at the
lower-right corner of the active cell.) When you use AutoFill in this manner, Excel analyzes the selected cells and
uses this information to complete the series. If cell A1 contains 1 and cell A2 contains 3, Excel recognizes this
pattern and fills in 5, 7, 9, and so on. This also works with decreasing series (10, 9, 8, and so on) and dates. If
there is no discernible pattern in the selected cells, Excel performs a linear regression and fills in values on the
calculated trend line.
Excel also recognizes common series names such as months and days of the week. If you type Monday into a
cell and then drag its fill handle, Excel fills in the successive days of the week. You also can create custom
AutoFill lists using the Custom Lists panel in the Excel Options dialog box. Finally, if you drag the fill handle with
the right mouse button, Excel displays a shortcut menu to enable you to select an AutoFill option.
Excel 2013 introduces a new feature — Flash Fill — that can also be an alternative to
formulas. See Chapter 16 for more information about Flash Fill.
In some cases, you may not want others to see your formulas. For example, you may have a special formula you
developed that performs a calculation proprietary to your company. You can use the Format Cells dialog box to
hide the formulas contained in these cells.
To prevent one or more formulas from being viewed:
1. Select the formula or formulas.
2. Right-click and choose Format Cells to show the Format Cells dialog box (or press Ctrl+1).
3. In the Format Cells dialog box, click the Protection tab.
4. Place a check mark in the Hidden check box, as shown in Figure 2-7.