Module 5 · 3 hours
5.1 Why Formatting Matters
Typed quickly, no formatting
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | empid | name | dept | doj | salary |
| 2 | e101 | ravi kumar | accounts | 44267 | 28500 |
| 3 | e102 | priya singh | hr | 44743 | 26000 |
| 4 | e103 | amit mahto | sales | 43850 | 31000 |
| 5 | e104 | sneha oraon | admin | 45236 | 18500 |
Same data, formatted in 2 minutes
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Emp ID | Employee Name | Department | Joining Date | Salary |
| 2 | E101 | Ravi Kumar | Accounts | 12-03-2021 | ₹28,500 |
| 3 | E102 | Priya Singh | HR | 01-07-2022 | ₹26,000 |
| 4 | E103 | Amit Mahto | Sales | 20-01-2020 | ₹31,000 |
| 5 | E104 | Sneha Oraon | Admin | 06-11-2023 | ₹18,500 |
5.1 Why Formatting Matters
5.2 Text Formatting
| Tool | Shortcut / Location | Best used for |
|---|---|---|
| Bold | Ctrl+B | Headings, totals |
| Italic | Ctrl+I | Notes, remarks |
| Underline | Ctrl+U | Titles, signatures |
| Font | Home › Font box | Calibri / Arial for office |
| Font size | Home › Size box | 11 data, 14-16 titles |
| Font colour | Home › A▾ | Red for alerts, navy titles |
| Format Cells | Ctrl+1 | All options in one box |
5.2 Text Formatting
Click the buttons to format the cell, just like the Home tab.
Font: Calibri | Size: 11 | Colour: Automatic
5.3 Cell Formatting
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fee Collection - April | |||
| 2 | Student | Course | Remarks | Fee |
| 3 | Anjali Kumari | Tally | Paid in two instalments via UPI | 4,500 |
| 4 | Rohit Sahu | Excel | Full | 3,000 |
| Tool | What it does |
|---|---|
| Fill colour (paint bucket) | Background colour of the cell |
| Borders | Lines around cells; grid lines do NOT print |
| Alignment | Left / Center / Right and Top / Middle / Bottom |
| Wrap Text | Long text goes onto multiple lines in the same cell |
| Merge & Center | Joins cells into one, centres the title |
5.3 Cell Formatting
Rule: text left, numbers right, headings center.
Before (cut off):
| Purpose of Visit |
| Meeting with HR regarding joi |
After Wrap Text:
| Purpose of Visit |
| Meeting with HR regarding joining documents |
Home › Alignment › Wrap Text. Manual line break: Alt+Enter
Before (title in A1 only):
| A | B | C | |
|---|---|---|---|
| 1 | BrainBit Infotech Staff List |
After Merge & Center (A1:C1):
| A | B | C | |
|---|---|---|---|
| 1 | BrainBit Infotech Staff List | ||
Use only for titles. Merged cells inside data cause sorting/filter problems.
5.4 Number Formatting
| Format | Typed / stored | Displayed | Note |
|---|---|---|---|
| Number | 45230.5 | 45,230.50 | Comma separator, 2 decimals |
| Currency | 45230.5 | ₹45,230.50 | Rupee symbol for money |
| Percentage | 0.18 | 18% | Value x 100 with % sign |
| Date | 46297 | 02-10-2026 | Excel stores dates as numbers |
| Time | 0.75 | 06:00 PM | Fraction of a day (0.75 = 18 hrs) |
| Decimal places | 3.14159 | 3.14 | Increase / Decrease Decimal buttons |
Where: Home › Number group drop-down, or Ctrl+1 › Number tab. Quick keys: Ctrl+Shift+$ currency, Ctrl+Shift+% percent.
| A: Stored value | B: Displayed | |
|---|---|---|
| 1 | 45230.5 | 45230.5 |
| 2 | 0.18 | 0.18 |
| 3 | 46297 | 46297 |
| 4 | 0.75 | 0.75 |
| 5 | 1250.756 | 1250.756 |
Column A never changes - only the display in B does.
5.4 Number Formatting
| You want | Type this | Then apply |
|---|---|---|
| 18% | 18% | (auto Percentage) |
| ₹2,500 | 2500 | Currency |
| 15 Aug 2026 | 15-08-2026 | Date / Custom |
| 9:30 AM | 9:30 AM | Time |
| Mobile number | 9431012345 | Number, 0 dp (or Text) |
| PIN code 834001 | 834001 | General |
Practical
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | EMP ID | NAME | DEPT | DESIGNATION | DOJ | SALARY | MOBILE |
| 2 | E101 | Ravi Kumar | Accounts | Accountant | 44267 | 28500 | 9431012345 |
| 3 | E102 | Priya Singh | HR | HR Executive | 44743 | 26000 | 9835023456 |
| 4 | E103 | Amit Mahto | Sales | Sales Officer | 43850 | 31000 | 7004034567 |
| 5 | E104 | Sneha Oraon | Admin | Receptionist | 45236 | 18500 | 9122045678 |
| 6 | E105 | Rahul Verma | Accounts | Accounts Assistant | 45397 | 22000 | 6201056789 |
Goal: Convert this into a printable HR Register for the HR Manager of BrainBit Infotech Teaching, Ranchi.
dd-mm-yyyy.Practical - Result
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | BRAINBIT INFOTECH - HR REGISTER | ||||||
| 2 | As on 02-10-2026 | Ranchi, Jharkhand | ||||||
| 3 | Emp ID | Employee Name | Department | Designation | Date of Joining | Monthly Salary | Mobile No. |
| 4 | E101 | Ravi Kumar | Accounts | Accountant | 12-03-2021 | ₹28,500 | 9431012345 |
| 5 | E102 | Priya Singh | HR | HR Executive | 01-07-2022 | ₹26,000 | 9835023456 |
| 6 | E103 | Amit Mahto | Sales | Sales Officer | 20-01-2020 | ₹31,000 | 7004034567 |
| 7 | E104 | Sneha Oraon | Admin | Receptionist | 06-11-2023 | ₹18,500 | 9122045678 |
| 8 | E105 | Rahul Verma | Accounts | Accounts Assistant | 15-04-2024 | ₹22,000 | 6201056789 |
| 9 | Total Monthly Salary | ₹1,26,000 | |||||
Practice Example 1 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Marks Sheet - Class 12 Commerce | |||
| 2 | Name | Accounts | Economics | Business St. |
| 3 | Anjali Kumari | 78 | 85 | 69 |
| 4 | Rohit Sahu | 64 | 72 | 58 |
| 5 | Kajal Toppo | 91 | 88 | 94 |
| 6 | Deepak Yadav | 55 | 61 | 70 |
| Marks Sheet - Class 12 Commerce | |||
| Name | Accounts | Economics | Business St. |
| Anjali Kumari | 78 | 85 | 69 |
| Rohit Sahu | 64 | 72 | 58 |
| Kajal Toppo | 91 | 88 | 94 |
| Deepak Yadav | 55 | 61 | 70 |
Title: Merge & Center A1:D1, Bold 16. Headings: Bold + fill + center. Marks: center aligned. A1:D6: All Borders.
Practice Example 2 of 5
| A | B | |
|---|---|---|
| 1 | Item | Price |
| 2 | Notebook (200 pg) | 60 |
| 3 | Ball Pen (pack of 10) | 100 |
| 4 | Stapler | 145.50 |
| 5 | A4 Paper Ream | 299 |
| 6 | Calculator | 450 |
| A | B | |
|---|---|---|
| 1 | Item | Price |
| 2 | Notebook (200 pg) | ₹60.00 |
| 3 | Ball Pen (pack of 10) | ₹100.00 |
| 4 | Stapler | ₹145.50 |
| 5 | A4 Paper Ream | ₹299.00 |
| 6 | Calculator | ₹450.00 |
Select B2:B6 › Home › Number › Currency (₹) with 2 decimals. Fill A1:B1 navy with white bold font. Column A width = AutoFit.
Practice Example 3 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Present | Working Days | Attendance |
| 2 | Manish Kumar | 22 | 26 | 0.85 |
| 3 | Pooja Kumari | 25 | 26 | 0.96 |
| 4 | Sanjay Munda | 18 | 26 | 0.69 |
| 5 | Ritu Sharma | 24 | 26 | 0.92 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Present | Working Days | Attendance |
| 2 | Manish Kumar | 22 | 26 | 84.6% |
| 3 | Pooja Kumari | 25 | 26 | 96.2% |
| 4 | Sanjay Munda | 18 | 26 | 69.2% |
| 5 | Ritu Sharma | 24 | 26 | 92.3% |
D2:D5 › Percentage, 1 decimal. Sanjay (69.2%) is below 75% - shown in red font with light red fill.
Practice Example 4 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Time In | Visitor | Purpose |
| 2 | 46,297 | 0.40625 | Arun Prasad | Interview for Accounts Assistant post |
| 3 | 46,297 | 0.479166 | Meena Devi | Delivery of courier documents for HR |
| 4 | 46,297 | 0.597222 | Vikash Singh | Meeting with Sales Manager about order |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Time In | Visitor | Purpose |
| 2 | 02-10-2026 | 09:45 AM | Arun Prasad | Interview for Accounts Assistant post |
| 3 | 02-10-2026 | 11:30 AM | Meena Devi | Delivery of courier documents for HR |
| 4 | 02-10-2026 | 02:20 PM | Vikash Singh | Meeting with Sales Manager about order |
A: Date format dd-mm-yyyy. B: Time format h:mm AM/PM. D: Wrap Text, column width ~30. Vertical align: Middle.
Practice Example 5 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fee Collection Report - Sept 2026 | |||
| 2 | Course | Students | Fee | Collection |
| 3 | Tally Prime | 18 | 4,500 | 81,000 |
| 4 | Advanced Excel | 24 | 3,000 | 72,000 |
| 5 | DCA | 15 | 6,000 | 90,000 |
| 6 | Python Basics | 12 | 7,500 | 90,000 |
| 7 | Total | 69 | 3,33,000 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fee Collection Report - Sept 2026 | |||
| 2 | Course | Students | Fee | Collection |
| 3 | Tally Prime | 18 | ₹4,500 | ₹81,000 |
| 4 | Advanced Excel | 24 | ₹3,000 | ₹72,000 |
| 5 | DCA | 15 | ₹6,000 | ₹90,000 |
| 6 | Python Basics | 12 | ₹7,500 | ₹90,000 |
| 7 | Total | 69 | ₹3,33,000 | |
Total row: Bold, top border (double bottom border optional). C and D in ₹ with 0 decimals. Title merged A1:D1.
Module 6 · 4 hours
Before we start
Formulas are the foundation of Excel. Every function, report and dashboard you will ever build is made of formulas.
| Time | Topic |
|---|---|
| 45 min | What is a formula + = sign |
| 45 min | Cell references (A1, B1) |
| 45 min | Operators & order of operations |
| 45 min | Basic calculations + errors |
| 60 min | Practical + 5 practice examples |
The ONE rule to remember today
Every formula starts with an equal sign
No = sign → Excel treats it as text
6.1 What is a Formula?
You type
=10+20Cell shows 30
You type
10+20Cell shows 10+20 (text)
A formula is an instruction you give Excel to calculate something. It always begins with =.
=10+20=10+20.6.1 What is a Formula?
Try: =10+20 10+20 =50*3 =100/4 =200*10%
| A | B | |
|---|---|---|
| 1 | 30 |
Cell A1 displays 30. The formula bar above the grid shows =10+20.
6.2 Cell References
Column letter C + Row number 3 = C3
| A | B | C | |
|---|---|---|---|
| 1 | 30 |
Formula in C1 is =A1+B1. It means: "add whatever is in A1 to whatever is in B1". Change A1 and C1 updates automatically.
=, click A1, type +, click B1, press Enter. Fewer typing mistakes!6.2 Cell References
=12000+5000=B2+B3| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Rent | 12,000 |
| 3 | Food | 5,000 |
| 4 | Total | =B2+B3 |
6.3 Mathematical Operators
=20+5 → 25=20-5 → 15=20*5 → 100=20/5 → 4=200*10% → 20=5^2 → 25=200*10% means 10% of 200. Excel reads 10% as 0.10. So =B2*18% gives 18% of B2.6.4 Order of Operations
=B2+C2/2 is WRONG. Correct: =(B2+C2)/2.6.4 Order of Operations
| Formula | Answer |
|---|---|
| =2+3*4 | 14 |
| =(2+3)*4 | 20 |
| =20-10/2 | 15 |
| =(20-10)/2 | 5 |
| =100-20*2+5 | 65 |
| =(100-20)*(2+5) | 560 |
A shop sells 3 shirts at ₹400 and gives a ₹100 discount.
₹100 off the whole bill: =3*400-100 → 1100 ✔ (* done first)
Typed in wrong order: =400-100*3 → 100 ✘
₹100 off each shirt: =(400-100)*3 → 900
6.5 Basic Calculations
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value 1 | Value 2 | Formula | Result | |
| 2 | Add | 500 | 100 | =B2+C2 | 600 |
| 3 | Subtract | 500 | 100 | =B3-C3 | 400 |
| 4 | Multiply | 500 | 100 | =B4*C4 | 50,000 |
| 5 | Divide | 500 | 100 | =B5/C5 | 5 |
Column D shows the formula text so you can see it; in real Excel the cell shows only the result.
=B2+C2, press Enter.=B3-C3.6.5 Basic Calculations
| Error | Meaning | Example cause | Fix |
|---|---|---|---|
| #DIV/0! | Divided by zero or empty cell | =B2/C2 when C2 is blank | Check the divisor cell |
| #NAME? | Excel does not recognise text in formula | =SUMM(B2:B5) | Fix the spelling |
| #VALUE! | Text used where a number is needed | =B2+"abc" | Remove text / spaces from cell |
| #REF! | Referenced cell was deleted | Deleted column B | Undo (Ctrl+Z) or rewrite |
| ##### | Column too narrow (not an error!) | Big number / date | Widen the column |
Practical
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Rent | ₹12,000 |
| 3 | Food | ₹5,000 |
| 4 | Electricity | ₹2,000 |
| 5 | Transport | ₹2,500 |
| 6 | Total | ₹21,500 |
=B2+B3+B4+B5 and press Enter.=B8-B6 → ₹8,500. Savings % in B10 = =B9/B8 → 28%.Practice Example 1 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Rate | Amount |
| 2 | Rice (kg) | 25 | 52 | |
| 3 | Dal (kg) | 5 | 120 | |
| 4 | Oil (litre) | 3 | 165 | |
| 5 | Sugar (kg) | 4 | 44 | |
| 6 | Total |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Rate | Amount |
| 2 | Rice (kg) | 25 | 52 | 1,300 |
| 3 | Dal (kg) | 5 | 120 | 600 |
| 4 | Oil (litre) | 3 | 165 | 495 |
| 5 | Sugar (kg) | 4 | 44 | 176 |
| 6 | Total | 2,571 |
D2: =B2*C2 (then D3, D4, D5 the same way). D6: =D2+D3+D4+D5 = ₹2,059
Practice Example 2 of 5
| A | B | |
|---|---|---|
| 1 | Component | Amount |
| 2 | Basic | 15,000 |
| 3 | HRA | 6,000 |
| 4 | DA | 3,000 |
| 5 | PF Deduction | 1,800 |
| 6 | Gross Salary | |
| 7 | Net Salary |
| A | B | |
|---|---|---|
| 1 | Component | Amount |
| 2 | Basic | 15,000 |
| 3 | HRA | 6,000 |
| 4 | DA | 3,000 |
| 5 | PF Deduction | 1,800 |
| 6 | Gross Salary | 24,000 |
| 7 | Net Salary | 22,200 |
B6: =B2+B3+B4 = 24,000 B7: =B6-B5 = 22,200
Practice Example 3 of 5
| A | B | |
|---|---|---|
| 1 | Subject | Marks (out of 100) |
| 2 | English | 72 |
| 3 | Hindi | 81 |
| 4 | Maths | 66 |
| 5 | Science | 74 |
| 6 | Social Sc. | 79 |
| 7 | Total | |
| 8 | Percentage |
| A | B | |
|---|---|---|
| 1 | Subject | Marks |
| 2 | English | 72 |
| 3 | Hindi | 81 |
| 4 | Maths | 66 |
| 5 | Science | 74 |
| 6 | Social Sc. | 79 |
| 7 | Total | 372 |
| 8 | Percentage | 74.4% |
B7: =B2+B3+B4+B5+B6 = 372. B8: =B7/500*100 = 74.4 (or =B7/500 formatted as %). Note: no brackets needed - / and * go left to right.
Practice Example 4 of 5
| A | B | |
|---|---|---|
| 1 | Detail | Value |
| 2 | Distance (km) | 420 |
| 3 | Mileage (km/litre) | 35 |
| 4 | Petrol price (per litre) | 105 |
| 5 | Litres needed | |
| 6 | Trip cost |
| A | B | |
|---|---|---|
| 1 | Detail | Value |
| 2 | Distance (km) | 420 |
| 3 | Mileage (km/litre) | 35 |
| 4 | Petrol price | 105 |
| 5 | Litres needed | 12 |
| 6 | Trip cost | ₹1,260 |
B5: =B2/B3 = 12 litres. B6: =B5*B4 = ₹1,260. One formula: =B2/B3*B4
Practice Example 5 of 5
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Cost Price | Selling Price | Profit | Profit % |
| 2 | Mobile Cover | 120 | 199 | ||
| 3 | Earphones | 350 | 499 | ||
| 4 | Charger | 280 | 260 | ||
| 5 | Power Bank | 900 | 1,150 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Cost | Selling | Profit | Profit % |
| 2 | Mobile Cover | 120 | 199 | 79 | 65.8% |
| 3 | Earphones | 350 | 499 | 149 | 42.6% |
| 4 | Charger | 280 | 260 | -20 | -7.1% |
| 5 | Power Bank | 900 | 1,150 | 250 | 27.8% |
D2: =C2-B2. E2: =D2/B2 formatted as % (or =(C2-B2)/B2*100). Charger shows a loss (-20). Highest Profit %: Mobile Cover (65.8%).
Module 7 · 4 hours
Introduction
Formula (long way)
=B2+B3+B4+B5+B6+B7+B8+B9+B10Function (short way)
=SUM(B2:B10)A function is a ready-made formula built into Excel. You give it a name and a range; Excel does the work.
B2:B10 means "from B2 to B10". The colon : is read as "to".
Interactive
| A | B | |
|---|---|---|
| 1 | Salesperson | Sales (Rs) |
| 2 | Amit | 45,000 |
| 3 | Bhavna | 38,500 |
| 4 | Chandan | 52,000 |
| 5 | Divya | Leave |
| 6 | Faiz | 41,000 |
| 7 | Gita | |
| 8 | Harsh | 60,500 |
| 9 | Isha | 33,000 |
| 10 | Jay | 47,500 |
| 11 | ||
| 12 | Result |
Click each function. Watch the highlighted range and the result in B12.
Function: SUM
=SUM(B2:B10)| A | B | |
|---|---|---|
| 1 | Item | Amt |
| 2 | Pens | 120 |
| 3 | Files | 300 |
| 4 | Ink | 450 |
| 5 | Result | 870 |
=SUM() and press Enter.Function: AVERAGE
=AVERAGE(B2:B10)| A | B | |
|---|---|---|
| 1 | Item | Amt |
| 2 | Pens | 120 |
| 3 | Files | 300 |
| 4 | Ink | 450 |
| 5 | Result | 290 |
=AVERAGE(Function: MIN
=MIN(B2:B10)| A | B | |
|---|---|---|
| 1 | Item | Amt |
| 2 | Pens | 120 |
| 3 | Files | 300 |
| 4 | Ink | 450 |
| 5 | Result | 120 |
=MIN(Function: MAX
=MAX(B2:B10)| A | B | |
|---|---|---|
| 1 | Item | Amt |
| 2 | Pens | 120 |
| 3 | Files | 300 |
| 4 | Ink | 450 |
| 5 | Result | 450 |
=MAX(Function: COUNT
=COUNT(B2:B10)| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Asha | 18 |
| 3 | Bijay | Absent |
| 4 | Chetna | 15 |
| 5 | Dev | |
| 6 | Result | 2 |
=COUNT(Function: COUNTA
=COUNTA(A2:A10)| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Asha | 18 |
| 3 | Bijay | Absent |
| 4 | Chetna | 15 |
| 5 | Dev | |
| 6 | Result | 4 |
=COUNTA(Compare
| Cell contains | COUNT | COUNTA |
|---|---|---|
| Number (500) | ✔ counted | ✔ counted |
| Text ("Absent") | ✘ | ✔ |
| Date (02-10-2026) | ✔ (dates are numbers) | ✔ |
| Blank cell | ✘ | ✘ |
| A space " " typed | ✘ | ✔ (careful!) |
=COUNTA(A2:A10)-COUNT(B2:B10)| Function | Question it answers |
|---|---|
SUM | What is the total? |
AVERAGE | What is typical? |
MIN | What is the lowest? |
MAX | What is the highest? |
COUNT | How many numbers? |
COUNTA | How many entries? |
Practical
Jharkhand Electronics, Ranchi - Sales Jan to Sep 2026
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | ₹2,85,000 |
| 3 | Feb | ₹2,40,000 |
| 4 | Mar | ₹3,10,000 |
| 5 | Apr | ₹2,75,000 |
| 6 | May | ₹3,30,000 |
| 7 | Jun | ₹2,95,000 |
| 8 | Jul | ₹2,60,000 |
| 9 | Aug | ₹3,45,000 |
| 10 | Sep | ₹3,90,000 |
| Summary | Formula | Result |
| Total sales | =SUM(B2:B10) | ₹27,30,000 |
| Average / month | =AVERAGE(B2:B10) | ₹3,03,333.33 |
| Lowest month | =MIN(B2:B10) | ₹2,40,000 |
| Best month | =MAX(B2:B10) | ₹3,90,000 |
| Months with data | =COUNT(B2:B10) | 9 |
| Months listed | =COUNTA(A2:A10) | 9 |
Practical
Practice Example 1 of 5
| A | B | |
|---|---|---|
| 1 | Student | Marks (50) |
| 2 | Anjali | 42 |
| 3 | Bikash | 38 |
| 4 | Chhaya | 47 |
| 5 | Dinesh | 29 |
| 6 | Ekta | 45 |
| 7 | Firoz | 33 |
| Function | Formula | Answer |
|---|---|---|
| SUM | =SUM(B2:B7) | 234 |
| AVERAGE | =AVERAGE(B2:B7) | 39.00 |
| MAX | =MAX(B2:B7) | 47 |
| MIN | =MIN(B2:B7) | 29 |
| COUNT | =COUNT(B2:B7) | 6 |
| COUNTA | =COUNTA(A2:A7) | 6 |
Topper: Chhaya (47). Lowest: Dinesh (29).
Practice Example 2 of 5
| A | B | |
|---|---|---|
| 1 | Day | Visitors |
| 2 | Mon | 120 |
| 3 | Tue | 95 |
| 4 | Wed | 110 |
| 5 | Thu | 130 |
| 6 | Fri | 160 |
| 7 | Sat | 210 |
| 8 | Sun | 240 |
| Function | Formula | Answer |
|---|---|---|
| SUM | =SUM(B2:B8) | 1,065 |
| AVERAGE | =AVERAGE(B2:B8) | 152.14 |
| MAX | =MAX(B2:B8) | 240 |
| MIN | =MIN(B2:B8) | 95 |
| COUNT | =COUNT(B2:B8) | 7 |
| COUNTA | =COUNTA(A2:A8) | 7 |
Busiest: Sunday (240). Quietest: Tuesday (95). Insight: schedule more staff on weekends.
Practice Example 3 of 5
| A | B | |
|---|---|---|
| 1 | Trainee | Score (20) |
| 2 | Asha | 18 |
| 3 | Bijay | Absent |
| 4 | Chetna | 15 |
| 5 | Deepu | 20 |
| 6 | Esha | 12 |
| 7 | Farhan | Absent |
| 8 | Gaurav | 17 |
| 9 | Hema | 19 |
| Function | Formula | Answer |
|---|---|---|
| SUM | =SUM(B2:B9) | 101 |
| AVERAGE | =AVERAGE(B2:B9) | 16.83 |
| MAX | =MAX(B2:B9) | 20 |
| MIN | =MIN(B2:B9) | 12 |
| COUNT | =COUNT(B2:B9) | 6 |
| COUNTA | =COUNTA(A2:A9) | 8 |
Absent = =COUNTA(A2:A9)-COUNT(B2:B9) = 2. Note AVERAGE ignores "Absent" text.
Practice Example 4 of 5
| A | B | |
|---|---|---|
| 1 | Month | Bill (Rs) |
| 2 | Apr | 1,850 |
| 3 | May | 2,420 |
| 4 | Jun | 2,980 |
| 5 | Jul | 2,650 |
| 6 | Aug | 2,310 |
| 7 | Sep | 2,140 |
| Function | Formula | Answer |
|---|---|---|
| SUM | =SUM(B2:B7) | 14,350 |
| AVERAGE | =AVERAGE(B2:B7) | 2,391.67 |
| MAX | =MAX(B2:B7) | 2,980 |
| MIN | =MIN(B2:B7) | 1,850 |
| COUNT | =COUNT(B2:B7) | 6 |
| COUNTA | =COUNTA(A2:A7) | 6 |
Highest: June (summer AC use). Budget: =AVERAGE(B2:B7)*6 = ₹14,350
Practice Example 5 of 5
| A | B | |
|---|---|---|
| 1 | Expense | Amount |
| 2 | Tea & snacks | 240 |
| 3 | Courier | 150 |
| 4 | Stationery | 560 |
| 5 | Auto fare | 120 |
| 6 | Printer ink | 950 |
| 7 | Water cans | 180 |
| 8 | Photocopy | 85 |
| 9 | Cleaning | 300 |
| Function | Formula | Answer |
|---|---|---|
| SUM | =SUM(B2:B9) | 2,585 |
| AVERAGE | =AVERAGE(B2:B9) | 323.12 |
| MAX | =MAX(B2:B9) | 950 |
| MIN | =MIN(B2:B9) | 85 |
| COUNT | =COUNT(B2:B9) | 8 |
| COUNTA | =COUNTA(A2:A9) | 8 |
Closing balance: =D1-SUM(B2:B9) = ₹2,415
Module 8 · 3 hours
The Big Idea
In real work you write a formula once and copy it to 100s of rows. Excel adjusts the cell addresses as it copies.
Sometimes that is exactly what you want. Sometimes it breaks your sheet.
Relative Reference
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | Qty | Formula in C | Amount |
| 2 | 500 | 4 | =A2*B2 | 2,000 |
| 3 | 300 | 6 | =A3*B3 | 1,800 |
| 4 | 120 | 10 | =A4*B4 | 1,200 |
| 5 | 75 | 8 | =A5*B5 | 600 |
=A2*B2 has no $ signs. When copied down 1 row, every row number goes up by 1.
The small square at the bottom-right of a selected cell is the fill handle. Drag it down, or double-click it.
Absolute Reference
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Price | Formula | Tax | GST Rate | 18% | |
| 2 | 1,000 | =A2*F1 | 180 | |||
| 3 | 2,500 | =A3*F2 | 0 | |||
| 4 | 400 | =A4*F3 | 0 | |||
| 5 | 1,200 | =A5*F4 | 0 |
F1 moved to F2, F3, F4 - which are empty. Tax becomes 0!
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Price | Formula | Tax | GST Rate | 18% | |
| 2 | 1,000 | =A2*$F$1 | 180 | |||
| 3 | 2,500 | =A3*$F$1 | 450 | |||
| 4 | 400 | =A4*$F$1 | 72 | |||
| 5 | 1,200 | =A5*$F$1 | 216 |
The $ signs lock F1. Every row uses the same rate.
Absolute Reference
Read $F$1 as "always column F, always row 1".
Relative – both change when copied
| Press F4 | Becomes | Locked |
|---|---|---|
| 1st | $A$1 | Column + Row |
| 2nd | A$1 | Row only |
| 3rd | $A1 | Column only |
| 4th | A1 | Nothing (back) |
Mixed Reference
| Reference | Column | Row |
|---|---|---|
| =$A2 | 🔒 locked (A) | changes |
| =A$2 | changes | 🔒 locked (2) |
Classic example: a multiplication table. In B2 type:
=$A2*B$1Copy B2 across and down to F6. $A always looks at column A (left numbers). $1 always looks at row 1 (top numbers).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
Cell D4 contains =$A4*D$1 = 3 x 3 = 9.
Summary
| Type | Looks like | When copied | Use it for |
|---|---|---|---|
| Relative | A2 | Both change | Row-by-row calculations: =B2*C2 |
| Absolute | $A$2 | Nothing changes | One fixed rate / constant: GST, commission, discount |
| Mixed (column) | $A2 | Only row changes | Tables reading from a fixed column |
| Mixed (row) | A$2 | Only column changes | Tables reading from a fixed header row |
Practical
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ranchi Electricals - GST Invoice Sheet | GST Rate | 18% | |||
| 2 | ||||||
| 3 | Product | Price | Qty | Taxable Value | GST | Total |
| 4 | LED Bulb 9W | 120 | 10 | 1,200 | 216.00 | 1,416.00 |
| 5 | Ceiling Fan | 1,850 | 2 | 3,700 | 666.00 | 4,366.00 |
| 6 | Extension Board | 350 | 4 | 1,400 | 252.00 | 1,652.00 |
| 7 | Mixer Grinder | 2,999 | 1 | 2,999 | 539.82 | 3,538.82 |
| 8 | Wire Coil 90m | 1,450 | 3 | 4,350 | 783.00 | 5,133.00 |
| 9 | Grand Total | 13,649 | 2,456.82 | 16,105.82 | ||
| Cell | Formula | Type |
|---|---|---|
| D4 | =B4*C4 | Relative |
| E4 | =D4*$F$1 | Absolute rate |
| F4 | =D4+E4 | Relative |
| D9 | =SUM(D4:D8) | Function |
=E4/2.Practical
=B4*C4 E4: =D4*$F$1 (press F4) F4: =D4+E4=SUM() for D, E and F.Grand total: ₹16,105.82 (taxable ₹13,649 + GST ₹2,456.82)
Practice Example 1 of 5
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Salesperson | Sales | Commission | Rate | 5% | |
| 2 | Rakesh | 85,000 | ||||
| 3 | Seema | 1,20,000 | ||||
| 4 | Tarun | 64,000 | ||||
| 5 | Usha | 98,000 |
| A | B | C | |
|---|---|---|---|
| 1 | Salesperson | Sales | Commission |
| 2 | Rakesh | 85,000 | 4,250 |
| 3 | Seema | 1,20,000 | 6,000 |
| 4 | Tarun | 64,000 | 3,200 |
| 5 | Usha | 98,000 | 4,900 |
C2: =B2*$F$1, copy down. If rate changes to 7%, only F1 changes.
Seema at 7% = 8,400
Practice Example 2 of 5
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Subscription | USD / month | INR / month | USD rate | 88 | |
| 2 | Cloud storage | 12 | ||||
| 3 | Design tool | 25 | ||||
| 4 | Email service | 6 | ||||
| 5 | Antivirus | 8 |
| A | B | C | |
|---|---|---|---|
| 1 | Subscription | USD | INR |
| 2 | Cloud storage | 12 | ₹1,056 |
| 3 | Design tool | 25 | ₹2,200 |
| 4 | Email service | 6 | ₹528 |
| 5 | Antivirus | 8 | ₹704 |
C2: =B2*$F$1. Total INR: =SUM(C2:C5) = ₹4,488
Practice Example 3 of 5
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | MRP | Discount | Net Price | Disc % | 10% |
| 2 | School Bag | 1,200 | ||||
| 3 | Water Bottle | 350 | ||||
| 4 | Lunch Box | 480 | ||||
| 5 | Geometry Box | 220 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | MRP | Discount | Net Price |
| 2 | School Bag | 1,200 | 120 | 1,080 |
| 3 | Water Bottle | 350 | 35 | 315 |
| 4 | Lunch Box | 480 | 48 | 432 |
| 5 | Geometry Box | 220 | 22 | 198 |
C2: =B2*$F$1 D2: =B2-C2. Copy both down.
Practice Example 4 of 5
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | |||||
| 3 | 2 | |||||
| 4 | 3 | |||||
| 5 | 4 | |||||
| 6 | 5 |
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
B2: =$A2*B$1, copy right to F2, then copy B2:F2 down to row 6. Without $ you get huge wrong numbers.
Practice Example 5 of 5
| A | B | C | |
|---|---|---|---|
| 1 | Branch | Sales | Share % |
| 2 | Ranchi | 4,50,000 | |
| 3 | Dhanbad | 3,20,000 | |
| 4 | Jamshedpur | 3,80,000 | |
| 5 | Bokaro | 2,50,000 | |
| 6 | Deoghar | 1,50,000 | |
| 7 | Total |
| A | B | C | |
|---|---|---|---|
| 1 | Branch | Sales | Share % |
| 2 | Ranchi | 4,50,000 | 29.0% |
| 3 | Dhanbad | 3,20,000 | 20.6% |
| 4 | Jamshedpur | 3,80,000 | 24.5% |
| 5 | Bokaro | 2,50,000 | 16.1% |
| 6 | Deoghar | 1,50,000 | 9.7% |
| 7 | Total | 15,50,000 | 100% |
B7: =SUM(B2:B6). C2: =B2/$B$7 formatted as %. Copy down.
Module 9 · 2 hours
How Excel Thinks
| 0.25 | 06:00 AM |
| 0.5 | 12:00 PM |
| 0.75 | 06:00 PM |
| 46297.5 | 02-10-2026 12:00 PM |
Entering Dates and Time
| You type | Excel understands | |
|---|---|---|
| 02-10-2026 | 2 Oct 2026 | ✔ |
| 02/10/2026 | 2 Oct 2026 | ✔ |
| 2-Oct-26 | 2 Oct 2026 | ✔ |
| 2 Oct 2026 | 2 Oct 2026 | ✔ |
| 02.10.2026 | Text! | ✘ |
| 2nd Oct 2026 | Text! | ✘ |
| Type | Result |
|---|---|
| 9:30 | 09:30 (AM) |
| 5:45 PM | 17:45 |
| 17:45 | 05:45 PM |
| Keys | Inserts |
|---|---|
| Ctrl+; | Today's date (fixed) |
| Ctrl+Shift+; | Current time (fixed) |
Date Formats
| Custom code | Shows | Use |
|---|---|---|
| dd-mm-yyyy | 02-10-2026 | Indian office standard |
| dd/mm/yy | 02/10/26 | Short forms |
| dd-mmm-yyyy | 02-Oct-2026 | No confusion between day/month |
| d mmmm yyyy | 2 October 2026 | Letters, certificates |
| dddd | Friday | Day name for schedules |
| mmm-yy | Oct-26 | Monthly reports |
| hh:mm AM/PM | 02:30 PM | Time 12-hour |
| hh:mm | 14:30 | Time 24-hour |
dd-mm-yyyy.Code key: d = day, m = month, y = year. More letters = longer form (mmm = Oct, mmmm = October).
dd-mmm-yyyy in reports sent outside India so nobody confuses 02-10 with 10-02.Today's Date and Time
Today's date. Updates every day.
Today's date AND current time.
(Live from this computer's clock)
() - no arguments.| =TODAY() | Ctrl+; | |
|---|---|---|
| Changes tomorrow? | Yes (dynamic) | No (fixed) |
| Use for | Calculations | Records / logs |
Date Calculations
| Task | Formula | Example | Result |
|---|---|---|---|
| Add days | =B2+30 | 02-10-2026 + 30 | 01-11-2026 |
| Subtract days | =B2-7 | 02-10-2026 - 7 | 25-09-2026 |
| Days between | =C2-B2 | 15-12-2026 - 02-10-2026 | 74 days |
| Add months | =EDATE(B2,6) | 6 months after 02-10-2026 | 02-04-2027 |
| Full years | =DATEDIF(B2,TODAY(),"Y") | from 12-03-2021 | 5 years |
| Get parts | =YEAR(B2) =MONTH(B2) =DAY(B2) | 02-10-2026 | 2026 / 10 / 2 |
| Hours worked | =(C2-B2)*24 | 09:30 AM to 06:00 PM | 8.5 hours |
DATEDIF is a hidden function: Excel does not suggest it while typing, but it works. Units: "Y" years, "M" months, "D" days.Practical
Employee Joining Date Register (as on 02-10-2026)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Joining Date | Review Date | Years of Service |
| 2 | Ravi Kumar | 12-03-2021 | 12-09-2021 | 5 |
| 3 | Priya Singh | 01-07-2022 | 01-01-2023 | 4 |
| 4 | Amit Mahto | 20-01-2020 | 20-07-2020 | 6 |
| 5 | Sneha Oraon | 06-11-2023 | 06-05-2024 | 2 |
| 6 | Rahul Verma | 15-04-2024 | 15-10-2024 | 2 |
| 7 | Kiran Tirkey | 18-05-2026 | 18-11-2026 | 0 |
| Column | Formula in row 2 |
|---|---|
| B Joining Date | Typed: 12-03-2021 (format dd-mm-yyyy) |
| C Review Date (6-month probation) | =EDATE(B2,6) |
| D Years of Service | =DATEDIF(B2,TODAY(),"Y") |
=INT((TODAY()-B2)/365.25)Practice Example 1 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Invoice Date | Due Date (30 days) | Days Left |
| 2 | INV-101 | 05-09-2026 | ||
| 3 | INV-102 | 18-09-2026 | ||
| 4 | INV-103 | 25-09-2026 | ||
| 5 | INV-104 | 01-10-2026 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Inv. Date | Due Date | Days Left |
| 2 | INV-101 | 05-09-2026 | 05-10-2026 | 3 |
| 3 | INV-102 | 18-09-2026 | 18-10-2026 | 16 |
| 4 | INV-103 | 25-09-2026 | 25-10-2026 | 23 |
| 5 | INV-104 | 01-10-2026 | 31-10-2026 | 29 |
C2: =B2+30 D2: =C2-TODAY() (format General). Values as on 02-10-2026.
INV-101 is due first (05-10-2026).
Practice Example 2 of 5
| A | B | C | |
|---|---|---|---|
| 1 | Name | Date of Birth | Age (years) |
| 2 | Anita | 14-06-2001 | |
| 3 | Bharat | 02-11-1998 | |
| 4 | Champa | 30-01-2004 | |
| 5 | Dilip | 02-10-1995 |
| A | B | C | |
|---|---|---|---|
| 1 | Name | DOB | Age |
| 2 | Anita | 14-06-2001 | 25 |
| 3 | Bharat | 02-11-1998 | 27 |
| 4 | Champa | 30-01-2004 | 22 |
| 5 | Dilip | 02-10-1995 | 31 |
C2: =DATEDIF(B2,TODAY(),"Y"). Dilip's birthday is today, so he just turned 31.
Practice Example 3 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | In Time | Out Time | Hours |
| 2 | Mon | 09:30 AM | 06:00 PM | |
| 3 | Tue | 09:45 AM | 06:15 PM | |
| 4 | Wed | 10:00 AM | 05:30 PM | |
| 5 | Thu | 09:15 AM | 07:00 PM | |
| 6 | Total |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | In | Out | Hours |
| 2 | Mon | 09:30 AM | 06:00 PM | 8.50 |
| 3 | Tue | 09:45 AM | 06:15 PM | 8.50 |
| 4 | Wed | 10:00 AM | 05:30 PM | 7.50 |
| 5 | Thu | 09:15 AM | 07:00 PM | 9.75 |
| 6 | Total | 34.25 |
D2: =(C2-B2)*24, format Number 2 dp. Total: =SUM(D2:D5) = 34.25 hours.
Wednesday was short (7.5 h).
Practice Example 4 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Event | Event Date | Days Left | Weekday |
| 2 | Annual Function | 15-12-2026 | ||
| 3 | Batch Exam | 20-10-2026 | ||
| 4 | Placement Drive | 07-11-2026 | ||
| 5 | Republic Day | 26-01-2027 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Event | Date | Days Left | Weekday |
| 2 | Annual Function | 15-12-2026 | 74 | Tuesday |
| 3 | Batch Exam | 20-10-2026 | 18 | Tuesday |
| 4 | Placement Drive | 07-11-2026 | 36 | Saturday |
| 5 | Republic Day | 26-01-2027 | 116 | Tuesday |
C2: =B2-TODAY() D2: =B2 with custom format dddd. Values as on 02-10-2026.
Practice Example 5 of 5
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Book | Due Date | Return Date | Days Late | Fine | Rs/day: 5 |
| 2 | Tally Prime Guide | 20-09-2026 | 26-09-2026 | |||
| 3 | Excel Made Easy | 22-09-2026 | 21-09-2026 | |||
| 4 | Office English | 25-09-2026 | 01-10-2026 | |||
| 5 | Typing Master | 28-09-2026 | 28-09-2026 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Book | Due | Returned | Days Late | Fine |
| 2 | Tally Prime Guide | 20-09-2026 | 26-09-2026 | 6 | ₹30 |
| 3 | Excel Made Easy | 22-09-2026 | 21-09-2026 | 0 | ₹0 |
| 4 | Office English | 25-09-2026 | 01-10-2026 | 6 | ₹30 |
| 5 | Typing Master | 28-09-2026 | 28-09-2026 | 0 | ₹0 |
D2: =MAX(0,C2-B2) (no negative days). E2: =D2*5 or with the rate in a cell use $F$1.
Total fine = ₹60.
Module 10 · 3 hours
What is an Excel Table?
| A | B | C | |
|---|---|---|---|
| 1 | Name | City | Amount |
| 2 | Sunil | Ranchi | 4,500 |
| 3 | Meera | Dhanbad | 3,200 |
| 4 | Kunal | Bokaro | 5,100 |
| A | B | C | |
|---|---|---|---|
| 1 | Name ▾ | City ▾ | Amount ▾ |
| 2 | Sunil | Ranchi | 4,500 |
| 3 | Meera | Dhanbad | 3,200 |
| 4 | Kunal | Bokaro | 5,100 |
Why Tables?
Step-by-Step
tblCustomers.Table Features
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Cust ID ▾ | Name ▾ | City ▾ | Amount ▾ |
| 2 | C-101 | Sunil Oraon | Ranchi | 4,500 |
| 3 | C-102 | Meera Jha | Dhanbad | 3,200 |
| 4 | C-103 | Kunal Das | Bokaro | 5,100 |
| 5 | C-104 | Farah Khan | Ranchi | 2,800 |
| 6 | C-105 | Vivek Lal | Jamshedpur | 6,100 |
| 7 | Total | 21,700 |
Try adding a row first as a plain range, then reload the slide (F5) and try as a table.
| Feature | What you see |
|---|---|
| Automatic filtering | ▾ buttons on every header |
| Structured data | Banded rows, a name, headings always used |
| Automatic expansion | Type below the last row: table and formatting extend |
| Total row | Table Design › tick Total Row › choose Sum / Average / Count |
| Calculated columns | Type one formula, it fills the whole column |
SUBTOTAL, so when you filter the table, the total shows only the visible rows.Table Features
Inside a table, formulas use column names instead of cell addresses.
| Normal formula | Table formula |
|---|---|
| =C2*D2 | =[@Qty]*[@Rate] |
| =SUM(E2:E50) | =SUM(tblSales[Amount]) |
@ means "this row". tblSales[Amount] means "the whole Amount column of tblSales", however long it gets.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Rate | Amount |
| 2 | Pen drive | 3 | 450 | 1,350 |
| 3 | Mouse | 5 | 299 | 1,495 |
| 4 | Keyboard | 2 | 650 | 1,300 |
| 5 | Total | 10 | 4,145 |
Practical
Customer Database - Jharkhand Computer World (table name: tblCustomers)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Cust ID ▾ | Name ▾ | City ▾ | Mobile ▾ | Product ▾ | Amount ▾ | Date ▾ |
| 2 | C-1001 | Sunil Oraon | Ranchi | 9431011111 | Laptop | 48,500 | 05-01-2026 |
| 3 | C-1002 | Meera Jha | Dhanbad | 9835022222 | Printer | 12,800 | 18-01-2026 |
| 4 | C-1003 | Kunal Das | Bokaro | 7004033333 | Desktop | 39,900 | 02-02-2026 |
| 5 | C-1004 | Farah Khan | Ranchi | 9122044444 | UPS | 6,500 | 14-02-2026 |
| 6 | C-1005 | Vivek Lal | Jamshedpur | 6201055555 | Laptop | 52,000 | 03-03-2026 |
| 7 | C-1006 | Sapna Kujur | Ranchi | 9431066666 | Tablet | 18,900 | 21-03-2026 |
| 8 | C-1007 | Rohan Gupta | Dhanbad | 9835077777 | Monitor | 9,800 | 09-04-2026 |
| 9 | C-1008 | Nisha Ekka | Hazaribagh | 7004088888 | Laptop | 46,000 | 27-04-2026 |
| 10 | Total | 8 customers | 2,34,400 |
tblCustomers.Practice Example 1 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Category | Stock | Rate |
| 2 | Mouse | Accessories | 45 | 299 |
| 3 | Keyboard | Accessories | 30 | 650 |
| 4 | Pen Drive 64GB | Storage | 60 | 450 |
| 5 | HDD 1TB | Storage | 12 | 4,200 |
| 6 | Webcam | Accessories | 8 | 1,800 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Category | Stock | Rate | Value |
| 2 | Mouse | Accessories | 45 | 299 | 13,455 |
| 3 | Keyboard | Accessories | 30 | 650 | 19,500 |
| 4 | Pen Drive 64GB | Storage | 60 | 450 | 27,000 |
| 5 | HDD 1TB | Storage | 12 | 4,200 | 50,400 |
| 6 | Webcam | Accessories | 8 | 1,800 | 14,400 |
| 7 | Total | 155 | 1,24,755 |
E2: =[@Stock]*[@Rate] fills automatically. Total Row: Stock = Sum, Value = Sum.
Practice Example 2 of 5
| A | B | C | |
|---|---|---|---|
| 1 | Student | Course | City |
| 2 | Ankit | DCA | Ranchi |
| 3 | Bulbul | Tally | Khunti |
| 4 | Chirag | DCA | Ranchi |
| 5 | Dolly | Excel | Ramgarh |
| 6 | Ehsan | Tally | Ranchi |
| 7 | Fatima | DCA | Lohardaga |
Table created › click Course ▾ › untick all › tick DCA › OK.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Course | City |
| 2 | Ankit | DCA | Ranchi |
| 3 | Chirag | DCA | Ranchi |
| 4 | Fatima | DCA | Lohardaga |
3 DCA students. Total Row with Count on Student shows 3 (only visible rows).
Practice Example 3 of 5
| A | B | C | |
|---|---|---|---|
| 1 | Supplier | Supplies | Phone |
| 2 | Shree Traders | Paper | 9431100001 |
| 3 | Gupta Electronics | UPS | 9835100002 |
| 4 | Tirkey Furnitures | Chairs | 7004100003 |
| 5 | City Printers | Toner | 9122100004 |
tblSuppliers.Practice Example 4 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Item | Qty | Rate |
| 2 | 02-10-2026 | Tea | 40 | 10 |
| 3 | 02-10-2026 | Samosa | 65 | 12 |
| 4 | 02-10-2026 | Coffee | 25 | 20 |
| 5 | 02-10-2026 | Sandwich | 18 | 35 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Item | Qty | Rate | Amount |
| 2 | 02-10-2026 | Tea | 40 | 10 | 400 |
| 3 | 02-10-2026 | Samosa | 65 | 12 | 780 |
| 4 | 02-10-2026 | Coffee | 25 | 20 | 500 |
| 5 | 02-10-2026 | Sandwich | 18 | 35 | 630 |
| 6 | Total | 2,310 |
E2: =[@Qty]*[@Rate]. Total Row Sum = ₹2,310.
After adding Cold drink: ₹2,610.
Practice Example 5 of 5
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Batch | Course | Timing | Students |
| 2 | B-01 | Excel Basics | Morning | 22 |
| 3 | B-02 | Tally Prime | Evening | 18 |
| 4 | B-03 | Excel Basics | Evening | 25 |
| 5 | B-04 | Spoken English | Morning | 30 |
| 6 | B-05 | Tally Prime | Morning | 15 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Batch | Course | Timing | Students |
| 2 | B-01 | Excel Basics | Morning | 22 |
| 3 | B-04 | Spoken English | Morning | 30 |
| 4 | B-05 | Tally Prime | Morning | 15 |
| 5 | Total | 67 |
Total Row Sum of all batches = 110. Filter Timing = Morning › total changes to 67 because Total Row counts only visible rows.
Module 11 · 3 hours
Introduction
| Data tab › Sort & Filter |
| Home tab › Sort & Filter (right side) |
| Filter on/off: Ctrl+Shift+L |
Sorting
Click a sort button
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Department | Join Date | Salary |
| 2 | Ravi Kumar | Accounts | 12-03-2021 | 28,500 |
| 3 | Priya Singh | HR | 01-07-2022 | 26,000 |
| 4 | Amit Mahto | Sales | 20-01-2020 | 31,000 |
| 5 | Sneha Oraon | Admin | 06-11-2023 | 18,500 |
| 6 | Neha Sinha | Accounts | 15-08-2019 | 34,000 |
| 7 | Suresh Prasad | Accounts | 10-02-2017 | 42,000 |
| 8 | Kavita Munda | Sales | 25-09-2024 | 24,000 |
| 9 | Imran Ansari | IT | 03-06-2022 | 38,000 |
| Data type | Options shown |
|---|---|
| Text | A to Z / Z to A |
| Numbers | Smallest to Largest / Largest to Smallest |
| Dates | Oldest to Newest / Newest to Oldest |
Multi-level Sorting
| A | B | C | |
|---|---|---|---|
| 1 | Name | Department | Salary |
| 2 | Suresh Prasad | Accounts | 42,000 |
| 3 | Neha Sinha | Accounts | 34,000 |
| 4 | Ravi Kumar | Accounts | 28,500 |
| 5 | Sneha Oraon | Admin | 18,500 |
| 6 | Priya Singh | HR | 26,000 |
| 7 | Imran Ansari | IT | 38,000 |
| 8 | Amit Mahto | Sales | 31,000 |
| 9 | Kavita Munda | Sales | 24,000 |
Filtering
Or tick values in the checklist
| Logic | How |
|---|---|
| AND (different columns) | Filter Department, then filter Salary. Both apply. |
| OR (same column) | Tick 2 values, or Custom Filter with "Or". |
| Between | Custom Filter: >= 25000 And <= 35000 |
Workplace Scenario
15 records
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Emp ID ▾ | Name ▾ | Department ▾ | City ▾ | Salary ▾ |
| 2 | E101 | Ravi Kumar | Accounts | Ranchi | 28,500 |
| 3 | E102 | Priya Singh | HR | Ranchi | 26,000 |
| 4 | E103 | Amit Mahto | Sales | Dhanbad | 31,000 |
| 5 | E104 | Sneha Oraon | Admin | Ranchi | 18,500 |
| 6 | E105 | Neha Sinha | Accounts | Jamshedpur | 34,000 |
| 7 | E106 | Suresh Prasad | Accounts | Ranchi | 42,000 |
| 8 | E107 | Kavita Munda | Sales | Ranchi | 24,000 |
| 9 | E108 | Imran Ansari | IT | Bokaro | 38,000 |
| 10 | E109 | Pooja Kumari | Accounts | Dhanbad | 30,000 |
| 11 | E110 | Deepak Yadav | Sales | Jamshedpur | 35,500 |
| 12 | E111 | Ritu Sharma | HR | Bokaro | 22,000 |
| 13 | E112 | Arjun Tirkey | Accounts | Ranchi | 31,500 |
| 14 | E113 | Mohit Jain | IT | Ranchi | 29,000 |
| 15 | E114 | Sunita Devi | Admin | Dhanbad | 21,000 |
| 16 | E115 | Vikram Singh | Sales | Ranchi | 27,500 |
Practical
Use the 15-employee list from the previous slide (type it into Excel first). Clear the filter after each question.
| # | Investigation question | Tool |
|---|---|---|
| 1 | How many employees are based in Ranchi? | City = Ranchi |
| 2 | List Sales staff from highest to lowest salary. | Filter Sales + Sort Largest to Smallest |
| 3 | Who earns between ₹25,000 and ₹35,000? | Number Filters › Between |
| 4 | Who joined in 2025 or later? | Date Filters › After 31-12-2024 |
| 5 | Whose names begin with "S"? | Text Filters › Begins With |
| 6 | Top 3 salaries in the company? | Number Filters › Top 10 › 3 |
| 7 | How many are in Accounts OR HR? | Tick Accounts + HR |
| 8 | IT employees based in Ranchi? | Department = IT AND City = Ranchi |
Practical
Filter each department and read the Average in the status bar to check these figures.
Practice Example 1 of 5
| A | B | |
|---|---|---|
| 1 | Student | Marks |
| 2 | Aman | 78 |
| 3 | Bina | 92 |
| 4 | Chandu | 65 |
| 5 | Dimple | 88 |
| 6 | Ekansh | 71 |
| 7 | Fiza | 95 |
| A | B | C | |
|---|---|---|---|
| 1 | Student | Marks | Rank |
| 2 | Fiza | 95 | 1 |
| 3 | Bina | 92 | 2 |
| 4 | Dimple | 88 | 3 |
| 5 | Aman | 78 | 4 |
| 6 | Ekansh | 71 | 5 |
| 7 | Chandu | 65 | 6 |
Ranks must be typed as values (not formulas based on position) before sorting back.
Practice Example 2 of 5
| A | B | C | |
|---|---|---|---|
| 1 | Customer | City | Purchase |
| 2 | Sunil | Ranchi | 4,500 |
| 3 | Meera | Dhanbad | 3,200 |
| 4 | Farah | Ranchi | 2,800 |
| 5 | Vivek | Jamshedpur | 6,100 |
| 6 | Sapna | Ranchi | 5,400 |
| 7 | Rohan | Bokaro | 3,900 |
| A | B | C | |
|---|---|---|---|
| 1 | Customer | City | Purchase |
| 2 | Sunil | Ranchi | 4,500 |
| 3 | Farah | Ranchi | 2,800 |
| 4 | Sapna | Ranchi | 5,400 |
3 customers; total ₹12,700.
Practice Example 3 of 5
| A | B | C | |
|---|---|---|---|
| 1 | Date | Branch | Amount |
| 2 | 05-03-2026 | Ranchi | 62,000 |
| 3 | 12-03-2026 | Dhanbad | 41,000 |
| 4 | 18-04-2026 | Ranchi | 75,000 |
| 5 | 21-03-2026 | Bokaro | 58,000 |
| 6 | 28-03-2026 | Ranchi | 39,000 |
| 7 | 02-04-2026 | Dhanbad | 81,000 |
| A | B | C | |
|---|---|---|---|
| 1 | Date | Branch | Amount |
| 2 | 05-03-2026 | Ranchi | 62,000 |
| 3 | 21-03-2026 | Bokaro | 58,000 |
2 orders. Date Filters › All Dates in the Period › March (or Between 01-03 and 31-03).
Practice Example 4 of 5
| A | B | |
|---|---|---|
| 1 | Name | City |
| 2 | Pooja | Ranchi |
| 3 | Arif | Dhanbad |
| 4 | Kiran | Ranchi |
| 5 | Babita | Dhanbad |
| 6 | Anil | Ranchi |
| 7 | Deepa | Bokaro |
| A | B | |
|---|---|---|
| 1 | Name | City |
| 2 | Deepa | Bokaro |
| 3 | Arif | Dhanbad |
| 4 | Babita | Dhanbad |
| 5 | Anil | Ranchi |
| 6 | Kiran | Ranchi |
| 7 | Pooja | Ranchi |
Practice Example 5 of 5
| A | B | |
|---|---|---|
| 1 | Product | Price |
| 2 | LED Bulb 9W | 120 |
| 3 | Ceiling Fan | 1,850 |
| 4 | LED Tube Light | 340 |
| 5 | Switch Board | 210 |
| 6 | Smart LED Strip | 899 |
| 7 | Socket 16A | 95 |
| A | B | |
|---|---|---|
| 1 | Product | Price |
| 2 | LED Bulb 9W | 120 |
| 3 | LED Tube Light | 340 |
Smart LED Strip (₹899) matches "LED" but is removed by the price condition (AND).
Recap
| Shortcut | Does | Module |
|---|---|---|
| Ctrl + B / I / U | Bold / Italic / Underline | 5 |
| Ctrl + 1 | Format Cells dialog | 5 |
| Ctrl + Shift + $ / % | Currency / Percent | 5 |
| Ctrl + ` | Show formulas | 6 |
| Alt + = | AutoSum | 7 |
| F4 | Toggle $ references | 8 |
| Ctrl + ; | Today's date | 9 |
| Ctrl + Shift + ; | Current time | 9 |
| Ctrl + T | Create Table | 10 |
| Ctrl + Shift + L | Filter on / off | 11 |
| Alt + ; | Select visible cells | 11 |
| Ctrl + Z | Undo anything | All |