Blog

What are Functions?

Functions are pre-defined ways of performing certain tasks or calculations in a consistent and repeatable way. Each Function is created to do a specific task by taking specified inputs in order to output a processed result. Functions are a great way to help automate your spreadsheet’s calculations and can save you a lot of time and effort, as the underlying code for the task has already developed for you.

Types of Functions

Functions are either built-in to the application, or can be user defined – meaning created by the user (or third party) typically via a a VBA macro or add-in. User defined functions (UDFs) can be created to add functionality not already available in the standard Excel function set. For simplicity we will be referring to built-in functions here.

In total there are about 500 hundred built-in Excel functions. New versions of Excel may add additional functions for:

Do I need to know all the functions to work with Excel?

For most users, knowledge of a just a small selection of the available functions is sufficient for most common tasks. The majority of competent users will probably only ever use 10%-20% of the available Excel functions, as many of them are specific to particular use cases, such as engineering or finance calculations.

Here are 10 of the most used Excel functions.

Better still, knowing that a function exists for a particular task without remembering the specifics, is enough for a confident user to be able to search for, and find, the solution from the function search tool.

Examples of recently added built-in functions include:

• how Excel connects to data on the internet,
• conditional functions (MAXIF, SUMIF etc)
• functions for non Latin character sets,
• error validation, and
• arrays functions.

Different Ways to Insert a Function in Excel

Functions consist of a name, typically descriptive of the functionality, and opening and closing parentheses, or round brackets.

There are several ways to access functions:

• If you know the function name, you can directly type the function into a cell, after typing “=”


• Type directly in the Formula Bar, having pre-selected the output cell, after typing “=”, this is the same as typing directly into the cell.


• Open the Insert Formula Manager via Shortcut (Shift+F3),
• Open the Insert Formula Manager by Clicking on the ‘fx’ shortcut in the Formula Bar



• In the Ribbon at the top of the screen, select the Formulas option,


• click the Insert Function ‘fx‘ picture, to open the Insert Formula Manager


• Select a function from a specific category in the Ribbon>Formulas>Function Library

Depending on the Function access method used, you will either be able to type the function name and arguments directly, or use the Function Insert Function pop-up window to choose the function by name or lookup the function by describing the function required in the ‘search for a function’ text box to return possible suggestions, before complete the function selection, and input of arguments.

How to use a function

Functions in Excel are called by referring to their name, followed by a pair of round brackets.  The following NOW function example returns the current date and time to the cell when executed.

Function Arguments – input values

Functions may not require any additional information – or arguments to work correctly, as in the NOW() example below, which returns the current data and time.

=NOW()

Some functions require that you provide addition input arguments, which are added inside the function brackets. The Absolute function, ABS takes a single numeric value and returns the absolute value (i.e. without a +/- sign).

=ABS(-9)

Sometimes, multiple arguments might be required (or allowed, if optional). Where more than one argument is given, commas are used to separate them. The SUM function can take multiple inputs.

Summing 2 values together, using 2 input arguments separated by commas:

=SUM(1,2)

or instead with 3 numbers:

=SUM(1,2,3)

You can also refer to the contents of individual cells in functions. Cells are named by the combination their column letter(s) and row number.

The following sums the values in cell A1 and cell B2.

=SUM(A1,B2)

Or you can refer to a range of cells as a single argument (typically a rectangular range bounded by the top left corner cell and bottom right corner cell linked with a semi colon ‘:’).

To sum the contents of 4 contiguous cells: A1, A2, B1 & B2, a cell range can used.

=SUM(A1:B2)

TIP: Later versions of Excel provide handy format argument coloring. Each Argument will be a different color, with the corresponding cell or range shaded with the equivalent color for ease of identification.

Click anywhere in the formula bar’s function text, to activate the color highlighting.

Nesting Functions

If you need to you can also nest functions, one within another.

In this case we sum the values in the range A1 to B2, as before. Then the absolute function is applied to the result, which will remove any negative sign from the final sum. The SUM function was processed first adding together any negative numbers and positive numbers.

=ABS(SUM(A1:B2))

This is not the same if the function nesting is reversing. Nested functions are processed by Excel from the innermost function outward, rather than from left to right.

In this case, each value have any negative signs removed by the ABS function, before all resultant positive values get summed.

=SUM(ABS(A1:B2))

NOTE:  With functions, numbers are typed in as their digits, but text (or strings) must be wrapped in double quotes / quotation marks, (or speech marks), to avoid being mistaken by Excel for variables or cell references.

eg. =LEN(“Hello”)

Related Topics

• Other Excel Functions
• SUM function
• ABS function

021