Leading the way in Microsoft Office Development

What are Worksheet Functions ?

Formulas can be made much more efficient by using worksheet functions.

The built-in worksheet functions can be used to perform calculations, data manipulation and help to analyse data.

Worksheet functions can only be used within a formula and the function name is always followed by a set of parentheses.

A function can either be the only item in a formula or can be part of a larger formula.

You must have parentheses around all of your arguments and the opening parentheses must appear immediately after the name of the function.

There are over 300 worksheet functions and you can use them to help analyse your data.

Some of the functions that are available are equivalent to complicated formulas which you would otherwise have to create by hand.

Although formulas are very useful and can be quite complicated some of the functions such as the "Lookup and Reference" cannot be reproduced using formulas.

Using Worksheet Functions

Worksheet functions can reduce the risk of errors and improve the overall efficiency of your workbook.

Functions can be thought of as being made up of three parts: an equal sign, a function name and a list of arguments enclosed in brackets.

The SUM() function is a great example. This can be used to quickly add up the values in a range of cells.

The cells in column H are used to illustrate the functions used in the adjacent cells.

It is very common for your formulas to only contain worksheet functions. The cells G2, G3 and G4 are examples.

Functions are identical to formulas in the sense that they are updated automatically when the arguments change.

Any changes to the contents of a cell are automatically reflected in any formulas that refer to that cell.

Inserting Worksheet Functions

There are several ways you can insert your functions:

1) Pressing (Insert > Function).

2) Clicking the Insert Function button "fx" on the formular bar.

3) Typing an equal sign directly into a cell. This method does not display the "Insert Function" dialog box.

4) Using the Name Box on the left of the formula bar.

5) Pressing (Shift + F3) to display the (Insert > Function) dialog box.

Using the (Insert > Function) dialog box

There are several ways to insert a function but the easiest method is to use the "Insert Function" dialog box.

Select (Insert > Function) to display the "Insert Function" dialog box or alternatively you can use the shortcut key (Shift + F3).

This dialog box can also be used to insert your functions whether you want to insert them at the beginning or in the middle of a formula.

This dialog box is designed to try and help you find the correct function.

Using the "Insert Function" dialog box will insert an equal sign (=) at the start of the formula automatically.

 (Insert > Function) dialog box

Type a brief description of what you want to do and press Go.

You can obtain more information a particular function by selecting it and pressing the "Help on this function" button at the bottom.

Every time you select a function name in the list, a description of the function and any corresponding arguments will be displayed.

If you do not know the name of the function then you can filter the list of available functions by selecting a specific category from the drop-down list.

After you have chosen a function and pressed OK, the "Function Arguments" dialog box will automatically appear to indicate the arguments that are required by that function.

Inserting Function Arguments

Arguments can be numbers, text, cell references, named ranges, arrays, formulas or even other functions.

Any arguments that are displayed in bold are required. The other arguments are optional.

If you need any extra help with a particular function you can press F1 or refer to the Functions section.

If you insert a formula as an argument to a function you do not need to insert another equal sign (=). This is only necessary at the start of a formula.

If you want to insert a function as an argument to another function, this is referred to as a nested function and is covered in detail on the next page.

You can either type in the arguments directly or if the arguments are cell references you can select or drag the mouse over the relevant cells.

Displaying the function parentheses, commas and argument names can be useful is you are unfamiliar with the function arguments.

Pressing (Ctrl + Shift + A) when the insertion point is to the right of a function name will display this information.

Pressing (Ctrl + A) after you enter the name of function (e.g. =sum) will display the Function Arguments dialog box.

Using the Formula Bar - Insert Function button

An alternative way to display the "Insert Function" dialog box is to press the Insert Function button on the formula bar.

This button is a shortcut and will display the (Insert > Function) dialog box.

Typing directly into a cell

You can also just type a function name directly into a cell.

You can type functions directly into a formula, assuming you know the correct syntax.

When you enter functions, type the function name in lowercase letters. When you press Enter any valid function names are automatically converted to uppercase verifying that you have typed them correctly.

Whenever you type the name of a function directly into a formula a yellow tool tip text appears indicating the arguments that are required by that function.

You can select the tool tip text with the left mouse button to be taken directly to the corresponding Help page.

Using the Name Box

As soon as you insert an equal sign (=) the Name Box on the left of the formula bar changes to a function drop-down list displaying your 10 most recently used functions.

This provides you with a quick way to insert frequently used functions.

The button at the bottom "More Functions" will display the (Insert > Function) dialog box.

As soon as a function is selected the "Function Arguments" dialog box is displayed.

Analysis ToolPak Add-in

If you attempt to use a function from an add-in which is not loaded the formula will return #NAME?.

Things to Remember

All formulas or formulas containing functions must start with an equal sign (=).

Valid function names are converted to uppercase automatically after you press Enter, so always enter function names in lowercase. This verifies that you have typed them correctly.

Some worksheet functions will format the cells to an appropriate format after they have been entered. This only happens if the format is the General number format (e.g. NOW()).

It is sometimes possible to enter a worksheet function without providing all the necessary arguments (e.g. EOMONTH()), whereas other functions cannot be entered until all the arguments have been given (e.g. MAX()).

(Shift + F3) - Display the (Insert > Function) dialog box and can be used when editing your formulas.

Remember to check your (Tools > Options)(View tab, Zero values). This controls whether empty cells contain a zero or not.