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.
A function can either be the only item in a formula or can be part of a larger formula.
Functions - Complete List
This Complete List of Functions provides details and examples for all 471 functions.
Formulas - Complete List
This Complete List of Formulas provides details and examples.
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.
Worksheet functions can only be used within a formula and the function name is always followed by a set of parentheses.
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.
You must have parentheses around all of your arguments and the opening parentheses must appear immediately after the name of the function.
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.
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.
If you receive the error "Undefined Function" or "Function Not Available" you may have lost one of your library references or ones of your addins.
You can always write your own worksheet functions using VBA when you need a function with more flexibility.
Remember to include a speech mark character in a function you must use double speech marks.
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).
Remember to check your (Tools > Options)(View tab, Zero values). This controls whether empty cells contain a zero or not.
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopNext