How to Enter Excel Formulas and Functions Directly in Worksheet Cells - dummies

How to Enter Excel Formulas and Functions Directly in Worksheet Cells

By Ken Bluttman

Perhaps the easiest entry method is typing a formula directly in a cell in an Excel worksheet. Just type formulas that contain no functions and press the Enter key to complete the entry. Try this simple example:

  1. Click a cell where the formula is to be entered.

  2. Enter this simple math-based formula:

    =6 + (9/5) *100

  3. Press the Enter key.

    The answer is 186. (Don’t forget the order of operators.)

Excel makes entering functions in your formulas as easy as a click. As you type the first letter of a function in a cell, a list of functions starting with that letter is listed immediately.

Entering functions has never been this easy.
Entering functions has never been this easy.

The desired function in this example is MIN, which returns the minimum value from a group of values. As soon as you type M (first enter the equal sign if this is the start of a formula entry), the list in the figure appears, showing all the M functions.

Now that an option exists, either keep typing the full function name, or scroll to MIN and press the Tab key. The following figure shows just what happens when you do the latter. MIN is completed and provides the required syntax structure — not much thinking involved! Now your brain can concentrate on more interesting things, such as poker odds. (Will Microsoft ever create a function category for calculating poker odds? Please?)

In the next figure, the MIN function is used to find the minimum value in the range A7:A15 (which is multiplied with the sum of the values in A1 plus A2). Entering the closing parenthesis and then pressing the Enter key completes the function. In this example, the answer is 1222.

A1: 15

A2: 32

A7: 38

A8: 39

A9: 28

A10: 26

A11: 29

A12: 30

A13: 30

A14: 28

A15: 31

The formula in D5 is =(A1+A2) * MIN(A7:A15).

Excel’s capability to show a list of functions based on spelling is called Formula AutoComplete.

Completing the direct-in-the-cell formula entry.
Completing the direct-in-the-cell formula entry.

You can turn Formula AutoComplete on or off in the Excel Options dialog box by following these steps:

  1. Click the File tab at the top left of the screen.

  2. Click Options.

  3. In the Excel Options dialog box, select the Formulas tab.

  4. In the Working with Formulas section, check or clear the Formula AutoComplete check box.

  5. Click the OK button.

    Setting Formula AutoComplete.
    Setting Formula AutoComplete.