## Free Excel Training Online Lesson Four: Basic Excel Formulas

## 2KO Africa welcomes you back
to our free Excel training online. Now that we have had a look at rounding
numbers and handling numbers with decimals, we can go on to Totaling Columns
and Rows.

**Lesson 4**

**Use Formulas to Get Totals Quickly**

The most common task when one
has a set of numbers is to find out what they all add up to. If you are
running a small shop for example, you will have a series of expenses. These
expenses will be things like rental, basic services like water, electricity,
refuse removal and so on, as well as expenses related to staff and products
purchased for sale purposes. You can set out all these expenses in a column
and then keep track of these expenses from month to month and year to year.
By having the numbers all together, the owner of the shop will at a glance
be able to see when expenses suddenly rise fast, or if something looks
wrong, and he / she can go and investigate.

Alternately, one can keep track of the sales one is making, and this can be
done on a per transaction basis, or a per day basis, totaling the cash up at
the end of the day. Excel provides the perfect tools for this process. As a
business person, you want to exert your energy in areas that you can
control, rather than spending hours a day working on calculations. Excel
provides the tools to handle the calculations. Let's make some statements
which are quite sweeping, but completely accurate:

- Excel never miscalculates
Being a computer, it will always do the Math right.

Bad inputs (by user) however, can lead to bad outputs

- It is always up to you to
check that the answers look right.
Excel will calculate correctly, but for many reasons, our inputs can sometimes be less than perfect. Things like BODMAS (law of mathematics) and simple user error, can lead to poor inputs, which will in turn spit out the incorrect results.

**Adding Two or Three Numbers Together**

In Lesson 1, we looked at
doing basic formulas. In a way this is a recap of that lesson. If you are
already familiar with how to add 2 or 3 numbers together, jump ahead to the
next part of this lesson, adding multiple values together.

**Look at Table 7 below.**

We wish to use a formula in Excel to add B2 and B3 together and place the
answer in B4.

The formula to use would be the following:

=B2+B3

To add D2 and D3 together and place the answer in D4...

The formula to use would be the following:

=D2+D3

*Table 7*

The same principle applies when adding values in rows.

To add B2 and D2 together and place the answer in E2...

The formula to use would be the following:

=D2+B2

Note that it does not matter which of the two values comes first, the answer
is the same. It is like adding 5+3 and getting 8; you'd get the same answer
when adding 3+5.

**Adding Multiple Values Together**

The above method is the method
I would use, if required to add two or three values together. However, in
the case of the shop owner, the number of values will in almost every case
be substantially more than 2 values.

Let's look at Table 8 below.

*Table 8*

## If we wish to add the values
up in Column B, and place the result in B10, then we would not use a formula
like =B2+B3+B4+B5+B6+B7+B8

The above formula would just take too long to put in.

Excel has a much quicker way to calculate a series of cells. The function to
use is the SUM function. In fact, Excel has an even quicker way, and it is
called AUTOSUM.

**How to use the SUM Function**

Here are the steps to follow:

1.
Click the BLANK cell where the
answer must appear. In our case this would be B8.

2. Type =sum(

3. Highlight (select) the cells to be added together (in our case this
would be B2 to B8)

4. Press Enter

The answer appears in the cell where you typed = to begin.

The result is as follows:

In cell B10, Excel fills in the answer, which is 3394. If you look at cell
B10, this is what you will see. However, to help us understand where the
3394 comes from, Excel stores the formula and displays the formula in the
Formula Bar, whilst the cell selector is on B10. Table 9 shows this.

*Table 9*

If you look at the
formula bar (in Table 9 you can see it clearly above column letters D and
E), you will see the formula used. In this case, we added the blank cell B9,
rather than stopping at B8. Since B9 is blank, it has no effect on the
answer.

However, because we added B9 into the formula, if we ever inserted a number
into B9, the total would be updated immediately and automatically by Excel.
Also, we can insert blank rows at row 9, and we would not have to adjust the
formula. If we added 2 rows, the formula would expand to take the extra rows
into account. More about that in another lesson.

**How to Check the Answer is Right**

Excel offers a way of checking
that your answer is correct. Sometimes, user error creeps in and we make an
error in the formula, and we are unaware that we did so. Therefore, we
always recommend checking your answers.

**Here is how to do a quick check.**

1.
Highlight the cells with your
mouse that you want added together. In this case that would be cells B2 to
B8.

2. Whilst the cells are highlighted, look at the bottom right of your
screen, and you will see Excel supplies a quick summary of the values
highlighted.

3. The summary includes count (being the number of values added),
average (being the average of all the highlighted numbers) and sum (being
the total of the numbers highlighted).

Since we want the total, we should look at the sum value in the summary. See
table 10 below.

*Table 10*

We can see now that the total we got from =sum(B2:B9) is the same as the
summary total at the bottom, that is 3394.

**Practise on your own
**

Create the following on a blank Excel sheet

1. Use =sum() formulas in cells A8, B8 and C8 to total the
values in the columns

2. Use =sum() formulas in cells D1 through to D7 to total the
values in the rows

3. Finally use =sum() formula in cells D8 to total up all the
total values in either the rows or the columns.

## PS the answer should be the
same, whether you use the row or columns to total

**Check your answers:**

## If you would like to check
your answers,
click here
(Note the file will open in Excel)

**To start lesson 5, click here**

To go back to the index of
free online excel lessons from 2KO Africa in Cape Town,
click here