COMPUTER PACKAGES AND ICT RESOURCES: NOTES/PRACTICAL GUIDE

📘 Microsoft Excel – Comprehensive Notes & Practical Guide

1. Introduction to Microsoft Excel

What is Excel?

Microsoft Excel is a spreadsheet program used for data organization, analysis, and visualization. It helps in managing data through tables, formulas, charts, and pivot tables.

Key Features

  • Data entry and management

  • Mathematical and logical calculations

  • Data visualization (charts, graphs)

  • Data analysis (PivotTables, Power Query)

  • Automation with Macros and VBA

Excel Interface Components

Element Description
Workbook The entire file (e.g., SalesReport.xlsx)
Worksheet A single spreadsheet tab inside a workbook
Cell The intersection of a row and column (e.g., A1)
Ribbon Toolbar with tabs (Home, Insert, Formulas, etc.)
Formula Bar Displays or edits cell formulas
EXCEL NOTESDownload
SPREADSHEET_Download

2. Basic Operations

Cell Data Types

  • Text – words, labels (e.g., “January”)

  • Numbers – numeric values for calculations

  • Date/Time – recognized formats for scheduling

  • Formulas – start with =, perform calculations (e.g., =A1+B1)

Basic Data Entry & Navigation

  • Move with Arrow Keys

  • Jump to cell: Ctrl + Arrow Key

  • Select all: Ctrl + A

  • Insert row/column: Ctrl + Shift + “+”

  • Delete row/column: Ctrl + “–”

➕ 3. Formulas and Functions

Formulas are Excel’s backbone — always start with =.

Example:

 
=A1+B1

Common Mathematical Functions

Function Purpose Example
SUM() Adds values =SUM(A1:A5)
AVERAGE() Finds mean =AVERAGE(B2:B10)
MIN() / MAX() Minimum / Maximum =MAX(C2:C10)
COUNT() Counts numbers =COUNT(A1:A10)
ROUND() Rounds number =ROUND(A1,2)

Logical Functions

Function Description Example
IF() Conditional check =IF(A1>50,"Pass","Fail")
AND() / OR() Logical tests =AND(A1>0, B1<100)
NOT() Negates logic =NOT(A1=5)

Text Functions

Function Description Example
CONCATENATE() / & Joins text =A1&" "&B1
LEFT() / RIGHT() Extracts text =LEFT(A1,3)
LEN() Counts characters =LEN(A1)
UPPER(), LOWER(), PROPER() Text case conversion =PROPER(A1)

Lookup Functions

Function Description Example
VLOOKUP() Vertical lookup =VLOOKUP(A2,Table,2,FALSE)
HLOOKUP() Horizontal lookup =HLOOKUP(A2,Table,2,FALSE)
XLOOKUP() (Newer Excel) More powerful lookup =XLOOKUP(A2,A:A,B:B)
INDEX() + MATCH() Dynamic lookup combo =INDEX(B:B,MATCH(A2,A:A,0))

👉 Practice: Excel Lookup Practice File (Microsoft Support)

📊 4. Data Formatting and Visualization

Formatting

  • Change font, color, alignment

  • Number formats: currency, percentage, date

  • Conditional formatting: Home → Conditional Formatting

    • Example: Highlight cells >100

Charts and Graphs

Chart Type Use Case
Column/Bar Compare categories
Pie Show proportions
Line Track trends over time
Scatter Show correlation
Combo Two data series

👉 Example Practical:
Create a monthly sales chart using Insert → Charts → Column.

🧮 5. Data Tools

Tool Description Where to Find
Sort & Filter Organize data Home → Sort & Filter
Remove Duplicates Clean dataset Data → Data Tools
Data Validation Restrict input Data → Data Tools → Data Validation
What-If Analysis Scenario tools Data → Forecast → What-If

🧱 6. Pivot Tables & Pivot Charts

What is a PivotTable?

A PivotTable helps summarize and analyze large datasets quickly.

Steps to Create:

  1. Select data

  2. Go to Insert → PivotTable

  3. Choose fields for Rows, Columns, Values, Filters

Example:
To summarize sales by region:

  • Rows: Region

  • Values: SUM(Sales)

PivotChart: Visual version of a PivotTable.

👉 Practice File: Microsoft PivotTable Tutorial

⚙️ 7. Data Analysis Tools

Tool Purpose
Goal Seek Find input for desired result
Solver Advanced goal seeking with constraints
Descriptive Statistics Summarize data (via Data Analysis ToolPak)
Power Query Clean and transform data
Power Pivot Handle large datasets with relationships

🧠 8. Excel Shortcuts (Windows)

Action Shortcut
Save Ctrl + S
Copy / Paste Ctrl + C / Ctrl + V
Undo / Redo Ctrl + Z / Ctrl + Y
Select all Ctrl + A
Insert Row Ctrl + Shift + +
Delete Row Ctrl + -
AutoSum Alt + =
Create Chart Alt + F1

👉 Full list: Microsoft Excel Shortcut Reference

🔄 9. Excel Automation (Macros & VBA)

What are Macros?

Macros record repetitive tasks and replay them automatically.

How to Record a Macro:

  1. View → Macros → Record Macro

  2. Perform actions

  3. Stop Recording

Example VBA Code:

 
Sub AutoFillDate() Range("A1:A10").Value = Date End Sub

👉 Tutorial: Intro to VBA in Excel (Microsoft Learn)

📈 10. Practical Exercises

Exercise Skills Covered
Sales Tracker Data entry, formulas, charting
Student Marks Sheet IF statements, grading logic
Inventory Dashboard Pivot tables, charts, conditional formatting
Budget Planner SUM, AVERAGE, Scenario Analysis
Employee Database Data validation, lookup functions

🧾 11. Tips for Effective Excel Use

  • Use tables instead of raw ranges (Ctrl + T)

  • Always name ranges for clarity

  • Keep formulas consistent and documented

  • Protect sheets to prevent accidental edits

  • Regularly save and back up your work

📚 12. Additional Learning Resources

✅ End-of-Course Checklist

Task Completed
Mastered basic formulas ☐
Created a chart ☐
Built a PivotTable ☐
Used conditional formatting ☐
Automated a task with a Macro ☐