G.C.E. A/L ICT Tamil Medium Competency 1.1 | தரவு மற்றும் தகவல் Sri Lankan G.C.E. A/L ICT Tamil Medium complete notes for Competency 1.1. Covers Data, Information, Data Life Cycle, Big Data (5Vs), and Valuable

📚 G.C.E. A/L ICT (தமிழ் ஊடகம்)

Competency 1.1: தகவலின் அடிப்படை கூறுகள் மற்றும் அவற்றின் பண்புகள்

📌 இந்தப் பாடத்தின் கற்றல் நோக்கங்கள்: தரவு (Data), தகவல் (Information), Data Life Cycle, தரமான தகவலின் பண்புகள், மற்றும் Big Data பற்றிய அடிப்படை அறிவை Theory, Examples, மற்றும் Question & Answer வடிவில் எளிமையாகக் கற்றுக்கொள்ளலாம்.

Theory

Data (தரவு) என்றால் என்ன?

Data என்பது எந்தவொரு செயலாக்கமும் (Process) செய்யப்படாத உண்மைகள் (Raw Facts) அல்லது மதிப்புகள் ஆகும். இவை தனியாக இருந்தால் பெரிதாக அர்த்தம் தராது.

• எண்கள், எழுத்துக்கள், படங்கள், ஒலி, வீடியோ, குறியீடுகள் போன்ற பல வடிவங்களில் இருக்கும்.

உதாரணம்: 45, 67, 89 அல்லது John, Male, 25, Colombo

Theory

Information (தகவல்) என்றால் என்ன?

Data-வை Process (செயலாக்கம்) செய்த பிறகு கிடைப்பதே Information ஆகும். இது Meaningful (அர்த்தமுள்ள), Useful (பயனுள்ள), மற்றும் Decision Making (முடிவெடுக்க) உதவும்.

உதாரணம்: 65, 70, 90, 80 (Data) → Average: 76.25 (Information)

Comparison

Data மற்றும் Information இடையிலான வித்தியாசம்

Data (தரவு)	Information (தகவல்)
Raw Facts (மூல உண்மைகள்)	Processed Data (செயலாக்கப்பட்ட தரவு)
Meaning (அர்த்தம்) இல்லை	Meaning (அர்த்தம்) உள்ளது
Decision (முடிவு) எடுக்க முடியாது	Decision எடுக்க உதவும்
Input (உள்ளீடு)	Output (வெளியீடு)

Q&A

கேள்வி: Data மற்றும் Information இன் இரண்டு வித்தியாசங்களை எழுதுக.

பதில்: 1. Data என்பது Raw Facts. 2. Information என்பது Process செய்யப்பட்ட Data.

Theory

Data Life Cycle (தரவு வாழ்க்கைச் சுழற்சி)

Data தொடர்ந்து கீழ்க்கண்ட கட்டங்களைக் கடக்கிறது:

Creation → Storage → Processing → Use → Sharing → Archive → Delete

1. Data Creation: Keyboard, Sensors, Scanner, Camera, Online Form மூலம் புதிய Data உருவாக்கப்படுவது.

2. Data Management: Data-வை Store, Update, Backup, Protect, Recover செய்வது (எ.கா: Bank Database).

3. Removal of Obsolete Data: பயன்பாடு இல்லாத பழைய Data-வை நீக்குதல். (நன்மைகள்: Memory Save, Fast Processing, Security, Less Cost).

Theory

Data இன் வகைகள்

• Numeric Data: 25, 100, 450
• Text Data: Faaz, School, ICT
• Image Data: Photo, PNG, JPEG
• Audio Data: MP3, WAV
• Video Data: MP4, AVI

Q&A

கேள்வி: Data Life Cycle என்றால் என்ன?

பதில்: Data உருவாக்கப்பட்டு (Creation), பாதுகாக்கப்பட்டு (Management), இறுதியில் பயன்பாடு இல்லாத போது நீக்கப்படுவது (Removal) வரையிலான சுழற்சி.

Theory

Quality Data (தரமான தரவு)

தரமான Data ஆனது Correct (சரியான), Complete (முழுமையான), Consistent (நிலையான), Reliable (நம்பகமான), மற்றும் Valid (செல்லுபடியாகக்கூடிய)தாக இருக்க வேண்டும்.

உதாரணம்: Name, Age, Address, Phone அனைத்தும் சரியாக இருந்தால் அது Quality Data.

Theory

Valuable Information இன் 5 முக்கிய பண்புகள்

• 1. Timeliness: தேவையான சரியான நேரத்தில் தகவல் கிடைப்பது. (எ.கா: இன்றைய Weather Report).
• 2. Accuracy: தகவல் பிழையின்றி துல்லியமாக இருப்பது. (எ.கா: Exam Marks சரியாக இருத்தல்).
• 3. Context: தகவல் தேவையான சூழ்நிலைக்குப் பொருத்தமாக இருப்பது. (எ.கா: Doctor-க்கு நோயாளியின் Health Report).
• 4. Understandability: தகவல் Chart, Graph, Table மூலம் எளிதாகப் புரியக்கூடியதாக இருப்பது.
• 5. Less Uncertainty: சரியான முடிவெடுக்கத் தேவையான தெளிவான தகவல் (சந்தேகத்தைக் குறைப்பது). (எ.கா: GPS Navigation).

Theory

Big Data என்றால் என்ன?

Traditional Database மூலம் கையாள முடியாத மிகப்பெரிய அளவிலான Data ஆகும். Facebook, Google, YouTube, Hospitals, Banks, IoT Devices போன்றவை இதன் முக்கிய மூலங்களாகும்.

Theory

Big Data இன் 5V க்கள்

• Volume: மிகப்பெரிய அளவு (Huge Amount).
• Velocity: மிக வேகமாக உருவாகும் தரவு (High Speed).
• Variety: பல வகையான Data (Text, Image, Video, etc.).
• Veracity: தரவின் நம்பகத்தன்மை மற்றும் துல்லியம்.
• Value: அந்தத் தரவிலிருந்து பெறக்கூடிய பயனுள்ள தகவல்.

Application

Big Data Analysis பயன்பாடுகள்

• Business Intelligence (வணிக நுண்ணறிவு)
• Weather Forecast (வானிலை முன்னறிவிப்பு)
• Medical Research (மருத்துவ ஆராய்ச்சி)
• Fraud Detection (மோசடி கண்டறிதல்)
• Artificial Intelligence & Recommendation Systems

Q1

Data என்றால் என்ன?

பதில்: செயலாக்கப்படாத (Process செய்யப்படாத) உண்மைகள் அல்லது மதிப்புகள் Data எனப்படும்.

Q2

Information என்றால் என்ன?

பதில்: Process (செயலாக்கம்) செய்யப்பட்ட Data Information ஆகும்.

Q3

Data மற்றும் Information இடையிலான வேறுபாடுகளை எழுதுக.

பதில்: Data என்பது Raw Facts மற்றும் இதற்கு அர்த்தம் இருக்காது. Information என்பது Process செய்யப்பட்ட, அர்த்தமுள்ள Data ஆகும்.

Q4

Valuable Information இன் பண்புகள் யாவை?

பதில்: Timeliness, Accuracy, Context, Understandability, Less Uncertainty.

Q5

Big Data என்றால் என்ன?

பதில்: சாதாரண Database மூலம் கையாள முடியாத, மிகப்பெரிய அளவு, வேகம் மற்றும் பல்வகை தன்மை கொண்ட தரவுத் தொகுப்புகள்.

Q6

Big Data இன் 5V-களை எழுதுக.

பதில்: Volume, Velocity, Variety, Veracity, Value.

❓ அடிக்கடி கேட்கப்படும் கேள்விகள் (FAQ)

Q: Data (தரவு) என்றால் என்ன? A: Data என்பது எந்தவொரு செயலாக்கமும் (Process) செய்யப்படாத உண்மைகள் (Raw Facts) அல்லது மதிப்புகள் ஆகும். இவை தனியாக இருந்தால் பெரிதாக அர்த்தம் தராது.

Q: Data மற்றும் Information இடையிலான வேறுபாடு என்ன? A: Data என்பது செயலாக்கப்படாத மூல உண்மைகள் (Raw Facts) மற்றும் இதற்கு அர்த்தம் இருக்காது. Information என்பது செயலாக்கப்பட்ட (Processed) Data ஆகும், இது அர்த்தமுள்ளதாகவும் முடிவெடுக்க உதவுவதாகவும் இருக்கும்.

Q: Big Data இன் 5V க்கள் யாவை? A: Big Data இன் 5V க்கள்: Volume (அளவு), Velocity (வேகம்), Variety (பல்வகைமை), Veracity (நம்பகத்தன்மை), மற்றும் Value (மதிப்பு/பயன்).

Q: மதிப்புள்ள தகவலின் (Valuable Information) பண்புகள் யாவை? A: மதிப்புள்ள தகவலின் முக்கிய பண்புகள்: Timeliness (சரியான நேரம்), Accuracy (துல்லியம்), Context (சூழல்), Understandability (புரிதல் தன்மை), மற்றும் Less Uncertainty (குறைந்த நிச்சயமற்ற தன்மை).

🎓 Expert ICT, Coding, School Classes, Digital Marketing & University Project Guidance

Struggling with your university final year project? Want to master coding, upscale your business with expert digital marketing, or learn absolute computer basics from scratch? We offer high-quality individual and group online classes conducted in English, Sinhala, or Tamil mediums. Get guaranteed academic success and professional growth with tailored guidance.

---

🎓 University Final Year Project Guidance & AI

Get specialized, end-to-end mentoring and technical support to pass your degree or master's program with flying colors:

• 🏫 Targeted Institutes: Expert guidance tailored for BIT UCSC, UoM, SLIIT, NIBM, and other leading universities.
• 🔬 Postgraduate Support: Comprehensive assistance for MSc Software Final Year Projects.
• 🤖 AI & Smart Applications: Step-by-step implementation of AI, Machine Learning (ML), and automation modules.
• ✅ Guaranteed Success: Help with documentation, system architecture, coding, and viva preparation.

---

🏫 School ICT & Corporate Beginner Classes

• 💻 Non-IT Staff Computer Basics: Absolute beginner-friendly online classes covering essential computer skills, office tools, and internet operations.
• 🎒 Primary & Secondary (Grades 1-10): Interactive online ICT classes tailored to build strong foundations from early ages.
• 📝 Exam Prep: Dedicated training packages for GCE O/L, GCE A/L ICT, and GIT exams.
• 🌍 Global Syllabuses: Complete curriculum coverage for Local, Edexcel, and Cambridge in English & Tamil Mediums.

---

📢 Software Development & Digital Marketing Services

• ⚙️ Software & Web Development: Professional custom software application and website development built using PHP & MySQL.
• 🎯 Social Media Management: Content creation, publishing, and channel management for Facebook, Instagram, TikTok, and YouTube.
• 📈 Ad Boosting: Highly targeted paid advertising campaigns to drive leads, traffic, and sales to your business.

---

📞 Connect With Us Instantly

Book your slot for online classes or get a premium tech service quote today!

💬 WhatsApp: +94 729622034

📧 Email: ITClassSL@gmail.com

---

🌐 Explore Our Resources & Communities

Stay updated with our latest tutorials, project ideas, and student guides across all our official platforms:

• 📺 YouTube Tutorials: Subscribe to our Channel
• 💼 Professional Network: Connect on LinkedIn
• ✍️ Tech Blog: Visit our WordPress Site
• ❓ Project Q&A: Follow our Quora Guide Profile
• 📰 Monthly Updates: Read Our Newsletter
• 🌐 Official Portfolios: Wix Site | Google Business | Strikingly Portfolio
• 🗣️ Student Forum: Join our ElaKiri Discussion Thread

G.C.E. A/L ICT Exam Solutions Part II | G12-ICT-TM-FWC-P2 Hardware, Networks & OS Complete step-by-step solutions for A/L ICT Part II. Covers Computer Generations, Network Topology, TCP/UDP, Virtual Memory, Paging, and FAT with exam tips

🎓 G.C.E. A/L ICT Exam Solutions (Part II)

Hardware, Networks, Operating Systems & Step-by-Step Calculations

📚 About this guide: Complete, step-by-step solutions for G.C.E. Advanced Level ICT Part II structured questions. Covers Computer Evolution, Network Topology, TCP/UDP, Virtual Memory, Paging, and File Allocation Table (FAT) with clear formulas, diagrams, and exam-oriented memory tricks.

Q1✅ (5) John Presper Eckert

Principal Inventor of ENIAC

Theory: ENIAC was the world's first general-purpose electronic digital computer, designed by John Presper Eckert and John W. Mauchly.

🎯 Exam Memory Trick:
ENIAC → Eckert & Mauchly
Stored Program → Von Neumann
Analytical Engine → Charles Babbage
First Programmer → Ada Lovelace

Q2✅ (3) Integrated Circuits

Third Generation Computers

Theory: 1st Gen = Vacuum Tubes, 2nd Gen = Transistors, 3rd Gen = Integrated Circuits (IC), 4th Gen = VLSI/Microprocessors, 5th Gen = AI.

Q3✅ (4) Flash Memory & ROM

Non-Volatile Memory

Theory: Non-volatile memory retains data when power is off. RAM, Cache, and Registers are volatile. Flash and ROM are non-volatile.

Q4✅ (2) RAM

Outside the Microprocessor

Theory: ALU, Control Unit, Registers, and Cache are inside the microprocessor. RAM, Hard Disks, and SSDs are outside (external memory).

Q5✅ (5) Store BIOS program

NOT a typical use of RAM

Theory: RAM is temporary and volatile, used for running programs and temporary data. The BIOS program is permanent and must be stored in non-volatile ROM.

Q2(v)✅ Network Diagram

ABC Company Network Design

Theory: 4 Departments (D01-D04) each need a Switch. All switches connect to a central Router. The Router connects to a Firewall, which connects to the Internet. Proxy and DNS servers sit in the ICT Services (D04) department.

                        Internet
                            │
                      +-------------+
                      |  Firewall   |
                      +-------------+
                            │
                      +-------------+
                      |   Router    |
                      +-------------+
          ┌────────────┼─────────────┬─────────────┐
     +---------+  +---------+  +---------+  +---------+
     | Switch  |  | Switch  |  | Switch  |  | Switch  |
     +---------+  +---------+  +---------+  +---------+
          │            │             │             │
     Accounts      Sales      Admin      ICT Services
      (D01)        (D02)        (D03)        (D04)
                                              │
                         ┌────────────────────┴───────────────┐
                  +---------------+                  +---------------+
                  | Proxy Server  |                  |  DNS Server   |
                  +---------------+                  +---------------+
          

Q2(vi)✅ TCP vs UDP

Differences Between TCP and UDP

Feature	TCP	UDP
Reliability	Reliable (checks delivery)	Unreliable (no checks)
Connection	Connection-oriented	Connectionless
Speed	Slower (error checking)	Faster (no overhead)
Examples	HTTP, Email, FTP	Video Streaming, VoIP, DNS

🎯 Exam Memory Trick:
TCP = Trustworthy (Reliable, slower)
UDP = Ultra Fast (Unreliable, faster)

Q3(a)✅ 4 bits & 12 bits

Page Number & Offset Bits

Given: 16-bit Virtual Address, Page Size = 4 KB.

Step 1 (Offset): 4 KB = 4096 Bytes = 2¹². Therefore, Offset = 12 bits.

Step 2 (Page Number): 16 total bits - 12 offset bits = 4 bits.

+------------+----------------+
| Page Number|     Offset     |
|   4 bits   |    12 bits     |
+------------+----------------+
          

Q3(b)(i)✅ 24788

Physical Address Calculation

Given: Virtual Address = 12500.

Step 1: Page Number = 12500 ÷ 4096 = 3 (Remainder/Offset = 212).

Step 2: Page Table shows Page 3 is in Frame 6 (110₂).

Step 3: Physical Address = (Frame × Page Size) + Offset = (6 × 4096) + 212 = 24788.

Q3(b)(ii-iv)

Paging Concepts

• Why a page is absent: 1) Not loaded into RAM yet. 2) Swapped out to disk due to full RAM.
• Advantages of Paging: Eliminates external fragmentation; supports virtual memory (running programs larger than physical RAM).
• Hardware for mapping: Memory Management Unit (MMU) inside the CPU.

Q3(c)(i)✅ Block 503

First Block of promax.py

Theory: Trace the chain backwards. The first block is the one not pointed to by any other block.

Chain: 503 → 501 → 500 → 502 → -1 (End).

Q3(c)(ii)✅ 16 KB

Disk Space Occupied

Theory: The file uses 4 blocks (503, 501, 500, 502). Each block is 4 KB.

Calculation: 4 blocks × 4 KB = 16 KB.

Q3(c)(iii)✅ Updated FAT

File Grows to 20 KB

Theory: Needs 1 more block (20 KB - 16 KB = 4 KB). The free block is 504.

Action: Change block 502's pointer from -1 to 504. Change block 504's pointer to -1.

Block   Next Block
500     502
501     500
502     504  ← Updated
503     501
504     -1   ← Updated (End)
          

Quick Answer Key

Q	Answer	Topic
1	5	ENIAC (Eckert & Mauchly)
2	3	3rd Gen = Integrated Circuits
3	4	Non-Volatile = Flash & ROM
4	2	RAM is outside Microprocessor
5	5	BIOS is stored in ROM, not RAM
2v	Diagram	Router → Firewall → Internet
2vi	Table	TCP (Reliable/Slow) vs UDP (Fast)
3a	4 & 12	Page Number & Offset bits
3bi	24788	Physical Address Calculation
3biv	MMU	Hardware for address mapping
3ci	503	First FAT Block
3cii	16 KB	File Size (4 blocks × 4KB)

⭐ Most Important A/L ICT Topics to Master:
🖥️ Computer Generations & Logic Gates (Half/Full Adder)
🧮 Number Systems & Binary Arithmetic
🌐 IP Addressing, CIDR, VLSM, and Network Devices
🧠 Operating Systems: Process States, Virtual Memory, Paging, FAT
🛠️ SDLC, Software Testing, and Feasibility Studies

❓ Frequently Asked Questions (FAQ)

Q: Who invented ENIAC and what was its significance? A: ENIAC (Electronic Numerical Integrator and Computer) was the world's first general-purpose electronic digital computer, designed by John Presper Eckert and John W. Mauchly.

Q: What is the difference between TCP and UDP? A: TCP is connection-oriented, reliable, and performs error checking (making it slower), ideal for web browsing and emails. UDP is connectionless, unreliable, and faster, making it ideal for video streaming and online gaming.

Q: How do you calculate Page Number and Offset bits in Virtual Memory? A: First, convert the Page Size to bytes and express it as a power of 2 (e.g., 4KB = 2^12). The exponent (12) is the Offset bits. Subtract this from the total Virtual Address bits to get the Page Number bits.

Q: What hardware component translates virtual addresses to physical addresses? A: The Memory Management Unit (MMU), which is a hardware component inside the CPU, performs address mapping from virtual addresses to physical addresses using the page table.

Q: How do you find the first block of a file in a File Allocation Table (FAT)? A: The first block (directory entry) is the block number that is not pointed to as the "Next Block" by any other block in the FAT chain.

🎓 Expert ICT, Coding, School Classes, Digital Marketing & University Project Guidance

Struggling with your university final year project? Want to master coding, upscale your business with expert digital marketing, or learn absolute computer basics from scratch? We offer high-quality individual and group online classes conducted in English, Sinhala, or Tamil mediums. Get guaranteed academic success and professional growth with tailored guidance.

---

🎓 University Final Year Project Guidance & AI

Get specialized, end-to-end mentoring and technical support to pass your degree or master's program with flying colors:

• 🏫 Targeted Institutes: Expert guidance tailored for BIT UCSC, UoM, SLIIT, NIBM, and other leading universities.
• 🔬 Postgraduate Support: Comprehensive assistance for MSc Software Final Year Projects.
• 🤖 AI & Smart Applications: Step-by-step implementation of AI, Machine Learning (ML), and automation modules.
• ✅ Guaranteed Success: Help with documentation, system architecture, coding, and viva preparation.

---

🏫 School ICT & Corporate Beginner Classes

• 💻 Non-IT Staff Computer Basics: Absolute beginner-friendly online classes covering essential computer skills, office tools, and internet operations.
• 🎒 Primary & Secondary (Grades 1-10): Interactive online ICT classes tailored to build strong foundations from early ages.
• 📝 Exam Prep: Dedicated training packages for GCE O/L, GCE A/L ICT, and GIT exams.
• 🌍 Global Syllabuses: Complete curriculum coverage for Local, Edexcel, and Cambridge in English & Tamil Mediums.

---

📢 Software Development & Digital Marketing Services

• ⚙️ Software & Web Development: Professional custom software application and website development built using PHP & MySQL.
• 🎯 Social Media Management: Content creation, publishing, and channel management for Facebook, Instagram, TikTok, and YouTube.
• 📈 Ad Boosting: Highly targeted paid advertising campaigns to drive leads, traffic, and sales to your business.

---

📞 Connect With Us Instantly

Book your slot for online classes or get a premium tech service quote today!

💬 WhatsApp: +94 729622034

📧 Email: ITClassSL@gmail.com

---

🌐 Explore Our Resources & Communities

Stay updated with our latest tutorials, project ideas, and student guides across all our official platforms:

• 📺 YouTube Tutorials: Subscribe to our Channel
• 💼 Professional Network: Connect on LinkedIn
• ✍️ Tech Blog: Visit our WordPress Site
• ❓ Project Q&A: Follow our Quora Guide Profile
• 📰 Monthly Updates: Read Our Newsletter
• 🌐 Official Portfolios: Wix Site | Google Business | Strikingly Portfolio
• 🗣️ Student Forum: Join our ElaKiri Discussion Thread

Microsoft Excel Training Notes (Basic to Advanced) | Hands-On Guide 2026 tutorial, Excel formulas, VLOOKUP, Pivot Tables, Excel Dashboard, Data Validation, Excel training notes, Basic to Advanced

📊 Microsoft Excel Training Notes

Basic to Advanced | Hands-on Examples & Step-by-Step Exercises

📚 About this guide: A complete, structured Microsoft Excel course progressing from beginner to advanced levels. Covers formulas, cell references, VLOOKUP/XLOOKUP, Pivot Tables, Data Validation, and Interactive Dashboards. Perfect for a 20–30 hour instructor-led training or self-paced learning with practical exercises after every topic.

Module 1

Introduction to Excel

What is Excel? Spreadsheet software used to store data, perform calculations, analyze information, and build dashboards.

Key Interface Parts: Workbook (file), Worksheet (sheet), Row (horizontal, 1,2,3), Column (vertical, A,B,C), Cell (intersection), Formula Bar, Ribbon, Name Box.

🛠️ Hands-on Exercise 1 Create a Student Marks Sheet with columns: Student, Maths, Science, English. Enter sample data and save as Student_Marks.xlsx.

Module 2

Formatting

Learn to make data professional: Font styles, Borders, Fill Color, Text Color, Merge & Center, Wrap Text, and Alignment.

🛠️ Hands-on Exercise Format a "Product & Price" table: Blue heading, white text, bold, borders, and apply Currency Format to prices.

Module 3

Basic Formulas

Rule: Every formula starts with =.

Operator	Meaning	Example
+	Add	=A2+B2
-	Subtract	=A2-B2
*	Multiply	=A2*B2
/	Divide	=A2/B2
^	Power	=A2^2

Module 4

Common Functions

• =SUM(B2:B10) → Total of a range.
• =AVERAGE(B2:B10) → Mean value.
• =MAX(B2:B10) → Highest value.
• =MIN(B2:B10) → Lowest value.
• =COUNT(B2:B10) → Counts cells with numbers.
• =COUNTA(B2:B10) → Counts non-empty cells.

Module 5

Cell References

• Relative (A2): Changes automatically when copied.
• Absolute ($A$2): Never changes (locks row & column). Use for fixed rates like VAT.
• Mixed (A$2 or $A2): Locks either the row or the column only.

🛠️ Hands-on Exercise Calculate VAT (18%). Price in A2, VAT Rate in $D$1. Formula: =A2*$D$1

Module 6

AutoFill

Use the small square at the bottom-right of a cell to drag and automatically fill numbers, months, days, or copy formulas.

🛠️ Hands-on Exercise Type "January" in a cell and drag the fill handle down. Observe how Excel automatically fills February, March, etc.

Module 7

Sorting & Filtering

Sort: A-Z, Z-A, Smallest to Largest, Largest to Smallest.
Filter: Temporarily hide data to view specific criteria (e.g., show only "IT Department" or "Salary > 50000").

Module 8

Conditional Formatting

Automatically highlight cells based on rules: Duplicate values, Greater/Less than, Top 10, or Color Scales.

🛠️ Hands-on Exercise Select a column of student marks. Apply Conditional Formatting to highlight all marks greater than 75 in green.

Module 9

IF Function

Syntax: =IF(condition, value_if_true, value_if_false)

Example: =IF(B2>=50, "Pass", "Fail")

🛠️ Hands-on Exercise If a student's mark is >75, display "Excellent". Otherwise, display "Average".

Module 10

AND & OR Functions

• =AND(A2>50, B2>50) → True only if BOTH conditions are met.
• =OR(A2>50, B2>50) → True if AT LEAST ONE condition is met.

Module 11

Date Functions

• =TODAY() → Current date.
• =NOW() → Current date and time.
• =YEAR(A2), =MONTH(A2), =DAY(A2) → Extract specific parts of a date.

Module 12

Text Functions

• =LEFT(A2,3) → Extracts first 3 characters.
• =RIGHT(A2,2) → Extracts last 2 characters.
• =MID(A2,2,5) → Extracts 5 characters starting from position 2.
• =LEN(A2) → Counts total characters.
• =TRIM(A2) → Removes extra spaces.
• =UPPER(), =LOWER(), =PROPER() → Change text case.

🛠️ Hands-on Exercise Convert "john smith" into "John Smith" using the =PROPER() function.

Module 13

Lookup Functions

• VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Searches vertically.
• XLOOKUP (Excel 365): =XLOOKUP(lookup_value, lookup_array, return_array). More flexible, searches any direction.
• HLOOKUP: Searches horizontally across the first row.

Module 14

Data Validation

Restrict what can be entered into a cell. Common uses: Drop-down lists, number limits, date limits.

🛠️ Hands-on Exercise Create a drop-down list for "Department" with options: Sales, HR, Finance, IT. (Data Tab → Data Validation → List).

Module 15

Charts

Visualize data using Bar Charts, Pie Charts, Line Charts, and Column Charts. Select data → Insert Tab → Choose Chart.

Modules 16-19

Pivot Tables, Charts & Tables

• Pivot Tables: Summarize large datasets instantly (Insert → Pivot Table). Drag fields to Rows, Columns, and Values.
• Pivot Charts: Visual representation of a Pivot Table.
• Excel Tables: (Ctrl+T) Provides automatic formatting, built-in filters, and structured references.
• Freeze Panes: (View → Freeze Panes) Keeps headers visible while scrolling.

Modules 20-22

Protection, Named Ranges & What-If

• Protect Sheet: (Review → Protect Sheet) Prevents unauthorized edits. Can set a password.
• Named Ranges: Assign a name (e.g., "Sales") to a range (e.g., A2:A20). Use =SUM(Sales) for readability.
• What-If Analysis: Goal Seek (find input for a desired output), Scenario Manager, Data Tables.

Modules 23-24

Advanced & Financial Functions

• INDEX & MATCH: Powerful alternative to VLOOKUP. =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• PMT: Calculate loan EMI. =PMT(rate, nper, pv)

Module 25 + Final Project

Interactive Sales Dashboard

Dataset: Date, Product, Region, Salesperson, Qty, Price.

Tasks:

1. Format as Table & Calculate Total Sales (=Qty*Price).
2. Apply Currency Format & Conditional Formatting (> Rs.100,000).
3. Create Data Validation Drop-downs & Sort/Filter.
4. Build Pivot Table (Sales by Region) & Pivot Chart.
5. Insert Slicers (Product, Region) for an interactive Dashboard.