MOROGORO VOCATIONAL TEACHERS’ TRAINING COLLEGE

MICROSOFT EXCEL

2025

Prepared By: Benson C. Gamba Email: gamba1922@[Link]

 MICROSOFT OFFICE EXCEL

Microsoft Excel is a widely used spreadsheet application that is designed for storing, organizing, analyzing,
and presenting data.

A spreadsheet is a digital tool used to organize, store, analyze, and manipulate data in a grid of rows and
columns. Each box in the grid is called a cell, and it can contain text, numbers, or formulas.

TYPES OF SPREADSHEET SOFTWARE

There are many types of spreadsheet software that are produced by different companies. Below are some
of the types of spreadsheet software.

i) Microsoft Excel: Used in Web, Windows, Mac, Android, iOS

ii) Google Sheets: Used in Web, iOS, Android
iii) LibreOffice Calc: Used in Windows, Mac, Linux, Android
iv) Smartsheet: Used in Web, Android, iOS
v) Quip: Used in Windows, Mac, Android, iOS

GENERAL USES OF MICROSOFT EXCEL

The following are the common uses of Microsoft Excel in our daily activities.
i) It is used for recording and storing data
ii) It used for analyzing data
iii) It is used for Processing Examination Results
iv) It is used for sorting data in Ascending and Descending order.
v) It is used for graphing data (Data Visualization)
vi) It is used for Filtering data
vii) It is used for recording financial or statistical data.
viii) It used for analyzing data in order to get the logical meaningful data.
ix) It is used for creating Invoices.
x) It is used for creating Payrolls.
xi) It is used for preparing Budgets
xii) It is used for Creating Attendance
xiii) It is used to calculate Profit and Loss in business.
xiv) It is used for preparing Timetables

1
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
THE BENEFITS OF MICROSOFT EXCEL IN THE TEACHING AND LEARNING PROCESS

Microsoft Excel is a versatile tool that has found wide application in various fields, including education. As
a spreadsheet program, Excel offers powerful features that enhance data organization, analysis, and
visualization. In the teaching and learning process, it serves as an indispensable resource for both teachers
and students. The following are ten key benefits of using Microsoft Excel in education.

1. Data Organization and Management

Microsoft Excel provides a structured format for organizing data in rows and columns. Teachers can use it
to maintain records such as student attendance, grades, and assignment submissions. Its ability to store large
amounts of data in an easily navigable format ensures that educational information is well-organized and
accessible.

2. Simplifies Complex Calculations

Microsoft Excel’s formulas and functions allow for quick and accurate calculations. Teachers can compute
total marks, Average marks, positions and Grades for their students as well as saving time and minimizing
errors, while students can use it to solve mathematical problems, enhancing their understanding of equations
and calculations.

3. Streamlines Teacher Workflows

Microsoft Excel helps teachers to simplify routine tasks such as lesson planning, performance tracking, and
report generation. Its built-in templates and customizable features help teachers save time, allowing them
to focus on improving lesson quality.

4. Facilitates Visual Learning

Microsoft Excel enables teachers to create visual aids such as charts, graphs, and pivot tables. These tools
make it easier for students to comprehend complex data and abstract concepts. For example, teachers can
visually represent trends or compare statistical data to make lessons more engaging.

5. Supports Project-Based Learning

Microsoft Excel is an ideal platform for research projects and group activities. Students can use it to
organize and analyze data from experiments, surveys, or case studies, fostering collaboration and enhancing
their research abilities.

6. Encourages Independent Learning

By using Excel, students can engage in self-directed learning. Its user-friendly interface and powerful
features encourage exploration and problem-solving, enabling learners to gain confidence in managing and
interpreting data on their own.

2
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
7. Enhances Collaboration

With cloud-based versions like Excel in Microsoft 365, students and teachers can collaborate in real-time.
This feature supports teamwork by allowing multiple users to work on the same document simultaneously,
fostering a cooperative learning environment.

8. Enhances Analytical Skills

Microsoft Excel helps students to develop critical thinking and analytical skills. The ability to filter, sort,
and summarize data helps learners interpret information, recognize patterns, and make informed decisions.

9. Adaptable Across Subjects

Excel’s flexibility makes it suitable for a variety of disciplines. In mathematics, it can be used to
demonstrate algebraic formulas and statistical concepts. In science, it can analyze experimental data, and in
social studies, it can handle demographic or economic datasets.

Hence, Microsoft Excel is a valuable tool that supports both teachers and students in various aspects of the
teaching and learning process. From simplifying data management to promoting analytical thinking and
collaboration, it enhances educational outcomes while preparing learners for real-world challenges. Its
versatility across subjects and ability to handle diverse tasks make it an essential resource in modern
education. By integrating Excel into teaching strategies, teachers can create engaging, efficient, and
impactful learning experiences.

MICROSOFT EXCEL VERSIONS

Microsoft Excel comes into different versions with different features. The following is a list of some
Microsoft Excel versions starting from the oldest to up-to-date version.

1. Microsoft Excel 2003

2. Microsoft Excel 2007
3. Microsoft Excel 2010
4. Microsoft Excel 2013
5. Microsoft Excel 2016
6. Microsoft Excel 2019
7. Microsoft Excel 2021
Note
Microsoft Excel 2.0, 3.0, 4.0, 95, 97, 2000 and 2002 versions were phased out and therefore they are out of
use.

3
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
MICROSOFT EXCEL TERMINOLOGIES AND FEATURES
1. Workbook.
A workbook is an Excel file that contains one or more worksheets. It contains the worksheets such as Sheet
1, Sheet 2 and Sheet 3. The default file name for Microsoft Excel is Book1.
2. Worksheet.
An Excel worksheet is a single spreadsheet that contains cells organized by Rows and Columns.
3. Worksheet Tabs
The worksheet tabs represent the worksheet names such Sheet 1, Sheet 2 and Sheet 3. You can rename
them to the meaningful names.
4. Column

A Column is a Vertical section in a worksheet. It is a series of cells vertically organized in the entire
worksheet. The Columns are indicated or designated by letters such as A, B, C, D, E and F.

The maximum limit of columns in Microsoft Excel 2003 is 256 whereas the maximum limit of columns in
Microsoft Excel 2007, 2010, 2013, 2016 and 2019 is 16,384.

5. A Column Header

The Column Header is row that is used to identify each column in the worksheet. The Column Header is
also known as the Column Heading. It contains the letters such as A, B, C, D, E, F and G.

6. A Row
A Row is the Horizontal section in a Worksheet. Row is a series of cells horizontally organized in the entire
worksheet.
The rows are indicated or designated by numbers such as 1, 2, 3, 4, 5 and 6. The maximum number of rows
in Microsoft Excel 2003 is 65,536 whereas the maximum number of rows in Microsoft Excel 2007, 2010,
2013, 2016 and 2019 is 1,048,576.

7. Row Header

The Row Header is the column that is used to identify each row in the worksheet. The Row header is also
known as Row Heading. It contains the numbers such as 1, 2, 3, 4, 5 and 6.

8. A Formula Bar

A Formula Bar is a section that displays the data of an active cell. You can use a formula Bar to edit data
of an active cell.

4
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
9. Name Box

Name Box is the box that displays the name of cell that is currently selected in the worksheet. A selected
cell is called an Active Cell.

10. A Cell Address/Cell Reference

The Cell Address is the name of a cell that is a combination of a Column Letter and a Row Number.
Examples of cell addresses are A1, B1, D5 and K20.

Note: When we are writing the formulas we normally use the Cell Addresses instead of using Numbers.

11. A Cell

A cell is an intersection between a column and a row. It is a basic unit that stores data in a worksheet such
as Numbers, Text and Formulas.

12. Merge Cells

Merge Cells refers to an action of joining many cells to form one cell. We normally merge the cell when
we want to write the titles and subtitles in the worksheet.

13. Wrap Text

It refers to an action of starting a new line within a cell. A long text cannot go out of a cell.

14. Text Orientation

Text orientation refers to the direction of the text in a cell. Therefore, Text Orientation is also known as
Text Direction.

15. Sorting Data

Sorting refers to the process of arranging data in a specific order. In Ms Excel you can arrange data in either
Ascending or Descending order.

16. Filtering Data

Filtering refers to the process of selecting Specific or required data from a list or a group of data. Filtering
in Excel lets you temporarily hide unwanted data.

5
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
17. Data Types

Data types refer to the types of data that can be entered in the Worksheet cell. The following are three
main types of Data in Microsoft Excel.

i. Numbers (Constants)
ii. Text (Labels)
iii. Formulas (Mathematical Expressions or Equations)

18. Methods used for Editing Data in a Worksheet Cell

There are three ways that you can use to edit data in a worksheet cell.

i. Using Formula Bar

ii. Using F2 Key
iii. Double Clicking the Cell

MICROSOFT EXCEL ERRORS

Microsoft Excel errors occur when Excel cannot display the result of a mathematical operation.
Types of Excel errors
i. #N/A Error

#N/A Error means that the numbers you are referring to in your formula cannot be found. This can
occur when you accidentally delete a number or row that is being used in your formula, or are
referring to a sheet that was deleted or not saved. The #N/A error most often occurs when using a
LOOKUP function. LOOKUP function returns a value from a range (one row or one column) or an
array.

ii. #NAME? Error

This error means can’t find the name. It occurs when you have misspelled a name of a function, the
name of a named range, or even an incorrect argument. This error suggests that you need to check and
correct the syntax.
iii. ##### Error
This error means the cell cannot display value. It usually occurs when the cell size is not large or wide
enough to display the content. This error can be fixed by enlarging the cell and display the content
correctly.

6
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
 iv. #NULL! Error
This error mean Empty value. This error occurs when the wrong reference operator is used or not
found. It also occurs when Excel cannot determine or find the specified range. To avoid this error,
ensure that the formulas are written correctly, and the operators are used correctly.
v. #REF! Error
This error means no reference. This type of error in Excel is also one of the most frequent that you
can find. It generally occurs when we accidentally delete or replace information about the values that
make up an already established function or formula in Excel. To solve this error, it is convenient that
you undo all the actions to recover the information that has been deleted or accidentally changed.
Otherwise, we would have to formulate again.
vi. #NUM! Error
This error means Invalid number. Excel error occurs when Excel usually cannot display the result of
a mathematical operation. This type of error can occur for two reasons:
 A formula or function contains numeric values that aren’t valid. Example, calculating the square
root of a negative number.
 The result of an operation is too large or small for Excel to display it. For example, calculating
the power of 1000 raised to 300.
vii. #VALUE! Error
This error means Invalid value. This is one of the most frequent Excel errors, particularly when you
enter erroneous data or arguments in a formulas or functions, such as spaces, characters, texts, or
formulas requiring numbers. To avoid this error you must ensure that the Excel function or formula
parameters are correct.
viii. Error #DIV/O!
This error means Divided by zero. It occurs when the denominator is zero (i.e. It that it occurs when
you are trying to divide a number by zero). To handle this type of error, use the IF function, for
example, =IF(A3,A2/A3,0), to return 0.

7
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
MICROSOFT EXCEL FORMULAS
Microsoft Excel is basically used for recording data and performing calculations based on the data.
Calculations can be done by using mathematical operators and functions.
A: Mathematical Operators
Mathematical Operators are also known as Arithmetic operators/Operations. They are used in
manipulating data. Note: When you are writing the formulas using Mathematical Operators should begin
with an Equal Sign (=).

The following are the five basic mathematical operators which you can use in performing different
calculations.
1. Addition (+) Example: =A1 + B1
2. Subtraction (-) Example: =A1 - B1
3. Multiplication (*) Example: =A1 * B1
4. Division (/) Example: =A1 / B1
5. Exponent (^) Example: =A1 ^ B1

B: Microsoft Excel Basic Functions

Functions are ready-made formulas built into the spreadsheet to perform a series of operations on the
specified range of values.

Or

A function is a predefined formula that performs calculations using specific values in a particular order.

Elements of a Function

The function consists of three main Parts or elements including Equal sign, Function Name and Argument.

i. The Equal sign (=): It indicates the starting of the function. Each formula must begin with an equal
sign.

ii. The Function name: It indicates the operation to be performed. Examples; SUM, AVERAGE
and PRODUCT.

iii. The Argument: It indicates the values to be acted upon. That means, Arguments contain the
numbers involved in calculation OR values that are used in a formula. Examples; The numbers
stored in the cells A1, B1 and C1.

8
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
EXAMPLES OF MICROSOFT EXCEL BASIC FUNCTIONS
Microsoft Office Excel has many Functions (Ready-made formulas) which are used to perform different calculations
depending on the data provided.

1. SUM Function
The SUM function gives the total of the selected range of cell values.
Formula: = SUM (First Value, Second Value, Third Value, Fourth Value, … )
Example: = SUM (A1, B1, C1, D1 )
OR
Formula: =SUM (First Value : Last Value)
Example: = SUM (A1:D1 )

2. SUMIF Function
SUMIF function is used to sum the values in a range that meet criteria that you specify. The following are the
examples of the criteria that can be used in SUMIF function.
i) Less than (<),
ii) Less than or equal to (<=),
iii) Greater than (>),
iv) Greater than or equal to (>=)
v) Equal to (=)
Suppose that in a row that contains the numbers from cell A1 to D1, you want to sum only the numbers that are
less than 50. Therefore, the formula will be written as shown below.
Example: = SUMIF (A1:D1, “<50” )

3. AVERAGE Function
The Average Function returns the average (arithmetic mean) of its argument.
Formula: = AVERAGE (First Value, Second Value, Third Value, Fourth Value, … )
Example: = AVERAGE (A1, B1, C1, D1 )
OR
Formula: = AVERAGE (First Value : Last Value)
Example: = AVERAGE ( A1:D1 )
4. PRODUCT Function
The Product Function multiplies the numbers in a range of cells.
Formula: = PRODUCT (First Value, Second Value, Third Value, Fourth Value, … )
Example: = PRODUCT (A1, B1, C1, D1 )
OR
Formula: = PRODUCT (First Value : Last Value)
Example: = PRODUCT ( A1:D1 )

9
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
5. IF Function
The IF Function checks a given condition and returns a particular value if it is true and return another
value if the condition is false.
Suppose you have the average score of a student in cell E2 recorded in a worksheet. Now to grade
the average score using IF function you have to prepare the Data Range as shown below.

Data Range
i) 80 to 100 = A
ii) 70 to 79 = B
iii) 60 to 69 = C
iv) 50 to 59 = D
v) 0 to 49 = F

Formula

Function 1: =IF (E2>=80, “A”, IF (E2>=70, “B”, IF (E2>=60, “C”, IF (E2>=50, “D”, IF (E2>=0, “F”)))))
Or
Function 2: =IF (E2>=80, “A”, IF (E2>=70, “B”, IF (E2>=60, “C”, IF (E2>=50, “D”, IF (E2<50, “F”)))))
Or
Function 3: =IF (E2>=80, “A”, IF (E2>=70, “B”, IF (E2>=60, “C”, IF (E2>=50, “D”, “F”))))

6. RANK Function
The function returns the statistical rank of a given value within a supplied array of values. Thus, it
determines the position of a specific value in an array (Returns a rank of a number in a list of
numbers).
Suppose you have the numbers from cell E2 to cell E15. Now the formula for ranking these numbers
will be written as shown below.

Formula: = RANK (E2, $E$2: $E$15)

Formula: =[Link] (E2, $E$2:$E$15)

7. COUNT Function

The COUNT Function counts the total number of cells in a range that contains

Suppose you have the numbers from Cell E2 to Cell E15. Now the formula for getting the number of
cells which contain the numbers only is written as shown below

Formula: = COUNT (E2:E15)

10
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
8. COUNTA Function
The COUNTA function is used to count the cells which contain several types of data values.
Suppose you have recorded the data from cell E2 to cell E15. Now the formula for getting the number
of cells which contain the data is written as shown below.

Formula: = COUNTA (E2:E15)

9. COUNTIF Function
Excel COUNTIF function is used for counting cells which contain numbers within a specified range
that meet a certain criterion, or condition.
i) Less than (<),
ii) Less than or equal to (<=),
iii) Greater than (>),
iv) Greater than or equal to (>=)
v) Equal to (=)
Suppose you have the numbers from cell E2 to cell E15. Now the formula for getting the number of
cells which contain the numbers which are greater than or equal to 50 is written as shown below

Formula: = COUNTIF (E2:E15, “>=50”)

10. MAX Function

It returns the largest (maximum) value in a set of values.

Suppose you have the numbers from cell E2 to cell E15. Now the formula for getting the largest number
is written as shown below

Formula: = MAX (E2:E15)

11. MIN Function

It returns the smallest (minimum) value in a set of values.

Suppose you have the numbers from cell E2 to cell E15. Now the formula for getting the smallest
number is written as shown below

Formula: = MIN (E2:E15)

12. SQRT Function

SQRT Function provides the square root of a positive number.
Formula: = SQRT (Number)
Example: = SQRT (A1)

11
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
13. TODAY Function
TODAY Function returns the Current Date.
Formula: =TODAY ( )
14. NOW Function
NOW Function returns the Current Date and Time.
Formula: =NOW ( )

15: DATEDIF Function

The DATEDIF function in Microsoft Excel calculates the difference between two dates in various units,
such as Years, Months, or Days.
The DATEDIF function does not have a built-in unit to calculate the number of weeks directly. However,
you can calculate the number of weeks between two dates using a combination of other functions or simple
math, leveraging the results from DATEDIF.
Therefore, to calculate the number of weeks, you can calculate the total number of days using DATEDIF
and then divide by 7 to get the number of weeks.
Hence, DATEDIF function is particularly useful for determining durations like age, tenure, or the time
between two events.

i) DAYS (D)
i. Calculating the number of Days from Start Date to End Date.

Formula: =DATEDIF (Start Date, End Date, “D”)

Example: =DATEDIF (A1, B1, “D”)
ii. Calculating the number of Days from Start Date to Current Date.

Formula: =DATEDIF (Start Date, TODAY ( ), “D”)

Example: =DATEDIF (A1, TODAY ( ), “D”)

ii) WEEKS
i. Calculating the number of Weeks from Start Date to End Date.

Formula: = (DATEDIF (Start Date, End Date, “D”)/7)

Example: = (DATEDIF (A1, B1, “D”)/7)

12
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
 Or
Formula: = INT ((End Date - Start Date)/7)
Example: = INT ((B1-A1)/7)

i. Calculating the number of Weeks from Start Date to Current Date.

Formula: = (DATEDIF (Start Date, TODAY ( ), “D”)/7)

Example: = (DATEDIF (A1, TODAY ( ), “D”)/7)

Or
Formula: = INT ((TODAY ( ) - Start Date)/7)
Example: = INT ((TODAY ( )-A1)/7)

iii) MONTHS (M)

i. Calculating the number of Months from Start Date to End Date.

Formula: =DATEDIF (Start Date, End Date, “M”)

Example: =DATEDIF (A1, B1, “M”)

ii. Calculating the number of Months from Start Date to Current Date.

Formula: =DATEDIF (Start Date, TODAY ( ), “M”)

Example: =DATEDIF (A1, TODAY ( ), “M”)

iv) YEARS (Y)

i. Calculating the number of Years from Start Date to End Date.

Formula: =DATEDIF (Start Date, End Date, “Y”)

Example: =DATEDIF (A1, B1, “Y”)

ii. Calculating the number of Years from Start Date to Current Date.
Formula: =DATEDIF (Start Date, TODAY ( ), “Y”)
Example: =DATEDIF (A1, TODAY ( ), “Y”)

13
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
16. The Functions for Changing Text Cases
There are three formulas which are used for changing text cases in Microsoft Excel. These include Upper,
Lower and Proper functions.
1. UPPER Function
UPPER Function converts all letters in a text from lowercase to uppercase.
Formula: =UPPER (Text)
Example: =UPPER (A1)

2. LOWER Function
LOWER Function converts all letters in a text from uppercase to lowercase.
Formula: =LOWER (Text)
Example: =LOWER (A1)

3. PROPER Function
The PROPER Function converts the first letter in each word in uppercase and leave all the other
letters in lowercase.
Formula: =PROPER (Text)
Example: =PROPER (A1)

17. COUNTIFS Function

The COUNTIFS function in Excel is used to count the number of cells that meet multiple criteria
across one or more ranges. It's an extension of the simpler COUNTIF function, which only handles
one condition.
Syntax:
= COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example: How many female students scored above 50 Marks?
A B C
1 STUDENT NAME GENDER MARKS
2 JUMA ALLY MALE 90
3 NEEMA MUSA FEMALE 55
4 AMANI JULIUS MALE 45
5 MZEE BABU MALE 86
6 ANNA JAMES FEMALE 45
7 ASHA ALLY FEMALE 75
8 SALEHE JOHN MALE 80

Formula: = COUNTIFS (B2:B8, "Female", C2:C8, ">50")

14
Prepared By: Benson C. Gamba Email: gamba1922@[Link]
17. SUMIFS Function
The SUMIFS function in Excel is used to sum values that meet multiple criteria across one or more ranges.
Syntax:
= SUMIFS (sum range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example: Find the total sum of salaries for all female employees whose salaries are greater than or equal
to 5000.

A B C
1 STUDENT NAME GENDER SALARY
2 JUMA ALLY MALE 4500
3 NEEMA MUSA FEMALE 5600
4 AMANI JULIUS MALE 3500
5 MZEE BABU MALE 4500
6 ANNA JAMES FEMALE 7200
7 ASHA ALLY FEMALE 4500
7 SALEHE JOHN MALE 6000

Formula: =SUMIFS (C2:C7, B2:B7, "Female", C2:C7, ">=5000")

15
Prepared By: Benson C. Gamba Email: gamba1922@[Link]