Microsoft Office Tutorials and References

In Depth Information

**How to Make Your Formulas Function Even Better**

the information in all the cells in one operation. You first select the cell

ranges to hold the information. (Excel lets you select more than one cell

range for this kind of thing — see Chapter 3 for details.) Then you construct

the entry on the Formula bar and press Ctrl+Enter to put the entry into all the

selected ranges.

The key to making this operation a success on a physical keyboard is to hold

the Ctrl key down while you press Enter so that Excel inserts the entry on the

Formula bar into all the selected cells. If you forget to hold Ctrl and you just

press Enter, Excel places the entry in the first cell only of the selected cell

range. On the Windows virtual keyboard, you just tap Ctrl followed by Enter

in succession.

You can also speed up data entry in a list that includes formulas by making

sure that the Extend Data Range Formats and Formulas check box is selected

in the Editing Options section of the Advanced tab in the Excel Options dialog

box. (Choose File
➪
Options
➪
Advanced or press Alt+FTA.) When this check

box is selected, Excel automatically formats new data that you type in the

last row of a list to match that of like data in earlier rows and copies down

formulas that appear in the preceding rows. Note, however, that for this new

feature to kick in, you must manually enter the formulas and format the data

entries in at least three rows preceding the new row.

How to Make Your Formulas

Function Even Better

Earlier in this chapter, I show you how to create formulas that perform a

series of simple mathematical operations, such as addition, subtraction,

multiplication, and division. (See the section “Fabricating those fabulous

formulas!”) Instead of creating complex formulas from scratch out of an intricate

combination of these operations, you can find an Excel function to get the

job done.

A
function
is a predefined formula that performs a particular type of

computation. All you have to do to use a function is supply the values that the

function uses when performing its calculations. (In the parlance of the

Spreadsheet Guru, such values are the
arguments of the function.
) As with

simple formulas, you can enter the arguments for most functions either as a

numerical value (for example,
or –
4.56
) or, as is more common, as a cell
22

reference (
B10
) or as a cell range (
C3:F3
).