People use Excel for a seemingly infinite number of applications, but it is primarily used for storing data and performing calculations but to get the most out of Excel, you need to be familiar with formulas and functions.
This article provides a detailed overview of formulas and functions in Excel.
Formulas
A formula is a mathematical expression that calculates a value. One of the first formulas a lot of us learn to use is something like this:
1 + 1 = 2
To calculate this formula in Excel, we would need to make a small change. Calculations in spreadsheets always start with an equals sign. The screenshot below shows what happened when I clicked in cell E1, typed =1+1 then pressed Enter. The formula is displayed above cell E1 in Excel’s formula bar.
The Formula Bar
The Formula Bar is an area in Excel where you enter and view calculations. It’s not a place where babies go to drink… (Total dad joke, right?)
Mathematical Operators
You can use all of the common mathematical operators in Excel. Check out the following table for a summary.
| Operator | Symbol |
|---|---|
| Addition | + |
| Subtraction | – |
| Multiplication | * |
| Division | / |
| Exponent | ^ |
BEDMAS and PEDMAS
Do you remember BEDMAS? Some people call it BODMAS or PEDMAS. It depends on where you live and where you went to elementary school.
BEDMAS or PEDMAS are acronyms designed to help you remember the order of operations. All calculations are performed in specific order.
BEDMAS or PEDMAS stands for:
- Brackets or Parentheses
- Exponents
- Division
- Multiplication
- Addition
- Subtraction
The items in this bulleted list are meant to remind you that any part of a formula enclosed in brackets will be evaluated first. After that, exponents will be calculated, followed by division and multiplication, then addition and subtraction.
Let’s look at a couple of examples, keeping BEDMAS in mind.
Example #1
= 3 + 3 * 5
This calculation would work out to 18:
- 3 * 5 = 15
- 15 + 3 = 18
Remember: division and multiplication are always evaluated before addition and subtraction!
Example #2
= (3 + 3) * 5
This calculation would result in 30.
- 3 + 3 = 6
- 6 * 5 = 30
Calculations contained in brackets or parentheses are evaluated first, followed by addition and subtraction.
How to Enter Formulas
Now that we have discussed formulas, mathematical operators, and order of operations, let’s talk about entering formulas in Excel.
There are two options. You can type a formula directly in a cell or you can type an equals sign then click cells referenced in the formula. One or two examples will help illustrate these approaches.
Typing Formulas
In the screenshot below I clicked in cell B6 and typed =B3+B4+B5. When I pressed Enter, the result was calculated. January’s total is 180.
Clicking on Relevant Cells
This time, I’m going to click relevant cells as I enter the formula:
- I’ll click in cell C6 and type =.
- Next, I’ll select cell C3 and type +.
- Then, I’ll click cell C4 and type +.
- Finally, I’ll click cell C5 and press Enter.
The result is 200.
Note that in both cases, we created very similar formulas. The method you choose for entering formulas comes down to personal preference.
Functions
A function is a predefined formula that calculates a result. Like all Excel formulas, a function begins with an equals sign followed by the function name and one ore more arguments enclosed in parentheses. All functions use the following general syntax:
=FUNCTION(arg1, [arg2], …)
FUNCTION is the name of the predefined formula being used.
arg1 is the first number, text, cell address, or range of cells being referenced.
arg2 [optional] is the second number, text, cell address, or range of cells.
The SUM Function
Let’s look at the SUM function, as an example. In the screenshot below, I clicked in cell D6 and typed =sum(D3:D5) then pressed Enter. The result, when we add the figures in column D, is 190.
AutoSum
Adding columns of numbers is such a common task in Excel that Microsoft has created a button specifically for the purpose. The AutoSum button is located on Excel’s ribbon in the Home > Editing group.
If I wanted to add up the income numbers in March using AutoSum, I would have selected cell D6, clicked the AutoSum button and pressed Enter.
How to Enter Functions
Like formulas, there are several different ways you can enter functions. You can type a function into a cell or you can use a combination of typing and clicking on cells, or ranges of cells, reference by the function.
Typing Functions
In the screenshot below, I clicked in cell B8 and typed =sum(B6:D6). When I pressed Enter the result was calculated. Total sales for January through March was 570.
Clicking on Relevant Cells
This time, I’m going to click relevant cells as I type the function:
- I’ll click in cell B8 and type =SUM(.
- Next, I’ll drag to select the range B6:D6.
- Finally, I’ll type ) and press Enter.
The result, once again, will be 570.
Note that in both cases, we created a function to add up values in the range B6:D6. The method you choose is based entirely on personal preference.
What’s Next?
Learning about formulas and functions will help you become more productive in Excel.
If you’ve enjoyed reading this article, you should check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. Both functions are useful for anyone who needs to combine data from multiple sources.
See you next time!
