Excel Notes
Excel Open Shortcut Key: - Window + R = Type Excel + Enter
Home Tab
Insert Tab
Page Layout
Formula
Data
Review
View
Home Tab:-
Clipboard
Font
Alignment
Number
Styles
Cell
Editing
Insert Tab:-
Table
Iiiustrations
Charts
Links
Text
Page Layout:-
Theme
Page Setup
Scale of Fit
Sheet Options
Arrange
Formulas:-
Function Library
Defined Names
Formulas Auditing
Calculation
Function Library
Autosum Formulas
RANDBETWEEN: - =Randbetween (Bottom, Top)
(Randbetween Formula Se Value
Change Na Ho Uske Liye Copy + Paste Value Krna Hai.)
SUM: - =Sum (Select Row Number)
AVERAGE: - =Average (Select Row Number)
COUNT NUMBER: - =Count (Select Row Number)
MIN: - =Min (Select Row Number)
MAX: - =Max (Select Row Number)
P/F: - =If (%>33,”pass”,”fail”)
P/F: - =If (min (Select Row Num) <33,”supply”, If (max
(Select Row Num)>33,”pass”))
GRADE: - =If (%>80,”A++”, If (%>70,”A+”, If (%>60,”A”,
If (%>50,”B”, If (%>40,”C”, If (%>33,”D”, If
(%< 33,”Fail”)))))))
DIVISION: - =If (%>60,”First”, If (%>45,”Second”, If (%>
33,”Third”, If (%< 33,”Fail”))))
RANK: - =Rank (First %, All % + F4 Frizz)
Financial Formulas
PMT: - Payment
IPMT: - Interest Payment
PPMT: - Principal Payment
PV: - Present Value
FV: - Future Value
NPER: - Total Period
RATE: - Interest Rate
PMT: - IPMT + PPMT
PMT: - =Pmt (Interest Rate/12, Loan Period*12, Loan Amt)
IPMT: - =Ipmt (Interest Rate/12, Per Month, Loan Period*12,
Loan Amt)
PPMT: - Ppmt (Interest Rate/12, Per Month, Loan Period*12,
Loan Amt)
PV: - =PV (Interest Rate/12, Loan Period*12, Pmt)
FV: - =FV (Interest Rate/12, Loan Period*12, Pmt)
NPER: - =Nper (Interest Rate/12, Pmt, Loan Amt)
RATE: - =Rate (Loan Period*12, Pmt, Loan Amt)
BALANCE: - =Loan Amt – Ppmt
(Balance 2 Times Nikalna pdta he Fr Drag Kr
Skte Hai)
Logical Formulas
IF: - =If (Logical Test>/<, Value,”True”,”False”)
OR: - =Or (Logical 1> Value, Logical 2> Value)
AND: - =And (Logical 1> Value, Logical 2> Value)
NOT: - =Not (Logical)
IF/OR IF/AND IF/NOT
TEXT Formulas
UPPER: - =Upper (Text)
LOWER: - =Lower (Text)
PROPER: - =Proper (Text)
LEFT: - =Left (Text, Left Side Se Num of Text)
MID: - =Mid (Text, Center_Start_Num, Num of Text)
RIGHT: - =Right (Text, Right Side Se Num of Text)
REPLACE: - =Replace (Old Text, Change Text of Start Num,
Num of Text, “New Text”)
REPT: - =Rept (Text, Jitne Times Rept Krna Hai Text
Num)
EXACT: - =Exact (Similar Text Value is True, Different
Text Value is False)
TRIM: - =Trim (Extra Space Cut Krne Ke Liye)
CONCATENATE: - =Concatenate (Text1,” “, Text2,” “,
Text3…..)
TEXT: - =Text (Select Date, Day ke Liye “DD”_Month ke
Liye “mm”_Year ke Liye “yy”)
LEN: - =Len (Text Num Count Krne Ke Liye)
Date & Time Formulas
DATE: - =Date (Year, Month, Day)
DAY: - =Day (Select Date)
MONTH: - =Month (Select Date)
YEAR: - =Year (Select Date)
EDATE: - =Edate (Start Date, Jitne Month Bad Ki Date
Nikalni he Month Num)
EOMONTH: - =Eomonth (Start Date, Current Month Ki Last
Date Niklti Hai)
WEEKDAY: - =Weekday (Week Ka Day Niklta Hai)
WEEKNUM: - =Weeknum (Konsa Week Chle Rha he Uska
Num Niklta Hai)
TODAY: - =Today (Current Date Niklti Hai)
NOW: - =Now (Current Date + Time Niklta Hai)
Lookup & Reference Formulas
Three Types of Array Lookup Array/ Lookup Vector, Array,
Table Array.
MATCH: - =Match (Lookup Value, Lookup Array,
[Match_Type_1, 0])
INDEX: - Index (Array, Row_Num, Column_Num)
LOOKUP: - =Lookup (Lookup Value, Lookup Vector, Result
Vector)
VLOOKUP: - =Vlookup (Lookup Value, Table Array, Col.
Index Num, Range Lookup_1, 0)
HLOOKUP: - =Hlookup (Lookup Value, Table Array, Row
Index Num, Range Lookup_1, 0)
HYPERLINK: - =Hyperlink (“Link Likhna He Ya Copy
Paste Krna He”, “Jo Link He Uska Name
Likhna Hai”)
TRANSPOSE: - =Transpose (Array)
[Transpose Me Phle Puri Table Ki Range Ko Select Krni Hai
Uske Bad Formula Lgana He Or Bracket Close Krne Ke bad
{CTRL+SHIFT+ENTER} Ek Sath Press Krna Hai.]
MATH & TRIG FORMULAS
ABS: - =Abs (Negative Num Ko Positive Me Change
Krta Hai)
EVEN: - =Even (Odd Num Ko Even Me Change Krta Hai)
ODD: - =Odd (Even Num Ko Odd Me Change Krta Hai)
POWER: - =Power (Num, Power)
PRODUCT: - =Product (Multiple Krta Hai)
RANDBETWEEN: - =Randbetween (Bottom, Top)
RAND: - =Rand
FACT: - =Fact (Factorial Nikalta Hai)
FACTDOUBLE: - =Fact double (Even Num Lene Se Only
Even No Ka Multiple Krta Hai or Odd
Num Lene Se Only Odd No Ka Multiple
Krta Hai)
SQRT: - =Sqrt (Square Root Nikalta Hai)
SUM: - =Sum (Num1, Num2, Num3…)
SUMIF: - =Sumif (Range, Criteria, Sum Range)
SUMIFS: - =Sumifs (Sum Range, Criteria Range1, Criteria1,
Criteria Range2, Criteria2)
More Function Formulas
STATISTICAL
COUNT: - =Count (Select Row Number) [Only Num
Count Krne Ke Liye]
COUNTA: - =Counta (Select Row Number) [Num Or
Text Dono Count Krne Ke Liye]
COUNTIF: - =Countif (Range, Criteria)
COUNTIFS: - =Countifs (Criteria Range1, Criteria1,
Criteria Range2, Criteria2)
COUNTBLANK: - =Countblank (Range)
ENGINEERING
BIN2DEC: - =Bin2Dec (10100101)
DEC2BIN: - =Dec2Bin (45)
INFORMATION
ISTEXT: - =Istext (Value) [Text per True or Num Per
False Ans Aayega]
ISBLANK: - =Isblank (Value) [Blank Place per True Or
Fill Place per False Ans Aayega]
DATA
GET EXTERNAL DATA
CONNECTIONS
SORT & FILTER
DATA TOOLS
OUTLINE
DATA TOOLS
DATA VALIDATION
CONSOLIDATE
SCENARIO MANAGER
GOAL SEEK
REVIEW
PROOFING
COMMENTS
CHANGES
VIEW
WORKBOOK VIEW
SHOW/HIDE
ZOOM
WINDOW
MACROS
SHEETS
1. RESULT
2. HOTEL MANAGEMENT
3. HOSPITAL DATA
4. EMPLOYEE SALARY SHEET
5. EMPLOYEE SALARY SLIP
6. EMPLOYEE ATTANDENCE SHEET
7. INVOICE BILL
8. MEDICAL BILL
9. EMI CALCULATION SHEET
10. DAY BOOK REPORT
11. PIE CHART
12. BAR CHART
13. DATA ANALISIC CHART
14. MEDICIENS BILLING DATA
15. SUMIF/COUNTIF FORMULAS SHEET