Other Excel AddIn, 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 builtin 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:
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
builtin 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 builtin
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
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
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:
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.
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 cell B1 and either;
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:
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
nonadjoining 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 nonadjoining, 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 popup 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
Back to Excel Addin, Templates and Training Main Page
Special : Get the Excel VBA Training for free when you purchase our Technical Indicators now!
