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.
- Calculate a mortgage payment in Excel
- Change case in Excel to UPPER, lower or Proper
- Count cells between two dates
- Count cells equal to a specific value
- Count cells not between two numbers
- Count cells not equal to x or y
- Count cells that contain odd numbers
- Find duplicates in Excel using Conditional Formatting
- Get first day of the month
- Identify VAT rate based on country
- IF function with 3 conditions
- Separate First and Last name in Excel
- Subtotal by item type using
- Sum sales by year using
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.