Excel में Automatic Student Marksheet कैसे बनाएं?

Q: Q1. XLOOKUP Formula काम नहीं कर रहा, #NAME? Error आ रहा है।

XLOOKUP सिर्फ Excel 2019 और Microsoft 365 में available है। अगर आपके पास पुराना version है (2013, 2016), तो XLOOKUP की जगह VLOOKUP या INDEX-MATCH use करें। उदाहरण: =VLOOKUP($N$5, Data!$A:$N, 3, 0) — यह Roll No से Name fetch करेगा (Column 3 = Name)।

Q: Q2. VBA Code save होने के बाद काम नहीं कर रहा।

File को .xlsx के बजाय .xlsm (Excel Macro-Enabled Workbook) format में save करें। File → Save As → File Type → Excel Macro-Enabled Workbook (*.xlsm)।

Q: Q3. Grade Calculation में "*" आ रहा है।

इसका मतलब है कि Marks किसी भी Grade range में fit नहीं हो रहा। Grade Lookup Table (M21:O28) को check करें — सभी ranges continuous हैं या नहीं। 0 से 100 तक कोई gap नहीं होनी चाहिए।

Q: Q4. Print Preview में Marksheet पूरी page में नहीं आ रही।

VBA code में PrintArea को सही set करें। साथ ही Page Setup में Scaling को "Fit Sheet on One Page" करें: VBA में .FitToPagesWide = 1 और .FitToPagesTall = 1 add करें।

Q: Q5. नए Student add करने पर XLOOKUP automatically काम करेगा?

हाँ! इसीलिए हमने Step 2 में Excel Table (Master_Data) बनाई थी। Table में नई row add करते ही XLOOKUP automatically उसे include कर लेगा।

Q: Q6. Developer Tab नहीं दिख रहा है।

File → Options → Customize Ribbon → Right Panel में "Developer" के आगे checkbox लगाएं → OK click करें।

Kunal

5 hours ago

Excel में Student Marksheet कैसे बनाएं? Step-by-Step Tutorial | TheOfficeTutorials

XLOOKUP, Nested IF Formula, Grade System और VBA Print Button के साथ एक Professional Annual Marksheet बनाएं – Step-by-Step Complete Guide

📋 विषय-सूची (Table of Contents)

Introduction – Marksheet क्यों और कैसे?
File Structure – दो Sheets का Setup
Step 1: Data Sheet बनाएं (Master Data)
Step 2: Excel Table बनाएं (Structured Reference)
Step 3: Marksheet Sheet का Layout बनाएं
Step 4: XLOOKUP Formula लगाएं (Auto Data Fetch)
Step 5: Grade Lookup Table बनाएं
Step 6: Marks, Total, Percentage Formula
Step 7: Grade Formula (Nested IF)
Step 8: Division/Result Formula
Step 9: VBA Print Button बनाएं
Step 10: Professional Formatting
FAQ – अक्सर पूछे जाने वाले सवाल

💡

Introduction – Marksheet क्यों Automatic बनाएं?

अगर आप एक Teacher हैं, School Administrator हैं, या Coaching Center चलाते हैं, तो हर Exam के बाद Students के लिए Marksheet बनाना एक बड़ा काम होता है। हर बार manually Roll Number देखकर, नाम टाइप करके, Marks जोड़कर Grade निकालना — यह सब बहुत time-consuming और error-prone होता है।

इस Tutorial में हम एक Fully Automatic Excel Marksheet बनाएंगे जिसमें:

✅

इस Marksheet में क्या होगा? सिर्फ Roll Number डालते ही — Student का नाम, पिता का नाम, Marks, Grade, Percentage, और Division सब कुछ Automatically भर जाएगा। एक VBA Button से Print Preview भी होगी।

इस Tutorial को follow करने के लिए आपको Excel 2016 या उसके बाद का version चाहिए (XLOOKUP के लिए Excel 2019 / Microsoft 365 recommended है)।

🗂

File Structure – दो Sheets का Setup

हमारी Marksheet File में दो Sheets होंगी। पहली Sheet में सभी Students का Data होगा (Master Database), और दूसरी Sheet में Marksheet का Template होगा।

⚠️

Important: File को .xlsm (Excel Macro-Enabled Workbook) format में save करें, क्योंकि इसमें VBA Macros होंगे। सामान्य .xlsx में VBA save नहीं होता।

Step 1: Data Sheet बनाएं (Master Database)

सबसे पहले Excel open करें और पहली Sheet का नाम “Data” रखें। इस Sheet में Row 1 में निम्नलिखित Headers बनाएं:

Column	Header Name	उदाहरण Data
A	Roll No	1001, 1002, 1003…
B	Registration No	20240001, 20240002…
C	Name	Amit Kumar, Rahul Singh…
D	Father Name	Raj Kumar, Suresh Singh…
E	Mother Name	Sunita Devi, Meena Devi…
F	Mobile	9876543210…
G	Address	Patna, Delhi…
H	Class	10, 12…
I	Hindi	78, 85…
J	English	82, 88…
K	Math	90, 92…
L	Science	85, 86…
M	Social Science	80, 84…
N	Sanskrit/Urdu	75, 80…

📌 Pro Tip

Row 1 में सभी Headers बनाने के बाद, Row 2 से अपने सभी Students का data भरना शुरू करें। Marks columns (I से N) में 0 से 100 के बीच का integer value डालें।

Step 2: Excel Table बनाएं (Structured Reference)

Data enter करने के बाद, इस data range को एक Excel Table में convert करें। इससे XLOOKUP में Structured References का use होगा, जो formula को ज़्यादा readable और reliable बनाता है।

🔧 Steps

1. Data Sheet में किसी भी data cell पर click करें (जैसे A1)।
2. Keyboard पर Ctrl + T दबाएं।
3. “Create Table” dialog में “My table has headers” checkbox checked रखें।
4. OK click करें।
5. Table Name change करें: Table Design Tab → Table Name box में “Master_Data” टाइप करें।

💡

Excel Table क्यों ज़रूरी है? जब आप बाद में नए students add करेंगे, तो Table automatically expand हो जाएगी और XLOOKUP formula को manually update नहीं करना पड़ेगा। साथ ही Master_Data[Name] जैसा syntax formula को बहुत clear बनाता है।

अगर आप Excel के basic formulas सीखना चाहते हैं, तो यह भी देखें: Excel Basic Formulas Guide

Step 3: Marksheet Sheet का Layout बनाएं

अब दूसरी Sheet पर जाएं और उसका नाम “Marksheet” रखें। इस Sheet पर निम्नलिखित Layout बनाएं:

🏫 Header Area (Rows 3-5)

Cell	Content
`B3`	School Name (जैसे “ABC Public School”)
`B4`	“Result” (Bold, Large Font)
`B5`	“Annual Examination 2026”

🔍 Check Result Area (Rows 4-5, Right Side)

Cell	Content
`M4`	“Check Result” (Label)
`M5`	“Roll Number” (Label)
`N5`	Input Cell – यहाँ Roll Number डाला जाएगा (Yellow Background दें)

👤 Student Info Area (Rows 6-10)

Cell	Label	Formula Cell
`B6`	Student Name	`E6`
`B7`	Father’s Name	`E7`
`B8`	Roll Number	`E8`
`B9`	Registration Number	`E9`
`B10`	Class	`E10`

📚 Marks Detail Table (Rows 13-19)

Cell	Content
`B13`	Subject (Header)
`D13`	Total Mark (Header)
`F13`	Marks Obtained (Header)
`H13`	Grade (Header)
`B14:B19`	Hindi, English, Math, Science, Social Science, Sanskrit/Urdu
`D14:D19`	100 (सभी में)
`F14:F19`	XLOOKUP Formula (Marks Auto-Fetch)
`H14:H19`	Nested IF Grade Formula

📊 Result Area (Rows 20-22)

Cell	Content
`B20`	“Final Result”
`B21`	“Result/Division”
`D21`	Division Formula
`G21`	“Total Marks”
`H21`	SUM Formula
`B22`	“Grade”
`D22`	Grade Formula
`G22`	“Percentage”
`H22`	Percentage Formula

Step 4: XLOOKUP Formula लगाएं (Auto Data Fetch)

यह इस Marksheet की सबसे important feature है। N5 cell में जो भी Roll Number डाला जाएगा, उससे automatically Student की सारी information fetch हो जाएगी।

XLOOKUP function Excel 2019 और Microsoft 365 में available है। यह VLOOKUP का एक बेहतर version है।

📌 Syntax

XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

✍️ Student Name (Cell E6)

=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Name], , 0)

$N$5 — यह वह cell है जिसमें Roll Number डाला जाएगा ($$ से absolute reference)
Master_Data[Roll No] — Data Sheet की “Roll No” column में search होगा
Master_Data[Name] — मिलने पर “Name” column की value return करेगा
0 — Exact Match mode

✍️ बाकी Cells के लिए Formulas

Cell	Formula
`E7`	`=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Father Name],, 0)`
`E8`	`=$N$5` (Roll Number simply reference करें)
`E9`	`=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Registration No],, 0)`
`E10`	`=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Class],, 0)`

✍️ Marks Fetch (Cell F14 – Array Formula)

सभी 6 subjects के marks एक ही formula से fetch करने के लिए हम Dynamic Array (Spill) का use करते हैं। F14 में यह formula enter करें और Ctrl + Shift + Enter दबाएं:

=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[[Hindi]:[Sanskrit/Urdu]],, 0)

Master_Data[[Hindi]:[Sanskrit/Urdu]] — यह Hindi से Sanskrit/Urdu तक की सभी Columns (6 subjects) को एक साथ return करता है।
यह एक Spill Formula है — F14 में enter करने पर F14:F19 तक automatically fill हो जाएगा।
बाकी cells (F15:F19) में कोई formula न डालें।

🌟

Spill Formula की खासियत: जब आप F14 में यह Array formula डालेंगे, तो Excel स्वयं F14:F19 में values भर देगा। अगर F15:F19 में पहले से कुछ है तो वो delete करना होगा।

Step 5: Grade Lookup Table बनाएं

Marksheet Sheet के Right side में (Column M, N, O) एक Grade Reference Table बनाएं। यह table Grade Formula में reference की जाएगी।

📊 Subject-wise Grade Table (M19:O28)

Cell M	Cell N (Min Marks)	Cell O (Max Marks)
A+	91	100
A	81	90
B+	71	80
B	61	70
C+	51	60
C	41	50
D	33	40
E (Fail)	0	32

📊 Division/Percentage Table (M11:O15)

Cell M (Label)	Cell N (Min %)	Cell O (Max %)
1st Division	0.60 (60%)	1 (100%)
2nd Division	0.50 (50%)	0.59
3rd Division	0.33 (33%)	0.49
Fail	0	0.33

⚠️

ध्यान दें: Division table में percentage values को decimal format में enter करें। 60% के लिए 0.60 लिखें, न कि 60। Cells को Percentage format में format करें।

Step 6: Marks, Total और Percentage Formula

➕ Total Marks (Cell H21)

=SUM(F14:F19)

सभी 6 subjects के Marks का योग
Maximum Total = 600 (6 subjects × 100 marks each)

📊 Percentage (Cell H22)

=H21 / SUM(D14:D19)

H21 = Total Obtained Marks
SUM(D14:D19) = Total Maximum Marks (600)
Result एक decimal number आएगा (जैसे 0.82 = 82%)
Cell H22 को Percentage format में set करें: Ctrl+1 → Number → Percentage → 2 decimal places

Step 7: Grade Formula – Nested IF (प्रत्येक Subject के लिए)

प्रत्येक Subject की Grade calculate करने के लिए हम Nested IF + AND formula use करेंगे। यह formula Marks को Grade Lookup Table से compare करता है।

✍️ Hindi Grade (Cell H14)

Nested IF Formula – Grade Calculation
=IF(AND(F14>=$N$21,F14<=$O$21),$M$21,
  IF(AND(F14>=$N$22,F14<=$O$22),$M$22,
    IF(AND(F14>=$N$23,F14<=$O$23),$M$23,
      IF(AND(F14>=$N$24,F14<=$O$24),$M$24,
        IF(AND(F14>=$N$25,F14<=$O$25),$M$25,
          IF(AND(F14>=$N$26,F14<=$O$26),$M$26,
            IF(AND(F14>=$N$27,F14<=$O$27),$M$27,
              IF(AND(F14>=$N$28,F14<=$O$28),$M$28,"*"))))))))

F14 = Subject का प्राप्त Marks
$N$21 और $O$21 = Grade Lookup Table की Min और Max values ($ = Absolute Reference)
$M$21 = Grade का Label (A+, A, B+ आदि)
हर IF condition एक Grade range check करती है और उससे match होने पर उस Grade का label return करती है।
अंत में "*" default value है अगर कोई भी condition match न हो।

📋 Copy करने का तरीका

H14 में formula enter करने के बाद, इसे H15:H19 तक copy करें। चूँकि F14, F15, F16... row-wise change होगा और $N$21 आदि absolute हैं, formula automatically सही काम करेगा।

✍️ Overall Grade (Cell D22)

Overall Grade Average Marks के आधार पर calculate होगी। D22 में यह formula डालें:

Overall Grade Formula
=IF(AND(AVERAGE(F14:F19)>=$N$21,AVERAGE(F14:F19)<=$O$21),$M$21,
  IF(AND(AVERAGE(F14:F19)>=$N$22,AVERAGE(F14:F19)<=$O$22),$M$22,
    IF(AND(AVERAGE(F14:F19)>=$N$23,AVERAGE(F14:F19)<=$O$23),$M$23,
      IF(AND(AVERAGE(F14:F19)>=$N$24,AVERAGE(F14:F19)<=$O$24),$M$24,
        IF(AND(AVERAGE(F14:F19)>=$N$25,AVERAGE(F14:F19)<=$O$25),$M$25,
          IF(AND(AVERAGE(F14:F19)>=$N$26,AVERAGE(F14:F19)<=$O$26),$M$26,
            IF(AND(AVERAGE(F14:F19)>=$N$27,AVERAGE(F14:F19)<=$O$27),$M$27,
              IF(AND(AVERAGE(F14:F19)>=$N$28,AVERAGE(F14:F19)<=$O$28),$M$28,"*"))))))))

Step 8: Division / Result Formula

Division calculate करने के लिए Average Percentage को Division Table से compare किया जाएगा।

✍️ Division Formula (Cell D21)

Division / Result Formula
=IF((AVERAGE(F14:F19)/100) >= M13, $O$13,
  IF(AND(AVERAGE(F14:F19)/100 >= M14, AVERAGE(F14:F19)/100 <= N14), $O$14,
    IF(AND(AVERAGE(F14:F19)/100 >= M15, AVERAGE(F14:F19)/100 <= N15), $O$15,
      IF(AVERAGE(F14:F19)/100 >= N16, "Fail", ""))))

AVERAGE(F14:F19)/100 = Average Percentage (decimal में)
M13 = 0.60 (1st Division का minimum)
$O$13 = "1st Division" label
अगर Average ≥ 60% → 1st Division
अगर 50% ≤ Average ≤ 59% → 2nd Division
अगर 33% ≤ Average ≤ 49% → 3rd Division
अगर Average < 33% → Fail

💡

Note: Division Table के cells (M13:N16) में actual percentage values हैं (0.60, 0.50, आदि)। इसलिए formula में AVERAGE को 100 से divide किया जा रहा है ताकि दोनों same unit में हों।

Step 9: VBA Print Button बनाएं

अब हम एक VBA CommandButton बनाएंगे जो Print Preview launch करेगा और Marksheet का Print Area automatically set करेगा।

🔧 Button Insert करना

📌 Steps

1. Developer Tab → Insert → ActiveX Controls → Command Button
2. Marksheet Sheet पर button draw करें (Column M-N, Row 6 के आसपास)
3. Button पर Right Click → Properties → Caption: "Print Preview"
4. BackColor: Green (&H0000FF00&)
5. Design Mode बंद करें

💡

Developer Tab नहीं दिख रहा? File → Options → Customize Ribbon → Right Side में "Developer" checkbox check करें → OK।

✍️ VBA Code – Sheet2 (Marksheet)

Marksheet Sheet tab पर Right Click → View Code करें और निम्नलिखित code paste करें:

' ============================================
' CommandButton1 Click Event – Print Preview
' ============================================
Private Sub CommandButton1_Click()

    With ActiveSheet.PageSetup
        ' Print Area Set करें
        .PrintArea = "$B$3:$I$31"
        
        ' Paper Size – A4
        .PaperSize = xlPaperA4
        
        ' Margins (Centimeters में)
        .TopMargin = Application.CentimetersToPoints(1)
        .BottomMargin = Application.CentimetersToPoints(1)
        .LeftMargin = Application.CentimetersToPoints(1.5)
        .RightMargin = Application.CentimetersToPoints(1.5)
        
        ' Page Center करें
        .CenterHorizontally = True
        .CenterVertically = True
    End With
    
    ' Print Preview दिखाएं (Direct Print नहीं)
    ActiveWindow.SelectedSheets.PrintPreview
    
End Sub

' ============================================
' Worksheet Change Event – Button Color Change
' ============================================
Private Sub Worksheet_Change(ByVal Target As Range)

    ' अगर N5 (Roll Number cell) change हो
    If Not Intersect(Target, Range("N5")) Is Nothing Then
        
        If Range("N5").Value <> "" Then
            ' Roll Number है तो Button Green करें
            CommandButton1.BackColor = RGB(0, 180, 80)
        Else
            ' Roll Number नहीं है तो Button Gray करें
            CommandButton1.BackColor = RGB(200, 200, 200)
        End If
        
    End If
    
End Sub

✨

Smart Color Change: जब N5 में Roll Number डाला जाएगा, Button का color automatically Green हो जाएगा — यह User को indicate करता है कि Print करना safe है। जब N5 clear होगा, Button Gray हो जाएगा।

✍️ N5 Clear करने के लिए (Optional) – ThisWorkbook

अगर आप चाहते हैं कि Workbook open होने पर N5 automatically clear हो जाए, तो ThisWorkbook module में यह code add करें:

Private Sub Workbook_Open()
    ' File open होने पर Roll Number cell clear करें
    Sheets("Marksheet").Range("N5").ClearContents
End Sub

Step 10: Professional Formatting

Marksheet को professional look देने के लिए निम्नलिखित formatting करें:

🎨 Cells को Format करें

Area	Formatting
School Name (B3)	Font Size 16-18, Bold, Center Align, Blue Color
Headers (B13:H13)	Bold, Background Color: Dark Blue, Font: White
Subject rows (B14:H19)	Alternating Row Colors (Light Blue / White)
N5 (Input Cell)	Yellow Background (#FFFF00), Bold Border
Grade Column	Bold, Center Align
Total/Percentage (H21:H22)	Bold, Border

🔒 Cells Lock करें (Protection)

📌 Steps

1. पहले Ctrl+A से सभी cells select करें → Ctrl+1 → Protection Tab → "Locked" uncheck करें।
2. अब सिर्फ N5 (Roll Number Input) को select करें → Locked check करें।
3. Review Tab → Protect Sheet → Password set करें → OK।

अब सिर्फ N5 cell में input हो सकेगा, बाकी cells protected रहेंगे।

📝 Footer Notes

Marksheet के नीचे (Rows 29-31) ये notes add करें:

📌 Footer Text

B29: "Grade Rule: 91 to 100 – A+, 81 to 90 – A, 71 to 80 – B+, 61 to 70 – B, 51 to 60 – C+, 41 to 50 – C, 33 to 40 – D, 0 to 32 – E(Fail)"

B30: "Percentage Rule: Above 60% – 1st Division, 50% to 59% – 2nd Division, 33% to 49% – 3rd Division, Below 33% – Fail"

B31: "* This report is computer generated, hence does not require signature."

🎉 बधाई हो! आपकी Marksheet तैयार है!

आपने अब एक fully automatic, professional Excel Marksheet बना ली है जिसमें:

✅ XLOOKUP से Auto Data Fetch | ✅ Nested IF Grade Formula | ✅ Automatic Division Calculation | ✅ VBA Print Button

इस tutorial को Like करें और अपने Teacher/School friends के साथ share करें!

#Excel Hindi Tutorial #Marksheet in Excel #XLOOKUP Formula #VBA Macro Excel #Nested IF Formula #Grade System Excel #School Marksheet #Excel for Teachers #TheOfficeTutorials #Annual Examination #Excel Tips Hindi

Q1. XLOOKUP Formula काम नहीं कर रहा, #NAME? Error आ रहा है।

XLOOKUP सिर्फ Excel 2019 और Microsoft 365 में available है। अगर आपके पास पुराना version है (2013, 2016), तो XLOOKUP की जगह VLOOKUP या INDEX-MATCH use करें। उदाहरण: =VLOOKUP($N$5, Data!$A:$N, 3, 0) — यह Roll No से Name fetch करेगा (Column 3 = Name)।

Q2. VBA Code save होने के बाद काम नहीं कर रहा।

File को .xlsx के बजाय .xlsm (Excel Macro-Enabled Workbook) format में save करें। File → Save As → File Type → Excel Macro-Enabled Workbook (*.xlsm)।

Q3. Grade Calculation में "*" आ रहा है।

इसका मतलब है कि Marks किसी भी Grade range में fit नहीं हो रहा। Grade Lookup Table (M21:O28) को check करें — सभी ranges continuous हैं या नहीं। 0 से 100 तक कोई gap नहीं होनी चाहिए।

Q4. Print Preview में Marksheet पूरी page में नहीं आ रही।

VBA code में PrintArea को सही set करें। साथ ही Page Setup में Scaling को "Fit Sheet on One Page" करें: VBA में .FitToPagesWide = 1 और .FitToPagesTall = 1 add करें।

Q5. नए Student add करने पर XLOOKUP automatically काम करेगा?

हाँ! इसीलिए हमने Step 2 में Excel Table (Master_Data) बनाई थी। Table में नई row add करते ही XLOOKUP automatically उसे include कर लेगा।

Q6. Developer Tab नहीं दिख रहा है।

File → Options → Customize Ribbon → Right Panel में "Developer" के आगे checkbox लगाएं → OK click करें।

Download Practice File: Download Now