MSIMBAZI CENTER TRAINING

INSTITUTE(MCTI)

Module 3: Microsoft Excel

Microsoft Excel Course Outline (2 Weeks,
2 hrs/day)
This is a 2-week Microsoft Excel course, with 2 hours of instruction per day, designed to build
foundational and intermediate Excel skills for data entry, analysis, and reporting.

Week/Day Topic General Objective Specific Objectives How to Achieve

Week 1 Introduction to Excel Understand Excel - Navigate Excel Live demo + hands-on
Day 1 interface and basic workspace practice
operations. - Understand ribbons,
menus, and cells
Week 1 Data Entry & Basic Learn data input and - Enter/edit text, numbers Practice exercises +
Day 2 Formatting organization. - Apply bold, italic, cell worksheet
color
Week 1 Basic Formulas Perform basic - Use SUM, AVERAGE, Guided tasks + quiz
Day 3 calculations. MIN, MAX
- Understand cell
referencing
Week 1 Cell Referencing & Fill Understand how cell - Use absolute vs. relative Practice problems
Day 4 Series references work. references
- Auto-fill data series
Week 1 Formatting & Printing Format for - Format as Table Project: Format & print
Day 5 readability and - Page setup, print preview a report
printing.
Week 2 Charts & Graphs Visualize data - Create column, pie, line Hands-on: Build chart
Day 6 clearly. charts from sample data
- Modify chart design
Week 2 Logical Functions Make decisions in - Use IF, AND, OR Exercises + case study
Day 7 Excel. functions
- Combine conditions
Week 2 Data Validation Control data input. - Drop-down lists Build student grade form
Day 8 - Restrict cell input
Week 2 Conditional Formatting Highlight data - Use color scales, icon sets Challenge: Flag low
Day 9 automatically. - Use formulas in scores
formatting
Week 2 Mini Project & Recap Apply all skills in a - Create formatted report Individual project +
Day 10 real scenario. - Include charts & formulas presentation
- Print/export to PDF
 CHAPTER 1
INTRODUCTION TO EXCEL
Microsoft Excel Basics
1. What is a Spreadsheet Program?
A spreadsheet program is software used to organize, calculate, and analyze data in rows and
columns. It helps users manage data, perform mathematical operations, and create charts.

Example:

 Microsoft Excel
 Google Sheets
 LibreOffice Calc

2. What is Microsoft Excel?

Microsoft Excel is an electronic spreadsheet. You can use it to organize your data

Into rows and columns.

OR

Microsoft Excel is a spreadsheet program developed by Microsoft. It is widely used for data
entry, calculations, creating graphs, analyzing financial information, and more.

Example Uses:

 Budget planning
 Sales tracking
 Student grading
 Inventory management

3. Common Terms Used in Microsoft Excel:

Term Description
Workbook The entire Excel file. A workbook can contain one
or more worksheets.
Worksheet A single spreadsheet or sheet inside a workbook
where you enter data.
Cell The basic unit in a worksheet where a row and
column intersect (e.g., A1).
 Range A group of selected cells (e.g., A1:A5 or A1:C3).
Formula A mathematical expression (e.g., =A1+B1).
Function A built-in formula (e.g., =SUM (A1:A5),
=AVERAGE (B1:B5)).
Column Vertical sections labeled as A, B, C...
Row Horizontal sections labeled as 1, 2, 3...
4. Types of Microsoft Excel Screens:
Workbook
 A Workbook is the complete Excel file. It contains one or more Worksheets (sheets).
You can save, open, and edit a workbook.
Example: A file named [Link] is a workbook.

Worksheet

 A Worksheet is a single sheet inside a workbook.

It's made up of cells, arranged in rows and columns.
You can have multiple worksheets in one workbook, like "January", "February", etc.

[Link] of MS Excel
 Ribbon

The Ribbon in MS-Excel is the topmost row of tabs that provide the user with different
facilities/functionalities. These tabs are:

What is a Cell

A spreadsheet takes the shape of a table, consisting of rows and columns. A cell is created at the
intersection point where rows and columns meet, forming a rectangular box.
What is Cell Address?

The cell address is the name by which is cell can be addressed. For example, if row 7 is
interested in column G, then the cell address is G7.

 Home Tab

It provides the basic facilities like changing the font, size of text, editing the cells in the
spreadsheet, autosum.

 Formulas

It is a package of different in-built formulas/functions which can be used by user just by

selecting the cell or range of cells for values.

There are a variety of functions. Here are some of the most common functions you'll use:

SUM: This function adds all the values of the cells in the argument.

AVERAGE: This function determines the average of the values included in the argument. It
calculates the sum of the cells and then divides that value by the number of cells in the argument.

argument. This function is useful for quickly counting items in a cell range.

MAX: This function determines the highest cell value included in the argument.

MIN: This function determines the lowest cell value included in the argument.
  Data

The Data Tab helps to perform different operations on a vast set of data like analysis through
what-if analysis tools and many other data analysis tools.

 Review

This tab provides the facility of thesaurus, checking spellings, translating the text, and helps to
protect and share the worksheet and workbook.

 View

It contains the commands to manage the view of the workbook, show/hide ruler, gridlines, etc,
freezing panes, and adding macros.

 Insert

Comprises options like table format and style, inserting images and figures, adding graphs, charts
and sparklines, header and footer option, equation and symbols

 Page Layout

Themes, orientation and page setup options are available under the page layout option

6. Advantage of using Excel

Easy To Store Data: Since there is no limit to the amount of information that can be saved in a
spreadsheet, MS Excel is widely used to save data or to analyse data. Filtering information in
Excel is easy and convenient.

Easy To Recover Data: If the information is written on a piece of paper, finding it may take
longer, however, this is not the case with excel spreadsheets. Finding and recovering data is easy.

Application of Mathematical Formulas: Doing calculations has become easier and less time-
taking with the formulas option in MS excel

More Secure: These spreadsheets can be password secured in a laptop or personal computer and
the probability of losing them is way lesser in comparison to data written in registers or piece of
paper.

Data at One Place: Earlier, data was to be kept in different files and registers when the
paperwork was done. Now, this has become convenient as more than one worksheet can be
added in a single MS Excel file.
Neater and Clearer Visibility of Information: When the data is saved in the form of a table,
analyzing it becomes easier. Thus, information is a spreadsheet that is more readable and
understandable.

Starting Excel

You are encouraged to start using MS Excel as you read through the following materials to
familiarize with the topics and procedures.

1. Click the Start button on the Windows taskbar.

a. The Start menu opens

2. Point to Programs

a. The Programs menu opens

3. Click Microsoft Excel

a. Excel opens a new workbook

Note: an icon for MS Excel may be located either on the desktop or on the Office toolbar.

How to open MS Excel?

To open MS Excel on your computer, follow the steps given below:

Click on Start

Then All Programs

Next step is to click on MS Office

Then finally, choose the MS-Excel option

Alternatively, you can also click on the Start button and type MS Excel in the search option
available.

How to Create a New Spreadsheet

In Excel 3 sheets are already opened by default, now to add a new sheet:

In the lowermost pane in Excel, you can find a button.

Click on that button to add a new sheet. Look figure belo

How to Save the Workbook

Follow the below steps if you want to save your Workbook:

Step1: Click on the Office Button or the File tab.

Step 2: Click on Save As option.

Step 3: Write the desired name of your file.

Step 4: Click OK.

Row in Excel

A row in Excel is a horizontal line of cells and it runs from left to right across the worksheet. Each row is
identified by the unique number on its left side from where it started. The total number of rows you have
in a single sheet is 1,048,576. Look below

Column in Excel - A column is a vertical line of cells. Each column has a special letter tha tidentifies it
 Excel Practice Question
Week 1, Day 2: Data Entry & Basic Formatting

Instructions:
You are provided with the following data. Enter the information into an Excel sheet as shown, then apply
the required formatting.

Name Department Salary Status

Alice HR 50000 Active
Brian IT 62000 On Leave
Catherine Finance 58000 Active
David Marketing 54000 Active

Tasks:
 Enter the data into an Excel worksheet exactly as shown above.
 Make the following formatting changes:
 - Make the headers (Name, Department, Salary, Status) bold and italic.
 - Change the cell color of the header row to light blue.
 - Highlight all employees with a Salary greater than 55000 in light green.
 - Change the font color of the "Status" column to blue.
 Excel Formulas and Functions
Week 1, Day 3: Basic Formulas, functions and calculations

This document contains essential Excel formulas and functions, along with examples and
explanations for basic calculations.

1. Basic Excel Functions

Function Description Example Result
SUM Adds values in a =SUM(A1:A5) Returns the sum of
range values from A1 to
A5
AVERAGE Calculates the =AVERAGE(B1:B4) Returns the average
average of values of B1 to B4
MIN Finds the smallest =MIN(C1:C6) Returns the
value minimum value in
C1 to C6
MAX Finds the largest =MAX(C1:C6) Returns the
value maximum value in
C1 to C6

2. Cell Referencing in Excel

There are three types of cell referencing in Excel: Relative, Absolute, and Mixed.

Type Description Example Behavior

Relative Default, changes =A1+B1 Becomes =A2+B2
when copied when copied to next
row
Absolute Fixed, does not =$A$1 + B1 A1 remains constant
change
Mixed Partially fixed =$A1 or =A$1 Only column or row
stays constant

3. Additional Useful Functions

Function Description Example Result
COUNT Counts numeric =COUNT(A1:A5) Counts numbers
values only
COUNTA Counts non-empty =COUNTA(A1:A5) Counts all non-
cells empty cells
IF Performs a logical =IF(A1>10,"Yes","No") Returns "Yes" if A1
test > 10

********* *******Confidence is the plant of slow growth*******************

 NOW Returns current =NOW() e.g., 27/05/2025
date & time 11:00 AM
TODAY Returns current =TODAY() e.g., 27/05/2025
date
ROUND Rounds a number =ROUND(A1, 2) Rounds A1 to 2
decimal places

Practical Questions and Solutions on

formulas and functions
Question 1: Employee Salary Computation
Calculate Net Salary using the formula:
Net Salary = (Basic Salary + Allowance) - Tax
Tax = (Basic Salary + Allowance) * (Tax Rate / 100)

Name Basic Salary Allowance Tax Rate % Net Salary

John 500000 100000 10

Grace 650000 120000 12

James 400000 80000 8

✔ Formula: =(B2 + C2) - ((B2 + C2) * D2 / 100)

********* *******Confidence is the plant of slow growth*******************

Question 2: Student Grade Analysis
Total = Sum of subjects
Average = Total / 3
Grade Rules:
- A: >= 80
- B: >= 60 and < 80
- C: < 60

Name Math Science English Total Average Grade

Ali 76 65 80

Mary 88 92 90

Lucy 60 58 55

✔ Total: =SUM(B2:D2)

✔ Average: =E2/3

********* *******Confidence is the plant of slow growth*******************

Grade

✔ Grade: =IF(F2>=80,"A",IF(F2>=60,"B","C"))

Question 3: Product Discount Calculation

Discount Amount = Price × Discount %
Final Price = Price − Discount Amount

Product Price Discount % Discount Final Price

Amount

Laptop 1200000 10

Phone 850000 5

Tablet 600000 7

✔ Discount Amount: =B2 * C2 / 100

✔ Final Price: =B2 - D2

********* *******Confidence is the plant of slow growth*******************

********* *******Confidence is the plant of slow growth*******************
 Excel Chart Practical Exercises
Exercise 1: Sales by Region
Create a column chart to show total sales in different regions.

Region Sales

North 15000

South 12000

East 18000

West 10000

Exercise 2: Monthly Revenue

Create a line chart to show revenue trends over 6 months.

Month Revenue

Jan 5000

Feb 7000

Mar 8000

Apr 6000

May 7500

Jun 9000

Exercise 3: Product Sales Comparison

Create a bar chart to compare sales of 3 products.

Product Sales

A 3000

B 4500

C 3500
Exercise 4: Market Share
Create a pie chart to show market share of 4 companies.

Company Market Share

X 30

Y 25

Z 20

W 25

Exercise 5: Expense Breakdown

Create a doughnut chart for expense distribution.

Category Amount

Rent 1000

Utilities 300

Salaries 2000

Others 700

Exercise 6: Grades Distribution

Create a column chart showing distribution of student grades.

Grade Count

A 10

B 15

C 8

D 5

F 2
Exercise 7: Website Traffic Sources
Create a pie chart showing traffic sources.

Source Visitors

Direct 2000

Referral 1000

Organic 2500

Social 1500

Exercise 8: Annual Sales Growth

Create a line chart showing annual sales for 5 years.

Year Sales

2019 40000

2020 42000

2021 45000

2022 47000

2023 50000

Exercise 9: Employee Performance

Create a bar chart to compare performance scores of employees.

Employee Score

John 85

Alice 90

Bob 78

Diana 88
Exercise 10: Inventory Levels
Create a column chart showing current inventory levels.

Item Quantity

Laptops 50

Monitors 30

Keyboards 100

Mice 80
 Excel Practical Questions and Solutions

Question 1: Employee Salary Computation

Calculate Net Salary using the formula:

Net Salary = (Basic Salary + Allowance) - Tax

Tax = (Basic Salary + Allowance) * (Tax Rate / 100)

Name | Basic Salary | Allowance | Tax Rate % | Net Salary

John | 500000 | 100000 | 10 |

Grace | 650000 | 120000 | 12 |

James | 400000 | 80000 | 8 |

Formula: =(B2 + C2) - ((B2 + C2) * D2 / 100)

Question 2: Student Grade Analysis

Total = Sum of subjects

Average = Total / 3

Grade Rules:

- A: >= 80

- B: >= 60 and < 80

- C: < 60

Name | Math | Science | English | Total | Average | Grade

Ali | 76 | 65 | 80 | | |

Mary | 88 | 92 | 90 | | |

Lucy | 60 | 58 | 55 | | |

Total Formula: =SUM(B2:D2)

Average Formula: =E2/3

Grade Formula: =IF(F2>=80,"A",IF(F2>=60,"B","C"))

Question 3: Product Discount Calculation

 Excel Practical Questions and Solutions

Discount Amount = Price × Discount %

Final Price = Price - Discount Amount

Product | Price | Discount % | Discount Amount | Final Price

Laptop | 1200000 | 10 | |

Phone | 850000 | 5 | |

Tablet | 600000 | 7 | |

Discount Amount Formula: =B2 * C2 / 100

Final Price Formula: =B2 - D2

 Excel/Google Sheets Practice Questions
Question 1: Student Exam Scores Analysis
Scenario:
You are given the marks of 5 students in 5 subjects. Using Google Sheets:

Name Math English Kiswahili Science Social

Studies

John 75 65 80 90 85

Amina 45 60 55 70 50

Michael 85 88 90 95 92

Grace 60 58 70 65 75

Sarah 30 45 35 40 50

Instructions:
1. Calculate Total Marks for each student.
2. Calculate the Average Marks.
3. Determine the Maximum and Minimum subject score for each student.
4. Calculate the Range (Max - Min) of scores for each student.
5. Assign Grade based on Average:
- A = 80–100
- B = 60–79
- C = 50–59
- D = 40–49
- F = Below 40
6. Set Status:
- 'Pass' if average ≥ 50, otherwise 'Fail'.
7. Calculate the Performance Percentage = (Total Marks / 500) * 100
8. Draw a Histogram of the performance percentage of the 5 students.
Question 2: Class Test Performance Evaluation
Scenario:
A teacher conducted a test for 6 students in 4 subjects and wants to analyze the results
using Google Sheets:

Student Physics Chemistry Biology Mathematics

James 65 70 75 60

Lydia 90 85 88 92

Peter 40 45 38 50

Anita 70 75 78 80

Kelvin 55 60 50 58

Halima 30 28 35 40

Instructions:
1. Calculate Total Marks for each student.
2. Calculate Average Marks.
3. Determine the Maximum and Minimum subject mark for each student.
4. Calculate the Range = Max - Min.
5. Assign a Grade based on Average:
- A = 85–100
- B = 70–84
- C = 55–69
- D = 40–54
- F = Below 40
6. Set Status: 'Pass' if average ≥ 50, else 'Fail'.
7. Calculate Performance Percentage = (Total Marks / 400) * 100
8. Draw a Histogram showing number of students per grade (A, B, C, D, F).
 How a Company Uses Microsoft Excel for Employees

Company Name: Bright Tech Solutions Ltd.

Department: Human Resources & Payroll

Scenario:
BrightTech Solutions Ltd. uses Microsoft Excel extensively in the Human Resources (HR)
department to manage employee data, payroll calculations, leave tracking, and performance
evaluations.

Excel Usage Example: Employee Payroll Sheet

Employee ID Name Basic Salary Allowance Deduction Net Salary

001 John Musa 1,200,000 150,000 50,000 =C2+D2-E2

002 Asha Mdee 950,000 100,000 30,000 =C3+D3-E3

003 David Kim 1,100,000 120,000 40,000 =C4+D4-E4

Features Used:
- Excel Formulas (e.g., `=C2+D2-E2`) to automate net salary calculation.
- Data Validation to ensure all salary inputs are accurate.
- Conditional Formatting to highlight any deductions over a certain threshold.
- Pivot Tables to summarize monthly payments by department.

Additional Excel Sheets Used:

- Leave Tracker Sheet — tracks annual leave, sick leave, and remaining days.
- Performance Review Sheet — averages monthly scores and visualizes performance using
bar charts and line graphs.
- Training Schedule Sheet — shows training sessions with time slots, using Excel’s calendar
formatting.

Benefits for Workers:

- Employees can access their salary breakdown and leave balances.
- Transparent performance evaluation.
- Organized scheduling for training and reviews.
- Faster, error-free payroll processing.
Sample Questions for Additional Excel Sheets Used in a Company

1. Leave Tracker Sheet

This sheet helps track different types of employee leave and calculates remaining leave
days.

Sample Questions:
- Create a table with the following columns: Employee Name, Annual Leave Entitled, Sick
Leave Entitled, Leave Taken (Annual), Leave Taken (Sick), Remaining Annual Leave,
Remaining Sick Leave.
- Use formulas to calculate remaining leave days for both annual and sick leave.
- Apply conditional formatting to highlight employees with less than 5 leave days remaining.
- Create a pie chart showing the percentage of leave used vs. leave remaining for one
employee.

2. Performance Review Sheet

This sheet evaluates monthly employee performance and calculates average scores.

Sample Questions:
- Create a table with the following columns: Employee Name, January Score, February Score,
March Score, April Score, Average Score.
- Use AVERAGE function to compute each employee’s average performance.
- Apply conditional formatting to highlight scores below 60%.
- Create a bar chart to compare average performance between employees.
- Create a line chart to show performance trend of a single employee across the months.

3. Training Schedule Sheet

This sheet organizes training sessions and tracks attendance.

Sample Questions:
- Create a table with the following columns: Training Topic, Trainer, Date, Time, Duration,
Number of Attendees.
- Use Excel’s date and time formatting to display schedule properly.
- Use Data Validation to create a dropdown list for training topics.
- Apply conditional formatting to highlight training sessions with less than 10 attendees.
- Create a calendar view to show training sessions across the month.
Examples and Solutions for Additional Excel Sheets Used in a Company

1. Leave Tracker Sheet

Example Table:

Employee Name | Annual Leave Entitled | Sick Leave Entitled | Leave Taken (Annual) |
Leave Taken (Sick) | Remaining Annual Leave | Remaining Sick Leave
John Musa | 28 | 10 |5 |2 | =B2-D2 | =C2-E2
Asha Mdee | 25 | 12 |8 |3 | =B3-D3 | =C3-E3

Solution: Use formula `=B2-D2` to get Remaining Annual Leave and `=C2-E2` for Remaining
Sick Leave.

2. Performance Review Sheet

Example Table:

Employee Name | January | February | March | April | Average Score

John Musa | 75 | 80 | 78 | 85 | =AVERAGE(B2:E2)
Asha Mdee | 65 | 60 | 58 | 70 | =AVERAGE(B3:E3)

Solution: Use the AVERAGE function to calculate performance: `=AVERAGE(B2:E2)`.

Bar Chart: Insert -> Chart -> Bar -> Select employee names and their average scores.

Line Chart: Insert -> Chart -> Line -> Select one employee’s monthly scores.

3. Training Schedule Sheet

Example Table:

Training Topic | Trainer | Date | Time | Duration | Number of Attendees

Excel Basics | Jane Doe | 04/06/2025 | 10:00 | 2 hrs | 15
HR Policies | David John | 05/06/2025 | 14:00 | 3 hrs | 8

Solution:
- Format date and time: Select cells -> Format -> Date/Time.
- Conditional formatting: Highlight sessions with attendees < 10.
- Calendar view: Create a monthly layout and use cell references to fill in training dates.
 Google Sheets: Overview, Uses, and
Importance in Relation to Microsoft
Excel

Brief Explanation
Google Sheets is a free, cloud-based spreadsheet application provided by Google. It allows
users to create, edit, and collaborate on spreadsheets online in real time. Similar to Microsoft
Excel, it supports formulas, charts, pivot tables, and conditional formatting, but with a strong
focus on online collaboration and accessibility from any device with internet access.

Uses of Google Sheets

 Data Entry and Management - Organize lists, inventories, schedules, and records.
 Calculations and Analysis - Perform basic to advanced mathematical, statistical, and
logical calculations using formulas.
 Data Visualization - Create charts, graphs, and dashboards.
 Collaboration and Sharing - Multiple users can edit simultaneously with comments and
version history.
 Data Collection - Linked with Google Forms for collecting and storing data directly into a
sheet.
 Task and Project Management - Track tasks, deadlines, and progress collaboratively.
 Automated Workflows - Use Google Apps Script or tools like Zapier to automate
processes.
 Importing/Exporting Data - Import from CSV, Excel, or other online sources; export to
various formats.

Importance of Google Sheets in Relation to Microsoft Excel

Aspect Importance/Benefit in Excel Context
Compatibility Google Sheets can open, edit, and save Excel files
(.xlsx), enabling cross-platform work.
 Real-Time Collaboration Unlike Excel, which needs OneDrive or SharePoint
for real-time editing, Google Sheets offers built-in,
seamless collaboration.
Cloud-Based No installation needed. Accessible anywhere, ideal
for remote work or mobile use.
Auto-Save & Version History Changes are saved instantly with a history log —
helps reduce data loss.
Integration Easily connects with other Google Workspace tools
(Docs, Forms, Gmail, etc.) for streamlined
workflows.
Free to Use Google Sheets is free with a Google account, while
Excel typically requires a Microsoft 365
subscription.

Summary
Google Sheets is a powerful, web-based spreadsheet tool that complements Microsoft Excel
by offering better collaboration, cloud access, and integration with other online tools. While
Excel is more powerful for advanced data modeling and analytics, Google Sheets is excellent
for team projects, accessibility, and cost-effective solutions.

Excel/Google Sheets Practice Questions

Question 1: Student Exam Scores Analysis
Scenario:
You are given the marks of 5 students in 5 subjects. Using Google Sheets:

Name Math English Kiswahili Science Social

Studies

John 75 65 80 90 85

Amina 45 60 55 70 50

Michael 85 88 90 95 92

Grace 60 58 70 65 75

Sarah 30 45 35 40 50

Instructions:
1. Calculate Total Marks for each student.
2. Calculate the Average Marks.
3. Determine the Maximum and Minimum subject score for each student.
4. Calculate the Range (Max - Min) of scores for each student.
5. Assign Grade based on Average:
- A = 80–100
- B = 60–79
- C = 50–59
- D = 40–49
- F = Below 40
6. Set Status:
- 'Pass' if average ≥ 50, otherwise 'Fail'.
7. Calculate the Performance Percentage = (Total Marks / 500) * 100
8. Draw a Histogram of the performance percentage of the 5 students.

Question 2: Class Test Performance Evaluation

Scenario:
A teacher conducted a test for 6 students in 4 subjects and wants to analyze the results
using Google Sheets:

Student Physics Chemistry Biology Mathematics

James 65 70 75 60

Lydia 90 85 88 92

Peter 40 45 38 50

Anita 70 75 78 80

Kelvin 55 60 50 58

Halima 30 28 35 40

Instructions:
1. Calculate Total Marks for each student.
2. Calculate Average Marks.
3. Determine the Maximum and Minimum subject mark for each student.
4. Calculate the Range = Max - Min.
5. Assign a Grade based on Average:
- A = 85–100
- B = 70–84
 - C = 55–69
- D = 40–54
- F = Below 40
6. Set Status: 'Pass' if average ≥ 50, else 'Fail'.
7. Calculate Performance Percentage = (Total Marks / 400) * 100
8. Draw a Histogram showing number of students per grade (A, B, C, D, F).

How a Company Uses Microsoft Excel for Employees

Company Name: Bright Tech Solutions Ltd.

Department: Human Resources & Payroll

Scenario:
BrightTech Solutions Ltd. uses Microsoft Excel extensively in the Human Resources (HR)
department to manage employee data, payroll calculations, leave tracking, and performance
evaluations.

Excel Usage Example: Employee Payroll Sheet

Employee ID Name Basic Salary Allowance Deduction Net Salary

001 John Musa 1,200,000 150,000 50,000 =C2+D2-E2

002 Asha Mdee 950,000 100,000 30,000 =C3+D3-E3

003 David Kim 1,100,000 120,000 40,000 =C4+D4-E4

Features Used:
- Excel Formulas (e.g., `=C2+D2-E2`) to automate net salary calculation.
- Data Validation to ensure all salary inputs are accurate.
- Conditional Formatting to highlight any deductions over a certain threshold.
- Pivot Tables to summarize monthly payments by department.

Additional Excel Sheets Used:

- Leave Tracker Sheet — tracks annual leave, sick leave, and remaining days.
- Performance Review Sheet — averages monthly scores and visualizes performance using
bar charts and line graphs.
- Training Schedule Sheet — shows training sessions with time slots, using Excel’s calendar
formatting.

Benefits for Workers:

- Employees can access their salary breakdown and leave balances.
- Transparent performance evaluation.
- Organized scheduling for training and reviews.
- Faster, error-free payroll processing.

Sample Questions for Additional Excel Sheets Used in a Company

1. Leave Tracker Sheet

This sheet helps track different types of employee leave and calculates remaining leave
days.

Sample Questions:
- Create a table with the following columns: Employee Name, Annual Leave Entitled, Sick
Leave Entitled, Leave Taken (Annual), Leave Taken (Sick), Remaining Annual Leave,
Remaining Sick Leave.
- Use formulas to calculate remaining leave days for both annual and sick leave.
- Apply conditional formatting to highlight employees with less than 5 leave days remaining.
- Create a pie chart showing the percentage of leave used vs. leave remaining for one
employee.

2. Performance Review Sheet

This sheet evaluates monthly employee performance and calculates average scores.

Sample Questions:
- Create a table with the following columns: Employee Name, January Score, February Score,
March Score, April Score, Average Score.
- Use AVERAGE function to compute each employee’s average performance.
- Apply conditional formatting to highlight scores below 60%.
- Create a bar chart to compare average performance between employees.
- Create a line chart to show performance trend of a single employee across the months.

3. Training Schedule Sheet

This sheet organizes training sessions and tracks attendance.

Sample Questions:
- Create a table with the following columns: Training Topic, Trainer, Date, Time, Duration,
Number of Attendees.
- Use Excel’s date and time formatting to display schedule properly.
- Use Data Validation to create a dropdown list for training topics.
- Apply conditional formatting to highlight training sessions with less than 10 attendees.
- Create a calendar view to show training sessions across the month.

Examples and Solutions for Additional Excel Sheets Used in a Company

1. Leave Tracker Sheet

Example Table:

Employee Name | Annual Leave Entitled | Sick Leave Entitled | Leave Taken (Annual) |
Leave Taken (Sick) | Remaining Annual Leave | Remaining Sick Leave
John Musa | 28 | 10 |5 |2 | =B2-D2 | =C2-E2
Asha Mdee | 25 | 12 |8 |3 | =B3-D3 | =C3-E3

Solution: Use formula `=B2-D2` to get Remaining Annual Leave and `=C2-E2` for Remaining
Sick Leave.

2. Performance Review Sheet

Example Table:

Employee Name | January | February | March | April | Average Score

John Musa | 75 | 80 | 78 | 85 | =AVERAGE(B2:E2)
Asha Mdee | 65 | 60 | 58 | 70 | =AVERAGE(B3:E3)

Solution: Use the AVERAGE function to calculate performance: `=AVERAGE(B2:E2)`.

Bar Chart: Insert -> Chart -> Bar -> Select employee names and their average scores.

Line Chart: Insert -> Chart -> Line -> Select one employee’s monthly scores.

3. Training Schedule Sheet

Example Table:

Training Topic | Trainer | Date | Time | Duration | Number of Attendees

Excel Basics | Jane Doe | 04/06/2025 | 10:00 | 2 hrs | 15
HR Policies | David John | 05/06/2025 | 14:00 | 3 hrs | 8
Solution:
- Format date and time: Select cells -> Format -> Date/Time.
- Conditional formatting: Highlight sessions with attendees < 10.
- Calendar view: Create a monthly layout and use cell references to fill in training dates.
 Pivot Table katika Excel
✅ Maana ya Pivot Table:
Pivot Table ni jedwali la muhtasari (summary table) linalokuwezesha:
- Kuchuja (Filter) data
- Kujumlisha (Sum) data
- Kuainisha (Group) data (mfano: kwa mwezi, kwa mkoa, kwa bidhaa)
- Kufanya uchambuzi (Analyze) haraka bila kuandika formula ngumu

🧠 Mfano Rahisi:
Una data ya mauzo kama hii:

Tarehe Mkoa Bidhaa Mauzo

Jan Arusha Sukari 500

Jan Arusha Unga 700

Jan Dodoma Sukari 600

Feb Arusha Sukari 400

Kwa kutumia Pivot Table, unaweza kupata muhtasari kama huu:

Pivot Table: Mauzo kwa Mwezi na Mkoa

Mkoa Bidhaa Jan Feb

Arusha Sukari 500 400

Arusha Unga 700

Dodoma Sukari 600

⭐ Umuhimu wa Pivot Table kwenye Excel:

1. Inaokoa muda: Hutakiwi kuandika formula nyingi.
2. Inaboresha uchambuzi: Unaweza kubadili mtazamo wa data kwa haraka (pivot).
3. Inasaidia kutengeneza ripoti: Kwa haraka na kitaalamu.
4. Rahisi kutumia: Haikuhitaji kuwa mtaalamu wa Excel ili kuitumia.
5. Inasaidia kufanya maamuzi: Kwa kuwa inafanya data iwe wazi na inayoeleweka haraka.