Advanced MS Word Practical Assignment (20+ Integrated Tasks)

A complete, classroom-ready practice module with examples, sub-tasks, and skill-building

challenges.

Task 1: Create, Save & Organize a New Document

Objective: Learn file creation, naming, and saving.

Instructions:

1. Open Microsoft Word.

2. Create a Blank Document.

3. Type a 10–12 line paragraph about any personal interest or hobby.

4. Save the file as Hobby_YourName.docx inside a folder named MS Word Practice.

Mini-Task: Save another copy using Save As → Hobby_YourName_v2.docx.

Task 2: Apply Professional Font Formatting

Objective: Use bold, italic, underline, and mixed formatting.

Instructions:
Type the sentence:
“The quick brown fox jumps over the lazy dog near the river.”

• Make the first 4 words Bold

• Make the next 4 words Italic

• Make the last 4 words Bold + Underline

Mini-Task: Change the font to Calibri, size 14.

Task 3: Customize Font Style, Size & Color

Objective: Practice text styling.

Instructions:

1. Type your full name.

 2. Apply:

o Font: Arial

o Size: 20

o Color: Dark Blue

3. Add a Text Highlight Color of your choice.

Mini-Task: Add a shadow effect using Text Effects → Shadow.

Task 4: Master Text Alignment

Objective: Use all alignment types.

Instructions:
Type a 4-line paragraph about “Why computer skills are important.”
Then apply:

• Line 1 → Left Align

• Line 2 → Center Align

• Line 3 → Right Align

• Line 4 → Justify
Mini-Task: Adjust line spacing to 1.5.

Task 5: Create Bullet & Symbol Lists

Objective: Use bullets, symbols, and custom icons.

Instructions:
Create a bulleted list of 6 favorite foods.
Then:

• Change bullet style to Symbols → Wingdings

• Increase indent for items 4–6

Mini-Task: Add a sub-bullet under any one item.

Task 6: Insert & Format a Table (3×4)

Objective: Work with tables, borders, and shading.
Instructions:
Insert a 3×4 table with columns:
Name | Subject | Marks
Fill with any 3 students.
Apply:

• Header row → Bold + Light Gray Shading

• Borders → Thick Outside Border, Thin Inside Borders

Mini-Task: Center-align all marks.

Task 7: Merge & Split Cells

Objective: Learn cell merging and splitting.

Instructions:
Create a table like this:

Employee Details

Name Age Department

Sarah 30 Marketing

Tom 26 IT

Steps:

• Merge the top row into one cell.

• Split the Age cell for Sarah into two cells and type “30” and “Years”.
Mini-Task: Shade the header row.

Task 8: Page Layout – Margins, Orientation & Spacing

Objective: Control page formatting.

Instructions:

1. Change margins to Narrow.

2. Change orientation to Landscape.

 3. Insert a small 2×2 table of student grades.
Mini-Task: Add Page Color → Light Yellow.

Task 9: Insert a Page Break

Objective: Separate content across pages.

Instructions:

1. Type a 5-line paragraph.

2. Insert Page Break.

3. Type another paragraph on the new page.

Mini-Task: Add a Heading above each paragraph.

Task 10: Apply Page Borders

Objective: Decorate the page professionally.

Instructions:
Apply a Double-Line Page Border with:

• Color: Dark Blue

• Width: 2¼ pt
Mini-Task: Apply border to first page only.

Task 11: Insert Header & Footer

Objective: Add document identity.

Instructions:

• Header → Type your Full Name

• Footer → Insert Date (Auto-Update)

Mini-Task: Add Page Number → Bottom Center.

Task 12: Insert & Resize an Image

Objective: Work with images.

Instructions:
 1. Insert any picture.

2. Resize it to 3" × 3".

3. Apply Picture Style → Simple Frame.

Mini-Task: Add a caption below the image.

Task 13: Wrap Text Around an Image

Objective: Control text flow.

Instructions:
Insert an image and apply:

• Wrap Text → Tight

• Move the image to the right side of the paragraph.

Mini-Task: Try Wrap Text → Behind Text and observe the difference.

Task 14: Use Find & Replace

Objective: Edit documents quickly.

Instructions:
Type:
“Modern computer technology is changing the world of computer education.”
Replace computer → laptop using Ctrl + H.
Mini-Task: Replace technology → innovation.

Task 15: Check Spelling & Grammar

Objective: Use proofreading tools.

Instructions:
Type the incorrect sentence:
“Ths is an exmple of gramar cheker in wrd.”
Run Spelling & Grammar check.
Mini-Task: Turn on Editor → Clarity Suggestions.

Task 16: Insert a Hyperlink

Objective: Add clickable links.
Instructions:
Insert a hyperlink to:
[Link]
Mini-Task: Create a hyperlink to a file on your computer.

Task 17: Save & Export as PDF

Objective: Export documents.

Instructions:
Save your document as:
MSWord_Practice_YourName.pdf
Mini-Task: Compare file size of DOCX vs PDF.

Task 18: Format Text into Columns

Objective: Create newspaper-style layouts.

Instructions:
Type a 12-line paragraph about “The Future of Technology”.
Convert it into Two Columns.
Mini-Task: Add a Line Between Columns.

Task 19: Create a Numbered List

Objective: Use numbering styles.

Instructions:
List 5 places you want to visit, each with a short description.
Mini-Task: Change numbering style to Roman Numerals (I, II, III).

Task 20: Final Page Setup

Objective: Combine layout skills.

Instructions:

• Orientation → Portrait

• Margins → Moderate
 • Add Header → Assignment: MS Word Practice
Mini-Task: Insert a Watermark → Confidential.

MAIL MERGING IN MS WORD – COMPLETE NOTES

Mail Merge is a feature in Microsoft Word that allows you to create multiple personalized
documents (letters, certificates, envelopes, ID cards, labels, emails) using one main
document and one data source.

It saves time and ensures accuracy when sending the same document to many people.

What is Mail Merge?

Mail Merge is a process of combining:

• Main Document → The template (letter, certificate, notice, etc.)

• Data Source → A list of information (names, addresses, marks, etc.)

Word automatically inserts the data into the document to create personalized copies for
each entry.

Examples of Where Mail Merge Is Used

• Sending letters to many people

• Creating certificates for students

• Printing address labels

• Generating ID cards

• Sending personalized emails

• Creating fee receipts or mark sheets

Components of Mail Merge

Mail Merge has three main parts:

A. Main Document

This is the template you prepare.

Examples:

• Invitation letter

• Certificate format

• Fee reminder notice

B. Data Source

This contains the variable information.

It can be created using:

• MS Excel

• MS Word table

• Access database

• CSV file

Example fields:

• Name

• Class

• Roll Number

• Address

• Marks

C. Merge Fields

These are placeholders inserted into the main document.

Examples:

• «Name»

• «Address»

• «Roll_No»

• «Marks»
 Steps of Mail Merge (Very Important)

Step 1: Open MS Word

Create or open the main document (letter, certificate, etc.)

Step 2: Go to Mailings Tab

Click Mailings → Start Mail Merge → Step-by-Step Mail Merge Wizard

(This wizard makes the process easier.)

Step 3: Select Document Type

Choose what you want to create:

• Letters

• Envelopes

• Labels

• Email messages

• Directory

Step 4: Select Recipients

Choose your data source:

• Use an existing list (Excel file)

• Select from Outlook contacts

• Type a new list

If using Excel, select the sheet and confirm the table.

Step 5: Insert Merge Fields

Place your cursor where you want the data to appear.

Click: Insert Merge Field → Choose field (Name, Address, etc.)

Example:

Dear «Name»,

Your marks for the exam are «Marks».

Step 6: Preview Results

Click Preview Results to see how each document looks.

Step 7: Finish & Merge

Click: Finish & Merge → Print Documents / Edit Individual Documents

Word will generate all personalized copies.

Example of a Mail Merge Letter

Main Document Template:

Dear «Name»,

This is to inform you that your fee for the month of «Month» is pending.

Your total due amount is «Amount».

Please clear the dues at the earliest.

Thank you,

School Administration

Data Source (Excel):

Name Month Amount

John January 1500

Asha January 1500

Peter January 1500

Mail Merge will create 3 personalized letters.

Types of Documents You Can Create with Mail Merge

1. Letters
Personalized letters for many people.

2. Envelopes

Automatically print names and addresses.

3. Labels

Useful for bulk mailing or product labels.

4. Email Messages

Send customized emails to many recipients.

5. Directory

Creates a list (e.g., student directory, phone list).

Advantages of Mail Merge

• Saves time

• Reduces errors

• Creates personalized documents

• Easy to update data

• Works with Excel for large datasets

Common Mail Merge Fields

• «First_Name»

• «Last_Name»

• «Address»

• «City»

• «Phone»

• «Email»

• «Roll_No»

• «Marks»
 Tips for Perfect Mail Merge

• Always keep Excel data clean (no empty rows).

• Use proper column headings.

• Avoid merged cells in Excel.

• Save your files before merging.

• Preview before printing.

Short Exam Questions (with Answers)

Q1: What is Mail Merge?

Mail Merge is a feature in MS Word used to create multiple personalized documents using
one main document and one data source.

Q2: Name the three components of Mail Merge.

Main Document, Data Source, Merge Fields.

Q3: Which tab contains Mail Merge options?

The Mailings tab.

Q4: Can Excel be used as a data source?

Yes, Excel is the most commonly used data source.

Q5: What is the purpose of merge fields?

They act as placeholders for data from the data source.

Would you like me to also create:

• A Mail Merge practical assignment

• A Mail Merge step-by-step PDF layout

• A Mail Merge MCQ set

• A Mail Merge diagram / flowchart

KNEC/CDACC PRACTICAL EXAM

MS WORD PRACTICAL TEST

Duration: 1 Hour 30 Minutes

Instructions to Candidates:

1. Create and save your document as MSWORD_PRACTICAL_YOURNAME.docx.

2. Type the following document exactly as it appears.

3. Apply appropriate formatting and page layout features.

4. Insert:

o Headings using Heading Styles

o Automatic Table of Contents

o Automatic List of Figures

o Automatic List of Tables

o Distinctive Page Numbering

5. Save your work regularly.

TASK REQUIREMENTS

The final document should contain:

• At least 10 headings using Heading styles (Heading 1, Heading 2, Heading 3,

Heading 4).

• 5 figures with captions.

• 5 tables with captions.

• Automatic:

o Table of Contents

o List of Figures

o List of Tables

• Different page numbering styles:

 o Roman numerals for preliminary pages.

o Arabic numerals for main content pages.

CONTENT TO TYPE

DIGITAL LEARNING IN MODERN EDUCATION

Table of Contents

(Insert automatic TOC here)

List of Figures

(Insert automatic List of Figures here)

List of Tables

(Insert automatic List of Tables here)

CHAPTER ONE: INTRODUCTION

1.1 Background of Digital Learning

Digital learning refers to the use of technology in teaching and learning processes. Schools
and colleges use computers, projectors, and internet resources to improve learning
experiences.

1.1.1 Growth of Technology in Education

Many institutions now use online platforms for assignments, examinations, and
communication between students and teachers.

[Link] Benefits of E-Learning

E-learning allows learners to access notes and lessons at any time and from any location.

1.2 Objectives of Digital Learning

The objectives include improving accessibility, increasing flexibility, and supporting

interactive learning methods.
Table 1: Objectives of Digital Learning

Objective Description

Accessibility Learning from any location

Flexibility Learning at convenient time

Interaction Better communication

1.2.1 Importance of Online Resources

Online resources help learners access books, journals, and research materials quickly.

Figure 1: Online Learning Illustration

(Insert any suitable online learning image here)

CHAPTER TWO: DIGITAL TOOLS

2.1 Learning Management Systems

Learning Management Systems help teachers manage courses, assignments, and student
records.

Table 2: Examples of LMS Platforms

Platform Main Use

Moodle Online learning

Google Classroom Assignment management

Canvas Course management

2.1.1 Communication Tools

Communication tools improve interaction among learners and instructors.

Figure 2: Video Conferencing Tool

(Insert suitable communication image here)

2.2 Internet Usage in Education

Internet services support online research, video tutorials, and collaboration.

Table 3: Internet Services Used in Learning

Service Purpose

Email Communication

Search Engines Research

Cloud Storage File sharing

2.2.1 Challenges Facing Internet Learning

Some learners experience poor connectivity and limited access to devices.

[Link] Security Concerns

Cybersecurity threats may affect online learning systems.

Figure 3: Cyber Security Illustration

(Insert suitable cybersecurity image here)

CHAPTER THREE: COMPUTER LAB MANAGEMENT

3.1 Importance of Computer Laboratories

Computer laboratories provide students with practical experience in technology.

Table 4: Computer Lab Rules

Rule Purpose

No food allowed Maintain cleanliness

Handle devices carefully Prevent damage

Save work regularly Avoid data loss

3.1.1 Maintenance of Computers

Regular updates and cleaning improve computer performance.

Figure 4: Computer Laboratory

(Insert suitable computer lab image here)

3.2 Safety Precautions

Users should follow safety measures while using computer equipment.

Table 5: Safety Measures

Safety Measure Benefit

Proper wiring Prevent accidents

Antivirus software Improve security

Regular backups Prevent data loss

3.2.1 Environmental Protection

Electronic waste should be disposed of responsibly.

Figure 5: E-Waste Management

(Insert suitable e-waste image here)

CHAPTER FOUR: CONCLUSION

4.1 Summary

Digital learning has transformed education by improving accessibility and communication.

4.1.1 Recommendations

Institutions should invest in better internet connectivity and modern computer

laboratories.

[Link] Final Remark

Technology will continue playing a major role in the future of education.

PRACTICAL TASKS TO PERFORM

1. Create a cover page.

2. Apply Heading Styles correctly.

3. Insert Automatic Table of Contents.

4. Insert Automatic List of Figures.

5. Insert Automatic List of Tables.

6. Insert page borders.

7. Use:

o Roman numerals (i, ii, iii) for preliminary pages.

o Arabic numbering (1,2,3…) for main document.

8. Insert suitable images for all figures.

9. Apply professional formatting:

o Font size 12

o Line spacing 1.5

o Justified paragraphs

10. Save and print preview the document.

Microsoft Excel Practical Exercises

Detailed Instructions and Structured Tasks

MICROSOFT EXCEL
PRACTICAL EXERCISE 1.1: Summing Data in a Range
Using the data provided, calculate the sum of all the figures within the specified range. The
data is organized in columns A through G, and the relevant entries are as follows:

A B C D E F G
1 Mon Tue Wed Thur Fri TOTAL
2 Breakfast 3,560 3,186 2,952 3,395 3,436
3 Lunch 20,163 21,416 19,912 19,681 18,628
4 Bar 9,873 12,172 12,642 12,711 18,846
5 Snacks 2,405 3,544 2,694 3,120 3,712
6 TOTALS

Sum all the figures for each category and calculate the overall total for the week.

PRACTICAL EXERCISE 1.2: Data Entry and Formatting

Enter the given data into a worksheet. The data includes columns labeled A through E. The
main entries are:

A B C D E
1 Stationery Supplies Ltd
3 Date SalesPerson Item Receipt No Amount
4 21-Nov Carl Toys 1238 1,782.10
5 26-Nov Carl Stationery 1255 4,853.55
6 26-Nov Carl Toys 1395 51.35
7 Carl’s Total
8 21-Nov John Cards 1141 91.15
9 24-Nov John Books 1982 442.60
10 21-Nov John Toys 1885 561.50
11 26-Nov John Toys 1875 62.75
12 John’s Total
13 22-Nov Judy Books 1032 234.50
14 26-Nov Judy Sports goods 1920 472.60
15 Judy’s Total
16 25-Nov Mary Toys 1774 364.15
17 Mary’s Total
18 22-Nov Susan Electronics 1160 52.95
19 23-Nov Susan Cards 1075 81.60
20 23-Nov Susan Others 1745 132.95
21 24-Nov Susan Sports goods 1662 2,580.10
22 Susan’s Total
24 Grand Total

(i) Calculate the totals for each salesperson and determine the grand total.

(ii) Format the worksheet so that all the Totals are bold, set to two decimal places, with
commas as thousand separators. Center the title across columns A-E, and set its font size
to 16, bold, and italic.
(iii) Apply a double border around the entire table and a single line border inside the table.

(iv) Save the worksheet using the name Stationery Analysis.

PRACTICAL EXERCISE 1.3: Calculating Employee Payments

Using the provided table, calculate the total amount payable by the company to its
employees. The table is organized in columns A through E and includes:

A B C D E
1 Services Company Ltd
2 Overtime Details
3 Date Name Hours Worked Rate Amount
4 26-Nov Kennedy 5 70 350.00
5 26-Nov Kennedy 5 100 500.00
6 26-Nov Mary 5 100 500.00
7 26-Nov Lewis 4 100 400.00
8 30-Nov Judy 3 100 300.00
9 30-Nov Kennedy 6 70 420.00
10 30-Nov Lewis 5 100 500.00
11 30-Nov Kennedy 4 70 280.00
12 30-Nov Judy 5 100 500.00
13 30-Nov Lewis 5 100 500.00
14 02-Dec Judy 4 70 280.00
15 Total Amount

Sum the amounts for all employees to determine the total amount payable.

PRACTICAL EXERCISE 1.4: Payroll Calculations Using Formulas

A payroll consists of several components: Basic Pay, Allowances, Gross Salary,
Deductions, and Net Salary. The worksheet should be organized in columns A through F,
listing each employee's details.

Allowances are calculated as 23% of Basic Pay, while Deductions are 12% of Gross Salary.
For each cell, indicate whether a value or formula is entered; if a formula, write down the
formula in the respective cell.

AB C D E F
1 Stationery Supplies Ltd
3 Name Basic Pay Allowances Gross Salary Deductions Net Salary
4 Lewis
5 Francis
6 Edwin
7 Jane
8 Totals

For each employee, indicate in each cell if it is a value or formula. Write the formula in the
cell when required.

PRACTICAL EXERCISE 1.5: Updating Payroll with Salary Increments

As the Accountant for Stationery Supplies Ltd, you are provided with the current payroll in
the workbook titled "OLD PAYROLL." The payroll details are listed below:

A B C D E F
1 Stationery Supplies Ltd.
3 Name Basic Pay Allowances Gross Salary Deductions Net Salary
4 Lewis 15,791 3,137 18,928 1,256 17,672
5 Francis 15,537 3,061 18,598 776 17,822
6 Edwin 15,506 3,051 18,557 999 17,558
7 Bernard 15,417 3,025 18,442 1,099 17,343
8 George 15,008 2,902 17,910 718 17,192
9 Albert 14,969 2,890 17,859 846 17,013
10 Edward 14,651 2,795 17,446 760 16,686
11 Cornell 14,618 2,785 17,403 663 16,740
12 John 14,553 2,765 17,318 558 16,760
13 Carl 14,508 2,752 17,260 706 16,554
15 Totals 150,558 29,163 179,721 8,381 171,340

The salary review information provided in the workbook "INCREMENT" includes each
employee's current pay and the percentage increase to be applied:

A B C
1 Name Current Pay % Increase
2 Lewis 15,791 19%
3 Francis 15,537 19%
4 Edwin 15,506 22%
5 Bernard 15,417 18%
6 George 15,008 21%
7 Albert 14,969 17%
8 Edward 14,651 15%
9 Cornell 14,618 25%
10 John 14,553 19%
11 Carl 14,508 20%
13 Allowances
14 20%

Required: Using formulas, update the payroll with the specified changes in a new blank
worksheet. The updated worksheet is located in the workbook named NEW PAYROLL.

PRACTICAL EXERCISE 1.6

Business Financial Projection Model
You are managing a young and growing business, and you have identified the various
factors that influence its operations. These factors include sources of revenue and
expenses and are outlined in the table below. Using the figures provided and the specified
layout, your task is to create a financial projection model for the business covering the next
six years. The parameters required for these calculations are listed on Sheet 2

Sheet 1:

INCOME AND EXPENSES PROJECTIONS

1999 1999 2000 2001 2002 2003 2004
Sales 10,000
% Growth over the previous 20% 30% 20% 10% 10%
year
Materials
Wages
Other benefits
Others
Total Cost of Goods Sold
Salary: Office
Salary: Sales
Other Benefits
Advertising & Promotions
Depreciation
Miscellaneous
Total General & Admin.
Expenses
Total Operating Costs
Interest on Loans
Pre-tax Income
Tax
Profit
Sheet 2:

Parameter Value/Rate Description

Profit
Sales 10,000 Starts at 10,000 and grows by a percentage
Materials 17% 17% of Sales
Wages 14% 14% of Sales
Other benefits 2.1% 2.1% of Sales
Others 8% Starts at 100, then grows by 8% yearly
Salary: Office 10% Starts at 1,000, then grows by 10% annually
Salary: Sales 8% 8% of Sales
Other Benefits 17% 17% of Total Salary
Advertising & 2.5% 2.5% of Sales
Promotions
Depreciation 20 Fixed at 20 every year
Miscellaneous 10 Starts at 10 and grows by a fixed amount of 10
annually
Interest on Loans 10 A fixed amount of 10 each year
Tax 52% 52% of Pre-tax Income

Exercise Instructions
1. Open the worksheet named Income and Expenses [Link].

2. Rename Sheet1 as Projections and Sheet 2 as Parameters.

3. Calculate the Sales for the year 2000 using the percentage given in cell C5.

4. Copy the sales formula across to the year 2004.

5. Calculate the different items that make up the Total Operating Costs using the
parameters from the Parameters sheet.

6. Enter the formula for the year 1999 and copy it down to the year 2004. Use Absolute
Referencing effectively.

7. Hint: Total Cost of Goods Sold is the sum of Materials, Wages, Other Benefits, and
Others.

8. Calculate the Total Operating Costs by adding Total Cost of Goods Sold and Total
General and Administrative Expenses.

9. Calculate the Interest on Loans as a fixed amount each year.

 10. Determine the Pre-tax Income by subtracting Total Operating Cost and Interest on
Loans from Sales.

11. Calculate the Tax based on the Pre-tax Income.

12. Calculate the Profit by subtracting Tax from Pre-tax Income.

13. Format the worksheet as follows:

14. Make all Totals bold, apply zero decimal places, add comma formatting.

15. Center the heading between columns A1 and G1, set the heading font size to 16 and
make it bold.

16. Save the file as C:\Exams\[Link].

PRACTICAL EXERCISE 1.7

From the data given in the table below, create a Pie Chart to show the distribution of the
total amount amongst the various salesmen.

Salesman Qtr1 Qtr2 Qtr3 Qtr4 Total

Albert 148 156 171 140 615
Carl 122 131 153 118 524
Cornell 211 243 246 250 950
Edwin 129 150 92 218 589
Francis 311 270 247 322 1,150

Practical Exercise 1.8

SALESPERS ANNU QTR QTR QTR QTR TARG TOTA AVERA COMMISSI
ON AL 1 2 3 4 ET L GE ON
TARGE SALE SALES
T S
ALBERT 750 148 256 133 154 X 6 7
MICHAEL 650 187 143 258 143 X 8
CARL 800 233 200 216 152 X 9
GEORGE 700 256 145 136 259 X 10
LUCY 1,000 249 212 215 124 X 11
TOTAL X X X X X X 12
COMMISSI
ON: 6%

Instructions;
 • To calculate Total Sales, use the formula =SUM(B2:F2) for each row and
=SUM(G2:G11) for Row 12.

• To find Average Sales, use =AVERAGE(B2:F2) and drag the formula down for other
rows.

• To calculate Commission at 6%, use =G2*6% and drag the formula down for other
rows.

• To insert two rows above Row 10, right-click Row 10 and select "Insert" twice.

• The sign ######### means the column is too narrow; increase column width to fix.

• To delete an entire row or column, right-click the row or column header and select
"Delete."

• To compare QTR1 and QTR3 sales, select the cells for QTR1 and QTR3 data for all
salespersons.

• Use a column chart to compare QTR1 and QTR3 sales.

• To edit a chart, select the chart and use options to change series, legend, etc.

PRACTICAL EXERCISE 1.9

TOWN/DAY Mon Tue Wed Thur Fri F

Mombasa 30 29.5 31 28.5 32
Kisumu 31 33 30 30 32
Nakuru
Nairobi 24 23.5 22 23 24.5
TOWN/DAY Mon Tue Wed Thur Fri F
Mombasa
Kisumu
Nakuru
Nairobi

Questions:

• Use a formula to determine the temperature for Nakuru, given that Nakuru's
temperature is ¾ that of Mombasa.

• Insert two blank rows below Row 1.

• In Cell A2, type “Temp. in degrees Celsius.” In Cell A9, type “Temp. in degrees
Fahrenheit.”
 • Calculate the temperature in degrees Fahrenheit, using the conversion formula: F =
(C + 19) × 9 / 5.

• Add a blank row before Nakuru, and enter the row heading Kericho in both
temperature versions.

• Compute the temperature for Kericho in both versions, given that it is 4/5 the
temperature of Kisumu.

• Type “Average Temp. (C)” in Cell G3 and “Average Temp. (F)” in Cell G11.

• Calculate the average temperatures for all towns.

• Format all cells containing temperature values to zero decimal places, without
commas.

• Format all cells containing average temperature values with Arial Black, Italics, size
12 font.

• Center the titles across columns A through G.

• Apply a double border around the entire table, a single line border inside the table,
and shade the Average Temperature column gray.

• Use the Average values (C) in column G to create a 3-D Exploded Pie Chart showing
the temperature distribution for the towns. The chart title should be “Average Temp.
(C),” use the text in column A as the legend, and select “Show Value” for data labels.

• Move the created chart to Sheet3; do not insert it as an object.

• Position the chart so its top left corner is at cell A7 in Sheet3, and resize the chart to
fit within the range A7:H20.

• Save the worksheet and name it “Weather.”

PRACTICAL EXERCISE 1.10

Worksheet Tasks: January Expenses vs. Budget

Expense Category Budget Actual

Salaries and Wages 156,675.00 145,200.00

Rent 4,300.00 4,300.00

Electricity 1,000.00 1,207.00

Telephone 200.00 142.00

Advertisements 20,000.00 18,550.00

Freight and Clearing 15,650.00 13,400.00

Security 3,800.00 3,800.00

Instructions
• Insert a new column between the Budget and Savings columns.

• In cell C3, enter the heading Actual.

• Fill the new Actual column with the following figures:

Expense Category Actual (KES)

Salaries and Wages 145,200
Rent 4,300
Electricity 1,207
Telephone 142
Advertisements 18,550
Freight and Clearing 13,400
Security 3,800

• Calculate the Savings in cells D4:D10 by subtracting Actual from Budget.

• Format the sheet title (Expenses for the Month of January) to Arial Black, size 14,
Bold.

• Save the file as Audit 1.

• Format the range B4:D10 to display numbers with two decimal places.

• Adjust column C so all values are fully visible.

• Add the heading Savings % in cell E3, and calculate the savings as a percentage of
the budget.

• Format the range E4:E10 as percentages.

• In cell A12, enter the row title Total and calculate totals for Budget, Actual, and
Savings columns.
 • Copy the formula in E10 to E12 for total savings percentage.

• Format the new heading to match the existing headings and align it to the right.

• Save and close the worksheet.

PRACTICAL EXERCISE 1.11

Year Sales % Growth Materials Wages Other Others Total Cost

Over Previous Benefits of Goods
Year Sold
1999 10,000 1,700 1,400 210 100
2000 12,000 20% 2,040 1,680 252 108
2001 15,600 30% 2,652 2,184 328 117
2002 18,720 20% 3,182 2,621 393 126
2003 20,592 10% 3,501 2,883 432 136
2004 22,651 10% 3,851 3,171 476 147

Questions;

1. Insert five rows above Row 1.

2. Reduce the size of the picture to a Height of 0.96” by a Width of 1.66” (inches).

3. Drag and drop the picture to fit in the range A1:A5.

4. Add the Header ‘Balance Sheet 2001’ to the right section of the worksheet.

5. Make the heading size 14, and Bold.

6. Save the file as Balance Sheet.

PRACTICAL EXERCISE 1.12

Use the worksheet given below to answer the questions that follow:

Emp No Name Category Basic Pay Allowances Gross Pay Tax Deductions

E8 Cornell Assistant 4,600 300

E9 John Assistant 3,500 450
E2 Francis Supervisor 6,508 500
E3 Edwin Management 8,006 1,801
E10 Carl Assistant 4,200 100
E4 Bernard Management 7,917 1,775
E5 George Supervisor 5,500 850
E1 Lewis Management 8,291 2,500
E6 Albert Supervisor 5,700 760
E7 Edward Supervisor 7,151 1,545
Tax Rate 12%

Questions

1. Make all the column titles bold, and size 12. Center the title, across columns A1:H1
and make it size 16, and Bold.

2. Calculate the gross pay in cell F4.

3. Calculate the amount of tax deducted from each employee, given that the tax rate is
12% of the gross pay. Tax rate is found in cell A16 of the worksheet.

4. Format the text orientation in the range A4:G4 to 0 degrees.

5. Adjust the column width such that all the headings are visible.

6. In cell H4, enter the title Net Pay and calculate the Net pay for all employees.

7. Format the range G4:H13 to zero (0) decimal places.

8. Format the title Net Pay to match the other titles.

9. Set the range A1 to H16 as Print Area.

10. Using the Names in column B, Basic Pay in column D, and Allowances in column E,
insert a Clustered Column Pie Chart on the same sheet to show comparison of the
salaries for the employees. The Chart Title should be Employee Details, the Y-axis
should be Employee Names and the X-axis should be Thousands (Kshs).

11. Move the chart so that the top left corner is on cell A18.

12. Change the Chart Title to Employees’ Salary Details.

13. Change the Chart Type to Clustered bar with a 3-D visual effect.

14. Increase the Chart Title Font size to 14.

15. Change the text direction for the title of the X-axis to 0 degrees, and for the Y-axis to -
90

degrees.

16. Resize the chart such that the bottom left corner is on cell A55, while the bottom
right
 corner is on cell I55 so that all the details are clearly visible.

17. Save the worksheet as Salary Details (Reviewed Copy).

PRACTICAL EXERCISE 1.13

Use the data given in the table below:

SHEET 1:

Date Name Hours Worked Day of Week Rate Amount

27-Oct Albert 4 Sun 100 400
8-Nov Albert 3 Fri 50 150
18-Nov Lewis 4 Mon 50 200
9-Nov Albert 4 Sat 50 200
12-Nov Albert 3 Tue 50 150
27-Oct Carl 5 Sun 100 500
15-Nov Albert 4 Fri 50 200
19-Nov Albert 1 Tue 50 50
19-Nov Albert 4 Tue 50 200
5-Nov Bernard 4 Tue 50 200
7-Nov Bernard 5 Thu 50 250
20-Nov Albert 2 Wed 50 100
21-Nov Albert 4 Thu 50 200
28-Oct Carl 3 Mon 50 150
11-Nov Lewis 4 Mon 50 200
22-Nov Lewis 2 Fri 50 100
26-Oct Bernard 2 Sat 50 100
28-Oct Bernard 4 Mon 50 200
30-Oct Bernard 3 Wed 50 150
2-Nov Bernard 1 Sat 50 50
9-Nov Bernard 4 Sat 50 200
14-Nov Bernard 1 Thu 50 50
Maximum
Average

Questions;

1. Make all the column titles bold and size 12. Center the title, across columns A1:H1
and make it size 16, and Bold.
2. Calculate the gross pay in cell F4.
3. Calculate the amount of tax deducted from each employee, given that the tax rate is
12% of the gross pay. Tax rate is found in cell A16 of the worksheet.
 4. Format the text orientation in the range A4:G4 to 0 degrees.
5. Adjust the column width such that all the headings are visible.
6. In cell H4, enter the title Net Pay and calculate the Net pay for all employees.
7. Format the range G4:H13 to zero (0) decimal places.
8. Format the title Net Pay to match the other titles.
9. Set the range A1 to H16 as Print Area.
10. Using the Names in column B, Basic Pay in column D, and Allowances in column E,
insert a Clustered Column Pie Chart on the same sheet to show comparison of the
salaries for the employees. The Chart Title should be Employee Details, the Y-axis
should be Employee Names and the X-axis should be Thousands (Kshs).
11. Move the chart so that the top left corner is on cell A18.
12. Change the Chart Title to Employees’ Salary Details.
13. Change the Chart Type to Clustered bar with a 3-D visual effect.
14. Increase the Chart Title Font size to 14.
15. Change the text direction for the title of the X-axis to 0 degrees, and for the Y-axis to -
90 degrees.
16. Resize the chart such that the bottom left corner is on cell A55, while the bottom
right corner is on cell I55 so that all the details are clearly visible
17. Save the worksheet as Salary Details (Reviewed Copy)

PRACTICAL EXERCISE 1.14

The following is a simple payroll:

A B C D E F G H I J
1 Name Hours Worked Rate Basic Pay Gross Pay NSSF Pay Allowances Net
Pay

2 John 8 200
3 Peter 12 450
4 Sam 22 300
5 Njogu 30 286
6 Mary 16 220
7 Sally 45 468
8 Jane 15 150
9 Tina 3 280

Required:
Write formulae using cell names for the following expressions. State where the formula is

placed.

(i). Basic Pay = Hours Worked * Hourly Rate.

(ii). Allowances are allocated at 10% of the Basic Pay.

(iii). Gross Pay = Basic Pay + Allowances.

(iv). Tax Deduction is calculated at 20% of the Gross Pay.

(v). Net Pay = Gross Pay – Tax Deductions. (10 marks)

12

RACTICAL EXERCISE 1.15

The data below represents day sales of a certain wholesale shop in Sultan Hamud.

Enter the details into a worksheet using a spreadsheet package, and use it to answer the
questions

that follow. (4 marks)

Item Opening Closing Sold Buying Selling

Stock Stock Items Price Price
Sugar (bags) 250 130 120 2,500 2,650
Unga (ctn) 340 120 220 400 450
Salt (ctn) 271 107 164 200 250
Kimbo (ctn) 300 210 90 1,150 1,200
Blue band 250 30 220 220 265
(ctn)
GRAND
TOTAL

Required;

1. (a). Adjust the columns to fit the contents. (2 marks)

(b). The heading row should be Size 12 and Bold. The rest should be size 10. (2 marks)

(c). Apply the borders around the table as shown. (3 marks)

(d). Insert two new columns after the column for Selling Price. Enter the titles ‘Total’ and
‘Profit’ (2 marks)

(e). The column for the Totals should be Italic and shaded in blue. (3 marks)

(f). Position the contents in the totals column at the center of the cells. (2 marks)

(g). Add thousand separators where necessary, and set the number of decimal places to be

zero. (2 marks)

2. (a). Calculate the number of items sold for each. (2 marks)

(b). Calculate the total sales for each item on that day. (3 marks)

(c). Calculate the total sales for that day (the grand total sale)? (2 marks)

(d). What was the profit gained in each group of items? (2 marks)

(e). Calculate was the total profit the wholesale made that day. (2 marks)

(f). Save the changes to your document. (1 marks)

3. Down the worksheet, create another table with the same contents and respond to the

following:

i) Show what would happen if the buying price of sugar was increased by 2 percent.

(2 marks)

ii) Save the document as Income and Expenditure. (1 mark)

PRACTICAL EXERCISE 1.16

You have been asked to analyse the rainfall pattern of a city called Mooncity. The following

data, which shows the average weekly and monthly rainfall, has been provided.

Table 1.

MON TUE WED THUR FRI SAT SUN

10 5 30 20 15 0 50

Table 2.

Monthly Rainfall of Mooncity in mm.

 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

30 25 55 100 60 40 35 60 35 25 20 10

(a). Enter Table 1 and Table 2 into Microsoft Excel and Save as Mooncity (6 marks).

(b). Using the most appropriate formulas and functions,

(i). Calculate the total rainfall for the week and the year respectively. (4 marks).

(ii). Find the lowest rainfall for the week and the year respectively. (2 marks).

(iii). Find the highest rainfall for the week and the year respectively. (2 marks).

(iv). Find the mean rainfall for the week and the year respectively. (4 marks).

(c). Save the changes made to the worksheet. (2 marks).

(d). Create a 3-D column chart for Table 1. The title for the graph should be AVERAGE

PRACTICAL EXERCISE 1.17

You are in charge of a young and growing company. You have identified the various factors

(sources of revenue and expenses) that influence the company business as shown below.

All the figures are in thousands of Kenya Shillings except the percentages.

Category Detail
Revenue
Sales Starts at 10,000 and grows by 8%
Expenses
Raw materials 15% of sales
Transport 7% of sales
Electricity 2.1% of sales + 10
Rent Starts at 120 then grows by 2%
Salaries Starts at 1,000 then grows by 10%
Commissions (Sales) 1.8% of sales
Loan repayment 170
Advertising and promotion 2.5% of sales
Depreciation Fixed at 20
Miscellaneous Starts at 10 and grows by a fixed amount of 10
Total (Expenses)
Profit
1. Use the information provided in the table above to produce a financial projection model
for

the company for the next six years. (30 marks)

2. Format all the projected figures into Kshs. (3 marks)

3. Make a line graph of sales, total expenses and profit for the six year period. (10 marks)

4. Find the average expenses, sales and profit for the six year period. (5 marks)

5. Print the worksheet and the graph. (2 marks)

Practical Exercise: Student Grade Sheet

Instructions: Create a new Excel workbook and enter the following data exactly as shown
in the table below. Once the data is entered, perform the tasks listed under the "Exercise
Tasks" section.

1. Data Entry Table

NAME INTR TYPI WINDO WOR EXC ACCE [Link] PU ILLUSTRAT COR [Link]
O NG WS D EL SS NT B OR EL OP
Ann 78 64 56 88 66 89 66 90 65 87 34
Briton 12 53 76 83 56 87 66 77 55 78 34
Cate 12 74 87 90 77 80 88 87 55 77 54
Daniel 78 71 76 73 88 87 56 66 44 87 65
Evelyn 45 15 45 81 67 90 45 87 56 56 66
Fred 36 32 87 90 87 53 67 55 65 67 76
Grace 75 61 90 89 84 23 85 76 76 65 65
Hariso 78 72 76 81 83 34 84 54 76 89 67
n
Ivy 87 82 76 84 75 53 85 65 87 87 54
james 68 70 80 64 46 54 75 74 89 90 56
Kelly 86 62 87 73 34 55 87 76 90 65 67
Lewis 74 67 67 71 45 44 78 70 76 87 56
Miria 62 45 56 89 67 34 87 90 43 46 87
m
Newto 48 87 74 90 55 87 90 90 23 56 87
rn
Olivia 54 90 65 85 66 76 78 80 65 45 89
Peter 75 89 87 75 56 65 89 78 78 66 90
Queen 82 78 81 65 76 87 89 76 56 78 88
Richar 73 67 53 54 77 78 65 56 78 56 77
d
Sarah 76 56 69 54 87 76 80 56 90 56 90
 Titus 56 67 87 67 34 54 87 56 76 76 90

2. Exercise Tasks

Using the data above, perform the following operations using Excel formulas and functions:

• Total Marks: Create a column named TOTAL and calculate the sum of all units for
each student.

• Highest & Lowest: Create columns for MAX and MIN to find the highest and lowest
score achieved by each student across all units.

• Average: Calculate the AVERAGE score for each student (formatted to 2 decimal
places).

• Count: Use the COUNT function to verify the number of units each student sat for.

• Grading Logic: Create a GRADE column using the IF function (or IFS) based on the
average score:

• A: 80 and above

• B: 70 - 79

• C: 60 - 69

• D: 50 - 59

• E: Below 50

• Comments: Create a COMMENT column that assigns a status based on the

average:

• DISTINCTION: 70 and above

• CREDIT: 60 - 69

• PASS: 50 - 59

• FAIL: Below 50

3. Formatting Challenge

1. Header Styling: Bold the header row and apply a background fill color.

2. Borders: Apply "All Borders" to your entire dataset.

 3. Conditional Formatting: Highlight any score below 40 in Red Text to easily identify
weak areas.

Teaching Notes: Microsoft Access

Database Management Systems
Guide for Building Student Skills in Database Management

Objective
Build students' skills in database management using Microsoft Access.

Topic Overview
1. Launching/Starting MS Access

2. Definition of a Database

3. Examples of Databases

4. Exploring the MS Access Window

5. Creating a New Database (Blank & Template)

6. Identifying Database Objects (Table, Query, Macro, Report, Form)

7. Tables: Creation, Properties, Primary Key

8. Formatting Tables: Column Width, Row Height, Renaming, Freezing/Unfreezing

Columns

9. Filtering and Sorting Data

10. Queries: Creation and Modification

11. Forms: Creation, Formatting, Navigation, Data Entry

12. Reports: Creation, Formatting, AutoFormat, Print Preview

13. Macros: Creation and Execution

14. Using Access Help

15. Learning Activities: Create a College Database from Scratch

Brief Outlines
• Launching MS Access: Use the Run command, type "MS Access", press Enter/OK.

• Database Definition: A collection of related data or objects for a particular topic or

purpose.

• Examples: Asset tracking, contact management, event management, order entry,

time and billing, resource scheduling, etc.

• MS Access Window Parts:

• Microsoft Office Button

• Quick Access Toolbar

• Title Bar

• Ribbon

• Access Objects

• Datasheet

• Formula Bar

• Status Bar

• Getting Help: Click the Help button (far right on the ribbon) or press F1, type
question, click search, select topic.

Topic 1: Database Fundamentals

• Definition: Database – a collection of data or objects related to a particular topic or
purpose.

• Launching Access:

• Click the Run command.

• Type "MS Access".

• Press Enter or click OK.

• Kinds of Databases: Asset tracking, contact management, event management,

order entry, time and billing, resource scheduling, etc.

• Parts of Access Window: Office Button, Quick Access Toolbar, Title Bar, Ribbon,
Access Objects, Datasheet, Formula Bar, Status Bar.
Topic 2: Database Objects
• Table: Collects and stores data relating to a particular subject or topic.

• Query: Used to ask questions about the database; results displayed in a datasheet.

• Form: Displays table data one record at a time.

• Report: Used for viewing, compiling, summarizing, and printing information.

• Data Access Page: Web page for viewing and working with data from the internet or
intranet.

• Macro: Set of one or more actions for automating tasks.

Topic 3: Tables
• Field: Element of a table containing a specific item of information.

• Record: Collection of data about a person, place, event, or item; represented as a

row.

• Wizard: Tool that creates objects based on user answers.

• Datasheet View: Displays data in rows and columns; allows adding and deleting
records.

• Design View: Shows the design of a table, query, form, or macro; allows creating
and modifying objects.

Creating Tables
1. Method 1:

2. Click Create tab.

3. Select Table command from Tables group.

4. Method 2:

5. Click Create tab.

6. Choose Table Templates command.

7. Select appropriate template.

Adding Table Properties

• After creating the table, switch to Design View to set names, primary key, and
additional fields.
 • Enter field names, data types, descriptions, and field properties.

• Select a unique field as Primary Key.

• Return to Datasheet View to fill in records.

Managing Tables
• Closing: Right-click table tab, select Close, save if prompted.

• Opening: Right-click table tab, select Open.

• Deleting: Close table, select table in All Tables window, click Home tab, use Delete
command.

• Renaming: Close table, right-click table, select Rename, type new name, press
Enter.

Topic 4: Formatting Tables

• Font: Design applied to characters; e.g., Arial, Calibri.

• Freezing Columns: Keeps column visible while scrolling.

Formatting Steps
1. Open table and display it correctly.

2. Click Home tab.

3. Use Font group for style/size changes.

4. Use Dialogue Launcher for cell effects, background, and grid lines.

5. Change column width by dragging boundary.

6. Change row height by dragging row header.

7. Rename column by double-clicking field title or using Rename command.

8. Freeze/unfreeze columns via right-click or Home tab → Records group.

Topic 5: Filtering and Sorting Data

• Filter: Criteria applied to show subset or sort data.

• Sort Order: Ascending (A-Z, 0-100), Descending (Z-A, 100-0).

Filtering Steps
1. Use Advanced Filter under Home tab.
 2. Select fields, enter criteria, and save.

Sorting Steps
1. Click field to sort.

2. Go to Home tab → Sort & Filter group.

3. Select Ascending or Descending.

Topic 6: Queries
• Query: Database object to retrieve specific information.

Creating Queries in Design View

1. Click Create tab → Query Design.

2. Select required table, click Add, then Close.

3. Insert required fields, enter criteria, click Run.

Creating Queries Using Wizard

1. Click Create tab → Query Wizard.

2. Select Simple Query Wizard, click OK.

3. Select source table/query and fields, type title, finish.

Topic 7: Forms
• Form: Object for displaying and entering data one record at a time.

Creating Forms
1. Click Create tab → Form command.

Formatting Forms
1. Click Formatting tab.

2. Change font, style, color, size in Font group.

3. Use AutoFormat for quick styling.

Navigating Forms
1. Use Home tab → Field group → Go To.

2. Select First, Last, Next, Previous, or use navigation bar.

Topic 8: Reports
• Report: Object for viewing, compiling, summarizing, and printing data.

Creating Reports
1. Click Create tab → Report command.

Formatting Reports
1. Click Formatting tab.

2. Change font, style, color, size in Font group.

3. Use AutoFormat for quick styling.

Topic 9: Macros
• Macro: Automates tasks using one or more actions.

Creating Macros
1. Click Create tab → Macro.

2. Select Macro, fill actions and arguments.

3. Save and name the macro.

Running Macros
1. Click Database Tools tab → Run Macro.

2. Select macro, click OK.

Topic 10: Using Access Help & Learning Activities

• Click Help button or press F1, type question, search and select topic.

• Create a college database from scratch as a hands-on activity.