What is a Ms- excel?

 Excel is a spreadsheet program that can be used for storing, organizing and
manipulating data.

Features of Ms- excel

Cell reference
 Cell reference is a combination of the column latter and the row
number such as B4, D7, and F12 etc.

Prepared by Hamic UCC TRAINERS 1

How to insert a comment in Excel
When you work with excel to make big tables and graphs you might have use or need to
use the “insert comment” command. The comments will not be printed if you print the
table. The comments are used to remind you or any person that you share your document
with, some valuable information.
Lets start and you will understand as we use the function.

Start by writing the following table starting in cell A1.

Name Income 2001 Income 02 Income 03 Income 04 Income 05 Income 06
Frank 1,000000 1,200000 1,300000 1,200000 600,000 1,000000
George 900,000 800,000 900,000 800,000 1,000,000 900,000
Anna 500,000 700,000 600.000 900,000 800,000 750,000

When this text is written you should take a look at the values.
Why is that Frank suddenly have 600,000Tsh income in 2005?
When he usually have around 1.2millionTsh?
This I make an explanation for, “He had a long sickness period in 2005”
Lets say you send this table with an e-mail to a friend
Where will you write the explanation for the income 2005?
This is where we can use comment command
Start by selecting the cell that includes Frank’s income for 2005 i.e. F2
Click on Insert Menu and click on comment
Write the following information in the box
Frank was sick between April and September.

If you did the exercise correctly this is how it should look like.

Prepared by Hamic UCC TRAINERS 2

Click else where on the sheet to unselect the cell with the comment

Types of data you can enter

In Ms excel you can insert the following types of data
Text
Numeric data
Formulae
Text entries are used for titles, headings, and any notes. They are entries you do not want
to manipulate arithmetically.

Numeric data consist of numbers you want to add, subtract, multiply, divide and use in
formulae.

Formulae are used to calculate the value of a cell from the contents of other cells. For
instance, formulae may be used to calculate totals or average. Formulae always start with
an = sign. Typical formulae could look like this:
=A1+A2 or =SUM (A1:A6).
The following operators (symbols) are used in the formulae:
+ Add - Subtract * Multiply / Divide

Text Entry

Numeric Data

Formulae

Lets try to type in the following data and see how far we have gone.
Prepared by Hamic UCC TRAINERS 3
Keep on and type the formulae for calculating the total for each student
Start by placing the mouse on the cell E2 and type in = SUM (B2:D2) then press enter
key or you can click on the enter button (the green button with like a tick)
Go on and calculate for the following. But before that, add the Rows and columns for the
specific data. Remember the formulae for the rest of the following is just the same as for
sum but you only need to change the words you want to calculate for. When calculating
for average then you will need to write =Average (b2:d2) and so on.
1. The total for the each entire subject
2. The average for each student
3. The max number in each subject
4. The min number
5. The mode the median
Save the book with the name “Results” in My Documents Folder.

Let us now try to learn some of other formulae that can be useful in our environments.
We will have to write the required formulae for Grade and Remarks for each student. But
before we do that let us add a column for Remarks and Grade.
Remarks are words that are explaining on a certain performance. Now from our table we
will have to regard column for the average as the column that will be remarking and
grading. Therefore let us add a column for remark and select cell G2 and write the
formulae for remarks as follows
=IF(F2>80,"EXCELLENT",IF(F2>60,"GOOD",IF(F2>40,"AVERAGE",IF(F2<40,
"POOR"))))not we are referring to the cell F2 because it is the one that contains the
average values for Johnson. Once you have finished writing this formulae press enter and
excel will display the remark according to the value in the cell F2.
Go on and calculate the remarks for the rest of the students.
At the end of this exercise it should look like this.

Prepared by Hamic UCC TRAINERS 4

Now you can calculate the formula for remarks you should be proud of yourself.
Lets try one more thing with the similar procedure. We will write the formulae for Grade.
Add one more column for Grade and write the grading formula as follows in cell H2
=IF(F2>80,"A",IF(F2>60,"B",IF(F2>40,"C",IF(F2<40,"F"))))After writing the
formula pres enter to display the grading = in cell H2. has it worked?
Go on and find the grading for each student. When through or need a help raise u=your
hand. And this is what you should have at the end.

Exercise 2

Prepared by Hamic UCC TRAINERS 5

In this exercise you will try to use a few functions that you will probably find very useful.
We will make a table and a diagram showing flour production of a small mill in 2003.
knowing how much the mill produced each month of the year, and how much it sold, we
will calculate the following:
1. The total amount of flour produced in 2003
2. The total amount of flour sold during 2003
3. The amount of flour left over for each month
4. How many percentage of the total production was produced each month
Does it sound daunting?

Doing it all by hand would be strenuous task for sure but with excel it wont take long
once you have gotten the grip. And please ask for help if you have stuck somewhere. Just
raise your hand.

Make a table in Excel

So lets begin. Below you will find data over the mill production for each month. Start by
making these into a table in Excel by writing Month and cell A1 and continuing from
there.

2. Calculate the total amount produced and sold.

As you can see now the total amount of flour produced and sold has not been calculated
in the table above. Now you can do that in excel. We have to start calculate for the
amount of flour produced. Simply do this by selecting cell B14 and write =SUM
(B2:B13) in it then push enter. Excel now calculates the sum of all values in cells B2 to
B13. It should be 9360.
The formulae =SUM (B2:B13) is interpreted as follows:
= means that the cell should equal to
SUM >> The Sum (B2:B14) of the values in cells B2 to B13

Now calculate the total amount of flour sold in the same way. It should be 7900Kg.
please ask if you need any help.
Prepared by Hamic UCC TRAINERS 6
3. Calculate the amount of flour left over each month
Now we want to know how much flour was left over each month. Start by writing “Flour
Left over in the cell D1. To know how much flour was left over we need to subtract the
amount of flour sold from flour produced. Do this by first selecting cell D2 and write = in
it. Then click on the cell B2, followed by – and then click on cell C2, so that it brings
=B2-C2 written in cell D2. Now press enter and the difference between B2 and C2 will
appear on D2. It should be 20.
Copy the function to the whole column by extending it down using the mouse that is by
selecting cell D2, click and hold down the mouse button on the small square in the
bottom right corner of cell D2. Now extend the function down to cell D14. Ask if you
need help.

Here

Now continue and calculate the average sold and left over in the same way. Please ask if
you need help. Once you have come this far, our table should look like this:

Prepared by Hamic UCC TRAINERS 7

4. Calculate the percentage produced each Month
It would be interesting to know how much of the total amount is produced by each
month. First of all write “% of Yearly Production”. So how should we do this? We’ll do
it slowly, step by step follow the instruction given below.

First we want to format column E to show values in percentage. Does this by selecting
column E2, then go to Format Menu and choose Cells. In the window that pops up
choose the category “Percentage” and change Decimal places to 1.

Now continue by selecting cell D2 and write =B2/B14 in it. Press enter and the cell
should say 7.2%. if it doesn’t, ask for help. So 7.2 is the percentage of the total
production that was produced in January.

What we want to do now is to copy this function to the entire row. Try doing this by
selecting cell E2, then click on the bottom right corner and drag it down to cell E13.
What happened?
Does it look like this?

If it does you have followed the instruction to the point, excellent. The problem is that the
instruction was not really true. If you select cell E3 and look in the equation bar
(formulae bar) you will see =B2/B15. The same goes for the rest of the column. So how
do we do this?

We need to lock B14 in the equation. To do this, select cell E2 and write =B2/B14 in it
and press F4 key on the Keyboard. When you do this you will find that two $ symbols are
added to the equation, in before B. It looks like this: =B2/$B$14. These $symbols means
that B14 is fixed. Now push enter key and you will see 7.2% in the cell E2, right? So
finally we are going to copy this function to the entire row. Select cell E2, click on the
Bottom right corner and drag it down to E13. Now you should have all percentages in
column E. Done?

Quiz 1.

Prepared by Hamic UCC TRAINERS 8

1. Set the following work and calculate for Average, Minimum, Maximum, Mode,
Median

Create a column chart and a pie for all five students and for three exams i.e. final exam
60%, Project 25% and Test 25%
The Column chart heading should read FINAL EXAM RESULTS
On the X –axis should be students name while on the Y-axis should
display Pass mark.
The Pie chart heading should read AWARE FINAL COURSE
And each student’s values should be represented in percentage.

Quiz 2.
1. Prepare the document as shown below and calculate daily balance
2. Format all amounts to Tanzanian currency (TZS symbol)
3. Create a chart as shown below
4.
Agape Income & Expenditure

3% 9%
12%
11%
Monday
Tuesday
Wednesday
17%
Thursday
Friday
48%

COPYING OBJECTS FROM ANOTHER PROGRAM

Prepared by Hamic UCC TRAINERS 9
 AND CHARTS
Now we are going to make tables, cut and paste and move things back and forth between
the programs Word and Excel. We are going to do graphs showing the amount of eggs
my hens are producing each month and the amount of eggs I eat. First we will make
tables in Ms Word with statistics and then will copy the table into excel and make them
into diagrams.
So to begin with word does the table like the one below,
Month Eggs laid
1 56
2 54
3 55
4 53
5 54
6 52
7 50
8 52
9 54
10 53
11 55
12 56
Done? Excellent! Now continue and select the table you have just created by clicking on
it when it is selected go to edit menu and choose copy (you can also do this by pressing
the control key and c at the same time. Now the table is copied.
Open Excel and select cell A1 and click on edit menu and select Paste. Once we have the
data, now we can create the chart like the one below. Do that by selecting the column for
the eggs laid and select Insert menu and select Chart. In the chart type box select Line
chart and click on Next, Next and try to fill in some of important information for your
chart. Click on finish to insert your chart on the same sheet.
Make sure you have the same values in X and Y axis i.e. in the Y axis you should have
numbers from 0-60. You can change the values by clicking on the Y axis with a right
mouse button and select format axis. In the Pop up window you must select Scale tab and
change the minimum to 0 and maximum to 60. You can also change the fonts for
different labels so that you can end up with a good looking graph.

Eggs Production

60
50
40
Eggs laid

30 Eggs laid
20
10
0
1 2 3 4 5 6 7 8 9 10 11 12
Month

Prepared by Hamic UCC TRAINERS 10

Now the only problem is in excel and you want it to show in Ms Word document. Does
this by selecting the chart in excel by clicking on it and go to edit menu and select copy.
Now open the word document and select Edit menu and click on Paste.
Now go back to excel and add another column with the following data on the same
previous sheet.
Month Eggs laid Eggs eaten
1 56 42
2 54 27
3 55 30
4 53 50
5 54 28
6 52 52
7 50 31
8 52 31
9 54 30
10 53 31
11 55 30
12 56 56

Go on by making a line graph for the eggs eaten column, and follow the same procedure
as you did for the eggs laid chart. At the end you must have a chart looking like the one
below here.

Eggs eaten

60
50
Eggs eaten

40
30 Eggs eaten
20
10
0
1 2 3 4 5 6 7 8 9 10 11 12
Month

So far so good till here. Now let us try to combine the two of the columns to produce one
chart that will represent both eggs laid and eggs eaten. You can do that by selecting the
columns that contain the data that you want to create a chart for. Follow the same
procedure we did for previous chart. And when you are through your chart should look
like this.

Prepared by Hamic UCC TRAINERS 11

 Eggs statisics
Lastly you will have to
make the line chart for
60
the following data below.
50
Number of Eggs

40 Month Eggs laid Eggs eaten Eggs Over

1 56 Eggs
42 laid 14
30
2 54 27 eaten 27
Eggs
20 3 55 30 25
10 4 53 50 3
5 54 28 26
0
6 52 52 0
1 2 3 4 5 6 7 8 9 10 11 12
7 50 31 19
8 Month 52 31 21
9 54 30 24
10 53 31 22
11 55 30 25
12 56 56 0

At the end you should have the chart look like this.

Eggs Statistics

60
50
Number of Eggs

40 Eggs laid
30 Eggs eaten
20 Eggs Over
10
0
1 2 3 4 5 6 7 8 9 10 11 12
Month

Now you are an expert to make Charts. Remember a chart is a graphical representation of
data and different chart will work with different types of data. Not all charts can work in
every data you select.

Prepared by Hamic UCC TRAINERS 12