Understanding Functions Tutorial
Free Online Microsoft Excel Tutorial
Excel 2010 -
Formulas & Functions
* How to Find a Function
How to Insert a Function
How to Edit a Function
Commonly Used Functions: Sum and Count
Functions are predefined formulas that are the powerhouse behind Excel. Understanding functions, how Excel organizes functions, and creating functions using the Function dialog box takes Excel beyond basic math.
Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Understanding Functions Test
* How to Find a Function
Functions in Excel allow you to automatically perform simple to extremely complex calculations, analyze data and manipulate data depending on what it needs to be used for. Before spreadsheets, people in various different fields of work and study, would have had to manually analyze reams of data and manually analyze it using very rudimentary methods. Excel performs the same tasks that less than 30 years ago would have taken weeks, maybe even months to complete, in a less than a second.
People are often intimidated by functions in Excel, especially when they see a long, complicated looking, formulas referring to multiple cell references in a cell. For example: =VLOOKUP(G1,Sheet2!A1:B11,2,FALSE) may look like it is written in another language if you are not familiar with the function.
The best way to learn about functions and to get used to their syntax is by experimentation.
To find functions:
1. Click on the Formulas tab on the Ribbon.
2. In the Function Library, click on the relevant labeled category button (circled in yellow in the screenshot below) to view functions related to that category. For example, clicking on the button labelled Text will provide functions relating to working with cells containing text in Excel.
3. The Recently Used button contains a list of all the functions recently accessed and is a great way to access frequently used functions quickly.
4. The Financial button contains functions relating to financial calculations and analysis, for example, the PMT function.
5. The Logical button contains functions relating to the logical analysis of data by setting conditions, for example, IF.
6. The Text button contains functions to manipulate text, for example, to change the case of text.
7. The Date & Time button provides functions to make date and time calculations, for example, TODAY.
8. The Lookup & Reference button provides functions that allow you to search for specific strings of data or numerical value, for example, VLOOKUP.
9. The Math & Trig button offers a multitude of simple to very complex mathematical functions, for example, SUM.
10 The button labelled More Functions opens an additional library of functions for Statistical, Engineering, Cube, Information and Compatibility formulas.
Depending on your field of work or study, you may wish to experiment with functions in the relevant libraries.
To quickly find a specific function, you can use the Insert Function dialogue box:
1. Click on Insert Function in the Function Library group under the Formulas tab. This will launch the Insert Function dialogue box. You can also quickly launch the Function Arguments dialogue box by clicking on the fx symbol to the left of the Formula Bar or by using the keyboard shortcut: Shift+F3.
2. In the Search for a function box (circled in yellow in the screenshot above) type the function you are looking for, for example, type: To add numbers together into the box, to find a function that will let you do addition (ie SUM).
3. Press Enter on your keyboard or the Go button to the right of the Search box and the results of your search will display in the Select a function box.
4. If you click on a Function name in the Select a function box, a short description of the function will appear below the list.
It is worthwhile spending a little bit of time reviewing the functions listed in the different categories in the Function Library group and also to use the Search for a Function box to find functions that may be useful to you.
* How to Insert a Function
If you know the name of a function, for example, SUM, you can always type it into a cell manually by inserting the equal sign (=) and the name of the function into the cell and have Excel prompt you as to the arguments needed to complete the formula. A much easier method is to use the Insert Function dialogue box, especially if the function you wish to use is complex.
To insert a function:
1. Position your cursor in the cell in which you wish to insert the function.
2. Select the function directly by clicking on it in the Function Library group or use the Insert Function dialogue box to search for a function and then click on it.
3. Choosing either of the methods noted in 2 above will launch the Function Arguments dialogue box (see screenshot below) which contains the name of the function, argument box and a description of each argument.
4. In the SUM function example above, the arguments (circled in yellow) are called Number1, Number2 etc. Depending on the function you have selected, the arguments will be labelled differently.
5. Click in the argument box and then click back in your worksheet and select the cells you wish to include in each argument. You can also type values directly into the argument boxes, for example, in the SUM function (see screenshot above) if we enter 20 as the first argument (Number1) and 30 as the second argument (Number2), the result 50 will display in the cell we selected.
6. Read the description of the argument located below the argument boxes to get a general overview of what content is needed to build the argument.
7. The calculation value of the selected cells appear below the arguments as well as in the bottom left-hand corner of the dialogue box. It is recommended that you keep an eye on formula results, especially when using more complex functions, to ensure you are selecting and/or inserting the correct arguments for the function.
8. When you have completed all the arguments necessary for the function to work, click on the OK button to insert the function into the selected cell in your worksheet.
Different functions require radically different arguments and number of arguments. Entering the wrong number of arguments or incorrect references or values for an argument will result in an error message or incorrect result.
* How to Edit a Function
The ability to edit a function is equally as important as the ability to insert one. You may wish to make small changes such as changing the cell references or values included in the function without have to recreate the entire function.
To edit a function:
1. Select the cell containing the function.
2. Click on the Insert Function button in the Function Library group under the Formulas tab.
3. This will launch the Function Arguments dialogue box (see screenshot below) containing the details of cell references and values used in arguments.
4. Edit any of the arguments and references by clicking in the relevant argument box and making adjustments.
5. Press OK to accept the changes made.
It is also possible to edit a function directly in the cell that contains it. Double click in the cell and use the formula bar to edit the function or edit the function directly within the cell. Excel displays a formula prompt that shows the arguments required for the function to work. This is to guide you on how to complete the function. If you click on the different arguments within this prompt, the section to which it apples within the function, will be highlighted (see screenshot below):
* Commonly Used Functions: Sum and Count
In order for you to get a taste of how Functions work in practice, we have selected two of the most commonly used functions to experiment with, namely Sum and Count. The SUM function you may already be very familiar with: it is a function for adding two or more numerical values together. The Count function, also known as Count Numbers, will count how many numbers there are in a selected range.
To use the SUM function:
1. Open a blank worksheet in Excel.
2. Enter the numbers: 20, 34, 56, 87 in cells A1:A4.
3. Position your cursor in cell A5. This is the cell we will insert the SUM function into.
4. Click on the Formulas tab on the Ribbon and in the Function Library group, click on the Insert Function button.
5. In the Search for a function box delete the text that is in there and type: Sum (or another description like: add numbers together) and press Enter on your keyboard or Go in the dialogue box to execute the search.
6. Select SUM from the Select a function box and press OK. This will launch the Function Arguments dialogue box.
7. Excel anticipates the values for arguments and will have inserted A1:A4 in the argument box labelled Number1. You can delete this and experiment with inserting the arguments for the numbers you wish to SUM yourself. Your experiment may look something like this:
8. Press OK to complete the SUM function and insert it into the selected cell (A5).
To use the Count function:
1. Open a blank worksheet in Excel.
2. In cells A1 to A4 insert the same numbers as used in the example above (20, 34, 56, 87).
3. Position your cursor in cell A5. This is the cell in which we will insert the Count function.
4. Click on the Formulas tab on the Ribbon and in the Function Library click on the Insert Function button.
5. In the Insert Function dialogue box, delete the content of the Search for a Function box and instead type: count numbers, into the box.
6. Press Enter on your keyboard or the Go button in the dialogue box to search for functions.
7. In the Select a Function box, click on COUNT to select it and press OK. This will launch the Function Arguments dialogue box.
8. Although Excel will have automatically completed the first argument for you, delete the contents of the argument box labelled Value1 in order to practice completing arguments.
9. With your cursor still positioned in the Value1 box, click back in the worksheet and select cells A1:A4 by dragging through them with your mouse cursor. You can also select other non-adjacent cells to include by holding down the control key on your keyboard while selecting cells.
10. Once you are happy with the contents of your argument(s), press OK to insert the function into the selected cell (A5).
TIP: The COUNT function will not count words or names. If you wish to count a list of items, names or other non-numerical cell contents, use the COUNTA function.