■

Microsoft Excel

Complete Assignment Guide

Step-by-Step Practical Guide with Formulas & Screenshots

13 Chapters | Beginner to Advanced | Hindi + English

Topics: Student List • Marksheet • Sales Analysis • Salary Slip • Loan EMI • Attendance • Macros •
Dashboard & Charts
 TABLE OF CONTENTS

01 Student List with Names, Roll Numbers & Classes

02 Formatted Marksheet with Bold Headings & Borders

03 Total and Average Marks Calculation

04 Sales List – Sort by Amount & Filter Top 10

05 Full Name from First & Last Name in Proper Case

06 Employee Years of Service Calculation

07 Grade Calculation Based on Percentage Marks

08 Highlight Overdue Payments (Conditional Formatting)

09 Salary Slip using VLOOKUP & HLOOKUP

10 Loan EMI Calculator

11 Attendance Sheet with Present/Absent List

12 Macro to Bold Headers, Apply Colors & Table Borders

13 Personal Finance Tracker Dashboard with Charts

 CHAPTE Student List with Names, Roll Numbers &
R 01
Classes
Ek basic student list banana Excel ka pehla aur sabse zaroori step hai. Yahan hum properly structured data
table banayenge jisme headings, data, aur formatting hogi.

1 New Workbook open karo aur Sheet ka naam do

• Excel open karo → File → New → Blank Workbook
• Sheet tab par double-click karo aur naam likho: Student_List

2 Column Headers banao (Row 1 mein)

Cell Header Text Width (approx)

A1 Roll Number 15

B1 Student Name 25

C1 Class 10

D1 Section 10

E1 Father's Name 25

F1 Contact Number 18

3 Sample Data Enter karo (Row 2 se shuru)

Roll No Name Class Section Father's Name Contact

101 Aarav Sharma 10th A Rajesh Sharma 9876543210

102 Priya Gupta 10th B Suresh Gupta 9823456710

103 Rohit Verma 9th A Mahesh Verma 9812345670

104 Sneha Joshi 9th C Dinesh Joshi 9845671230

4 Table Format Apply karo

• Poora data select karo: A1 se F (last row) tak
• Insert → Table click karo (Ctrl+T shortcut bhi kaam karta hai)
• "My table has headers" ka checkbox tick karo → OK
• Table Style choose karo: Table Design → Table Styles → Blue

■ Tip: Table se auto-filter aata hai, sorting aasaan ho jaati hai.

5 Roll Number ko Auto-fill karo

• A2 mein 101 aur A3 mein 102 likho
• Dono cells select karo → corner pe chhoti dot (Fill Handle) ko neeche drag karo
• Excel automatically 103, 104... sequence banayega

■■ Note: Column widths adjust karne ke liye column border par double-click karo – Excel automatically best
fit kar dega.
 CHAPTE Formatted Marksheet with Bold Headings &
R 02
Borders
Marksheet mein students ke subjects ke marks hote hain. Hum ise professionally format karenge with bold
headings, colored cells aur proper borders.

1 New Sheet banao: "Marksheet" naam se

2 School Header Area banao

• A1:H1 select karo → Merge & Center
• Likho: DELHI PUBLIC SCHOOL – ANNUAL MARKSHEET 2024-25
• Font: 14pt, Bold, Background: Dark Blue, Text: White

3 Column Headers banao (Row 3)

Column Header

A3 Roll No

B3 Student Name

C3 Hindi

D3 English

E3 Maths

F3 Science

G3 Social Sc.

H3 Total

I3 Percentage

4 Bold + Border Formatting karo

• Header row (A3:I3) select karo
• Home → Font → Bold (Ctrl+B)
• Background color: Dark Blue (#1a3a5c)
• Font color: White
• Poora table select karo → Home → Borders → All Borders
• Outer border ke liye: Thick Box Border choose karo

■ Tip: Row height 25-30 set karo better readability ke liye. Row select → Right click → Row Height → 25
 CHAPTE
R 03 Total and Average Marks Calculation
Ab hum Marksheet mein formulas use karke Total, Percentage aur Average calculate karenge. Yeh Excel ka
sabse important part hai.

1 Total Marks Formula (H4 cell mein)

SUM Formula =SUM(C4:G4)

• Yeh formula C4 se G4 tak ke saare marks add kar dega

• H4 mein formula likho, phir neeche wali cells mein copy karo

2 Percentage Calculate karo (I4 cell mein)

Percentage =H4/500*100

• Total marks ko 500 se divide karo (5 subjects × 100 marks each)

• I4 ko select karo → Format Cells → Number → Percentage → 2 decimal places

3 Average Calculate karo

AVERAGE =AVERAGE(C4:G4)

4 Class Average row banao (last row mein)

Class Avg
=AVERAGE(C4:C13)
(Hindi)

• Yahi formula baaki subjects ke liye bhi apply karo

• Row ko merge karo aur "Class Average" label lagao

5 Highest aur Lowest marks find karo

MAX (Highest) =MAX(H4:H13)

MIN (Lowest) =MIN(H4:H13)

■■ Note: Formula copy karne ke liye cell ko select karo, Ctrl+C press karo, phir paste karo. Ya fill handle
(corner dot) ko neeche drag karo.
 CHAPTE
R 04 Sales List – Sort by Amount & Filter Top 10
Ek sales dataset ko sort aur filter karna business analysis ke liye bahut zaroori skill hai. Hum yahan Large
function aur AutoFilter use karenge.

1 "Sales_Data" sheet banao

Column Data Type Example

A Date 01-Apr-2024

B Salesperson Rahul Mehta

C Product Laptop

D Quantity 5

E Unit Price 45000

F Total Amount =D2*E2

2 Data ko Amount se Sort karo (Z to A – highest first)

• Kisi bhi cell par click karo data ke andar
• Data → Sort click karo
• "Sort by" mein Total Amount choose karo
• Order: Largest to Smallest → OK

3 AutoFilter se Top 10 Sales filter karo

• Header row select karo → Data → Filter (Ctrl+Shift+L)
• "Total Amount" column ke dropdown arrow par click karo
• Number Filters → Top 10... select karo
• "Top 10 Items" setting keep karo → OK

4 LARGE function se Top 10 separately nikalo

1st Highest =LARGE($F$2:$F$100, 1)

2nd Highest =LARGE($F$2:$F$100, 2)

• Yahan 1, 2, 3... change karo 1st, 2nd, 3rd rank ke liye

■ Tip: Filter hatane ke liye Data → Clear click karo.

 CHAPTE Full Name from First & Last Name in Proper
R 05
Case
Jab database mein First Name aur Last Name alag columns mein hote hain, toh hum unhe combine karke
Full Name banate hain aur PROPER function se correct capitalization apply karte hain.

1 Setup karo

Column A (First Name) Column B (Last Name) Column C (Full Name Result)

rahul sharma ?

PRIYA GUPTA ?

sneha JOSHI ?

2 CONCATENATE + PROPER formula (C2 mein)

Method 1 (&
=PROPER(A2&" "&B2;)
operator)

Method 2
=PROPER(CONCAT(A2," ",B2))
(CONCAT)

Method 3
=PROPER(TEXTJOIN(" ",TRUE,A2,B2))
(TEXTJOIN)

• PROPER() function har word ka pehla letter capital kar deta hai
• " " (space) dono names ke beech space add karta hai

3 Result example

Input A Input B Output C (PROPER)

rahul sharma Rahul Sharma

PRIYA GUPTA Priya Gupta

sneha JOSHI Sneha Joshi

4 Values ko paste karo (Formula remove karke)

• C column select karo → Ctrl+C (copy)
• Same column par right-click → Paste Special → Values Only
• Ab formula remove ho jayega, sirf text rahega

■ Tip: Agar sirf First Name ka first letter capital karna hai toh sirf =PROPER(A2) use karo.
 CHAPTE
R 06 Employee Years of Service Calculation
DATEDIF function Excel ka powerful tool hai jo do dates ke beech ka difference calculate karta hai – years,
months, ya days mein.

1 "Employee_Service" sheet banao

Column Header Example Data

A Emp ID EMP001

B Employee Name Rajesh Kumar

C Date of Joining 15-Mar-2015

D Years of Service (Formula)

E Months of Service (Formula)

F Service Category (Formula)

2 Years of Service formula (D2 mein)

Years only =DATEDIF(C2, TODAY(), "Y")

=DATEDIF(C2,TODAY(),"Y")&" Years,
Full detail
"&DATEDIF;(C2,TODAY(),"YM")&" Months"

• C2 = Joining Date
• TODAY() = aaj ki date (automatically update hoti hai)
• "Y" = complete years count karta hai
• "YM" = remaining months (years ke baad)

3 Service Category (F2 mein) – IF formula

Category =IF(D2>=10,"Senior",IF(D2>=5,"Mid-Level","Junior"))

Years of Service Category

10+ years Senior Employee

5-9 years Mid-Level Employee

0-4 years Junior Employee

■■ Note: Date format consistent rakhna zaroori hai. DD-MM-YYYY format use karo aur cells ko Date format
mein set karo.
 CHAPTE Grade Calculation Based on Percentage
R 07
Marks
Nested IF formula ya IFS function use karke students ko percentage ke basis par grade assign karte hain –
A+, A, B, C, D, ya F.

1 Grade System define karo

Grade Percentage Range Remarks

A+ 90% and above Outstanding

A 80% – 89% Excellent

B 70% – 79% Very Good

C 60% – 69% Good

D 50% – 59% Average

F Below 50% Fail

2 Nested IF Formula (Grade column mein)

=IF(I2>=90,"A+",IF(I2>=80,"A",IF(I2>=70,"B",IF(I2>=60,"C",I
Nested IF
F(I2>=50,"D","F")))))

• I2 = Percentage cell
• Conditions zyada se zyada (90+) se chhhoti (50) ki taraf jaati hain

3 Modern IFS Formula (Excel 2019+)

=IFS(I2>=90,"A+",I2>=80,"A",I2>=70,"B",I2>=60,"C",I2>=50,"D
IFS Function
",TRUE,"F")

• IFS function zyada readable aur easy to write hai

• TRUE,"F" = yeh default condition hai (baaki sab fail)

4 Grade cells ko color code karo (Manual)

• A+ cells: Green fill | A cells: Light Green
• B cells: Yellow | C cells: Orange
• F cells: Red fill, Bold text

■ Tip: Conditional Formatting se yeh automatically ho sakta hai – Home → Conditional Formatting →
Highlight Cell Rules
 CHAPTE
R 08 Highlight Overdue Payments in Red
Conditional Formatting ek powerful feature hai jo cells ko automatically highlight karta hai jab koi condition
meet hoti hai – jaise payment overdue ho.

1 "Payments" sheet banao

Column Data

A Invoice Number (INV001, INV002...)

B Customer Name

C Invoice Date

D Due Date

E Amount (■)

F Payment Status (Paid/Unpaid)

2 Overdue rows highlight karo – Red color

1. Poora data range select karo: A2:F20 (ya last row tak)
2. Home → Conditional Formatting → New Rule
3. "Use a formula to determine which cells to format" choose karo
4. Formula box mein likho:

Overdue
=AND($D2<TODAY(),$F2="Unpaid")
Formula

5. Format → Fill → Red color choose karo

6. OK → Apply

3 Formula ka explanation

Formula Part Meaning

$D2<TODAY() Due date aaj se pehle hai (overdue)

$F2="Unpaid" Payment abhi bhi unpaid hai

AND(...,...) Dono conditions sach honi chahiye

4 Additional Rules add karo

• Due Today: Formula: =$D2=TODAY() → Orange color
• Paid: Formula: =$F2="Paid" → Green color
• Coming soon (next 7 days): Formula: =AND($D2>TODAY(),$D2<=TODAY()+7,$F2="Unpaid") →
Yellow
■ Tip: Multiple rules manage karne ke liye: Home → Conditional Formatting → Manage Rules
 CHAPTE
R 09 Salary Slip using VLOOKUP & HLOOKUP
VLOOKUP aur HLOOKUP formulas se hum Emp ID enter karte hi poori salary information automatically
fetch kar sakte hain. Yeh real-world HR systems mein use hota hai.

1 "Employee_Data" sheet banao (Master Data)

EmpID Name Designation Department Basic Salary HRA% DA% PF%

EMP001 Rajesh Kumar Manager IT 50000 40 20 12

EMP002 Priya Sharma Analyst Finance 35000 40 20 12

EMP003 Amit Verma Developer IT 45000 40 20 12

2 "Salary_Slip" sheet banao

• B1 mein: "Enter Employee ID:" label
• C1 mein: Emp ID enter karne ki cell (yellow highlight karo)

3 VLOOKUP formulas (Employee info fetch karne ke liye)

Employee
=VLOOKUP(C1, Employee_Data!A:H, 2, FALSE)
Name

Designation =VLOOKUP(C1, Employee_Data!A:H, 3, FALSE)

Basic Salary =VLOOKUP(C1, Employee_Data!A:H, 5, FALSE)

Argument Value Meaning

1st (lookup_value) C1 Jis Emp ID ko dhundhna hai

2nd (table_array) Employee_Data!A:H Data kahan hai (dusri sheet)

3rd (col_index) 2, 3, 5... Konsa column return karna hai

4th (range_lookup) FALSE Exact match chahiye

4 Salary Calculations

Basic Salary
=VLOOKUP(C1,Employee_Data!A:H,5,FALSE)
(B5)

HRA (B6) =B5 * VLOOKUP(C1,Employee_Data!A:H,6,FALSE)/100

DA (B7) =B5 * VLOOKUP(C1,Employee_Data!A:H,7,FALSE)/100

 Gross Salary
=B5+B6+B7
(B8)

PF Deduction
=B5 * VLOOKUP(C1,Employee_Data!A:H,8,FALSE)/100
(B9)

Net Salary (B10) =B8-B9

■■ Note: Salary Slip ko print-friendly banane ke liye: Page Layout → Margins → Narrow, phir Print Preview
check karo.
 CHAPTE
R 10 Loan EMI Calculator
PMT function se hum monthly EMI calculate kar sakte hain. Yeh home loan, car loan, personal loan sab ke
liye kaam aata hai.

1 "EMI_Calculator" sheet banao

Cell Label (Column A) Input/Formula (Column B)

A1 LOAN EMI CALCULATOR (Merged, Title)

A3 Loan Amount (■) B3 = 500000 (User input)

A4 Annual Interest Rate (%) B4 = 8.5 (User input)

A5 Loan Tenure (Years) B5 = 20 (User input)

A7 Monthly Interest Rate B7 = B4/12/100

A8 Total Months B8 = B5*12

A9 Monthly EMI (■) B9 = PMT formula

A10 Total Payment (■) B10 = B9*B8

A11 Total Interest (■) B11 = B10-B3

2 PMT Formula (B9 mein)

EMI Formula =PMT(B4/12/100, B5*12, -B3)

PMT Argument Our Value Explanation

rate B4/12/100 Annual rate ko monthly rate mein convert

nper B5*12 Total number of months

-pv -B3 Loan amount (negative sign zaroori hai)

3 Sample EMI Calculation

Parameter Value

Loan Amount ■5,00,000

Annual Interest Rate 8.5%

Tenure 20 years

Monthly EMI ■4,340 approx.

Total Payment ■10,41,600 approx.

Total Interest Paid ■5,41,600 approx.

 4 Input cells ko protect karo
• Formula cells (B7:B11) select karo → Yellow background dedo
• Input cells (B3:B5) ko different color (e.g. Light Blue) dedo
• Data → Data Validation se input range set karo (e.g. loan > 0)

■ Tip: IFERROR wrap karo formula ke around taaki error na aaye: =IFERROR(PMT(...),"Enter valid values")
 CHAPTE
R 11 Attendance Sheet with Present/Absent List
Monthly attendance tracker banayenge jisme P (Present) aur A (Absent) mark hoga aur automatically total
present days count hoga COUNTIF se.

1 "Attendance" sheet setup

• Row 1: Column headers – Name, Roll No, phir dates (1, 2, 3... 31)
• Column A: Student names
• Column B: Roll Numbers
• C1 onwards: Dates of the month (1 to 31)

2 Date headers automatically fill karo

Date in C1 =DATE(2024,4,1) [Format as: d]

Next date D1 =C1+1 (phir drag karo)

• Date cells select karo → Format Cells → Number → Custom → d

• Sirf din number dikhega (1, 2, 3...)

3 P/A mark karo aur Data Validation lagao

• Data cells select karo (C2:AG20)
• Data → Data Validation → List
• Source mein likho: P,A,H,L (Present, Absent, Holiday, Leave)
• Ab har cell mein dropdown se select kar sakte ho

4 Total Present days count karo (COUNTIF)

Total Present =COUNTIF(C2:AG2,"P")

Total Absent =COUNTIF(C2:AG2,"A")

Attendance % =COUNTIF(C2:AG2,"P")/COUNTA(C2:AG2)*100

5 Conditional Formatting: A cells red, P cells green

• Data range select karo → Conditional Formatting → New Rule
• Rule 1: Cell value = "A" → Red fill
• Rule 2: Cell value = "P" → Green fill
• Rule 3: Cell value = "H" → Gray fill
 CHAPTE Macro to Bold Headers, Apply Colors &
R 12
Table Borders
Macro ek recorded set of actions hai jo ek click mein repeat ho sakti hai. VBA (Visual Basic for Applications)
use karke hum custom macros likhte hain.

1 Macro enable karo

• File → Options → Trust Center → Trust Center Settings
• Macro Settings → Enable all macros → OK
• Developer tab enable karo: File → Options → Customize Ribbon → Developer ✓

2 VBA Editor open karo

• Developer → Visual Basic (Alt+F11 shortcut)
• Insert → Module click karo
• Neeche diya gaya code paste karo:

3 VBA Code – Format Sheet Macro

Sub FormatSheet()
Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim rng As Range

Set ws = ActiveSheet
lastRow = [Link]([Link], "A").End(xlUp).Row
lastCol = [Link](1, [Link]).End(xlToLeft).Column

' --- STEP 1: Bold Headers (Row 1) ---

With [Link](1)
.[Link] = True
.[Link] = 12
.[Link] = RGB(255, 255, 255) ' White text
.[Link] = RGB(26, 58, 92) ' Dark Blue background
.RowHeight = 28
End With

' --- STEP 2: Alternate Row Colors ---

Dim i As Long
For i = 2 To lastRow
If i Mod 2 = 0 Then
[Link](i).[Link] = RGB(219, 234, 254) ' Light Blue
Else
[Link](i).[Link] = RGB(255, 255, 255) ' White
End If
Next i
 ' --- STEP 3: All Borders ---
Set rng = [Link]([Link](1,1), [Link](lastRow, lastCol))
With [Link]
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(148, 163, 184) ' Gray border
End With

' --- STEP 4: Thick Outer Border ---

With [Link](xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With

' --- STEP 5: AutoFit Columns ---

[Link]

MsgBox "Formatting Complete!", vbInformation

End Sub

4 Macro run karo

• Developer → Macros → FormatSheet → Run
• Ya shortcut assign karo: Developer → Macros → Options → Ctrl+Shift+F

■■ Note: File ko .xlsm format mein save karo (Macro-Enabled Workbook), warna macros save nahi honge.
 CHAPTE Personal Finance Tracker Dashboard with
R 13
Charts
Ek complete Personal Finance Dashboard banayenge jisme income, expenses aur savings track honge,
charts ke saath – bilkul professional finance app jaisa!

1 Data Sheet banao: "Finance_Data"

Column A Column B Column C Column D

Month Income (■) Expenses (■) Savings (■)

January 50000 35000 =B2-C2

February 52000 38000 =B3-C3

March 48000 32000 =B4-C4

...till Dec ... ... =Auto

2 Expense Categories sheet banao: "Categories"

Category Amount (■) Percentage

Rent/EMI 15000 =B2/SUM(B$2:B$8)*100

Food & Groceries 7000 =B3/SUM(B$2:B$8)*100

Transport 3000 ...

Utilities 2500 ...

Entertainment 2000 ...

Medical 1500 ...

Miscellaneous 4000 ...

3 "Dashboard" sheet banao

• New sheet add karo: right-click tab → Insert Sheet → Dashboard
• Gridlines hide karo: View → Show → uncheck Gridlines
• Tab color change karo: right-click → Tab Color → Gold

4 KPI Summary Cards banao (top mein)

Card Label Formula

A2:C4 Total Annual Income =SUM(Finance_Data!B:B)

D2:F4 Total Annual Expenses =SUM(Finance_Data!C:C)

 G2:I4 Total Annual Savings =SUM(Finance_Data!D:D)

J2:L4 Savings Rate % =I4/A4*100

• Har card ko merge karo, border aur color deo

• Font size: 20-24, Bold, colored text

5 Charts create karo

Chart 1: Monthly Income vs Expenses vs Savings (Line Chart)

• Finance_Data sheet mein A1:D13 select karo
• Insert → Charts → Line → Line with Markers
• Chart title: "Monthly Financial Overview 2024"
• Dashboard sheet mein cut-paste karo

Chart 2: Expense Categories Breakdown (Pie Chart / Donut)

• Categories sheet mein A1:B8 select karo
• Insert → Charts → Pie → Doughnut
• Data Labels add karo: right-click chart → Add Data Labels → Value + Percentage
• Chart title: "Expense Breakdown"

Chart 3: Savings Progress (Column/Bar Chart)

• Finance_Data A1:A13 aur D1:D13 select karo (Ctrl se multi-select)
• Insert → Charts → Column → Clustered Column
• Colors: Savings bars ko green color deo

6 Dashboard Final Touches

• Ek bada title add karo: "PERSONAL FINANCE DASHBOARD 2024"
• Background: Light Gray (#f3f4f6) – page ka overall color
• Charts ko resize karke properly arrange karo
• Month dropdown add karo: Data Validation → List → Jan-Dec
• Charts ko lock karo: right-click → Format Chart Area → Properties → Don't move with cells

■ Tip: Dashboard ko protect karo: Review → Protect Sheet – taaki koi accidentally formula delete na kare. Sirf
input cells unlock rakho.

Feature Chart Type Data Source

Monthly Trend Line Chart Finance_Data A:D

Expense Split Donut/Pie Chart Categories A:B

Savings Bar Column Chart Finance_Data A, D

Income Summary KPI Card (no chart) SUM formulas

 QUICK REFERENCE – IMPORTANT EXCEL FORMULAS

Formula / Feature Syntax Use Case

SUM =SUM(A1:A10) Numbers add karo

AVERAGE =AVERAGE(A1:A10) Average calculate karo

MAX / MIN =MAX(A1:A10) Highest / Lowest value

COUNT / COUNTA =COUNTA(A1:A10) Filled cells count karo

COUNTIF =COUNTIF(A1:A10,"P") Condition-based count

IF (Simple) =IF(A1>50,"Pass","Fail") Condition check

Nested IF =IF(A1>=90,"A+",IF(A1>=80,"A","B")) Multiple conditions

IFS =IFS(A1>=90,"A+",A1>=80,"A",TRUE,"F") Multiple conditions (modern)

VLOOKUP =VLOOKUP(C1,Sheet2!A:H,3,FALSE) Data from another table

DATEDIF =DATEDIF(C2,TODAY(),"Y") Date difference in years

PMT =PMT(rate,nper,-pv) Loan EMI calculate

PROPER =PROPER(A1&" "&B1) Proper case name join

LARGE =LARGE(A1:A100,1) Nth largest value

TODAY() =TODAY() Aaj ki date

TEXT =TEXT(A1,"DD-MM-YYYY") Date format change

IFERROR =IFERROR(formula,"Error") Error handle karo

KEYBOARD SHORTCUTS (Time Bachao!)

Shortcut Action Shortcut Action

Ctrl+C Copy Ctrl+Z Undo

Ctrl+V Paste Ctrl+Y Redo

Ctrl+X Cut Ctrl+F Find

Ctrl+B Bold Ctrl+H Find & Replace

Ctrl+S Save Ctrl+T Create Table

Ctrl+A Select All Alt+F11 Open VBA Editor

F2 Edit Cell Ctrl+Shift+L Toggle Filter

Ctrl+Home Go to A1 Alt+Enter New line in cell

 ■ Assignment Complete! Sabhi 13 topics cover ho gaye.
Practice karo – Excel skills sirf karne se aati hain, padhne se nahi. ■