Formulas are calculations that Excel can perform automatically. Let's experiment with some formulas to add, subtract, multiply, and divide numbers together. Then, we'll look at some formulas that reference other cells.
Download the .XSLX file used in this video to follow along live.
Excel isn’t just for manually entering numbers, strings, and dates into cells. Its true power is derived from the use of a set of tools you may already have heard of. They’re called formulas and functions.
A formula is a set of calculations performed within a cell. For example, we could write a formula to add the numbers 2 and 3 together, rather than adding them together manually using a calculator or a pen and paper.
How would we do that? Note that if we just enter the phrase “2+3” into cell B2, Excel outputs a string of text that contains the value we entered into the cell — literally, 2+3. That’s not particularly useful — we don’t want a string representing the phrase we typed into the cell. We want a numerical answer!
So, what can we do? The key here is to use the equals sign. Whenever Excel sees the equals sign at the beginning of a cell input, it will realize that cell contains a formula rather than a string, and will evaluate that formula rather than spitting out text. Check it out — if we write ‘equals’ 2+3 into the input box and press Enter, Excel will return the value 5. It knows that we want to perform a mathematical calculation rather than input a phrase.
Now you know why we need to put an equals sign at the beginning of the formula bar whenever we’re writing a cell reference. If we don’t, Excel will recognize our input as a string of text rather than a formula! Note that you only need to include the equals sign once at the beginning of the formula bar. After doing so, you can use cell references and mathematical operators to your heart’s content. Here, we combine two cell references with a mathematical operator to add the contents of cells B2 — 5 — and B3 — 7 — to get a new value: 12, in cell B4.
Excel’s formulas understand all of the basic mathematical operators we would expect them to, including addition, subtraction, multiplication — defined using the asterisk symbol on the keyboard, division — defined using the forward slash symbol on the keyboard, and exponents — defined using the carat symbol on the keyboard. We can also include parentheses within formulas, just like we would in any equation. And when Excel evaluates formulas, it uses the standard mathematical order of operations to decide how to do so: parentheses first; then exponents; multiplication; division; addition; and subtraction.
Let’s try writing a more complex formula in cell B5: two to the first power; plus two divided by two; minus one. What output can we expect from this formula? First, Excel will evaluate the exponents: two to the first power is two. Then, it will evaluate the division: two divided by two equals one. Next, it will evaluate addition and subtraction, adding two and one together to get three, and subtracting one, resulting in a final output value of two. Check it out — that’s exactly what our formula returns!
Now, let’s try adding some parentheses into the equation. Now, Excel will evaluate the interior of the parenthesis first: one plus two equals three. Next, it evaluates the exponents: two to the third power is eight. Next, division: eight divided by two is four. And finally, subtraction: four minus one is three. Take a look — that’s also what our formula has output.
Those are the basics of formula in Excel. Next, it’s time to learn about functions — mathematical recipes used to make our formulas even more useful.