Mastering Excel functions

Terminology

A function has arguments. An argument is a value passed to a function when the function is called.

The arguments are always supplied to the function between brackets. Arguments are separated from one another by a comma or a semi-colon, depending on the Regional Settings of the computer you are working on.

Functions ALWAYS return something!

Only four functions require no arguments (the brackets remain empty):

• NOW()
• TODAY()
• RAND()
• PI()

Mathematical operators

The following operators are used in Excel for maths formulae and functions:

+ addition

- subtraction

* multiply (product)

/ division

^ power

Comparison operators

The following comparison operators are used:

< less than

<= less than or equal to

> greater than

>= greater than or equal to

= equal

<> not equal

Grab a function

I only — and always — use the first option, which is to use the Insert Function button.

Insert Function button

The Insert Function button is one way of accessing Excel’s functions. In the example below we use the SUM() function:

1. Select the cell where the calculation should be performed, and the answer displayed
2. Click on the Insert Function button
3. The Insert Function dialog opens
4. Click on the drop-down to see the list of categories
5. Select a category to display an alphabetic list of functions (select the All option if you do not know which category the function belongs to, or select Recent if you used the function recently)
6. Select the function you wish to use
7. Use the description to confirm your choice of functions
8. Click on OK
9. Complete the required argument(s) for the function; the SUM function requires at least one argument, in this example the range A1:A10
10. As each argument text box is selected, an explanation for the argument is displayed
11. The function is displayed in the Formula Bar as it is being built
12. The answer is displayed (if there is a mistake, an error code will be displayed in red)
13. Select the OK button to complete the function

Use the Tab key to move progress from one argument input to the next (and Shift+Tab to move backwards). This way Excel will (almost always) take care of additional syntax requirements, such as when quotation marks are required.

Function Library

I have never used this method and can’t recommend it, but maybe you find it easier…

1. Select the Formulas tab
2. Select the Recently Used category
3. Follow the instructions from Step 9 in the previous example

More advanced example

If the first example did not convince you, hopefully this second, more complex example, will! The VLOOKUP function has a relatively complex syntax, and it would be foolish to tackle it without all the help you can get!

1. We are going to enter the Serial Number we are searching for in cell F2.
2. We are going to enter the VLOOKUP function in cell G2 and this is where the Device Name is going to be displayed as the result of the function. Type =VL in cell G2 and you will see the VLOOKUP function appear in the auto-complete list — double-click on the VLOOKUP function
3. Click on the Insert Function button
4. To open the Function Arguments dialog
5. Your cursor should now be in the Lookup_value argument field
6. The description of the Argument that currently has focus

Next steps

• Learn all there is to know about Selecting function criteria in Excel.
• Have a look at the Excel functions you need to know for
  • IGCSE & AS
  • CAT
• Create a custom function in Excel