Lesson 4: Your First Formulas

Let Excel do the math: plus, minus, SUM, AVERAGE and more

Listen to this lesson (5.7 MB, about 6 minutes)Download the audio · Short pieces: 1 2 3 4 5
Excel Book1 File Home Insert Page Layout Formulas Data Review View Paste B Fill Borders Wrap Text Merge $ % AutoSum Σ B8fx=SUM(B3:B7) A B C D 2 Category Planned Spent 3 Rent 1800 1800 4 Food 700 756.4 5 Electric 120 98.15 6 Phone 60 60 7 Gas for car 150 171.3 8 Total 3280 1 2 3 1 AutoSum button 2 The formula 3 The cells it adds (the dotted box)
Click under the numbers and press AutoSum. Excel writes =SUM(B3:B7) and adds them up: 3280.

A formula is a math question you give Excel. Every formula starts with an equals sign (=). Excel shows the answer in the cell. The formula itself shows in the Formula Bar.

The math signs

Use cell addresses, not numbers

The real power is using cell addresses. Say A2 has 40 and B2 has 3.

  1. Click C2.
  2. Type =A2*B2
  3. Press Enter. C2 shows 120.

Now change B2 to 4. C2 changes to 160 by itself. That is why we use addresses.

Tip: Instead of typing A2, you can click on cell A2 while writing the formula. Excel types the address for you.

SUM: add up a list

  1. Click the empty cell under your list of numbers.
  2. Type =SUM(
  3. Select the numbers with the mouse, for example B2 to B8. Excel writes B2:B8. The colon means "from B2 to B8."
  4. Type ) and press Enter.

The whole formula is =SUM(B2:B8).

AutoSum: the fastest way

  1. Click the empty cell right under a column of numbers.
  2. On the Home tab, at the far right, click AutoSum. It looks like a big Greek E (Σ).
  3. Excel guesses the cells and draws a dotted box. If it is right, press Enter.

Even faster: click the cell and press Alt and = together.

Mac: On a Mac, AutoSum is Cmd+Shift+T.

AVERAGE, MIN, MAX, COUNT

They work just like SUM.

Tip: Click the little arrow next to AutoSum to pick Average, Count Numbers, Max, or Min from a list.
Tip: Select some numbers and look at the bottom right of the window. Excel shows the Average, Count, and Sum there, with no formula at all.

Copy a formula down

If C2 has =A2*B2 and you drag its fill handle down, C3 gets =A3*B3, C4 gets =A4*B4, and so on. Excel moves the addresses for you. This is called a relative reference. It is what you want most of the time.

When you do NOT want it to move: the dollar sign

Sometimes every row must use the same one cell, like a tax rate in cell F1. If you copy =B2*F1 down, the next row becomes =B3*F2. But F2 is empty. That is wrong.

The fix is to lock it with dollar signs: =B2*$F$1. The dollar signs mean "always use F1, never move." This is called an absolute reference.

  1. While typing the formula, click F1.
  2. Press F4. Excel adds the dollar signs for you: $F$1.
  3. Finish the formula and copy it down.
Tip: An easy way to remember: the dollar sign is a lock. $F$1 is locked in place.
Mac: On a Mac, press Cmd+T instead of F4 to add the dollar signs.

Try it

Go to the sheet 4 Budget. In D3, type =B3-C3 to find what is left of the planned money, then copy it down to D10. In B11 use AutoSum to add up the Planned column, and do the same for C11. In B13 find the biggest spending with =MAX(C3:C10), and in B14 the average spending with =AVERAGE(C3:C10). Last, in E3 type =B3*$H$2 to work out how much to save (H2 holds 10%), and copy it down. Click E10 and check that it still uses H2.

Download the practice workbook

Quiz

1. What does every formula start with?

Show the answer

An equals sign (=).

2. What does =SUM(B2:B6) do?

Show the answer

Adds up all the numbers from B2 to B6.

3. What is the shortcut for AutoSum?

Show the answer

Alt and = together (Cmd+Shift+T on a Mac).

4. What do the dollar signs in $F$1 do?

Show the answer

They lock the address, so it stays F1 when you copy the formula. Press F4 to add them.

Excel Made Easy. Free course. Written for easy reading.