Excel Formulas

Formulas are the foundation of working in Excel. You can use multiple built-in functions at once to perform calculations, create different scenarios, and solve problems.

An Excel formula can use any or all of the following:

Functions
Cell references
Constants
Operators

It’s important to understand how to use functions, how to reference different cells or ranges, perform mathematical calculations using pre-defined operators, and use constants where appropriate. So let’s talk a bit about each one.

Functions

A function is a predefined formula that performs calculations in a particular order using specific values. Excel functions are split into 14 categories: Compatibility, Cube, Database, Date and time, Engineering, Financial, Information, Logical, Lookup and reference, Math and trigonometry, Statistical, Text, Used defined (that are installed with add-ins), and Web.

Each function has its own syntax which needs to be used or else the function may return a wrong result or trigger an Excel error. You will also need to understand the type of arguments used by each function and how to reference cells correctly.

Each formula starts with the equal (=) sign followed by the function and arguments or cell reference.

In the above image, you can see an example of the SUM function which takes as argument the range A1:A10. The built-in function sums up all the values from the range and returns the total.

Arguments can refer to individual cells and cell ranges and need to be enclosed within parentheses. The above example includes a single range, but you can also reference individual cells like this: =SUM(A1, A2, A3, A4)

If you want to become proficient in Excel you’ll need to invest some time and learn the syntax of all the most used functions. This will also save you a lot of time because you won’t need to access the help articles each time you want to write a formula.

Cell references

Cell or range references are used to identify parts of your worksheet that you want Excel to use in your formulas. You can use cell references from different worksheets or even from different files.

The simplest reference used in Excel is the column-row used for cells. For example, when you use A1 in a formula, Excel knows to retrieve the value from Column A and Row 1.

An Excel sheet can use a maximum of 16,384 columns (from A to XFD) and 1,048,576 rows. Excel uses this matrix to retrieve the cells that match your reference. For example, cell D5 refers to the cell at the intersection of column D and row 5.

You can also reference ranges of cells like A1:A10. This tells Excel to use all the cells from A1 all the way down to A10.

There are three types of references in Excel: relative, absolute, and mixed. When I first started learning Excel the easiest method I found to remember this was to think of it like this:

when you use relative reference both the column and the row of your cell are changed when you copy the formula to another cell.
when you use absolute reference the column and the row of your cell are never changed when you copy the formula; this is great to use with constants since those values usually need to remain the same.
when you use mixed reference only the column or the row is changed when you copy the formula to another cell.

Constants

A constant is a value that is not calculated and doesn’t change. It always remains the same. Some examples of constants are date (e.g. 4/2/2022), numbers (e.g. 180), and text strings (e.g. “Sales”). However, a number or text string that results from a formula is not a constant, because it can change once the input is adjusted.

As a best practice, try to keep each constant in its own individual cell and group them in the same section of your worksheet. This way, you can easily make changes to your constants and it’s easier for others to understand your formulas.

Operators

Operators are the action you want to perform on the elements in your formulas, such as addition, subtraction, multiplication, or division. There are two main types of operators in Excel: arithmetic operators and logical operators (also known as comparison operators).

Arithmetic operators

These are used to perform basic mathematical operations such as addition, subtraction, or multiplication and produce numeric results. The following table shows the available arithmetic operators in Excel.

Arithmetic operators	Definition	Example
+	addition	7+3
–	subtraction (or negation)	11-4 (or -2)
*	multiplication	9*2
/	division	8/4
%	percent	10%
^	exponentiation	3^3

Logical operators

Logical operators compare two values and return either TRUE or FALSE. These are very useful when building formulas because it allows you to perform different calculations when certain conditions are met.

Logical operators	Definition	Example
=	equal to	A1=B1
<>	not equal to	A1<>B1
>	greater than	A1>B1
>=	greater than or equal to	A1>=B1
<	lower than	A1<B1
<=	lower than or equal to	A1<=B1

I’ve put together a list of formulas from real-world scenarios. Each article has its own workbook that you can download and practice. Once you go over these tutorials, I’m confident that you will be much better prepared to work with functions like COUNTIF, SUMIF, VLOOKUP, INDEX-MATCH, and IF.

One final piece of advice. Learn to fix the most common Excel errors like NULL!, #REF!, #DIV/0!, #NAME?, #N/A, #NUM!, #VALUE!.

Excel formula errors are inevitable and the more formulas you write, the more errors you’ll get. This is why I’ve written a very comprehensive guide on how to fix Excel errors.