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.
- Make sure cell
C1andC2contain a number - Now click cell
C3and type=C1+C2 - Press
Enter
You just added both numbers and have the sum of them in cell C3.
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?!
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.
2) Then in the formula bar edit the + sign to a - sign.
3) Press Enter
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).
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.
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
2) 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).
4) Right-click and select Paste.
Result: Excel now automatically updates the formula to add B1 and B2 and C1 and C2.
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.
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.
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.
3) For now, 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.
5) After you’ve added the range click OK.
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!
Let me know if you have any questions in the comments.
