Excel Formula Basics
Writing a spreadsheet formula is different from writing an equation in math class. The most notable difference is that Excel formulas start with the equal sign (=) instead of ending with it. Excel formulas look like =3+2 instead of 3 + 2 =. The equal sign indicates that what follows is part of a formula and not just a word or number that you want to appear in the cell. After you type the formula and press Enter on your keyboard, the result of the formula appears in the cell. For example, if you type the formula above, =3+2 into a cell and press Enter, the result, 5, appears in the cell. The formula is still there, but it doesn’t appear in your spreadsheet. If you select the cell, though, the formula appears in the formula bar at the top of the Excel screen.
Improve Formulas with Cell References
Excel formulas can also be developed using cell references. Continuing with our example, you would not enter the numbers 3 and 2, but instead would name cells where these numbers have been entered (see Using Cell References below for more on cell naming). When you write a formula this way, the formula cell always shows the sum of the numbers in those cells, even if the numbers change. Here’s a real-life example of how this approach can be useful. Say you lead a team of salespeople and are tracking their monthly and quarterly sales. You want to calculate their total sales for the year. Instead of entering every quarterly sales value into a formula, you use cell references to identify the cells where those values can be found within the spreadsheet.
Using Cell References
Each cell in Excel is part of a row and a column. Rows are designated with numbers (1, 2, 3, etc.) shown along the left side of the spreadsheet, while columns are designated with letters (A, B, C, etc.) shown along the top. To refer to a cell, use the column letter and row number together, such as A1 or W22 (the column letter always comes first). If you have a cell selected, you can see its reference at the top of the screen in the Name Box next to the formula bar. In the image above, notice the cell references in the formula bar: E2, I2, M2, and Q2. They refer to the quarterly sales numbers for the salesperson named Jean. The formula adds those numbers together to come up with the annual sales number. If you update the numbers in one or more of those cells, Excel will recalculate and the result will still be the sum of the numbers in the referred cells.
Create a Formula With Cell References
Try creating a simple formula using cell references.
Enter Cell References With Pointing
Pointing is yet another way to refer to the values you want to include in your formula; it involves using your pointer to select cells to include in your formula. This method is the fastest of those we’ve discussed; it’s also the most accurate because you eliminate the risk of making a mistake in typing in numbers or cell references. Here’s how to do it (starting with the spreadsheet from the examples above):
Mathematical Operators and Order of Operations
Now we turn to operations besides addition, including subtraction, division, multiplication, and exponentiation. The mathematical operators used in Excel formulas are similar to those you may remember from math class:
Subtraction – minus sign ( - )Addition – plus sign ( + )Division – forward-slash ( / )Multiplication – asterisk ( * )Exponentiation – caret ( ^ )
If more than one operator is used in a formula, Excel follows a specific order to perform the mathematical operations. An easy way to remember the order of operations is to use the acronym BEDMAS.
BracketsExponentsDivisionMultiplicationAdditionSubtraction
Here’s a simple example of the order of operations in use. In the formula =2*(3+2) the first operation Excel completes is the one inside the brackets (3+2), with the result of 5. It then performs the multiplication operation, 2*5, with the result of 10. (The values in the formula could be represented by cell references rather than numbers, but Excel would perform the operations in the same order.) Try entering the formula into Excel to see it work.
Enter a Complex Formula
Now let’s create a more complex formula.
How Excel Calculated the Result
In the above example, Excel arrived at the result of -4 using the BEDMAS rules as follows:
7 in cell C15 in cell C29 in cell C36 in cell C43 in cell C5
Brackets. Excel first carried out the operation within the brackets, C2-C4 or 5-6 for a result of -1.Exponents. There are no exponents in this formula, so Excel skipped this step.Division and Multiplication. There are two of these operations in the formula and Excel performed them from left to right. First, it multiplied -1 by 7 (the content of cell C1) to get a result of -7. It then performed the division operation, C3/C5 or 9/3, for a result of 3.Addition and Subtraction. The last operation Excel performed was the addition of -7+3 for the final result of -4.
