Excel formulas and functions (2023)

What is a formula?

A formula in Excel is an expression that returns a specific result. For example:

Note: All formulas in Excel must begin with an equal sign (=).

cell references

In the examples above, the values ​​are "encoded". That means the results won't change unless you edit the formula again and change a value manually. This is generally considered bad form because it hides information and makes a table difficult to maintain. Instead, use cell references so that values ​​can be changed at any time. On the next screen, C1 contains the following formula:

Please note that we are using cell references for A1, A2 and A3, these values ​​can be changed at any time and C1 will still show an accurate result.

All formulas return a result.

All formulas in Excel return a result even if the result is an error. A formula is used below to calculate the percent change. The formula returns a correct result in D2 and D3, but returns a #DIV/0! Error in D4 because B4 is empty:

There are different ways to deal with errors. In this case, you could supply the missing value in B4, or "catch" the error with the IFERROR function and display a friendlier message (or nothing at all).

Copy and paste formulas

The beauty of cell references is that they update automatically when a formula is copied to a new location. This means you don't have to keep entering the same basic formula over and over again. In the following screen, the formula in E1 was copied to the clipboard using Ctrl + C:

Below: Formula pasted into cell E2 using Ctrl + V. Notice that the cell references have changed:

The same formula stayed with E3. The cell addresses are updated again:

Relative and absolute references

Previous cell references are called relative references. This means that the reference is relative to the cell it lives in. The formula in E1 above is: Literally, this means "cell left 3 columns" + "cell left 2 columns" + "cell left 1 column". So if the formula is copied to cell E2, it will continue to work the same way. Relative references are extremely useful, but sometimes you don't want a cell reference to change. A cell reference that does not change when copied is called an absolute reference. To make an absolute reference, use the dollar sign ($): For example, in the following screen, we want to multiply by 10 each value in column D that is entered in A1. By using an absolute reference for A1, we "lock" that reference to change when the formula is copied to E2 and E3:

These are the final formulas in E1, E2, and E3: Note that when the formula is copied, the reference to D1 is updated, but the reference to A1 never changes. Now we can easily change the value in A1 and all three formulas will be recalculated. Then the value in A1 has changed from 10 to 12:

This simple example also shows why it makes no sense to hardcode values ​​into a formula. By storing the value in A1 in one place and referencing A1 with an absolute reference, the value can be changed at any time and all associated formulas are updated immediately. Tip: You can use the F4 key to switch between relative and absolute syntax.

How to enter a formula

To enter a formula: Instead of typing cell references, you can point and click on them as shown below. References are colour-coded:

All formulas in Excel must begin with an equal sign (=). No equal sign, no formula:

How to change a formula

To edit a formula you have 3 options: whichever option you use, press Enter to confirm your changes when you're done. If you want to cancel and leave the formula as it is, click the escape key.Video: 20 tips for entering formulas

What is a function?

When working in Excel, the words "formula" and "function" are used frequently, and sometimes interchangeably. They're closely related, but they're not exactly the same. Technically, a formula is any expression that begins with an equal sign (=). A function, on the other hand, is a formula with a specific name and purpose. In most cases, functions have names that reflect their intended use. For example, you're probably already familiar with the SUM function, which returns the sum of the given references: The AVERAGE function, as expected, returns the average of the given references: And the MIN and MAX functions return the minimum and maximum values, respectively. :Excel includes hundreds of specific functions. To get started, check out 101 Excel Key Features.

function arguments

Most functions require input to return a result. These entries are called "arguments". A function's arguments appear in parentheses after the function name, separated by commas. All functions require matching opening and closing brackets (). The pattern looks like this: For example, the COUNTIF function counts the cells that meet the criteria and takes two arguments, range and criteria: In the screen below, the range is A1:A5 and the criteria is "red" . The formula in C1 is: Video: Using the COUNTIF function Not all arguments are required. Arguments in square brackets are optional. For example, the YEARFRAC function returns the fractional number of years between a start date and an end date and accepts 3 arguments: start date and end date are required arguments, base is an optional argument. Below is an example of using YEARFRAC to calculate current age based on date of birth.

How to enter the function

If you know the name of the function, just start typing. Here are the steps:

1. Type the equal sign (=) and start typing. Excel creates a list of matching functions as you type:

When you see the feature you want in the list, use the arrow keys to select it (or just keep typing).2. Type Tab to accept a function. Excel completes the function:

3. Enter the required arguments:

4. Press Enter to confirm the formula:

   (Video) Top 10 Most Important Excel Formulas - Made Easy!

Combining Functions (Nesting)

Many Excel formulas use more than one function, and functions can be "nested" within one another. For example, below we have a birth date in B1 and we want to calculate the current age in B2:

The YEARFRAC function calculates years with a start date and an end date:

We can use B1 for the start date and then use the TODAY function to specify the end date:

If we press enter to confirm, we get the current age based on today's date:

Notice that we use the TODAY function to send an end date to the YEARFRAC function. In other words, the TODAY function can be nested inside the YEARFRAC function to provide the end date argument. We can take the formula one step further and use the INT function to split the decimal value:

Here the original YEARFRAC formula returns 20.4 to the INT function, and the INT function returns a final result of 20. Notes: Tip: The result of any formula or function can be entered directly into another formula or function.

Mathematical Operators

The following table shows the standard mathematical operators available in Excel:

logical operators

Logical operators support comparisons such as greater than, less than, etc. The logical operators available in Excel are listed in the table below: Video: How to create logical formulas

order of operations

When solving a formula, Excel follows a sequence called the "order of operations." First, each expression in parentheses is evaluated. Next, Excel solves for any exponent. After the exponents, Excel performs multiplication and division, then addition and subtraction. If the formula involves concatenation, this is done using standard mathematical operations. Finally, Excel evaluates the logical operators, if any. Tip: You can use the Evaluate function to see how Excel solves formulas step by step.

Convert formulas to values

Sometimes you want to get rid of formulas and just leave values ​​in their place. The easiest way to do this in Excel is to copy the formula and then paste it using Paste Special > Values. This overwrites the formulas with the values ​​they return. You can use a keyboard shortcut to paste values ​​or use the Paste menu on the Home tab of the ribbon. Video: Insert special links

What's next?

Below are guides to help you learn more about Excel formulas and functions. We also offer online video training.

29 tips for working with formulas and functions (video version here) 500 formula examples with full explanations 101 essential Excel functions Guide to all Excel functions (in progress) Excel formula errors (examples and corrections) Criteria for formula: 50 example formulas for condition formatting Using F9 to Debug a Formula (Video) Excel Formula Errors and Fixes (Video)

David Bruns

Hi, I'm Dave Bruns operating an Exceljet with my wife Lisa. Our goal is to help you work faster in Excel. We create short videos and illustrative examples on formulas, functions, pivot tables, conditional formatting and charts.