How to Write Basic Formulas in Excel

A formula is a calculation you type into a cell. It always starts with an equals sign, and instead of typed-in numbers it usually points to other cells. That's what makes a spreadsheet useful: change a price or a quantity, and every result that depends on it updates by itself.

The examples use a small receipt. Prices and quantities are filled in, two line totals are already there, and the empty cells in column D are where your formulas go.

fx

	A	B	C	D
1	Item	Price	Qty	Total
2	Notebook	8	3
3	Pen pack	5	2	10
4	Eraser	2	4	8
5
6	Subtotal
7	Coupon			6
8	After coupon
9	Split 3 ways

Write your first formula

The Notebook's total is its price times its quantity: 8 × 3.

1. Click the cell for the result

   Click D2.

2. Type an equals sign

   Type =. It tells Excel that what follows is a calculation. Without it, Excel stores what you type as plain text.

3. Click the first cell

   Click B2. Excel writes B2 into the formula and draws a colored border around the cell. Clicking is safer than typing the address, because you can't mistype it.

4. Type the operator and click the second cell

   Type * (the multiplication sign in Excel), then click C2. The formula now reads =B2*C2.

   D2fx=B2*C2

   	A	B	C	D
   1	Item	Price	Qty	Total
   2	Notebook	8	3	=B2*C2
   3	Pen pack	5	2	10
   4	Eraser	2	4	8

5. Press Enter

   The cell shows 24. Click D2 again: the cell shows the result, and the formula bar above the sheet shows the formula behind it.

=B2*C2Copy

Press Enter to confirm and move down a cell, or Tab to confirm and move right. Press Esc instead to cancel what you've typed.

The arithmetic operators

Excel uses these symbols for arithmetic. The examples use the receipt once it's finished.

Multiplication is *, not x, and division is /, not ÷. You can mix cell references and plain numbers, as in =D8/3, and there's no limit on how many cells a formula can use.

Finish the receipt

Each of the remaining formulas uses a result from the one before it.

1. Add the line totals

   In D6, add the three line totals. The result is 42.

   =D2+D3+D4Copy

2. Subtract the coupon

   In D8, subtract the coupon in D7 from the subtotal. The result is 36.

   =D6-D7Copy

3. Split the bill

   In D9, divide the amount after the coupon by 3 people. The result is 12.

   =D8/3Copy

   D9fx=D8/3

   	A	B	C	D
   1	Item	Price	Qty	Total
   2	Notebook	8	3	24
   3	Pen pack	5	2	10
   4	Eraser	2	4	8
   5
   6	Subtotal			42
   7	Coupon			6
   8	After coupon			36
   9	Split 3 ways			12

Adding cells one by one with + works for a few cells. For a long column, the SUM function is shorter: =SUM(D2:D4) gives the same 42.

Why use cell references instead of numbers?

You could type =8*3 in D2 and get 24. But that formula only knows the numbers 8 and 3. If the Notebook's price changes, the total stays wrong until someone notices.

=B2*C2 says "whatever is in B2 times whatever is in C2". Change B2 from 8 to 10 and Excel recalculates every formula that depends on it, straight down the receipt:

B2fx

	A	B	C	D
1	Item	Price	Qty	Total
2	Notebook	10	3	30
3	Pen pack	5	2	10
4	Eraser	2	4	8
5
6	Subtotal			48
7	Coupon			6
8	After coupon			42
9	Split 3 ways			14

The same goes for a typed result. A cell that holds 15 stays 15 forever; a cell that holds =A2+B2 changes whenever A2 or B2 changes. Keep each input in its own cell and point your formulas at it.

Order of operations: use parentheses

When a formula has several operators, Excel doesn't simply work left to right. It calculates powers (^) first, then multiplication and division, then addition and subtraction. Parentheses come before everything.

Suppose you want the after-coupon amount split three ways in one formula:

• =D6-D7/3 divides the coupon by 3 first, then subtracts: 42 − 2 = 40. Wrong.
• =(D6-D7)/3 subtracts first because of the parentheses, then divides: 36 ÷ 3 = 12. Right.

=(D6-D7)/3Copy

When you aren't sure what Excel will do first, add parentheses. They never hurt.

Edit a formula

• See what a cell really contains. Click the cell and look at the formula bar.
• Edit in place. Press F2 (on some laptops, Fn+F2) to edit the formula inside the cell, or click in the formula bar. Change it and press Enter, or press Esc to leave it as it was.
• Show every formula at once. Press Ctrl+` (the key above Tab) to show formulas instead of results in every cell. Press it again to switch back. It's a quick way to check a whole sheet.

When a formula goes wrong

Common mistakes

• Forgetting the equals sign. B2*C2 without = is just text. Excel shows it as typed.
• Typing results instead of formulas. A typed 24 doesn't update when the price changes. Use references.
• Using x or ÷. Excel's operators are * and /.
• Leaving out parentheses. =D6-D7/3 and =(D6-D7)/3 give different answers. Decide what should happen first and say so with parentheses.
• Pointing at the wrong cell. Click the result and check the colored borders: each one shows a cell the formula uses.

Practice

Practice

Item, price, quantity, total

Build a small table in Excel with the columns Item, Price, Quantity and Total, and fill in three or four items. Then:

1. In the first Total cell, enter =B2*C2.
2. Write the same kind of formula for each row below it. (The next tutorials show you how to copy a formula down in one step.)
3. Under the table, add the totals with +, subtract a discount, and divide the result by the number of people paying.
4. Change one price and watch every result update.

Then complete the receipt below. Click a highlighted cell, type the formula and press Enter.

Try it0 of 4 done

1. 1.In D2, multiply the Notebook's price (B2) by its quantity (C2).Show hint
2. 2.In D6, add the three line totals in D2, D3 and D4.Show hint
3. 3.In D8, subtract the coupon (D7) from the subtotal (D6).Show hint
4. 4.In D9, divide the amount after the coupon (D8) by 3 people.Show hint

D2fxFormula for D2Enter ↵

Click a dashed cell, type a formula in the bar and press Enter.

	A	B	C	D
1	Item	Price	Qty	Total
2	Notebook	8	3
3	Pen pack	5	2	10
4	Eraser	2	4	8
5
6	Subtotal
7	Coupon			6
8	After coupon
9	Split 3 ways

Check your skills

• Can you write a formula that adds two cells together?
• Can you write formulas that subtract, multiply and divide one cell by another?
• Can you use parentheses to make Excel calculate one part of a formula first?
• Can you explain why =A2+B2 updates by itself but a typed 15 doesn't?