What Is a Formula in Excel? A Plain-English Guide
By ExcelReady TeamUpdated 5 min read
The short answer
A formula in Excel is an instruction that calculates a value. It always starts with an equals sign, followed by numbers, cell references, operators such as + and , or functions such as SUM. For example, =B2C2 multiplies two cells. The cell shows the result, and the result updates by itself when the cells it uses change.
Key takeaways
- Every formula starts with =. Without it, Excel treats what you type as plain text or a number.
- A formula can contain numbers, cell references, operators and functions.
- A function, like SUM, is a ready-made formula with a name; you use functions inside formulas.
- Excel multiplies and divides before it adds and subtracts. Use brackets to change the order.
- Point formulas at cells instead of typing numbers, so the result updates when the data changes.
On this page+
So, what is a formula in Excel? It is an instruction you type into a cell that tells Excel to calculate something. Every formula starts with an equals sign (=), and the cell then shows the result instead of the instruction. This guide explains the parts of a formula in plain English, how Excel decides the order of a calculation, and how formulas differ from functions.
What is a formula in Excel?#
A formula is a calculation that lives in a cell and starts with =. When you press Enter, Excel works out the answer and shows it in the cell. The formula itself stays visible in the formula bar, the long box above the column letters.
Here is a small order list:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Notebook | 4 | 3 | 12 |
| 3 | Pens | 2 | 10 | 20 |
| 4 | Stapler | 9 | 1 | 9 |
Cell D2 holds the formula =B2*C2. The cell shows 12, because 4 × 3 = 12. If you change the price in B2 to 5, D2 changes to 15 straight away. That automatic update is the main reason to use formulas instead of typing answers.
Without the equals sign, Excel doesn't calculate. Typing B2*C2 just puts the text "B2*C2" in the cell.
The parts of an Excel formula#
A formula is built from a few kinds of parts. Most formulas use two or three of them.
| Part | What it is | Example |
|---|---|---|
| Equals sign | Tells Excel this is a formula | = |
| Constant | A number or text typed directly | 5, "Paid" |
| Cell reference | The address of a cell to use | B2 |
| Range | A block of cells, written first:last | D2:D4 |
| Operator | A maths or comparison sign | +, -, *, /, ^, > |
| Function | A named, built-in calculation | SUM(D2:D4) |
So =SUM(D2:D4)*1.2 has an equals sign, a function with a range inside it, an operator and a constant. On the order list it gives 41 × 1.2 = 49.2.
The maths signs are not always the ones you learned at school. Excel uses * to multiply and / to divide, and ^ for powers, so =3^2 is 9. To subtract, use the minus sign; the guide on how to subtract in Excel shows every common case.
Formula vs function: what's the difference?#
A function is a ready-made formula with a name, and you use it inside your own formulas. SUM is a function: =SUM(D2:D4) adds the three totals and returns 41. You could write =D2+D3+D4 instead, and it is still a formula, just without a function.
Functions save typing and handle jobs that are hard to write by hand, such as counting matches or looking up values. Each one takes arguments, the inputs inside its brackets, separated by commas. The SUM function guide is a good first example, and the article on what a function is in Excel explains arguments in more detail.
In short: every function call sits inside a formula, but not every formula uses a function.
In what order does Excel calculate a formula?#
Excel follows a fixed order of operations, so multiplying and dividing happen before adding and subtracting. Brackets come first and change the order.
| Formula | How Excel reads it | Result |
|---|---|---|
=2+3*4 | 3 × 4 first, then add 2 | 14 |
=(2+3)*4 | Brackets first: 5 × 4 | 20 |
=10-4/2 | 4 ÷ 2 first, then subtract | 8 |
=(10-4)/2 | Brackets first: 6 ÷ 2 | 3 |
The full order is: negative signs, then percent, then powers (^), then * and /, then + and -, then joining text with &, and finally comparisons such as = and >. Operators at the same level run left to right.
One surprise: =-2^2 returns 4, not -4, because Excel applies the negative sign before the power. Write =-(2^2) if you mean -4. When in doubt, add brackets. They never hurt, and they make the formula easier to read.
What happens when you copy a formula?#
When you copy a formula to another cell, its cell references move with it. These are called relative references. If you copy =B2*C2 from D2 down to D3, it becomes =B3*C3, which is exactly what you want for a column of totals.
To copy down quickly, select the formula cell and the cells below, then fill down (Windows: Ctrl+D; Mac: ⌘D). You can also double-click the small square at the bottom-right corner of the cell, called the fill handle.
Sometimes a reference must not move, such as a tax rate in one cell. Add dollar signs to lock it: $F$1 is an absolute reference and stays the same wherever you copy it. The step-by-step guide to writing your first Excel formula shows this with a full example.
How to see and edit a formula#
Click a cell and look at the formula bar to see its formula. To edit it, double-click the cell, or press F2 on Windows (Mac: Ctrl+U). Press Enter to save the change or Esc to cancel.
To show every formula on the sheet at once, press Ctrl+` (the key above Tab) on Windows or Mac. Press it again to go back to results. This is a quick way to check a sheet before you send it, and it is a common task in a basic Excel test for employment.
Common formula errors and what they mean#
If a formula can't produce a result, Excel shows an error code that starts with #. Each one points to a different problem.
- #NAME?: Excel doesn't recognise a word, often a misspelt function like
=SUMM(D2:D4)or text without quotes. - #DIV/0!: the formula divides by zero or by an empty cell.
- #VALUE!: the formula tries to do maths with text, such as
="ten"*2. - #REF!: a cell the formula used has been deleted.
- The formula shows as text: the cell was formatted as Text before you typed, or there is a space before the
=.
If a formula shows an old result and doesn't change when you edit the data, calculation may be set to Manual. The guide to fixing a formula that is not updating explains how to switch it back.
Practise it#
The fastest way to understand formulas is to type a few and watch them update. Practise your first formulas in a live spreadsheet: you write each formula, change the numbers, and the lesson checks your answer instantly. You can also try the exercise on this page, which tests the order of operations with brackets.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Each notebook costs the price in B2, minus the discount in D2. In E2, write a formula for the total cost of the quantity in C2. Use brackets so the discount comes off before you multiply.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Total | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 |
Tip: Put B2-D2 in brackets first, then multiply by C2.
Practise this in a lesson
Frequently asked questions
What is a formula in Excel, with an example?
A formula is an instruction that starts with = and calculates a result. For example, =B2*C2 multiplies the value in B2 by the value in C2, and =SUM(D2:D4) adds up D2, D3 and D4.
What is the difference between a formula and a function in Excel?
A formula is anything you type that starts with = and calculates something. A function is a named, built-in calculation such as SUM or AVERAGE. Functions are used inside formulas, so =SUM(A1:A5)*2 is one formula that contains one function.
Why does my formula show as text instead of the result?
Usually the cell was formatted as Text before you typed, or there is a space before the equals sign. Change the format to General, then click into the cell and press Enter again.
What are the 5 basic formulas in Excel?
Most beginners start with SUM, AVERAGE, MIN, MAX and COUNT. Together with simple maths like =B2*C2, they cover a large part of everyday spreadsheet work.