Excel functions wizard

Download this file to follow along with the video and to do the next exercise.

↓ Function Wizard File Download

The Function Wizard can be considered Excel’s cheat tool. It lists every function that can be used in Excel  and guides the user on how the function works.

We use it to:

• Find the correct function to use to solve a particular problem
• Understand what a function in a spreadsheet does if we are not familiar with it
• Understand what Excel requires in each part of the formula
• Test the results of the function before clicking enter.

Watch the video clip and/or read the text to understand how to use the Function Wizard

The function wizard can be activated by clicking on the button next to the formula bar (next to where a formula is entered as shown below) or by going to the Formula tab and choosing ‘Insert Function’.

Once you click the button you will see the following screen.

For convenience sake you can look for a function via various methods. On the diagram below you can see the convenient groupings that have been established and are available when you click the drop down box.

You can also type some search terms in the search box.

For example if we go to the Date & Time category and click on DATE you will see the following. Note that at the bottom of the box there is a description of what the function does and its syntax. This is very useful if you are looking to find a function that meets a certain need.

Once you have chosen a function and clicked OK, the following will appear which explains what the function does and what each entry requires.

In this case we have clicked on the DATE function. Note that the same description appears explaining the function as a whole. Also note that at the bottom is a description that relates to where your cursor currently is positioned. As the cursor is in the YEAR box, the description is for what is required in that box. You can type a number, link to a cell or create a formula.

Another useful benefit of the function wizard is that it can help you understand a function that you haven’t seen before. If for example you see the following in a formula (CUMIPMT) and you are not sure what it does.

While you are clicked on the cell, click on the Function Wizard and you will be taken straight to the relevant function as shown below.

Note that you can get a description of what the function does and the current results. If you are still unclear about what it does, then click on the Help on this Function at the bottom left and you will be taken straight to the relevant help files.

What happens when there is more than one function in a cell?

In the diagram below is a cell that contains an IF, ROUNDUP and SUM function. How do I check what the ROUNDUP function is doing?

The process is simple.

Click somewhere in the word you are interested in (e.g. in the red circle below in the word ROUNDUP) and then click the function wizard. You will immediately be taken to the correct function.

Notice as well that as you use the function wizard, on the right hand side you get a summary of what is in the cells and what the result will be. This is very useful to test whether you will get the result you expect.