Excel 2007 Training Video: Formulas

Learn how to let Excel do all the hard work with formulas. A transcript is available for this video.

Using formauls in Excel 2007

You can create formulas using the formula menu on the Ribbon.

Formulas can use cell references when calculating data.

Excel is a spreadsheet so that means it will take all the information on the spreadsheet and perform calculations for us, so we don't have to do the hard work.

The sales figures here in column C. I want to make a total of all the values in that column. The way Excel works is, to start a calculation you must press the equals (=) key. When you press equals (=) at the start of a cell entry, excel switches the way it thinks and starts to perform a calculation. So by entering equals (=) you are telling Excel I am not entering data, I'm starting a calculation.

Secondly it uses the calculation in the standard way, so for example if I want to calculate these values I can type equals (=) 54 + 67 + 34 + 28 + 48 and I can press Enter and it gives us the correct answer of 231. In the formula bar, you can see the formula that has just been entered.

Although 231 is the correct answer for the sales figures, It quite a long process and one problem is if there is an error and Monday's figure was 154 rather than 54, the calculation that we performed earlier has not been updated. In the formula bar, we have entered the number 54, so it's not reflecting the change made in cell C3. How do we change that?

Lets start again and rather than entering the number 154 for example, we enter the cell reference. The cell reference is the column and row number or letter, and I can enter C3. As I enter C3, the cell is given a blue border. I click on +, C4, it has a green border, and I continue with + C5 + C6 + C7. And you can see that a different color for each cell is denoted on the left hand side here. And when I press enter, I have the value of 331 which is excellent. And in the formula bar we can see is equal to C3 + C4 etc. If I change the Monday value to 54, the value here which is the total of all of the sales, should reflect the changes made to C3 because we're not using absolute numbers, but cell references. So it's referencing what's in each cell. I'm going to go to C3, change that back to 54, when I press return, its been changed to 231, excellent.

Although this works, and it makes changes to the values in here, the problem is that it is a labor intensive way of doing it. A quicker way to do it, is to use a function or a formula. This is where excel really does a lot of hard work for us. To explain what a function is, it's split into 3 sections.

The first part of the function will have an equals (=) sign. The equals sign tells excel that you are performing a calculation rather than entering information. The second part of the function is the function name, now each function name will be different. If for example you are adding items together, you can use the sum function. If you are finding out the average of all of the values, the function would be AVERAGE.

The third part of a function is the range of cells. This contains the numbers that you are performing a calculation on. In our example we have equals, we want to perform the addition to all the values so we will use the SUM function. And the range of cells are cells C3 to C7. And the answer that it will give us is 231.

On the home tab, on the right hand side, you will have an option to enter a function. Here we can see some of the common ones, such as average, sum and count numbers. Click on sum, we see =sum(c3:c7) so this is telling us that it will create a function with an equals sign at the beginning, a function name in the middle and the range of cells in brackets at the end. Click on enter and we can see the answer 231, and in the formula bar, we can see =sum(C3:C7). If we make a change, to Monday 154, press enter and you can see that the value changes here. That's formulas and how excel can do some of the hard work for us.

A bit of tidying up here is required here. I can't see all of the information in cell B5. The text is longer than the column. If we move the cursor in between the two column headers, we can see the cursor changes to a double header arrow. You can left click and hold and drag it to the left or right. Alternatively, if you use a double left mouse click, you can expand the column to the largest entry.

This demo looked at the different ways to calculate in excel. You can use the manual method using the + button, or you can use a function. The function feature on the home tab, click the dropdown from one of the different functions available. The function structure always starts with an = sign, followed by the function name, and in parenthesis the range of cells that you are performing the calculation on.