Formulas are the essence of using Excel. From simple arithmetic operations to manipulating arrays, you can use formulas to get almost everything done. You can use formulas in Excel to perform calculations, set a condition for formatting cells, and even create links between worksheets.
With all the uses it offers, I think it’s a prerequisite for all Excel users to learn how to create and use formulas. If you’re just starting out with Excel, don’t sweat it. Formulas are pretty simple to figure out.
In this article, we will be covering everything you need to know when creating and using formulas, including syntaxes, functions, arguments, and so much more!
How to Create Or Use Formulas in Excel?
Let’s get to the basics. When you enter a formula, you command Excel to perform a certain calculation. Instead of manually calculating values, you can create a function using a logic and let Excel perform the task for you.
Formulas are either created using operators, with or without the use of functions. Operators include arithmetic, comparison, text, and reference operators.
| Operator | Type | Definition | Example with Result | |
| + | Arithmetic | Add | =1+1 | =2 |
| – | Arithmetic | Subtract | =2-3 | =-1 |
| / | Arithmetic | Divide | =6/2 | =3 |
| % | Arithmetic | Percentage | =20% | – |
| ^ | Arithmetic | Assign an Exponent | =4^2 | =16 |
| = | Comparison | Equal to | =1=1 | TRUE |
| > | Comparison | Greater than | =1>2 | FALSE |
| < | Comparison | Less than | =1<2 | TRUE |
| >= | Comparison | Greater than or equal to | 2>=3 | FALSE |
| <= | Comparison | Less than or equal to | 2<=3 | TRUE |
| <> | Comparison | Not equal to | 3<>3 | FALSE |
| & | Text | Ampersand | =“Lion” & “King” | LionKing |
| : | Reference | Specify Range | =A1:A4 | =A1,A2,A3,A4 |
| , | Reference | Comma. Combine References in a function | =SUM(1,2) | =3 |
| (space) | Reference | Intersection Operator | =SUM(A2:A3 A2:B3) | =A2 |
| # | Reference | Error/ Spilled Range | • #NAME? •### •=SUM(J3#) | – |
| @ | Reference | Show a Reference | =SUM(Table2[@[ValueA]:[ValueZ]]) | – |
I would love to list the functions out just like I listed the operators, but that would take a whole new article! There are more than 300 functions in Excel that are categorized under Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, Statistical, Engineering, Cube, Information, Compatibility, and Web.
Creating Formulas Using Only Operators
Take, for example, you want to calculate the contents in cells A2, A3, and A4. I don’t know about you but I’m terrible are mental maths, so this would take me some time to count. However, I could use a simple formula to let Excel do it for me. In the formula bar, I entered the following formula:
=A2+A3+A4
Here, a task that would take me at least five minutes, is all done in about five seconds! Let’s take a closer look at this formula. See how I’ve started the formula with an equal to sign. When you start a value with an equal to (=) or a hyphen (-), Excel registers it as a formula.
Next, I entered A2, A3, and A4 in my formula. These are cell references. Excel uses these references to fetch data that are entered into the cells. In my sheet, I have 123, 456, and 789 in cells A2, A3, and A4 respectively. The formula can access these values through the references I passed.
The plus sign (+) that I used between the references is an arithmetic operator. This operator commands Excel to add up the values that are next to the operator.
When you hit Enter, voila! There’s your value: 1368.
Creating Formulas Using Functions and Operators
Operators are great however, they are quite limited. Say, you wanted to count the number of cells that hold a specific value. You could use the equals to (=) operator to check if the criteria are met but, how would you make your formula count the cell with the value? You would need to use a function; in this case, the COUNTIF function.
As we have already mentioned, there are a lot of functions. As a new Excel user, I always found it a hassle looking for the exact function to perform a calculation in my worksheet. That was until I came across the Insert Function tool. This tool is a lifesaver for beginner-level Excel users that are just starting to use functions.
The Insert Function allows you to look for a function by either describing it or looking it up by category. Not only that, but it also helps you while building the formula using arguments.
What I found most intriguing is that if the formula results in an error, the Insert Function tool will indicate the error next to the argument causing this error. This way, you won’t have to scratch your head around the formula for hours, trying to understand what argument is causing the error in Excel.
Let me demonstrate the use of the Insert Function tool with an example.
Take a look at this spreadsheet. We will be counting cells that hold the value “Pending” in column F.
- On your keyboard, use Shift + F3. If you don’t like using shortcuts, you can head to the Formulas ribbon and select Insert Function.
- In the Insert Function window, either describe or search by category:
- Description: As our description, we entered “count cell”
- Search by category: Select Statistical.
- Under Select a function, choose COUNTIF > OK.
- On the next window, specify the arguments. For COUNTIF, we need to specify the range and enter the condition.
- If you’re having issues with the function, go ahead and click on Help on this function for assistance.
- Click OK.
The best part about using the Insert Function tool is that you leave no room for syntax errors. This is because Excel applies the syntaxes for you. I highly recommend using this tool while creating formulas with functions in Excel if you’re new to Excel.