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:

 

  1. Excel never miscalculates

    Being a computer, it will always do the Math right.

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

  2. 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