Other Excel Add-In, Templates and Training

Lesson 4

THE BASICS OF FORMULAS

Download Workbook for this lesson

In this lesson we will start to take a look at what Excel does best and that is; work with formulas.

There aren’t too many spreadsheets about that do not contain formulas of some kind. Excel is regarded by many (including myself) as the number one spreadsheet package in the world today. In fact it has held this title for many years now and not without good reason. Once you become proficient in using Excel and become familiar with its capabilities, you too will no doubt wonder how you ever got by without it!

In this lesson we will look only at what I consider the bare minimum you should know about Excel and formulas. Once we have covered this we can move on to slightly more complex formulas and functions. You may or may not have heard the terms; formulas and functions used in reference to Excel before and wondered what’s the difference? The truth is they are more often than not used out of context. This is no doubt because the difference is quite subtle, though simple.

Functions

Excel has over 300 built-in Functions installed by default (there are more, but 300 will do for now) which are divided into ten separate categories:

  • Financial

  • Date & Time

  • Math & Trig

  • Statistical

  • Lookup & Reference

  • Database

  • Text

  • Logical

  • Information

  • Engineering

Possibly two of the most popular Functions (and easiest to use) are the SUM, which is categorized under Math & Trig and the COUNT. The SUM function simply adds together numbers and returns the total sum of the numbers. The COUNT simply counts numbers in any given range of cells. So a Function is a single predefined formula that is built into Excel.

Formulas

Once we have used one of Excels functions on a Worksheet we have created a formula . As we become more confident in using Excel we can start to join functions together to create different formulas. For example we might use the SUM and the COUNT function together in the same cell to create what would then become a formula.

So in a nutshell we can use a Function or Functions to create what then becomes a Formula.

Formula and Function Rules

There are only two rules when using functions to create formulas in Excel and these are:

  • All formulas and functions must begin with a = (equal sign).

  • For every open parenthesis (brackets) there must be a closing parenthesis.

Don’t confuse point 2 as meaning all formulas must have open and closing parenthesis, as this not always true. What is does mean is, all of Excels built-in Functions use at least one set of open and closing parenthesis.  These are the two rules that we must adhere to. 

There is also a valuable tip that is good practice to get into also and that is that whenever you type in a function name, always type it in lower case.  The reason for this is that if you have spelt the Function name correctly, Excel will automatically convert it to uppercase.  Therefore you can use it as an error checking tool.

Cell References

More often than not when we create a formula using one of Excels built-in functions we will be referring it to a cell or range of cells. An example of a single cell reference would be A1. An example of a range of cells reference would be A1:A10. For the first example we are referring to the content of cell A1 only, while in the second example we would be referring to the contents of cells A1, A2, A3, A4, A5, A6, A7, A8, A9, A10. Using the reference A1:A10 is just a simple method that Excel will automatically recognize. If we use the reference A1:C5 we are telling Excel to refer to the contents of cells A1, A2, A3, A4, A5, B1, B2, B3, B4, B5, C1, C2, C3 C4, C5 .

Relative and Absolute Cell References

Relative

In Excel there are two types of cell references, these are Relative and Absolute.  We will first look at relative cell references. All the examples in the Cell References paragraph are what are known as relative cell references.

As you may remember in lesson 1 we discussed that each cell on an Excel Worksheet has it’s own unique address, e.g. A1 is the relative address of the first cell on all Worksheets, while IV65536 is the relative address of the last cell on all Worksheets. The reason why they are called relative is because they are relative to the cell they are used in. This will be best explained by a simple example.

  • In cell A1 type the number 1 and in cell A2 type the number 2.

  • Select cells A1:A2 and use the Fill Handle to fill down to A10, so that we have the numbers 1 to 10 in cells A1:A10.

  • In cell B1 type this simple reference: =A1 and push Enter.

  • Select cell B1 and do one of the following

    • Copy and then select B2:B10 and paste.

    • Double click the Fill Handle.

You should now have the numbers 1 to 10 in both A1:A10 and B1:B10. This because we typed a relative cell reference in cell B1 (=A1) , which is telling Excel to make cell B1 equal the value of the cell one column to the left on the same row i.e. A1. So when we copy the reference in B1 i.e. =A1 and paste it into cell B2 Excel is still going to reference the cell one column to the left on the same row i.e. A2 . Copying the same cell (B1) and pasting it into cell B3 again tells Excel to reference the cell one column to the left on the same row i.e. A3.

Lets now copy the content of cell B1 and paste it into cell D1, this time we should get the result 0 (zero). If you click in cell D1 and look in the Formula bar you will see the relative cell reference: =C1. The reason we get the result of 0 (zero) is because the value of an empty cell is 0 (zero).

Absolute

Ok, let’s now look at what an Absolute cell reference is. Basically an absolute cell reference is a reference to a cell that does not change no matter where it is copied. Again this will be easier to see by using an example.

  • Delete the contents of cells B1:B10 and D1.

  • In cell B1 type the absolute cell reference =$A$1 and push Enter.

  • Select cell B1 and either

    • Copy and then select B2:B10 and paste.

    • Double click the Fill Handle

This time you should have the number 1 in cells B1:B10 and if you select any cell in B1:B10 and look in the Formula bar, they will all have the absolute cell reference =$A$1. This is because by using the $ (dollar sign) we are telling Excel to always refer to the same cell no matter where we copy this reference to. The $ in front of the A ($A) is telling Excel to make the column reference absolute, while the $ in front of the 1 ($1) is telling Excel to make the row reference absolute. So the reference in its entirety is what is known as an Absolute cell reference .

If you grasped this concept we can move on to what is known as a either an:

  • Absolute row relative column reference or;

  • Relative row absolute column reference.

Again the best way to see this is by using a small example.

Relative Row Absolute Column Reference

Try this:

  • Delete the contents of cells B1:B10.

  • In B1 type the Relative row absolute column reference: =$A1 and push Enter.

  • Select cell B1 and either.

    • Copy and then select B2:B10 and paste.

    • Double click the Fill Handle

You will have the numbers 1 to 10 in cells B1:B10. This is because the row portion of the reference (1) is relative. Now copy cell B10 to cell D1 and you should get the result 1.  This is because the column portion of the reference ($A) is absolute. If you click in any cell in the range B1:B10 or D1 and look in the Formula bar you will see that the row portion is always relative to the row the reference resides in, while the column reference is always absolute.

The same principle also applies to any reference that has an absolute row relative column reference. Again this can be best seen via the use of a small example.

Absolute Row Relative Column Reference

Try this:

  • Delete the contents of cells B1:B10 and D1.

  • In B1 type the absolute row relative column reference: =A$1 and push Enter.

  • Select cellB1and either;

    • Copy and then selectB2:B10and paste.

    • Double click the Fill Handle.

You will have the number 1 in cells B1:B10. This is because the row portion of the reference ($1) is absolute. Now copy cell B10 to cell D1 and you should get the result 0, this is because the column portion of the reference (A) is always relative to the column the reference resides in. If you click in any cell in the range B1:B10 and look in the Formula bar you will see that the row portion is always absolute. If you click in cell D1 and look in the Formula bar you should see =C$1.

Avoid Typing Whenever Possible

In all the above examples we have always said to type in the reference to a particular cell. While this is fine when typing in very simple references it is completely unnecessary.  What Excel allows us to do is to use the mouse pointer to type the reference for us. This is known as the click and point method. I strongly suggest forming this habit early on as good habits are as hard to break as bad ones! To see what we mean try this simple example:

  • Delete the contents of cells A1:B10 and cell D1.

  • Type any data into cell A1.

  • In cell B1 type = and move your mouse pointer outside of B1.

  • Place it over cell A1 and left click then push Enter.

You will see that Excel has placed the cell address =A1 in our cell for us. Using this method you are much less likely to make mistakes. From now on this is the method we will use in all examples.

There is one other way we can create a reference to another cell and this is via the Paste Link button on the Paste Special dialog box. We can see this method by simply selecting any cell then copying it and selecting the cell we wish to create the reference in, right click and select Paste Special then click the Paste Link button. If you look in the Formula bar you will see that Excel has created an absolute reference to the copied cell.

Toggle Through Absolute and Relative References

Another good habit to form is to use Function key 4 (F4) to toggle through absolute to relative references. This again will save typing and help prevent errors.

Try this

  • In cell B2 type = and then move your mouse pointer over cell A1 then select it.

  • Now click within the Formula bar so the mouse insertion point is either

    • immediately before A1.

    • Between the A and the 1.

    • Immediately after A1.

  • Now push the F4 button and your reference should change to absolute i.e. $A$1

  • Push F4 again and you will get a relative column absolute row reference i.e. A$1

  • Push F4 again as you will get an absolute column relative row reference; ie. $A1

  • Push F4 again and we will return to our original relative reference; ie. A1.

So as you can see, by pushing F4 we can toggle through relative to absolute reference easily.

Referencing Other Worksheets

So far we have looked at how to reference a cell on the same Worksheet, but it is common to reference cells on other Worksheets. This method is again made very simple by using the point and click method. To reference a cell on another Worksheet do this:

  • Type an = in any cell.

  • Then using the mouse pointer click the name tab of another Worksheet (Sheet2 in this case).

  • Select the cell you wish to reference (C7 in this case) then push Enter.

As you will see, Excel will place in the Sheet name and the cell. If you select the cell containing the reference and look in the Formula bar you will see a reference similar to: =Sheet2!C7.  Note Excel uses the ! (Exclamation mark) after the Worksheet name, this is how Excel knows that that Sheet2 is the name of a Worksheet.

Arguments and Syntax

Now we have been through the ways and means of referencing cells we can move on to the SUM function in Excel. Without doubt the SUM function is one of the most commonly used Excel functions. I will also use the SUM function to better explain what Arguments are in functions and what the Syntax of a Function is.

Arguments

Most of Excels functions need values of some sort to perform calculations and it is these values that are known as arguments. The argument for a function could be in the form of a number, text or logical value such as TRUE or FALSE. They could also be error values or arrays, but we won't go into this in Level 1.

Some functions will take only one argument while others can take up to 30. The SUM function for example takes up to thirty arguments, but only requires one. There are other functions that take 5 or 6 arguments and require that all the arguments have a value in them. Commas are always used to separate arguments in functions.

Syntax

The term syntax in Excel refers to the order in which arguments are accepted in functions. For example, if we have a function that takes two arguments and the first argument must be a number, while the second must be text we could not put the text value as the first argument and the number as the second. If we did we would have the wrong syntax

Now we have discussed Arguments and Syntax we can move on to creating a formula in Excel using the SUM function. The SUM function will add all the numbers in a range of cells. If there is text within the range the SUM function will ignore them. However the SUM function will not ignore text values not stored in cells (a text value is a number entered as text). I will use an example to show you what I mean by this statement.

  • Type the numbers 1 to 5 in cells A1:A5.

  • Now in cell A11 type =sum(

  • Click in cell A1 and holding down the mouse button drag down to cell A10.

  • Push Enter ( Excel will automatically add our closing parenthesis for us ).

You should get the result 15 and if you select cell A11 and look in the formula bar you should see the formula =SUM(A1:A10). If we now type the number 5 in cell A6 we will get the result 20. If we type the word dog in cell A7 our result will not change. If we then type: "5" (including the quotation marks) the SUM function will again not change. This is because the SUM function will ignore text entered in cells.

To see what I mean by my previous statement " However the SUM function will not ignore text values not stored in cells " we will need to add another argument. Remember Commas are always used to separate arguments in functions , so we shall do is what is known as edit our formula. The easiest method to edit a simple function like the SUM is to do so from within the Formula bar.

  • Select cell A11 then place your mouse insertion point between the A10 and the closing parenthesis.

  • Type a comma (this tells Excel we are adding another argument) then type "5" (including the quotation marks)

  • Push Enter.

This time our result in cell A11 will change to 25. This shows that while the SUM function will ignore text values stored in cells it does not ignore text values enter directly as an argument.

The range of cells that we use as an argument in the SUM function do not have to be adjoining as in the above example (A1:A10) they can be in non-adjoining ranges. This is the most likely reason we would use more than one argument.

  • Delete the SUM formula in cell A11.

  • Type the numbers 1 to 5 in cells D1:D5.

  • Type =sum( in cell A11 then select the range A1:A10.

  • Type a comma (,) to start another argument.

  • Select the range D1:D5 and push Enter.

You should get the sum value of all numbers in the ranges A1:A10 and D1:D5

We can make this task slightly easier by selecting the range A1:A10 then holding down the Ctrl key, select range D1:D5 and up to another 28 ranges if we wanted to.

If the numbers we wish to sum reside on another Worksheet, we would simply click the sheet name tab first then select the range on the Worksheet. If we want to sum numbers on both the Worksheets housing the sum formula and on another Worksheet, we could use the first argument for the current sheet and the second argument for the other sheet. If we wanted to sum the same range of cells on different Worksheets that are next to each other we could do so like this:

  • Make sure you have five Worksheets in the Workbook you are using. To do this go to Insert>Worksheet as many times as needed.

  • In cell A1 type =SUM(

  • Now select the first sheet, this is the sheet on the far left. Hold down your Shift key and select the name tab of the last Worksheet, this is the sheet on the far right.

  • Now release the Shift key and select the range you wish the SUM function to sum.

  • Push Enter.

Depending on the Worksheet names, order and range chosen, your formula should look something like: =SUM(Sheet1:Sheet5!D1:G20)  If you now type any numbers within your chosen range on any of the Worksheets, they will affect the result of the SUM formula accordingly. We should also note that if we add another Worksheet anywhere between our chosen sheets it too will have the chosen range included (D1:G20 in my example).

AutoSum

The AutoSum simply makes using the SUM function even easier, but only under the right conditions. Basically what the AutoSum will do is sum a range that is visibly directly above or to the left of the cell that we use it in (the default is above).  It will include all of the visible range until the first blank cell or the first cell containing text. Try these examples.

  • On a clean Worksheet put any numbers in the range D5:D10 and F5:J5 .

  • Select cell D15 and click the AutoSum icon on the Standard toolbar. It's the icon on the right of the globe that looks like a reversed Z.

You should get the formula =SUM(D5:D14). Notice it starts at cell D5. This is because D4 is the first blank cell. It includes the empty range D11:D14 because it assumes we will want to add numbers within this range at some stage.

Now try this;

  • Delete the AutoSum in cell D15 then scroll down until cell D10 is no longer visible. In other words row 11 should be our first visible row.

  • Select cell D12 and click the AutoSum icon

  • You should see: =SUM() in cell D12. Push Enter and you will get an error message.

  • Push Esc twice to cancel the operation.

The reason the AutoSum did not work is because there were no visible cells containing numbers above or to the left.

Try a similar exercise on the numbers in cells F5:J5 and the same thing will happen. Although we could not enter the AutoSum in the second example, it did write the =SUM() formula for us and place the mouse insertion point between the opening and closing parenthesis. This means we could simply select any range that we wanted to sum.

AutoCalculate

There might be times when you wish to find out the sum of a range of numbers quickly without having to add a formula to the Worksheet.  For these instances, we can simply use the AutoCalculate feature of Excel.

To do this, select a range of cells containing the numbers you want to sum and look in the Status bar. If the Status bar is not visible go to View>Status bar . If the range of cells you wish to sum are non-adjoining, hold down the Ctrl key while selecting the ranges.

We are not restricted to only displaying the sum of our selected range! If you right click on the Status bar a shortcut menu will pop-up allowing us to choose one of six basic formulas. Have a look at each of these in turn and if you are unsure of what they do, please ask.  

Goto Next Lesson