NOTES ABOUT SPREADSHEETS

Introduction Spreadsheets
Meaning of Spreadsheet
A spreadsheet is an electronic worksheet used to store, organize, calculate, and analyze data in
tabular form (rows and columns).

It consists of:

 Rows (1, 2, 3…)

 Columns (A, B, C…)
 Cells (A1, B2, etc.)

Each cell can contain:

 Text
 Numbers
 Formulas
 Functions

Spreadsheets are mainly used for:

 Calculations
 Record keeping
 Data analysis
 Budgeting
 Reports

One of the most popular spreadsheet programs is Microsoft Excel.

3️Starting Microsoft Excel)

Steps:

1. Click Start Menu

2. Type Microsoft Excel
3. Click to open
4. Select Blank Workbook
Advantages of Spreadsheets
1️ Fast Calculations

 Performs automatic calculations using formulas.

2️ Accuracy

 Reduces human calculation errors.

3️ Easy Data Organization

 Data arranged in rows and columns.

4️ Automatic Recalculation

 Updates results automatically when values change.

5️ Data Analysis Tools

 Sorting, filtering, charts, pivot tables.

6️ Graphical Representation

 Can create charts and graphs easily.

7️ Time Saving

 Quick calculations and data processing.

8️ Large Data Handling

 Can store thousands of records.

Disadvantages of Spreadsheets
1️ Error-Prone Formulas
  Wrong formula gives wrong results.

2️ Difficult for Very Large Databases

 Not ideal for complex database systems.

3️ Security Risks

 Data can be accidentally deleted or changed.

4️ Requires Basic Knowledge

 Users must understand formulas and functions.

5️ File Corruption Risk

 Files may become corrupted if not saved properly.

6️ Limited Multi-User Control

 Not as strong as database systems for multiple users.

Examples of Spreadsheet Software

1️ Microsoft Excel

 Most widely used in offices and schools.

2 Google Sheets

 Online spreadsheet tool by Google.

3️LibreOffice Calc

 Free and open-source spreadsheet program.

4️Apple Numbers

 Spreadsheet software for Apple devices.

5️WPS Spreadsheets

 Part of WPS Office suite.

 Practical Examples of Spreadsheet Use
 Student result sheet
 Salary calculation
 Budget planning
 Sales report
 Inventory management
 Attendance register

Microsoft Excel is a spreadsheet application developed by Microsoft. It is mainly used to:

 Store data in an organized way

 Perform mathematical calculations
 Analyze information
 Create charts and reports
 Manage financial records

It is part of the Microsoft Office package and is widely used in schools, offices, banks, and
businesses.

2. Basic Terminology in Microsoft Excel (Detailed

Explanation)
Understanding these basic terms is very important because they form the foundation of working
in Excel. Let us explain each one clearly and in detail.

1️ Workbook (Excel File)

A workbook is the entire Excel file that you create and save on the computer.

Detailed Explanation:

 When you open Excel and click “Blank Workbook”, you create a new workbook.
  A workbook can contain one or many worksheets.
 It is saved with an extension such as:
 .xlsx
 Example file names:
o [Link]
o [Link]
o [Link]

Important Points:

 Think of a workbook like a book.

 The worksheets are like the pages inside the book.
 You can add, delete, rename, or move worksheets inside a workbook.

2️ Worksheet
A worksheet is a single page inside the workbook where data is entered and calculations are
performed.

Detailed Explanation:

 A worksheet is made up of rows and columns.

 The area where rows and columns meet forms cells.
 By default, worksheets are named:
o Sheet1
o Sheet2
o Sheet3
 These names appear at the bottom of the screen.
 You can rename a worksheet by right-clicking on it.

Example:

You may have:

 Sheet1 → Student Results

 Sheet2 → Fees Payment
 Sheet3 → Attendance

Each worksheet stores different information.

1. ADD ROWS OR COLUMNS

Add a Row
Excel

1. Click the row number where you want the new row.
2. Right-click.
3. Select Insert.

Shortcut:

 Windows: Ctrl + Shift + +

 Mac: Cmd + Shift + +

Google Sheets

1. Right-click the row number.

2. Choose:
o Insert 1 row above
o Insert 1 row below

Add a Column
Excel

1. Click the column letter.

2. Right-click.
3. Select Insert.

Google Sheets

1. Right-click the column letter.

2. Choose:
o Insert 1 column left
o Insert 1 column right

2. DELETE ROWS OR COLUMNS

Delete a Row
Excel

1. Right-click the row number.

2. Select Delete.

Google Sheets

1. Right-click the row number.

2. Select Delete row.

Delete a Column
Excel

1. Right-click the column letter.

2. Select Delete.

Google Sheets

1. Right-click the column letter.

2. Select Delete column.

3. MERGE CELLS
Important: Merging combines multiple cells into one larger cell.

Merge in Excel
1. Select the cells.
2. Go to Home tab.
3. Click Merge & Center.
4. Choose:
o Merge & Center
o Merge Across
o Merge Cells

Merge in Google Sheets

1. Select cells.
 2. Click Merge cells icon in toolbar.
3. Choose:
o Merge all
o Merge horizontally
o Merge vertically

4. SPLIT CELLS
Spreadsheets don’t directly “split” a single cell like Word tables.

Option 1: Unmerge (if previously merged)

 Select the merged cell.

 Click Unmerge.

Option 2: Split Text Into Columns

Excel:

1. Select the cell(s).

2. Go to Data tab.
3. Click Text to Columns.
4. Choose:
o Delimited (comma, space, etc.)
o Fixed width

Google Sheets:

1. Select the cell(s).

2. Click Data → Split text to columns.
3. Choose separator.

3️ Rows
Rows are the horizontal lines in a worksheet.

Detailed Explanation:

 Rows run from left to right.

 They are identified by numbers.
  Example:
o Row 1
o Row 25
o Row 100

If you move sideways across the screen, you are moving across a row.

Important Facts:

 Modern Excel has over 1 million rows.

 Rows are used to organize data horizontally.
 Each row usually contains related information.

Example:

Row 1 Name Age Marks

This information is arranged across Row 1.

4️Columns
Columns are the vertical lines in a worksheet.

Detailed Explanation:

 Columns run from top to bottom.

 They are identified using letters.
 Example:
o Column A
o Column B
o Column C

After Z, Excel continues with:

 AA
 AB
 AC

Important Facts:

 Excel has over 16,000 columns.

 Columns are used to separate categories of information.
Example:

 Column A → Names
 Column B → Age
 Column C → Marks

Each column represents a different category.

5 ️Cell
A cell is the small rectangular box formed when a row and a column intersect (meet).

Detailed Explanation:

If:

 Column A meets Row 1 → Cell A1

 Column B meets Row 2 → Cell B2

That box is called a cell.

Cells are used to enter:

 Text (e.g., John)

 Numbers (e.g., 85)
 Dates (e.g., 12/02/2026)
 Formulas (e.g., =A1+B1)

Example:

If you type 50 in A1 and 20 in A2, each value is stored inside a cell.

6️ Cell Address (Cell Reference)

Every cell has a unique address.

How It Is Written:
Column Letter + Row Number
Examples:

 A1
 C10
 D5
 B20

This address helps Excel know which cell to use in calculations.

Example:

If you write:

=A1+B1

Excel adds the values in cell A1 and B1.

7️ Range
A range is a group of selected cells.

Detailed Explanation:

Instead of selecting one cell, you can select many cells together.

It is written using a colon (:)

Example:

A1:A10

This means:

 Starting from A1
 Ending at A10
 All cells between them are included

Example:

If you want to add marks from A1 to A5, you write:

=SUM(A1:A5)
Ranges can also be:

 B1:D5 (a block of cells)

8️ Active Cell
The active cell is the cell that is currently selected.

Detailed Explanation:

 It has a thick border around it.

 When you type something, it appears in the active cell.
 Only one cell can be active at a time.

Example:

If you click on cell C3:

 C3 becomes the active cell.

 Anything you type will go into C3.

3️Starting Microsoft Excel )

Steps:

5. Click Start Menu

6. Type Microsoft Excel
7. Click to open
8. Select Blank Workbook

4️Excel Interface
Excel Interface (Main Parts) – Detailed Explanation with
More Examples
In Microsoft Excel, the interface is everything you see and use when the program opens.
Understanding it well helps you work faster and correctly.

Let us now explain each part again with clear practical examples.

1️ Title Bar
Location:

At the very top of the Excel window.

What It Shows:

 The name of the workbook (file)

 The word “Excel”
 Window control buttons (Minimize, Maximize, Close)

Example 1:

If you create a file and save it as:

[Link]

The Title Bar will show:

SchoolFees - Excel

Example 2:

If the file is not saved yet, it will show:

Book1 - Excel

(Book1 is the default name before saving.)

Why It Is Important:

 Helps you know which file you are working on.

 If you open two files, you can see their names at the top.
  You can close or resize the window using the buttons.

2️ Ribbon
The Ribbon contains tabs, and each tab contains tools.

A. Home Tab (Examples)

This is the most used tab.

Example 1: Formatting Text

If you type:

John

in cell A1 and want to make it bold:

1. Select A1
2. Click Bold (B)

Now "John" appears bold.

Example 2: Changing Color

If you want the background of cell B1 to be yellow:

1. Select B1
2. Click Fill Color
3. Choose Yellow

Example 3: Alignment

If you want text in C1 to be centered:

1. Select C1
 2. Click Center Alignment

B. Insert Tab (Examples)

Used to insert charts, tables, pictures.

Example 1: Insert Chart

If you have this data:

A B
John 80
Mary 75
Peter 90

To create a chart:

1. Highlight A1:B3
2. Click Insert
3. Choose Column Chart

Excel creates a chart automatically.

Example 2: Insert Table

If you want to organize data:

1. Select data
2. Click Insert
3. Click Table

Now your data becomes a structured table.

C. Page Layout Tab (Examples)

Used when preparing to print.
Example 1: Change Orientation

If your worksheet is wide:

1. Click Page Layout

2. Click Orientation
3. Choose Landscape

Now the page prints horizontally.

Example 2: Set Print Area

If you want to print only A1:C10:

1. Select A1:C10
2. Click Page Layout
3. Click Set Print Area

Only that part will print.

Formulas Tab (Examples)

Used for calculations.

Example 1: Insert SUM Function

If numbers are in A1:A5:

1. Click A6
2. Go to Formulas
3. Click AutoSum

Excel automatically inserts:

=SUM(A1:A5)
Example 2: Insert IF Function

You can use:

=IF(A1>=50,"Pass","Fail")

This checks if marks are 50 or above.

E. Data Tab (Examples)

Used for organizing data.

Example 1: Sorting

If you have student names:

1. Select the list

2. Click Data
3. Click Sort A–Z

Names will arrange alphabetically.

Example 2: Filtering

If you want to see only students who scored above 80:

1. Select data
2. Click Filter
3. Click the dropdown arrow
4. Choose filter option

F. Review Tab (Examples)

Example 1: Spell Check

If you typed:
Studant

1. Click Review
2. Click Spelling

Excel suggests correction: "Student"

Example 2: Protect Sheet

1. Click Review
2. Click Protect Sheet
3. Enter password

Now others cannot edit cells.

View Tab (Examples)

Example 1: Zoom

If text looks small:

1. Click View
2. Click Zoom
3. Choose 150%

Now text appears larger.

Example 2: Freeze Panes

If you want row 1 (Headings) always visible:

1. Click View
2. Click Freeze Panes
3. Choose Freeze Top Row

Now headings stay visible when scrolling.

3️ Formula Bar
Location:

Below the Ribbon.

Example 1:

If cell B1 contains:

=10+20

The cell shows:

30

But the Formula Bar shows:

=10+20

Example 2:

If you want to edit a formula:

1. Click the cell

2. Edit in the Formula Bar
3. Press Enter

4️ Name BOX
Location:

Left side of Formula Bar.

Example 1:

Click cell D5
Name Box shows:
D5

Example 2: Quick Navigation

1. Type:

A100

in Name Box
2. Press Enter

Excel jumps directly to cell A100.

5️ Status Bar (With Examples)

Location:

Bottom of the screen.

Example 1: Quick Calculation

If you select cells A1:A5 containing numbers, the Status Bar shows:

 Sum
 Average
 Count

You don’t need to write a formula.

Example 2: Zoom Control

On the right side of Status Bar:

 You can drag the zoom slider to increase or decrease size.

Shows information about the worksheet.

5️ Types of Data in Excel
In Excel, data refers to the information you enter into cells. Understanding the different types of
data is very important because Excel treats each type differently when calculating or formatting.

Let us explain each type clearly and in detail.

1️ Text (Labels)
What is Text?

Text data (also called labels) includes:

 Names
 Words
 Headings
 Descriptions

Text is used to describe or identify data, not to calculate.

Examples of Text:

 John
 Mathematics
 Total Marks
 January
 Student Name

Important Features:

 Text is automatically aligned to the left of the cell.

 Excel does NOT perform calculations with text.
 Text is often used as headings in tables.

Example:
 A B
Name Marks
John 85

Here:

 "Name" and "Marks" are text.

 "John" is text.

2️ Numbers
What are Numbers?

Numbers are numerical values used in calculations.

Excel can:

 Add
 Subtract
 Multiply
 Divide

Numbers are used in formulas.

Examples of Numbers:

 10
 250
 85
 1000

Important Features:

 Numbers are aligned to the right by default.

 Excel can perform calculations on numbers.
 Numbers do not include currency signs unless formatted.
Example:

If:

 A1 = 10
 A2 = 20

Formula:

=A1+A2

Result = 30

3️ Dates
What are Dates?

Dates represent calendar values such as days, months, and years.

Excel recognizes dates and can perform calculations with them.

Examples:

 12/02/2026
 1-Jan-2025
 March 15, 2024

Important Features:

 Excel stores dates as serial numbers internally.

 You can calculate:
o Number of days between two dates
o Future or past dates

Example:

If:
  A1 = 01/01/2026
 A2 = 10/01/2026

Formula:

=A2-A1

Result = 9
(This shows the number of days between the two dates.)

Currency
What is Currency?

Currency is a number formatted with a money symbol.

Examples of symbols:

 $
 €
 £
 ₦
 ₹

Examples:

 $100
 ₦5,000
 €250

Important Features:

 It is still treated as a number.

 You can perform calculations on it.
 It includes:
o Currency symbol
o Two decimal places (usually)

Example:
Item Price
Book $20
Pen $5

Formula:

=20+5

Result = $25

5️Percentage
What is Percentage?

A percentage represents a portion of 100.

It is written with the % sign.

Examples:

 50%
 75%
 10%

Important Features:

 Excel stores percentages as decimal values.

o 50% = 0.5
o 25% = 0.25
 Used in:
o Discounts
o Interest rates
o Marks
o Tax calculations

Example:
If:

 A1 = 100
 B1 = 10%

Formula:

=A1*B1

Result = 10
(10% of 100 is 10)

6️Formulas
What is a Formula?

A formula is an equation that performs calculations.

All formulas start with:

Examples of Formulas:
=A1+A2
=A1-B1
=A1*B1
=A1/B1

6️Entering and Editing Data (Procedure)

To Enter Data:

1. Click a cell
2. Type data
3. Press Enter
To Edit Data:

1. Double-click the cell

2. Edit
3. Press Enter

7️ Formulas in Excel
A formula starts with =

Examples:
=A1+B1
=A1-B1
=A1*B1
=A1/B1

8️ Functions in Excel
Functions are built-in formulas.

Common Functions:

Function Purpose
Adds
SUM
numbers
AVERAGE Finds mean
Highest
MAX
value
MIN Lowest value
Counts
COUNT
numbers

Procedure: Using the SUM Function in Microsoft Excel

(Detailed Explanation)
The SUM function is one of the most commonly used functions in Excel.
It is used to add numbers together automatically.
Instead of adding numbers one by one like:

=A1+A2+A3+A4+A5

You can simply use:

=SUM(A1:A5)

This is faster, easier, and reduces mistakes.

Example Scenario
Let us say you have the following numbers:

Cell Value
A1 10
A2 20
A3 15
A4 25
A5 30

You want to find the total of these numbers.

Step-by-Step Procedure (Detailed

Explanation)

🔹 Step 1: Enter Numbers in A1:A5

Click on cell A1 and type:

10

Press Enter.

Then continue:

 A2 → 20
  A3 → 15
 A4 → 25
 A5 → 30

Now cells A1 to A5 contain numbers.

👉 The group A1:A5 is called a range.

The colon (:) means "from A1 to A5".

Step 2: Click on Cell A6

Click on cell A6.

Why A6?

Because:

 We want the total to appear below the numbers.

 A6 is empty and ready to display the result.

When you click A6:

 It becomes the active cell.

 It will have a thick border around it.

Step 3: Type the Formula

In cell A6, type:

=SUM(A1:A5)

Let us break this formula into parts:

 = → Tells Excel this is a formula.

 SUM → The function name (adds numbers).
 ( ) → Brackets that contain the range.
 A1:A5 → The cells to be added.

So Excel understands:
👉 “Add all numbers from A1 to A5.”
While typing, Excel may even suggest the function automatically.

Step 4: Press Enter

After typing the formula:

Press Enter.

Excel will now calculate the total.

Result:
100

Because:
10 + 20 + 15 + 25 + 30 = 100

The answer (100) appears in cell A6.

Automatic Updating (Very Important)

Now change any number.

For example:
Change A1 from 10 to 50.

Excel will automatically update the result in A6.

New total:
50 + 20 + 15 + 25 + 30 = 140

👉 This is called automatic recalculation.

You do not need to calculate again.

Another Method (Using AutoSum Button)

Instead of typing the formula manually:
Steps:

1. Click cell A6
2. Go to the Home tab
3. Click AutoSum (∑ symbol)
4. Press Enter

Excel will automatically insert:

=SUM(A1:A5)

This method is faster.

Basic Statistical Functions in Microsoft Excel

These functions are used to analyze numerical data in a worksheet.

Functions covered:

1. SUM
2. AVERAGE
3. MAX
4. MIN
5. COUNT

1️ SUM Function
🔹 Purpose:
Adds numbers together.

🔹 Syntax:
=SUM(number1, number2, ...)

or

=SUM(range)

Example 1: Total Marks

Worksheet Data

A (Marks)
50
60
70
80

Formula (In A6):

=SUM(A2:A5)

How Excel Calculates:

Step 1: Reads range A2:A5
Step 2: Adds numbers
50 + 60 + 70 + 80
Step 3: Total = 260

Result → 260

Procedure:
1. Enter numbers in cells
2. Click result cell
3. Type =SUM(A2:A5)
4. Press Enter

2️AVERAGE Function
🔹 Purpose:
Finds the arithmetic mean (total ÷ number of values)

🔹 Syntax:
=AVERAGE(range)
 Example: Average Marks
Using same data:

| 50 |
| 60 |
| 70 |
| 80 |

Formula:
=AVERAGE(A2:A5)

How Excel Calculates:

Step 1: Finds total → 260
Step 2: Counts numbers → 4
Step 3: Divides total by count
260 ÷ 4 = 65

Result → 65

🔹 Formula Logic:
AVERAGE = SUM ÷ COUNT

3️MAX Function
🔹 Purpose:
Finds highest value in range.

🔹 Syntax:
=MAX(range)
Example:
| 50 |
| 60 |
| 70 |
| 80 |

Formula:
=MAX(A2:A5)

How Excel Calculates:

Step 1: Compares values
50, 60, 70, 80
Step 2: Finds largest → 80

Result → 80

4️MIN Function
🔹 Purpose:
Finds smallest value.

🔹 Syntax:
=MIN(range)

Formula:
=MIN(A2:A5)

Calculation:
Values: 50, 60, 70, 80
Smallest value → 50
Result → 50

5️ COUNT Function
🔹 Purpose:
Counts number of numeric values only.

🔹 Syntax:
=COUNT(range)

Example:
A (Data)
50
60
Text
80

Formula:
=COUNT(A2:A5)

How Excel Calculates:

Step 1: Checks each cell
50 → Number → Count
60 → Number → Count
Text → Not counted
80 → Number → Count

Total numeric values = 3

Result → 3
 Important Notes About COUNT
 Counts only numbers
 Does NOT count text
 Does NOT count blank cells

If you want to count text → use COUNTA()

Combined Example in One Worksheet

A (Marks)
40
55
65
70
90

Formulas:
=SUM(A2:A6) → 320
=AVERAGE(A2:A6) → 64
=MAX(A2:A6) → 90
=MIN(A2:A6) → 40
=COUNT(A2:A6) → 5

How Excel Calculates Internally

1. Reads cell range
2. Identifies numeric values
3. Applies function rule:
o SUM → Adds
o AVERAGE → SUM ÷ COUNT
o MAX → Finds highest
o MIN → Finds lowest
o COUNT → Counts numbers
4. Returns result
5. Recalculates automatically if values change
Step-by-Step General Procedure
1️ Enter data in worksheet
2️ Click cell where result needed
3️ Type = and function name
4️Select range using mouse
5️Close bracket )
6️Press Enter
7️Use fill handle to copy if needed

Quick Comparison Table

Function What It Does Example Result
SUM Adds numbers 320
AVERAGE Mean value 64
MAX Highest value 90
MIN Lowest value 40
COUNT Counts numbers 5

Conclusion
These basic functions in Microsoft Excel are used for:

 Total calculation
 Data analysis
 Report preparation
 Result sheets
 Business calculations

Logical Functions in Excel – Notes, Calculations &

Procedures
Logical functions in Microsoft Excel are used to test conditions and return results based on
whether those conditions are TRUE or FALSE.

Logical functions are mainly used for:

 Decision making
  Conditional calculations
 Data validation
 Grading systems
 Financial analysis

1️What is a Logical Function?

A logical function checks a condition and returns:

 TRUE
 FALSE
 Or a specified value based on the condition

Example:

=5>3

Result → TRUE

2️Main Logical Functions in Excel

1. IF Function
🔹 Purpose:

Performs a logical test and returns one value if TRUE and another if FALSE.

🔹 Syntax:
=IF(logical_test, value_if_true, value_if_false)

🔹 Example:

If marks are in A1:

=IF(A1>=50,"Pass","Fail")

🔹 How It Is Calculated:

1. Excel checks if A1 ≥ 50
 2. If TRUE → returns "Pass"
3. If FALSE → returns "Fail"

2. AND Function
🔹 Purpose:

Returns TRUE only if all conditions are TRUE.

🔹 Syntax:
=AND(condition1, condition2, ...)

🔹 Example:
=AND(A1>=50, B1>=50)

🔹 Calculation:

 If both conditions are TRUE → TRUE

 If any condition is FALSE → FALSE

3. OR Function
🔹 Purpose:

Returns TRUE if any one condition is TRUE.

🔹 Syntax:
=OR(condition1, condition2, ...)

🔹 Example:
=OR(A1>=50, B1>=50)

🔹 Calculation:

 If at least one condition is TRUE → TRUE

 If all are FALSE → FALSE
4. NOT Function
🔹 Purpose:

Reverses the logical value.

🔹 Syntax:
=NOT(logical_test)

🔹 Example:
=NOT(A1>50)

🔹 Calculation:

 If A1>50 is TRUE → NOT returns FALSE

 If A1>50 is FALSE → NOT returns TRUE

5. Nested IF Function
🔹 Purpose:

Multiple conditions inside one formula.

🔹 Example:
=IF(A1>=75,"Distinction",
IF(A1>=60,"First Class",
IF(A1>=50,"Second Class","Fail")))

🔹 Calculation Process:

1. Check A1 ≥ 75
2. If FALSE → Check A1 ≥ 60
3. If FALSE → Check A1 ≥ 50
4. If none TRUE → "Fail"

6. IFS Function (Excel 2016 & later)

🔹 Syntax:
=IFS(condition1, value1, condition2, value2, ...)

🔹 Example:
=IFS(A1>=75,"Distinction",
A1>=60,"First Class",
A1>=50,"Second Class",
A1<50,"Fail")

🔹 How It Works:

Excel checks conditions from left to right and returns the first TRUE result.

3️Comparison Operators Used in Logical

Functions
Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal to

Example:

=A1<>10

Means A1 is NOT equal to 10.

4️ Procedure to Use Logical Functions in

Excel
Step 1: Enter Data

Input your data in worksheet cells.

Step 2: Select Cell for Result

Click the cell where you want the result.

Step 3: Type Formula

Start with = and enter the logical function.

Example:

=IF(A1>100,"High","Low")

Step 4: Press Enter

Excel calculates automatically.

Step 5: Copy Formula (If Needed)

Drag the fill handle to apply to other rows.

5️How Excel Calculates Logical Functions

1. Excel reads formula from left to right
2. Evaluates logical test
3. Determines TRUE or FALSE
4. Returns specified output
5. Recalculates automatically if values change

Excel uses an internal calculation engine that recalculates when:

 Data changes
 Workbook opens
 Manual recalculation is triggered

6️ Practical Examples
✔ Student Result
=IF(A1>=40,"Pass","Fail")
✔ Salary Bonus
=IF(A1>=50000,A1*10%,0)

✔ Eligibility Check
=AND(A1>=18,B1="Yes")

Important Points
 Logical functions are case-insensitive
 Text values must be inside quotation marks (" ")
 Always check brackets properly
 Avoid too many nested IFs (use IFS instead)

Logical Functions in a Worksheet – Examples & How They

Are Calculated
(Using Microsoft Excel)

Below are practical worksheet examples with:

 Sample data layout

 Formula used
 Step-by-step calculation explanation
 Final result

Example 1: IF Function (Student Pass/Fail)

Worksheet Data

A (Marks) B (Result)
65 ?
35 ?

Formula (In Cell B2):

=IF(A2>=40,"Pass","Fail")

How Excel Calculates (For A2 = 65)

 1. Excel checks: Is 65 ≥ 40?
2. Condition is TRUE
3. Returns "Pass"

Result → Pass

For A3 = 35

1. Excel checks: Is 35 ≥ 40?

2. Condition is FALSE
3. Returns "Fail"

Result → Fail

Example 2: IF Function (Bonus Calculation)

Worksheet Data

A (Salary) B (Bonus)
60000 ?
30000 ?

Formula (In B2):

=IF(A2>=50000,A2*10%,0)

How Excel Calculates (For 60000)

1. Check: 60000 ≥ 50000 → TRUE

2. Bonus = 60000 × 10%
3. 60000 × 0.10 = 6000

Result → 6000

For 30000

1. Check: 30000 ≥ 50000 → FALSE

2. Return 0
Result → 0

Example 3: AND Function (Subject Pass

Check)
Worksheet Data

A (Maths) B (Science) C (Final Result)

55 60 ?
45 30 ?

Formula (In C2):

=IF(AND(A2>=50,B2>=50),"Pass","Fail")

How Excel Calculates (Row 2)

1. Check A2 ≥ 50 → 55 ≥ 50 → TRUE
2. Check B2 ≥ 50 → 60 ≥ 50 → TRUE
3. AND(TRUE, TRUE) → TRUE
4. IF(TRUE,"Pass","Fail") → Pass

Result → Pass

Row 3

1. 45 ≥ 50 → FALSE
2. 30 ≥ 50 → FALSE
3. AND(FALSE, FALSE) → FALSE
4. IF(FALSE,"Pass","Fail") → Fail

Result → Fail

Example 4: OR Function
Worksheet Data
A (Sports Marks) B (Cultural Marks) C (Selected)
80 30 ?
40 35 ?

Formula:
=IF(OR(A2>=75,B2>=75),"Selected","Not Selected")

Row 2

1. A2 ≥ 75 → 80 ≥ 75 → TRUE
2. B2 ≥ 75 → 30 ≥ 75 → FALSE
3. OR(TRUE, FALSE) → TRUE
4. Result → Selected

Row 3

1. 40 ≥ 75 → FALSE
2. 35 ≥ 75 → FALSE
3. OR(FALSE, FALSE) → FALSE
4. Result → Not Selected

Example 5: Nested IF (Grade System)

Worksheet Data

A (Marks) B (Grade)
82 ?
67 ?
45 ?

Formula:
=IF(A2>=75,"A",
IF(A2>=60,"B",
IF(A2>=50,"C","Fail")))

For 82

1. 82 ≥ 75 → TRUE
 2. Return "A"

For 67

1. 67 ≥ 75 → FALSE
2. Check 67 ≥ 60 → TRUE
3. Return "B"

For 45

1. 45 ≥ 75 → FALSE
2. 45 ≥ 60 → FALSE
3. 45 ≥ 50 → FALSE
4. Return "Fail"

🔹 Example 6: NOT Function

📄 Worksheet Data

A (Status) B (Result)
60 ?

Formula:
=NOT(A2>50)

Calculation

1. A2>50 → 60>50 → TRUE

2. NOT(TRUE) → FALSE

Result → FALSE

Step-by-Step Procedure in Worksheet

1️Enter data in cells
2 ️Click result cell
3️ Type = and formula
4️Press Enter
5️Drag fill handle to copy formula
6️Excel recalculates automatically when values change

How Excel Evaluates in Background

 Checks logical test first
 Converts condition to TRUE or FALSE
 Applies function logic
 Returns final value
 Updates automatically if data changes

In a worksheet, logical functions:

 Compare values
 Evaluate conditions
 Return text, numbers, or calculations
 Work row by row
 Recalculate automatically

If you want, I can next give:

 Complete worksheet practice table (10–15 questions)

 Lab exam format with procedure
 Viva questions
 Short 5-mark/10-mark answer format
 Real-life business examples

Tell me which one you need 😊

Ranking in Excel – Procedures, Calculation & Examples

(Using Microsoft Excel)

Ranking is used to find the position of a number in a list (like marks, sales, salary, etc.).
1️What is Ranking?
Ranking assigns a position:

 Highest value → Rank 1 (if descending)

 Lowest value → Rank 1 (if ascending)

Excel uses:

 RANK() (older versions)

 [Link]() (newer versions)
 [Link]() (for average rank in case of tie)

2️Syntax of Ranking Function

🔹 [Link] (Most Common)
=[Link](number, ref, [order])

🔹 Meaning:

 number → The value you want to rank

 ref → Range of numbers
 order →
o 0 → Descending (Highest = Rank 1)
o 1 → Ascending (Lowest = Rank 1)

3️Example 1: Ranking Students (Highest

Marks = Rank 1)
Worksheet Data

A (Student) B (Marks) C (Rank)

John 85 ?
Mary 92 ?
A (Student) B (Marks) C (Rank)
Alex 78 ?
Sara 88 ?

Formula (In C2):

=[Link](B2,$B$2:$B$5,0)

How Excel Calculates (For John – 85)

Marks list: 85, 92, 78, 88

Step 1: Descending order → 92, 88, 85, 78

Step 2: Position of 85 → 3rd

Result → Rank 3

Final Ranks

Marks Rank
85 3
92 1
78 4
88 2

4️Example 2: Ascending Ranking (Lowest

Salary = Rank 1)
Worksheet Data

A (Employee) B (Salary) C (Rank)

A 30000 ?
B 50000 ?
C 25000 ?
D 40000 ?
Formula:
=[Link](B2,$B$2:$B$5,1)

(1 means ascending)

How Excel Calculates (For 30000)

Ascending order → 25000, 30000, 40000, 50000

Position of 30000 → 2

Result → Rank 2

✅ Final Ranks

Salary Rank
30000 2
50000 4
25000 1
40000 3

5️Example 3: Ranking with Tie Values

Worksheet Data

A (Marks) B (Rank)
90 ?
80 ?
90 ?
70 ?

Formula:
=[Link](A2,$A$2:$A$5,0)

How Excel Calculates

Descending order → 90, 90, 80, 70

Both 90s are in position 1 and 2

[Link] gives both → Rank 1

Next rank will skip to Rank 3

Result:

Marks Rank
90 1
80 3
90 1
70 4

6️[Link] (For Average Rank in Tie)

If two students score 90:

Positions: 1 and 2
Average rank = (1+2)/2 = 1.5

Formula:
=[Link](A2,$A$2:$A$5,0)

Result for both 90s → 1.5

7️Procedure to Perform Ranking in

Worksheet
Step 1: Enter Data

Type numbers (marks, salary, etc.)

Step 2: Select Rank Cell

Click first cell where rank should appear

Step 3: Enter Formula

Example:

=[Link](B2,$B$2:$B$5,0)

Step 4: Use Absolute Reference

Press F4 after selecting range

Example:

$B$2:$B$5

This keeps range fixed while copying.

Step 5: Press Enter

Step 6: Drag Fill Handle

Copy formula down for other rows

8️How Excel Calculates Ranking Internally

1. Excel reads the target number
2. Looks at full reference range
3. Sorts values internally (not visible)
4. Determines position
5. Returns rank
6. Recalculates automatically if values change

9 Important Points
✔ Use 0 for highest rank first
✔ Use 1 for lowest rank first
✔ Always lock range with $
✔ Tie values get same rank in [Link]
✔ Use [Link] for average rank
 Real-Life Example
Imagine you are calculating student marks:

Student Marks
80
75
90
85
70

Using:

=SUM(A1:A5)

You can quickly find the total marks.

9️ Cell Referencing
Cell referencing means using a cell’s address (like A1) inside a formula.

When you copy a formula from one cell to another, Excel may change the cell references
automatically. The way Excel changes (or does not change) the reference depends on the type of
cell reference used.

There are three types of cell references:

1. Relative Reference
2. Absolute Reference
3. Mixed Reference

Let us explain each one clearly with examples.

1️ Relative Reference
Meaning:
A relative reference changes automatically when you copy the formula to another cell.

Example:

A1

(No dollar signs.)

How It Works

Suppose you have:

A B
10
20
30

In cell B1, type:

=A1

Now copy the formula from B1 down to B2 and B3.

What Happens?

 In B1 → =A1
 In B2 → =A2
 In B3 → =A3

Excel automatically changes the row number.

This is called relative referencing because the reference changes relative to the new position.

Real-Life Example

If you calculate totals like this:

In C1:

=A1+B1

When you copy it down, Excel changes it to:

=A2+B2
=A3+B3

This is very useful when performing the same calculation for many rows.

2️ Absolute Reference
Meaning:

An absolute reference does NOT change when copied.

It uses dollar signs ($):

$A$1

The dollar sign locks:

 Column (A)
 Row (1)

How It Works

Suppose:

A B
100
200
300
Tax Rate 10%

Assume:

 Tax rate is in cell A4 (10%)

Now in B1 type:

=A1*$A$4

Then copy the formula down.

What Happens?
  In B1 → =A1*$A$4
 In B2 → =A2*$A$4
 In B3 → =A3*$A$4

Notice:

 A1 changes to A2, A3 (relative part)

 $A$4 stays the same (absolute reference)

👉 The tax rate does not move because it is fixed.

Why Use Absolute Reference?

When you want to:

 Multiply many values by the same number

 Use a fixed constant value
 Refer to a fixed cell like tax rate, discount rate, exchange rate

3️ Mixed Reference
Meaning:

A mixed reference locks either the row OR the column, but not both.

There are two types:

1. $A1 → Column locked, row changes

2. A$1 → Row locked, column changes

🔹 Example 1: A1 (Column Locked)

The column A is fixed.
The row changes.

If copied down:

$A1 → $A2 → $A3

Column A stays the same.
Row number changes.

🔹 Example 2: A$1 (Row Locked)

The row 1 is fixed.
The column changes.

If copied across:

A$1 → B$1 → C$1

Row 1 stays fixed.

Column changes.

Practical Example of Mixed Reference

Suppose you have:

BCD
12 3 4
25
36

You want to multiply:

Row values by column headings.

You may use:

=$A2*B$1

This locks:

 Column A
 Row 1

So formulas copy correctly across and down.

 Simple Comparison Table
Type Example Changes When Copied?
Relative A1 Yes
Absolute $A$1 No
Mixed $A1 or A$1 Partially

How to Add Dollar Signs Quickly

Instead of typing $ manually:

1. Click on the cell reference in the formula.

2. Press F4 on the keyboard.
3. Excel automatically adds dollar signs.

Press F4 repeatedly to switch between:

 A1
 $A$1
 A$1
 $A1

10 Formatting in Excel
Formatting Text
To Bold:

1. Select cell
2. Click Bold (B)

To Change Font Size:

1. Select cell
2. Choose size from Home tab
Formatting Numbers
1. Select cells
2. Go to Home tab
3. Choose:
o Currency
o Percentage
o Number
o Date

11 Inserting and Deleting

Inserting and Deleting Rows and Columns in Microsoft
Excel (Detailed Explanation)
In Excel, you often need to add new rows or columns to include more data or delete unwanted
rows or columns to clean up your worksheet. Let’s explain each operation step by step.

1️Inserting a Row
A row is horizontal, and inserting a row will push the existing rows down.

🔹 Step 1: Select the Row Number

 Look at the row numbers on the left side of the worksheet.

 Right-click the number where you want to insert a new row.

Example:
If you want to insert a row between row 3 and row 4:

 Right-click row 4.

🔹 Step 2: Click Insert

  From the menu that appears, click Insert.
 A new blank row will appear above the row you selected.

📘 Example:

Row Name Marks

1 John 85
2 Mary 90
3 Peter 78

 Right-click row 2 → Click Insert

Result:

Row Name Marks

1 John 85
2
3 Mary 90
4 Peter 78

Notice: Existing rows shift down automatically.

Computer Network Tools, Materials, Setup & Configuration Notes

1. Network Tools and Materials Assembly

a) Basic Network Tools

These are tools used when installing, maintaining, and troubleshooting networks.

 Cable Crimpers
Used to attach connectors (e.g., RJ45) to network cables.
 Cable Strippers
Remove outer insulation from cables without damaging inner wires.
 Cutters / Scissors / Screwdrivers / Pliers
o Cutters: cut wires
o Screwdrivers: tighten or loosen screws
o Pliers: grip and bend wires
 Cable Tie Tools
Help fasten and organize cables neatly.
  Fiber Optic Tools
Used to handle fiber cables (cutting, polishing, testing).
 Insertion–Extraction Tools
Used to insert or remove components like connectors safely.
 Manual/Automatic Switch Boxes
Used to switch between network connections.
 Network Testers
Check if cables are working correctly.
 Punch Down Tools
Used to insert wires into patch panels or sockets.
 Driver Installers
Software tools used to install network device drivers.
 Multimeter
Measures voltage, current, and resistance in cables.
 Tone Generator and Probe
Helps trace cables and identify faults.

b) Tools Usage and Safety

 Always use the correct tool for the task

 Wear protective gear (gloves, goggles)
 Avoid working with live electrical cables
 Keep tools clean and well stored
 Follow manufacturer instructions

2. Computer Network Materials

 Network Cables – Used to connect devices (e.g., Ethernet cables)
 Cable Trunking Covers – Protect and organize cables
 Connectors – Join cables (e.g., RJ45 connectors)
 RJ45 Sockets – Network wall outlets
 Patch Cords – Short cables used to connect devices
 Cable Ties – Used for cable management

3. Computer Network Setup

a) Network Cabling and Installation

Process of laying cables and connecting devices in a network.

b) Network Design Layout

Plan showing how devices are connected.

c) Cabling Standards and Codes

Rules that ensure proper installation (e.g., safety and performance standards).

d) Cable Termination

Attaching connectors to cables correctly.

e) Wireless Network Setup

Installing wireless devices like routers and access points.

f) Cable Management Best Practices

 Avoid tangled cables

 Label cables clearly
 Use trunking and ties

⚙️4. Computer Network Devices Configuration

a) Network Models

 TCP/IP – Used in real-world networking

 OSI Model – Conceptual model with 7 layers

b) IP Addressing

 Identifies devices on a network

Types:

 IPv4 – Uses numbers (e.g., [Link])

 IPv6 – Uses hexadecimal (more advanced)

Classful Addressing:

 Class A, B, C, D, E
CIDR (Subnetting):

 Divides networks into smaller parts

c) IP Address Assignment

 Static IP – Manually assigned

 Dynamic IP – Automatically assigned (DHCP)

d) Switch and Router Configuration

 Initial setup
 Assign IP addresses
 Configure ports

e) Routing Protocols

 EIGRP
 RIP
 OSPF

Used to determine best path for data.

f) Advanced Configurations

 VLANs – Separate network segments

 ACLs – Control access
 NAT & PAT – Translate IP addresses
 Port Security – Prevent unauthorized access
 STP – Prevent loops
 LACP – Combine multiple links

5. Wireless Access Point Configuration

 Set up Access Points (APs)
  Configure SSID (network name)
 Use services like DHCP, DNS, SMTP
 Apply wireless security (passwords, encryption)
 Manage users and devices

6. Network Security Configuration

a) Network Privileges

Permissions given to users.

b) Types of Privileged Accounts

 Administrator
 Standard user

c) Firewall and Security Policies

Control incoming and outgoing traffic.

7. Computer Network Documentation

a) Definition

Written records of network setup and operations.

b) Importance

 Helps in troubleshooting
 Improves maintenance
 Saves time

c) Types

 Physical (hardware layout)

 Logical (data flow)
 Configuration (settings)
8. Computer Network Components Disposal
a) Network Waste

Old or damaged network equipment.

b) Types

 E-waste – electronic waste

c) Hazards

 Environmental pollution
 Health risks

d) Disposal Methods

 Recycling
 Reuse
 Safe disposal through authorized agencies

e) Legal Regulations (Kenya Context)

 Waste Management Act 2022

Governs proper waste handling.
 Environmental Management and Coordination Act 2015
Protects environment from harmful waste.
 Public Procurement and Asset Disposal Act 2015
Guides disposal of government assets.

Documentation ensures proper management

 Proper disposal protects the environment

2️Inserting a Column
A column is vertical, and inserting a column will push the existing columns to the right.

🔹 Step 1: Select the Column Letter

 Look at the column letters at the top of the worksheet.

 Right-click the letter where you want to insert a new column.

Example:
If you want a new column between column A and B:

 Right-click column B.

🔹 Step 2: Click Insert

 Click Insert from the menu.

 A new blank column appears to the left of the selected column.

📘 Example:

A B C
Name Marks Grade

 Right-click B → Click Insert

Result:

A B C D
Name Marks Grade

✅ Notice: Existing columns shift to the right automatically.

3️Deleting a Row
Deleting a row removes the row completely, and the rows below move up.
🔹 Step 1: Select the Row Number

 Right-click the row number you want to delete.

Example: Delete row 3.

🔹 Step 2: Click Delete

 Click Delete from the menu.

 The selected row is removed, and rows below move up.

📘 Example:

Row Name Marks

1 John 85
2 Mary 90
3 Peter 78

 Delete row 2

Result:

Row Name Marks

1 John 85
2 Peter 78

4️Deleting a Column
Deleting a column removes the column completely, and the columns to the right shift left.

🔹 Step 1: Select the Column Letter

 Right-click the column letter you want to delete.

Example: Delete column B (Marks).

🔹 Step 2: Click Delete

 Click Delete from the menu.

 Column B is removed, and the other columns shift left.

Example:

A B C
Name Marks Grade

 Delete column B

Result:

A B
Name Grade
x

Important Tips

12 Sorting and Filtering

Sorting Data (Procedure)
1. Select data
2. Click Data tab
3. Click Sort A–Z or Z–A

Filtering Data (Procedure)

1. Select data
2. Click Data tab
3. Click Filter
4. Choose filter option
1 Charts in Excel
Charts help visualize data.

Types of Charts:

 Column chart
 Bar chart
 Line chart
 Pie chart

Procedure: Creating a Chart

1. Select data
2. Click Insert
3. Choose chart type
4. Chart appears automatically

1️4 Saving a Workbook

Procedure:
1. Click File
2. Click Save As
3. Choose location
4. Enter file name
5. Click Save

1️5 Printing in Excel

Procedure:
1. Click File
2. Click Print
3. Adjust settings
4. Click Print
1️6 Page Setup
You can:

 Change orientation (Portrait/Landscape)

 Set margins
 Adjust paper size

1️7 Protecting Data

Protect Worksheet:
1. Click Review tab
2. Click Protect Sheet
3. Enter password
4. Click OK

1️8Advantages of Microsoft Excel

✔ Fast calculations
✔ Automatic recalculation
✔ Data organization
✔ Graphical representation
✔ Easy data analysis
✔ Used in business and education

1️9 Uses of Microsoft Excel

 Budgeting
 Payroll systems
 Student grading
 Inventory management
 Financial analysis
 Business reporting
What is a Spreadsheet?

A spreadsheet is a computer application used to organize, analyze, and store data in a tabular
(row and column) format. It allows users to perform calculations automatically using formulas
and functions.

Popular spreadsheet programs include:

 Microsoft Excel
 Google Sheets
 LibreOffice Calc