Microsoft Office Tutorials and References
In Depth Information
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
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 in cell A1, type the formula =A1+1 into cell A2, and then copy the formula to 1
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 into cell A2. Select A1:A2 and drag the fill handle down to cell A100. (The fill 2
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.
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
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-6.
5. Use the Review
Protect command to protect the worksheet.
To prevent others from unprotecting the sheet, specify a password in the Protect Sheet