Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 93 ยท Formulas Fundamentals

Your First Formulas

Write your first Excel formulas: the equals sign, arithmetic operators, order of operations, parentheses, cell references and what happens when a formula is wrong.

A formula is an instruction you type into a cell that tells Excel to calculate something. The cell then shows the result, and Excel recalculates it automatically whenever the numbers it uses change. This is the feature that turns a grid of boxes into a spreadsheet, so almost everything else in this course builds on it.

Every formula starts with an equals sign. If you type 5+3 into a cell, Excel treats it as text. If you type =5+3, Excel calculates and shows 8. In this lesson you will learn the operators Excel understands, the order in which it applies them, how parentheses change that order, and what to do when a formula goes wrong.

On this site Excel cannot run, so the examples come in two parts. The formulas you see in code are real Excel syntax. The live editors contain a stand-in: a small spreadsheet engine written in JavaScript that evaluates formulas the way Excel does, so you can experiment.

Anatomy of a formula

Equals sign Tells Excel this is a formula.
Values Numbers, text or cell references.
Operators Symbols such as + - * / ^.
Auto update Change an input and the result follows.

Example: =B2*C2

1 / 2

Typing a formula

Click a cell, type =, then the calculation, then press Enter. While you type, the text appears both in the cell and in the formula bar above the grid. After you press Enter, the cell shows the result and the formula bar still shows what you typed. Click the cell again at any time to see its formula, or press F2 to edit it in place.

TEXT
=12+8        โ†’ 20
=12*8        โ†’ 96
=100/8       โ†’ 12.5
=2^10        โ†’ 1024

A formula can also start with + or - (Excel adds the = for you), but the equals sign is the habit to build.

Formulas are most useful when they refer to other cells instead of typing numbers. Suppose B2 holds a price and C2 holds a quantity:

TEXT
=B2*C2

If you later change the price in B2, the result updates by itself. That is the whole point: you type the data once and the formulas do the arithmetic. Cell addresses are covered in depth in Cell References: Relative, Absolute, Mixed; for now it is enough to know that B2 means "the value in column B, row 2".

You can build a reference by clicking. Type =, click the cell you want, type an operator, click the next cell, and press Enter. Clicking avoids typos in addresses.

The operators

Excel has four groups of operators.

Operator Meaning Example Result
+ Add =7+2 9
- Subtract (or negate) =7-2 5
* Multiply =7*2 14
/ Divide =7/2 3.5
^ Power =7^2 49
% Percent (divide by 100) =50% 0.5
& Join text ="Hi "&"Ann" Hi Ann
= <> < > <= >= Compare =7>2 TRUE

The arithmetic operators work on numbers. The & operator joins pieces of text into one string; numbers are converted to text automatically, so ="Total: "&5 gives Total: 5. The comparison operators return the logical values TRUE or FALSE, which you will use heavily with the IF function.

Text inside a formula goes in double quotes. Without quotes, Excel assumes a word is a name or a function and complains with #NAME?.

Order of operations

When a formula has more than one operator, Excel does not simply go left to right. It follows a fixed precedence, from highest to lowest:

  1. Negation, a minus sign in front of a value, as in -5
  2. Percent, %
  3. Power, ^
  4. Multiply and divide, * and /
  5. Add and subtract, + and -
  6. Join text, &
  7. Comparisons, = <> < > <= >=

Operators on the same level run from left to right. So =2+3*4 is 14, not 20, because the multiplication happens first.

Two results surprise almost everyone:

  • =-2^2 gives 4, not -4. In Excel the negation binds tighter than the power, so it is (-2)^2. Many programming languages do the opposite. If you want -4, write =-(2^2).
  • =2^3^2 gives 64, not 512. Chained powers run left to right in Excel, so it is (2^3)^2. Many languages evaluate chained powers right to left.

When you are not sure, add parentheses. They cost nothing and make the formula easier to read.

The engine in action

The first stand-in contains the whole mini engine. You do not need to read it now; it is the long block at the top. The interesting part is at the bottom, where a small sheet is created and formulas are evaluated. Press Run, then change a number or a formula and run it again.

JS A mini spreadsheet evaluates formulas

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last two lines of the first block show how errors look: dividing by A4 (which holds 0) gives #DIV/0!, and adding 1 to the text Ann gives #VALUE!. Excel formats those the same way. The next lessons explain every error in turn, starting in Formula Errors and How to Fix Them.

Watching the order happen

Excel has a tool called Evaluate Formula (Formulas tab โ†’ Evaluate Formula) that steps through a formula one operation at a time. The second stand-in does the same for plain arithmetic: it prints each operation in the order it is applied, so you can see precedence rather than memorize it.

JS Step through the order of operations

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at 10-4-3: the subtraction runs left to right, so it is (10-4)-3 = 3, not 10-(4-3) = 9. And 8/2*(2+2) is 16: both 8/2 and the parenthesized 2+2 give 4, and the multiplication comes last. Division and multiplication share a level, so 8/2 is done before *(...). (The stand-in logs steps in the order it reads them, left to right; the result is what matters.)

When something goes wrong

Excel catches some mistakes before you finish typing, and others only show up as an error value in the cell.

Mistakes Excel stops you from making. If the parentheses do not match (=(2+3*4) or an operator is missing a value (=5+), Excel shows a message such as "There's a problem with this formula" and will not store it. A frequent cause is an unmatched parenthesis or a missing comma. Excel colors matching parentheses while you type, which helps.

Mistakes that become error values. Valid-looking formulas can still fail when they run: =5/0 shows #DIV/0!, ="a"+1 shows #VALUE!, and a misspelled function name shows #NAME?. These errors travel: any formula that uses a cell with an error also shows an error.

Formulas that show as text. If a cell displays the formula itself (=B2*C2) instead of the result, the usual cause is that the cell was formatted as Text before you typed, or that you started with a space or an apostrophe. Change the format to General, then press F2 and Enter to re-enter the formula. See Formatting Cells for how formats work.

Numbers that look slightly off. Excel stores numbers in binary floating point and shows about 15 significant digits. Most of the time you will never notice, but some calculations leave tiny leftovers, which a later lesson on rounding and precision explains.

Never type a number into a formula when the number is really an input, such as a tax rate. Put it in its own cell and refer to it. Then changing the rate takes one edit instead of a hunt through every formula.

A few good habits

  • Start with = every time, and finish with Enter (or Tab, which also moves right).
  • Keep inputs in cells, not inside formulas.
  • Use parentheses whenever the order is not obvious to a human reader.
  • Press F2 to edit, and Esc to cancel an edit without losing the old formula.
  • Press Ctrl and the grave accent key (the backtick, left of 1) to toggle between showing results and showing formulas for the whole sheet. This is handy when auditing a workbook.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What do you have to type at the start of a cell so Excel treats the entry as a calculation?

    A formula starts with an equals sign (=). Without it, Excel stores what you typed as text or a number.
  2. 2. What does =2+3*4 return?

    Multiplication has higher precedence than addition, so 3*4 is calculated first (12) and then 2 is added, giving 14.
  3. 3. What does =-2^2 return in Excel?

    In Excel the negation sign binds tighter than the power operator, so the formula is (-2)^2 = 4. Write =-(2^2) to get -4.
  4. 4. A cell shows =B2*C2 as plain text instead of a number. What is the most likely cause?

    A cell formatted as Text stores everything you type as text, including formulas. Change the format to General and enter the formula again.

Summary

  • A formula starts with = and is recalculated automatically when its inputs change.
  • Operators: + - * / ^ % for numbers, & for joining text, and comparisons that return TRUE or FALSE.
  • Precedence from highest to lowest: negation, %, ^, * /, + -, &, comparisons. Equal levels run left to right.
  • Parentheses override the order and make formulas easier to read.
  • Keep inputs in cells; use cell references instead of typing numbers into formulas.
  • Errors such as #DIV/0! and #VALUE! appear in the cell and spread to formulas that depend on it.

Next: Cell References: Relative, Absolute, Mixed.