Software

Enter Excel functions visually

If you support Excel users who suffer from function-phobia, here's a lesson that's guaranteed to provide a cure.


If you support or teach Excel users, you know that one of the most difficult skills for a beginner to master is how to use functions. Of course, the Standard toolbar’s AutoSum button makes it easy to enter a simple SUM formula. But when it comes to composing complex formulas with functions that require two or more arguments, many users get lost. And Excel’s often-cryptic error messages only compound the frustration.

In a corresponding TechMails tip, we explained Excel’s Paste Function feature, which allows you to build a function by pointing and clicking instead of composing (typing) it manually. If you want to be the hero with your beginning Excel users, e-mail them a copy of this article and introduce them to the Paste Function feature.

If you can use a mouse, you can build a function
Here’s the mistake most people make when it comes to learning how to use an Excel function. They go to Help | Contents And Index, click the Index tab, and locate the function name in the list. They print the help text and then try to manually compose the function using the printout as a guide.

There’s nothing wrong with this approach. In fact, the help text contains many valuable examples of the functions at work. But for some users, Excel’s Paste Function feature provides an easier way to enter formulas.

In general, here’s how you would use the Paste Function feature:
  1. Click the cell where you want to enter the formula.
  2. Click the Paste Function icon on the Standard toolbar or go to Insert | Function.
  3. In the Paste Function dialog box, select the appropriate function category and then choose the function from the Function Name list. Excel will display a description of the function, including its arguments.
  4. Click OK, and the Function Wizard will display fields in which you enter the appropriate values or cell references for the function’s arguments. (You can key those entries directly, or you can use the mouse to select them.)

Building an IF function with the mouse
Let’s use the raw data in Figure A to demonstrate how this feature works. Suppose you want to compose an IF function (or “IF test”) that displays the word “Excellent” if the value in column B is at least 600, and you want to display the word “Good” if the value is less than 600.

Figure A
We’ll use the Paste Function feature to create an IF test that displays different results, depending on the values in column B.


Begin by selecting cell C2 and choosing Insert | Function. In the Function Category list, select Logical. Then select IF from the Function Name list. When you do, Excel will display a description and the arguments of the IF function, as shown in Figure B.

Figure B
The Paste Function dialog box displays a description and the arguments of the function you select.


Click OK, and Excel launches a special window that contains fields for each of the IF function’s arguments, as shown in Figure C. You can manually enter the arguments in these fields, or you can click the red arrow at the right-hand edge of a field and navigate to the cell or range of cells you need for your function.

Figure C
This window makes it easy to enter the arguments for your function.


Figure D shows what our screen looks like after we clicked the red arrow for the Logical_test field and clicked once on cell B2. Once you’ve selected the cell (or range of cells), click the red arrow again to return to the function-building window.

Figure D
Click the red arrow beside a field when you want to select a range with your mouse instead of typing it manually.


Next, type <600 to complete the rest of your Logical_test field, and press [Tab] to move to the Value_if_true field. Type Good, press [Tab] to move to the Value_if_false field, and type Excellent. When you move out of the field, you’ll notice that Excel encloses the word Good in quotation marks, as shown in Figure E.

Figure E
Here’s what our IF function looks like after we’ve filled in each of the three arguments.


Click OK to enter your function in the worksheet. Then, simply copy that cell down the appropriate number of rows. Figure F shows our final result.

Figure F
After we created the function visually, we copied that formula into the other cells in column C.

Get valuable tips for using worksheet functions, VBA code, and much more, all delivered straight to your inbox. Best of all, it's absolutely free. Sign up for the Excel TechMail today!

Editor's Picks