Formulas and Functions

If there is one tutorial you must read when learning about Excel, I’d probably say it’s this one about Excel formulas and functions. This topic will introduce you to the most powerful feature Excel has – I promise you, after reading this, your world will never be the same again.

I’ll first teach you how to use formulas and after that how to get started with Excel functions. Don’t worry, it’s gonna be easy and fun!

How to make a formula

Excel formulas are comparable to formulas you learned in high school. They are an expression to calculate the value of a specific cell.
You can enter a formula into a cell by clicking a cell and starting with the = character. You always need to start with typing the = character, otherwise Excel will consider everything you type as normal text.
In this example we will make a formula in cell C3 to add two numerical values we have in cell C1 and C2.

1. Make sure cell C1 and C2 contain a number
2. Now click cell C3 and type =C1+C2
3. Press Enter

You just added both numbers and have the sum of them in cell C3.

Typing a formula into cell C3

The formula in cell C3 adds cell C1 and C2

Pro tip: After you write = in a cell, instead of writing out the cell names you want to use in your formula, you can also simply click the cells. Automatically, Excel will fill the cell you’re editing with the names of the cell you clicked.
To show you the power of Excel formulas, try changing the values in either cell C1 or C2. Automatically Excel will update the value in cell C3. Cool, right?!

Change the value in cell C1 and press Enter

Automatically Excel updates the value of your formula in cell C3

How to edit a formula

Right now our formula sums up two values, but let’s say you want to edit it and subtract two values.
To do that, you can easily edit your formula:
1) Click the cell C3.

To edit a formula double-click the cell containing the formula

2) Then in the formula bar edit the + sign to a - sign.

Change the + sign to a – sign and press Enter

3) Press Enter

Now the edited formula in C3 shows an updated result

Now cell C3 subtracts the value in cell C2 from the value in cell C1.

Operator precedence

As you might remember from math class it’s important to know that there is an order in which formulas are calculated. The same happens in Excel; multiplication and dividing is done before adding and subtracting.
For example, when you have 2+3*4, first 3*4 will be calculated (=12) and then 2 will be added (=14).

Multiplications come before additions and subtractions

If you want to firstly add 2+3 (=5) and then multiply with 4 (=20) you need to use parentheses like this: (2+3)*4.

Using parentheses you can make subtraction go first

Copying and pasting formulas

Now I’ll show you a feature Excel has that will make your life much easier. You can copy and paste formulas you wrote into one cell to others and Excel will automatically update the cell references.
For example in cell A3 you have the sum of cell A1 and A2. You also want to get the sums of cell B1 and B2 in cell B3 and the sum of cell C1 and C2 in cell C3.
Instead of having to write the formula in all the three cells, you can simply copy and paste the formula you wrote in cell A3.
1) Write the formula =A1+A2 in cell A3 and press enter

You want the same formula in cell B3 and C3 quickly

2) Right-click cell A3 and select Copy

Right-click cell A3 and select Copy

3) Select cell B3 and C3 (use the CTRL key to select multiple cells at the same time).

Now select cell B3 and C3 (hold CTRL to select multiple cells)

4) Right-click and select Paste.

right-click the selected range and click the Paste symbol

Result: Excel now automatically updates the formula to add B1 and B2 and C1 and C2.

Automatically Excel copies your formula and updated the references to column B and C

How to insert a function

Excel functions are pre-made formulas that are built into Excel and that help you to make all kinds of work easier. For example you can use the function SUM(range) to add and get the sum of all the individual values of a range of cells.
Let’s say you want to add all the values of cell A1 until cell A5. To manually write A1+A2+A3+A4+A5 is a lot of work, so instead you can use the SUM() function.

It’s a lot of work to get the sum of lots of cells normally

Using the SUM function

1) To insert a SUM function in your cell C6, select it and click the Insert Function icon in the formula bar.

Instead, let’s select cell A6 and click the Insert Function icon

2) Now a window pops up where you can choose which function you want to insert. You can either search for functions or browse them by category.

Search or browse for a function

3) For now, select the SUM function and press OK.

Select the SUM function and press OK

4) In the next pop up you will build your function.
The SUM function needs at least a range of cells as input to sum up. To tell the SUM function which range it needs to use, you can either type A1:A5 in the box or you can click the icon next to the input box and select the cells manually with your mouse.

Now select the range you want to SUM and press OK

5) After you’ve added the range click OK.

Now you have the sum of your selected range in C6

Result: Now cells C6 shows you the sum of range A1:A5 using the SUM function.
As you saw in the function selection list, there are hundreds of Excel functions. In later lessons I’ll go into more details about each specific function but for now you’re good to go!