We are not all mathematicians, but some tasks in Microsoft Excel are best done with formulas. Maybe you’re new to writing formulas or just trying to get confusing errors. Here we cover the basics of structuring formulas in Excel.
Parts of a formula
While the exact elements may vary, a formula can use the following parts.
equal sign: All formulas in Excel, including Google Sheets, start with an equal sign (=). Once you type it into a cell, you may immediately see suggestions for functions or formulas.
cell reference: Although you can type values directly into formulas (as a constant), it is possible and usually more convenient to get values from other cells. An example of a cell reference is A1, the value in column A, row 1. References can be relative, absolute, or mixed.
- Relative reference:: This refers to the relative position of the cell. If you use the reference A1 in your formula and change the position of the reference (for example, if you copy and paste the data elsewhere), the formula is automatically updated.
- Absolute reference: This refers to a specific position of the cell. If you use the reference $A$1 in your formula and change the position of the reference, the formula will not update automatically.
- Mixed Reference: This refers to a relative column and an absolute row or vice versa. For example, if you use A$1 or $A1 in your formula and change the position of the reference, the formula will automatically update only for the relative column or row.
Constant: You can see a constant as an inserted value. This is a value that you enter directly into the formula in place of or next to a cell reference. For example, instead of using A1 in the formula, you can use the value 15.
Operator: This is a special character that performs a task. For example, the ampersand is the text concatenation operator for combining text strings. Here are a few more:
- Arithmetic Operators: These include an asterisk for multiplication and a plus sign for addition.
- Comparison Operators: These include a greater than, less than, and equal sign.
- Reference operators: These include a colon to indicate a range of cells as in A1:A5 and a comma to combine multiple ranges of cells as in A1:A5,B1:5.
parentheses: Just like in an algebra equation, you can use parentheses to specify the part of the formula to execute first. For example, if the formula is =2+2*3, the answer is 8 because Excel does the multiplication part first. But if you =(2+2)*3the answer is 12 because the part in parentheses is performed before the multiplication.
In addition, functions start with an open parenthesis, followed by the arguments (references, values, text, arrays, etc.) and end with the closing parenthesis. Even if nothing in parentheses appears as in =TODAY() that gives you the current date, you still need to include the parentheses.
Function: A common but not mandatory part of a formula is a function. As in our example above, the TODAY function returns today’s date. Excel supports many, many features for working with numbers, text, searches, information, and much more.
Formula Examples
Now that you know the basic parts of a formula, let’s take a look at the syntax for some examples.
Here is a formula to sum the values in two cells. You have the equal sign, the first cell reference (relative reference), the plus sign (operator), and the second reference (relative reference).
=A1+B1
This formula adds different values instead. You have the equal sign, the first value (constant), the plus sign (operator), and the second value (constant).
=15+20
For a function example, you can sum the values in a range of cells. Start with the equal sign, enter the function followed by an opening parenthesis, insert the first cell in the range, a colon (reference operator), the last cell in the range, and end with the closing parenthesis.
=SUM(A1:A5)
Another symbol you may see in a formula is a quotation mark. This is often used when creating formulas for working with text, although quotes are not exclusive to text. Here’s an example.
RELATED: 9 Useful Microsoft Excel Functions for Working with Text
You can use the SUBSTITUTE function in Excel to replace certain text with new text. With this formula, you can replace Smith with Jones in cell A1:
=SUBSTITUTE(A1,"Jones","Smith")
As you can see, both the current (Jones) and new (Smith) text are in quotes.
Get help from Excel
It can take some time and practice to master writing formulas. Fortunately, Excel does offer some help when you use functions in your formulas.
RELATED: How to find the function you need in Microsoft Excel
Start your formula
If you plan to use a function, you can quickly start the formula.
Select the cell where you want the formula, type the equal sign, and enter the first or two letters of the function you want to use. You will see a drop-down list of features that apply.
Double click on the desired function and you will see the syntax for the formula you need to create.
You can then click an argument in the formula and enter or select what you want to use. Follow the formula you see by entering commas or other expected operators until you complete the formula.
View the feature library
Even if you know the function you want, you can check the formula syntax in advance. This will help you prepare the data if it is not ready yet.
Go to the Formulas tab and click “Insert Function” on the left side of the ribbon.
Enter the function in the search box at the top, press “Go” and then select it from the results.
You will then see the expected syntax for the function at the bottom of the window. You will also receive a description of the function for additional help. Below you can see what you need for the COUNT function.
Hopefully these explanations and tips will help you create the formulas you need in Microsoft Excel!
RELATED: 12 Basic Excel Functions Everyone Should Know