ADVANCE EXCEL COURSE
• Topic wise Notes with Practice Exercise and Assignement
• Explained in Roman Hindi
• Level : Advance – After compeletion of Basic Computer Couse
• Require guidence of qualified Computer Instructor
• Scope of Work : Account, HR, Store, Marketing and Data
Handling
• Course Duration : 1 Month
Publisher
Computer Training Center
Super Success Institute, Muzaffarnagar
Copywrite 2022 : All right reserved
https://sscstudy.com/ Page 1
Advance Excel Course
Contents of book
All About Fill Option _________________________________________________________ 3
ADVANCE SORT ___________________________________________________________ 10
Paste Special ______________________________________________________________ 15
Advance Filter _____________________________________________________________ 20
TEXT function: _____________________________________________________________ 21
Logical function: ___________________________________________________________ 26
Math’s & Trig Functions _____________________________________________________ 28
Database function _________________________________________________________ 33
Statistical Function _________________________________________________________ 34
Lookup & Reference Function ________________________________________________ 38
Transpose ________________________________________________________________ 40
Factorial _________________________________________________________________ 40
DECIMAL TO BINARY CONVERT _______________________________________________ 41
Date & Time Function_______________________________________________________ 42
Financial function __________________________________________________________ 43
Pivote TABLE ______________________________________________________________ 46
INVOICE SHEET ____________________________________________________________ 49
STOCK MAINTAIN _________________________________________________________ 50
Attendence Sheet __________________________________________________________ 51
Advance Excel Charts _______________________________________________________ 52
MS Excel Shortcut Keys _____________________________________________________ 53
https://sscstudy.com Page 2
Advance Excel Course
ALL ABOUT FILL OPTION
https://sscstudy.com Page 3
Advance Excel Course
https://sscstudy.com Page 4
Advance Excel Course
ACROSS WORKSHEETES
COPY TABLE AND SELECT ALL SHEET
SHIFT + CLICK PER SHEETTHEN ACROSS WORKSHEET OPTION SHOW
https://sscstudy.com Page 5
Advance Excel Course
All pr sbhi paste hoga
Contents se only content paste hoge
Formats se Formating bhi paste hogi
https://sscstudy.com Page 6
Advance Excel Course
https://sscstudy.com Page 7
Advance Excel Course
https://sscstudy.com Page 8
Advance Excel Course
https://sscstudy.com Page 9
Advance Excel Course
ADVANCE SORT
https://sscstudy.com Page 10
Advance Excel Course
https://sscstudy.com Page 11
Advance Excel Course
https://sscstudy.com Page 12
Advance Excel Course
https://sscstudy.com Page 13
Advance Excel Course
https://sscstudy.com Page 14
Advance Excel Course
PASTE SPECIAL
CTRL + ALT + V
1. ALL
https://sscstudy.com Page 15
Advance Excel Course
2. FORMULAS
3. VALUES
https://sscstudy.com Page 16
Advance Excel Course
4. FORMATS
5. COMMENT
https://sscstudy.com Page 17
Advance Excel Course
6. VALIDATION
Operation
1. Add
https://sscstudy.com Page 18
Advance Excel Course
Skip Blank
Transpose (Horizontal to Vertical – Vertical to Horizontal)
https://sscstudy.com Page 19
Advance Excel Course
ADVANCE FILTER
A B C
1 DAY UNIT RUNNIG TOTAL
2 SUNDAY 4 =SUM(B$2:B2)
3 MONDAY 4 =SUM(B$2:B3)
4 TUESDAY 2 =SUM(B$2:B4)
5 WEDNESDAY 5 =SUM(B$2:B5)
6 THURSDAY 8 =SUM(B$2:B6)
7 FRIDAY 6 =SUM(B$2:B7)
8 SATURDAY 10 =SUM(B$2:B8)
https://sscstudy.com Page 20
Advance Excel Course
TEXT FUNCTION:
UPPER
LOWER
https://sscstudy.com Page 21
Advance Excel Course
PROPER
TRIM
https://sscstudy.com Page 22
Advance Excel Course
LEFT
MID
https://sscstudy.com Page 23
Advance Excel Course
RIGHT
LENGTH
https://sscstudy.com Page 24
Advance Excel Course
Concatenate
EXACT
https://sscstudy.com Page 25
Advance Excel Course
LOGICAL FUNCTION:
OR
➢ 45 % ……………… Anyone sales amount greater than and equal 5000
➢ Not Bonus………. All sales amount less than 5000
AND
➢ 52 % ……………… All sales amount greater than and equal 12000
➢ 12 % ………. Anyone/all sales amount less than 12000
https://sscstudy.com Page 26
Advance Excel Course
IF
➢ PASS ……… Above 300
➢ FAIL………… Below 300
https://sscstudy.com Page 27
Advance Excel Course
MATH’S & TRIG FUNCTIONS
SUM
Product
https://sscstudy.com Page 28
Advance Excel Course
Round
ODD & EVEN
https://sscstudy.com Page 29
Advance Excel Course
POWER & SQRT
LCM
https://sscstudy.com Page 30
Advance Excel Course
GREATER COMMON DIGIT
SUMIF
https://sscstudy.com Page 31
Advance Excel Course
SUBTOTAL
SUMPRODUCT
A B C D
1 NAME UNIT PRICE PRODUCT
2 CPU 4 250 =B2*C2
3 MOUSE 4 350 =B3*C3
4 LED 10 300 =B4*C4
5 KEYBORD 2 150 =B5*C5
6 MONITOR 8 200 =B6*C6
7 COMPUTER 10 170 =B7*C7
8 DVD 18 70 =B8*C8
SUMPRODUCT =SUMPRODUCT(B2:B8,C2:C8) =SUM(D2:D8)
https://sscstudy.com Page 32
Advance Excel Course
DATABASE FUNCTION
https://sscstudy.com Page 33
Advance Excel Course
STATISTICAL FUNCTION
AVERAGE, MAX, MIN
COUNT
https://sscstudy.com Page 34
Advance Excel Course
COUNTA
COUNTBLANK
https://sscstudy.com Page 35
Advance Excel Course
LARGE
SMALL
https://sscstudy.com Page 36
Advance Excel Course
MEDIAN
Countif
https://sscstudy.com Page 37
Advance Excel Course
LOOKUP & REFERENCE FUNCTION
VLOOKUP
HLOOKUP
https://sscstudy.com Page 38
Advance Excel Course
https://sscstudy.com Page 39
Advance Excel Course
TRANSPOSE
FACTORIAL
https://sscstudy.com Page 40
Advance Excel Course
DECIMAL TO BINARY CONVERT
BINARY TO DECIMAL CONVERT
https://sscstudy.com Page 41
Advance Excel Course
DATE & TIME FUNCTION
CTRL+: Current Date
Shift + Ctrl Current Time
=today() Current Date
=now() Current Date & Time
=month(now()) Current Month
=years(now()) Current Year
=day(now()) Current day
=days(start date, end date) different day
=Hour(now()) Current Hour
=Minute(now()) Current Minute
=Second(Now()) Current Second
=weekday(now()) Current weekday
https://sscstudy.com Page 42
Advance Excel Course
FINANCIAL FUNCTION
PMT Per Month Instalment
IPMT Per Month Interest
PPMT Per Month Principal
https://sscstudy.com Page 43
Advance Excel Course
https://sscstudy.com Page 44
Advance Excel Course
DB Function
https://sscstudy.com Page 45
Advance Excel Course
PIVOTE TABLE
S.N. NAME DEPARTMENT SALARY
1 SUMAN TEACHER 12000
2 PRIYA TEACHER 10000
3 ANKIT DOCTER 25000
4 AJAY COMPUTER 15000
5 AMAN AGRICULTURE 13000
6 VISHAL DOCTER 8000
7 MONA AGRICULTURE 9000
8 MONIKA COMPUTER 17000
9 PAYAL TEACHER 14000
10 MAHIMA DOCTER 20000
11 Ram NURSE 8000
12 SYAM TEACHER 10000
13 MOHAN LOYER 13000
14 SUHAN ADVOCATE 9000
15 SUNITA DOCTER 14000
https://sscstudy.com Page 46
Advance Excel Course
PIVOTE TABLE
Sum of SALARY
AGRICULTURE 22000
AMAN 13000
MONA 9000
COMPUTER 32000
AJAY 15000
MONIKA 17000
DOCTER 67000
ANKIT 25000
MAHIMA 20000
VISHAL 8000
SUNITA 14000
TEACHER 46000
PAYAL 14000
PRIYA 10000
SUMAN 12000
SYAM 10000
NURSE 8000
Ram 8000
LOYER 13000
MOHAN 13000
ADVOCATE 9000
SUHAN 9000
Grand Total 197000
https://sscstudy.com Page 47
Advance Excel Course
https://sscstudy.com Page 48
Advance Excel Course
INVOICE SHEET
Product Name HSN Code GST Rate Rate
Keyboard 2345 5% 210
Mouse 2346 5% 140
Monitor 2347 10% 2500
CPU 2348 20% 1000
Wireless Mouse 2349 10% 430
Wireless Keyboard 2350 10% 540
Power cable 2351 12% 100
Hard Disk 2352 5% 1500
VGA Cable 2353 10% 120
Motherboard 2354 25% 2000
Ram 2321 0.1 2200
https://sscstudy.com Page 49
Advance Excel Course
STOCK MAINTAIN
https://sscstudy.com Page 50
Advance Excel Course
ATTENDENCE SHEET
https://sscstudy.com Page 51
Advance Excel Course
ADVANCE EXCEL CHARTS
https://sscstudy.com Page 52
Advance Excel Course
MS EXCEL SHORTCUT KEYS
Ctrl + A (Select All)
Ctrl + B (Bold)
Ctrl + C (Copy)
Ctrl + D (Fill Down)
इस शॉर्ट कर् key को उपयोग माइक्रोसॉफ्र् एक्सेल में र्ाइप ककए गए डार्ा
को बिना कॉपी ककए अर्ाटत बिना Ctrl+C ककये बिना ही हम डायरे क्र् जो
ऊपर Cell में किया हुआ है उसे ननचे वाले Cell कॉपी कर सकते हैं .
Ctrl + E (Flash Fill)
Ctrl + F (Find)
Ctrl + G (GoTo)
इस बवकल्प को Microsoft Excel में GoTo ऑप्शन का उपयोग करने के
नलए ककया जाता है अर्ाटत यकि में पेज नंिर वन में हं और मैं पेज नंिर
10 में जाना चाहता हं तो उसके नलए मैं GoTo Option का उपयोग
करंगा.
Ctrl + I (Italic)
Ctrl + K (Hyperlink)
Ctrl + L (Left Alignment)
Ctrl + N (New Workbook)
Ctrl + O (Open)
https://sscstudy.com Page 53
Advance Excel Course
Ctrl + P (Print)
Ctrl + R (Right Alignment)
Ctrl + S (Save)
Ctrl + T (Table Create)
Ctrl + U (Underline)
Ctrl + V (Paste)
Ctrl + W (Worksheet Close)
Ctrl + X (Cut)
Ctrl + Y (Redo)
Ctrl + Z (Undo)
Format Painter (Ctrl+Shift+C)
MS Excel Shortcut Keys in Hindi F1 to F11
Ctrl + F1 ( Show or Hide Ribbon)
Ctrl + F2 (Print Preview)
Ctrl + F3 (Name Manager)
Ctrl + F6 (Switching Workbook)
इस शॉर्ट कर् का उपयोग माइक्रोसॉफ्र् एक्सेल में एक वकट िुक से िसरे
वकट िुक में जाने के नलए ककया जाता है .
Ctrl + F9 (Minimize Windows)
Ctrl + F10 (Maximize Windows)
Ctrl + Space (Select Current Column)
Shift + Space (Select Current Row)
Ctrl + Arrow Key
https://sscstudy.com Page 54
Advance Excel Course
Ctrl + Home (Goto First Cell)
Ctrl + End (Goto Last Cell)
Ctrl + + (Add New Column or Row)
Ctrl + – (Delete Column or Row)
Ctrl + : (Insert Current Time)
Ctrl + ; (Insert Current Date)
Alt + O+R+E Row Height
Alt +O+C+W Column Width
Alt + H + O + I Column width Auto adjust
Alt + H + O + A Row Height Auto adjust
Excel Other Keyboard Shortcut
Shortcut
Particulars
Keys
Ctrl + 0 इस शॉर्ट कर् की का उपयोग Selected column को Hide करने के नलए ककया जाता है .
Ctrl + 9 इस शॉर्ट कर् की का उपयोग Selected row को Hide करने के नलए ककया जाता है .
Ctrl + Shift +
इस Shortcut Keys का उपयोग Column को Unhide करने के नलए करते हैं .
0
Ctrl + Shift +
इस Shortcut Keys का उपयोग Row को Unhide करने के नलए करते हैं करते हैं .
9
इस Shortcut Keys का उपयोग Format cells window को open करने के नलए ककया
Ctrl + 1
जा सकता है .
इस Shortcut Keys का उपयोग सेलेक्र् ककए र्े क््र् को Strikethrough अर्ाटत उसके
Ctrl + 5
ऊपर लाइन खींचने के नलए ककया जाता है .
Ctrl + Shift
इस Shortcut Keys का उपयोग Insert dialog box show करने के नलए ककया जाता है .
++
Shift + F2 इस Shortcut Keys का उपयोग Insert comment करने के नलए ककया जाता है .
https://sscstudy.com Page 55
Advance Excel Course
Shift + F3 इस Shortcut Keys का उपयोग Excel formula box open होगा.
Shift + F5 इस Shortcut Keys का उपयोग Search box ओपन होगा.
Shift + F11 इस शॉर्ट कर् का उपयोग करके हम new worksheet ला सकते हैं .
F1 माइक्रोसॉफ्र् एक्सेल के help प्राप्त करने के नलए F1 ििाएं.
यह एक महत्वपर्ट शॉर्ट कर् की है जजसके उपयोग करके हम Selected cell को edit
F2
कर सकते हैं .
F4 यह शॉर्ट कर् का उपयोग करके हम last action को ररपीर् कर सकते हैं .
इसका उपयोग हम माइक्रोसॉफ्र् एक्सेल में Selected text का Spelling check के नलए
F7
करते हैं .
F11 इस Shortcut Keys का उपयोग Selected data का Chart िनाने के नलए ककया जाता है
F12 F12 िर्न प्रेस करके हम ककसी भी फाइल को Save as कर सकते हैं .
Shift + Space इस Shortcut Keys का उपयोग सभी Row को select करने के नलए ककया जाता है .
Ctrl + Space इस Shortcut Keys का उपयोग सभी Column को select करने के नलए ककया जाता है .
Alt + = Auto Sum फामल
ट ा लगाने के नलए इसका उपयोग ककया जाता है .
Alt + F1 Chart िनाने के नलए िनाने के नलए इस Shortcut Keys का उपयोग ककया जाता है .
Alt + F8 Macro display करने के नलए इस Shortcut Keys का उपयोग ककया जाता है .
Ctrl + Shift +
Border Outline लाने के नलए इसका उपयोग ककया जाता है
&
Ctrl +
Borders को हर्ाने के नलए इस शॉर्ट कर् का उपयोग ककया जाता है .
Shift+-
Ctrl + Shift +
Expand/Collapse formula bar के नलए इस Shortcut Keys का उपयोग ककया जाता है .
U
https://sscstudy.com Page 56
Advance Excel Course
Border Shortcut keys
https://sscstudy.com Page 57
