Functions and formulas are among the most essential elements of Excel. They both perform calculations and produce certain results. Although the two terms are sometimes used interchangeably, it is important to know the difference between them.

Note: This tutorial uses Excel 2013. In other Excel versions, there may be some slight differences in the described steps.

Watch the video below to learn the difference between functions and formulas in Excel, get overview of them, as well as see how to insert a function and enter a formula in Excel. If you prefer written instructions, then continue reading.

Functions

What is a Function?

A function is a built-in formula in Excel which performs a certain calculation. For example, the SUM function adds all the numbers in a range of cells, the IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE, the INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range, etc.

Function Categories in Excel

In Excel 2013, there are 436 functions. You can access the list of the available functions from the Function Library on the Formulas tab where they are grouped into categories such as Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig and More Functions which in turn include Statistical, Engineering, Cube, Information, Compatibility and Web categories. Functions are listed alphabetically within their categories.

In addition, there are two other categories – AutoSum and Recently Used, which include duplicate functions and respectively list the 5 most common mathematical and statistical and the 10 most recently used functions.

The Syntax of a Function  

A function starts with the equal sign (=), followed by the function name, and then one or more arguments enclosed within parentheses and separated by a comma. Arguments are input values that the function uses to perform the calculation. The number and type of arguments depend on the syntax required for a specific function. For example, let us look at the SUM function below in the picture:

Inserting a Function

There are two ways of inserting a function:

Inserting a function with the Insert Function dialog box

1) Select the cell where you want the result to appear.

2) Open the Insert Function dialog box in one of the following ways:

• By clicking the Insert Function button on the Formulas tab

• By clicking the Insert Function button next to the Formula Bar

• By using the keyboard shortcut SHIFT+F3

3) In the Insert Function dialog box, search for a function by typing a brief description of what you want to do in the Search for a function box and then clicking Go or select a function from a category. For example, let us select the SUM function. As you see, after the function is selected, the syntax and a brief description of the function is displayed below the Select a function box. For more information about the function and its arguments, click the link Help on this function at the bottom of the dialog box. Click OK to go to the Function Arguments dialog box.

4) In the Function Arguments dialog box, enter the function arguments and click OK. Arguments can be cell references, numbers, texts, or even formulas that you type directly into the argument boxes. In our example, cells A1:A5 are selected automatically and we just need to click OK for the function to be calculated. P.S. If Excel selects the wrong cell range, you can manually select or type the desired range into the argument box.

The result will appear in the selected cell.

Inserting a function manually

1) Select the cell where you want the result to appear.

2) Type the equal sign (=), the name of the function and an opening parenthesis. Alternatively, select the function you want to use from the list of suggestions which appears below the cell as soon as you start typing the first letter of a function. For example, let us again insert the SUM function.

3) Enter the function arguments by following a function ScreenTip below the cell which displays all the arguments the function takes, separating each argument with a comma. In our example, we will manually select the range A1:A5.

4) After entering all the required arguments, close the parentheses and press Enter on the keyboard.

The result will appear in the selected cell.

The Use of Functions in Excel

In addition to being used on their own, functions are also used inside formulas, as well as in Tables and PivotTables.

Formulas

What is a Formula?

A formula is a user-defined calculation which can contain values, cell references, functions, defined names and calculation operators. So, the difference between a function and a formula is that a function is a built-in calculation, while a formula is a user-defined calculation. In Excel Ribbon, there is a whole tab dedicated to formulas and containing the following groups:

• Function Library – Lists all Excel functions by their category, such as Financial functions or Text functions.
• Defined Names – Displays options for creating and managing names that refer to a cell, a cell range, a function, a formula, a constant, or a table and are used in formulas.
• Formula Auditing – Includes tools for evaluating and checking formulas.
• Calculation – Displays options for setting the calculation mode in Excel.

The Syntax of a Formula

A formula starts with the equal sign (=), followed by values, cell references, functions, defined names and calculation operators. For example, let us look at the formula below in the picture:

Entering a Formula

To enter a formula in Excel, take the following steps:

1) Select the cell where you want the result to appear.

2) Type the equal sign (=), enter all the values, cell references, functions, defined names and calculation operators you want to use in the formula and press Enter on the keyboard.

The result will appear in the selected cell.

The Use of Formulas in Excel

In addition to being used on their own, formulas are also used in Conditional Formatting, Defined Names, Data Validation, etc.

So, this was an overview of functions and formulas in Excel.

Which functions and formulas do you use most often? Write in the comment section below.