What Is Excel ?

Column 16384

Cell

1048576

Row
Address Bar

Sheet
 Formulas of MS Excel

Addition
=SUM(R:R)

Percentage
=Obtain marks/Total mark*100

Remarks Signs
=If(R>=60,”Pass”,”Fail”) Greater than “>”
Greater than “<”
Max Score Equal to “=”
=MAX(R:R) Great than equal to “>=”
Less than equal to “<=”
Minimum Score Not equal to “<>”
=MIN(R:R)

Average To link any cell with other cell

=AVERAGE(R:R)
=+R
Count
=COUNT(R:R) To show Date and Time
=NOW()
Count If Less Than =Today()
=COUNTIF(R:R,”<criteria”)
To show File name
Count If Greater Than =CELL("filename")
=Countif(R:R”>criteria”)

Grade
=IF(Marks Obtained>=250,"Pass","Fail")

Grade
=IF(Percentage>=80,”A+”,IF(Percentage>=60,”B”,IF(Percentage>50,”C”,”Fail”)))

Vlookup
 Prepared by M.Ali
Short Keys Of MS Excel
Add Row Ctrl+"+"
Delete Row Ctrl+"-"
To make Auto sum of any column area Altr+"="
To Repeat Above mentioned data by choice Altr+"arrow Down"
Jump to next Sheet Ctrl+Page up/down
To Hide Sheet Altr+O+H+H
To Unhide Sheet Altr+O+H+U
Rename Sheet Altr+O+H+R
Hide Column Ctrl+O (Zero)
Unhide Column Ctrl+Shift+O (Zero)
Add/Remove Filter Altr+D+F+F
Hide Row Ctrl+9
Unhide Row Ctrl+Shift+9
To set height of Row Altr+O+R+E
To set width of Column Altr+O+C+W
To select entire Column Ctrl+Space
To select entire Row Shift+Space
To Sort any data Altr+D+S
To move or copy Sheet Altr+E+M
To merge cell (in special setting) Altr+H+M+C
To use "Centre Alignment" Altr+H+AC
To Show Sheet in Formula Views Ctrl+(~) sign..
To open menu (Format Cell) Ctrl+1
To open (Any file) Ctrl+O
To Close (Any opened file) Altr+F4
To Close (Any opened Sheet) Ctrl+F4
To Set Zooming Size Altr+V+Z
To Insert Current Date Ctrl+(:) sign.
To Insert Current Time Ctrl+Shift(:) sign. Or Altr+O+E
Check Print Preview Altr+F+V
To view in Full screen mode Altr+V+U
To Set Print Area Altr+P+R+S
To select all sheet Ctrl+A
To use Save As Altr+F+A or F12
To save any file Ctrl+S
To take Print Ctrl+P
To copy any text Ctrl+C
To paste any text Ctrl+V
To cut any text Ctrl+X
To make Duplicate any Row wise Ctrl+D
To make Duplicate any Column wise Ctrl+R
To make Bold text Ctrl+B or Ctrl+2
To make Italic text Ctrl+I or Ctrl+3
To underline text Ctrl+U or Ctrl+4
To make Number text in two digists format Ctrl+Altr+(!)Sign

Page 3
 Prepared by M.Ali
Short Keys Of MS Excel
To make cross line on text Ctrl+5
Undo typing Ctrl+Z
Redo typing Ctrl+Y
To find data from selected area. Ctrl+F
To add new Sheet/work book Altr+I+W
To delete any sheet/work book Altr+E+L
To Show or Hide "formula bar" Altr+V+F
To Replace formula or data Ctrl+H
To work as Return Arrow Shift+Tab
Rewrite written cell F2
Repeat recent work done F4
To create Chart Sheet F11
To open Chart Sheet menu Altr+I+H
When two file opened in Excel to move next file Ctrl+Tab
To fix cell for selection F8
To check spelling F7
To cancel any recent command Esc
To use "freeze Pan" Altr+W+F+F
To cancel "freeze Pan/Unfreeze Pan" Altr+W+F
To minimize the complete file Ctrl+F9
To Maximize the complete file Ctrl+F10
To minimize the complete file Altr+Space+N

Page 4
 Addition
Syntax
=SUM(Range1,Range2,Range3... through to Range30).

Formatting
No special formatting is needed.

Horizontal

100 200 300 600 =SUM(C4:E4)

Vertical
100
200
300
600 =SUM(C7:C9)

Single Cells
100 300 600 =SUM(C13,D14,E13)
200

Multiple Ranges
100 400
200 500
3000 600
4800 =SUM(C17:C19,E17:E19)

What Does It Do ?
This function creates a total from a list of numbers.
It can be used either horizontally or vertically.
The numbers can be in single cells, ranges are from other functions.

Note
Many people use the =SUM() function incorrectly.

This example shows how the SUM has been combined with plus + symbols.
It should have been entered as either =C48+C49+C50 or =SUM(C48:C50).

100
200
300
600 =SUM(C48+C49+C50) Wrong!
=SUM(C48:C50) Correct
=C48+C49+C50 Correct
 Percentage %
Syntax
=Obtain marks/Total mark*100
OR
=Obtain marks/Total mark+%

GRADE SHEET
Marks
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Remarks
Obtained
Sana Khan 101 45 67 87 86 35 320 500 64
M. Ali 102 23 76 68 54 76 297 500 59.4
Kamran Saeed 103 34 67 78 75 82 336 500 67.2
Nadia Barlas 104 94 89 79 90 88 440 500 88
Nadeem Syed 105 23 56 74 33 67 253 500 50.6
Arif Mustafa 106 36 78 83 93 77 367 500 73.4
David 107 38 47 46 59 34 224 500 44.8
Furqan Haider 108 56 67 78 87 45 333 500 66.6
M. Ali 109 88 89 99 98 89 463 500 92.6
Majid Bilal 110 12 14 34 14 24 98 500 19.6
 Maximum Score
Syntax
=Max( Ranage:Range)

Minimum Score

Syntax
=Min( Ranage:Range)

GRADE SHEET
Marks Max Mix
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Remarks
Obtained Score Score
Sana Khan 101 45 67 87 86 35 320 500 64 87 35
M. Ali 102 23 76 68 54 76 297 500 59.4 76 23
Kamran Saeed 103 34 67 78 75 82 336 500 67.2 82 34
Nadia Barlas 104 94 89 79 90 88 440 500 88 94 79
Nadeem Syed 105 23 56 74 33 67 253 500 50.6 74 23
Arif Mustafa 106 36 78 83 93 77 367 500 73.4 93 36
David 107 38 47 46 59 34 224 500 44.8 59 34
Furqan Haider 108 56 67 78 87 45 333 500 66.6 87 45
M. Ali 109 88 89 99 98 89 463 500 92.6 99 88
Majid Bilal 110 12 14 34 14 24 98 500 19.6 34 12
 Average
Syntax
=Average(Ranage:Range)

GRADE SHEET
Marks Max Max
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Average Remarks
Obtained Score Score
Sana Khan 101 45 67 87 86 35 320 500 64 87 35 64
M. Ali 102 23 76 68 54 76 297 500 59.4 76 23 59
Kamran Saeed 103 34 67 78 75 82 336 500 67.2 82 34 67
Nadia Barlas 104 94 89 79 90 88 440 500 88 94 79 88
Nadeem Syed 105 23 56 74 33 67 253 500 50.6 74 23 51
Arif Mustafa 106 36 78 83 93 77 367 500 73.4 93 36 73
David 107 38 47 46 59 34 224 500 44.8 59 34 45
Furqan Haider 108 56 67 78 87 45 333 500 66.6 87 45 67
M. Ali 109 88 89 99 98 89 463 500 92.6 99 88 93
Majid Bilal 110 12 14 34 14 24 98 500 19.6 34 12 20
 Count
Syntax
=Count(Ranage:Range)

Count If Greater Then

Syntax
=COUNTIF(Ranage:Range,">criteria")

Count If Less Then

Syntax
=COUNTIF(Ranage:Range,"<criteria")

GRADE SHEET
Marks Max Max
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Average Remarks
Obtained Score Score
Sana Khan 101 45 67 87 86 35 320 500 64 87 35 71
M. Ali 102 23 76 68 54 76 297 500 59.4 76 23 55
Kamran Saeed 103 34 67 78 75 82 336 500 67.2 82 34 64
Nadia Barlas 104 94 89 79 90 88 440 500 88 94 79 88
Nadeem Syed 105 23 56 74 33 67 253 500 50.6 74 23 47
Arif Mustafa 106 36 78 83 93 77 367 500 73.4 93 36 73
David 107 38 47 46 59 34 224 500 44.8 59 34 48
Furqan Haider 108 56 67 78 45 246 500 49.2 78 45 67
M. Ali 109 88 89 99 98 70 444 500 88.8 99 70 94
Majid Bilal 110 12 14 34 14 21 95 500 19 34 12 19

Count 10 =COUNT(H16:H25)
87
Greater Than 6 =COUNTIF(H21:H30,">50") #N/A

LessThan 4 =COUNTIF(H21:H30,"<50")
 To link any cell with other cell

Syntax
=+Range

One Cell An other cell

Muhammad Ali Muhammad Ali =+C9
Naveed Naveed =+C10
4515 4515 =+C11

How show File Name & Location

Syntax
=cell("filename"

=cell("filename" 'file:///conversion/tmp/activity_task_scratch/530670659.xlsx'#$Additonal

Current Date & Time

Syntax
=Now()

Current Date & Time

7/5/2021 7:40 =Now()

Formatting
The result will normally be displayed in the dd/mm/yy format.
By using the Format,Cells,Number,Date command the format can be changed.

How to Insert Serial Number

Syntax
=Range+1 Then copy & Paste
Or
Select Two Range and Drag

1
2
3
4

Formatting
The result will normally be displayed in the dd/mm/yy format.
By using the Format,Cells,Number,Date command the format can be changed.
 Find Out Result Using "IF" Command
Syntax
=IF(Marks Obtained>=250,"Pass","Fail")

GRADE SHEET
Marks Max Max
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Average Result Remarks
Obtained Score Score
Sana Khan 101 45 67 87 86 35 320 500 64 87 35 71
M. Ali 102 23 76 68 54 76 297 500 59.4 76 23 55
Kamran Saeed 103 34 67 78 75 82 336 500 67.2 82 34 64
Nadia Barlas 104 94 89 79 90 88 440 500 88 94 79 88
Nadeem Syed 105 23 56 74 33 67 253 500 50.6 74 23 47
Arif Mustafa 106 36 78 83 93 77 367 500 73.4 93 36 73
David 107 38 47 46 59 34 224 500 44.8 59 34 48
Furqan Haider 108 56 67 78 87 45 333 500 66.6 87 45 72
M. Ali 109 88 89 99 98 89 463 500 92.6 99 88 94
Majid Bilal 110 12 14 34 14 24 98 500 19.6 34 12 19

Count =COUNT(H16:H25)

Greater Than =COUNTIF(H21:H30,">50")

LessThan =COUNTIF(H21:H30,"<50")
 Find Out Result Using "IF" Command
Syntax
=IF(Percentage>=80,"A+",IF(Percentage>=60,"B",IF(Percentage>50,"C","Fail")))

GRADE SHEET
Marks Max Max
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Average Result Grade Remarks
Obtained Score Score
Sana Khan 101 45 67 87 86 35 320 500 64 87 35 71 Pass
M. Ali 102 23 76 68 54 76 297 500 59 76 23 55 Pass
Kamran Saeed 103 34 67 78 75 82 336 500 67 82 34 64 Pass
Nadia Barlas 104 94 89 79 90 88 440 500 88 94 79 88 Pass
Nadeem Syed 105 23 56 74 33 67 253 500 51 74 23 47 Pass
Arif Mustafa 106 36 78 83 93 77 367 500 73 93 36 73 Pass
David 107 38 47 46 59 34 224 500 45 59 34 48 Fail
Furqan Haider 108 56 67 78 87 45 333 500 67 87 45 72 Pass
M. Ali 109 88 89 99 98 89 463 500 93 99 88 94 Pass
Majid Bilal 110 12 14 34 14 24 98 500 20 34 12 19 Fail

Count =COUNT(H16:H25)

Greater Than =COUNTIF(H21:H30,">50")

LessThan =COUNTIF(H21:H30,"<50")
 VLOOKUP FUNCTION Grade Book
Student Name ID# Test1 Test2 Test3 Test4 Test5 Total
Haider 6 67 56 89 68 80 500
Ali 3 56 57 78 46 47 500
Babar 4 78 89 88 90 87 500
Zahid 1 98 99 91 95 90 500
Faisal 7 34 23 22 12 67 500
Ameen 2 67 78 89 67 97 500
Rasool 5 24 34 34 23 34 500
Qadir 8 67 84 66 77 86 500
Yahya 9 23 46 57 87 90 500
Alia 10 45 67 45 78 88 500

Grading Criteria
0 FAIL
Table_Array is the
complete table of 40 D
information 50 C
60 B Col_Index is the
70 A Column# of Table_Array
80 A+

Solution With Instructions

STEP# 1
First you have to Calculate Per% by using this formula in Cell# J4
Marks Obtain Divided by Total Marks Multiply by100 =I4/H4*100
STEP# 2
After Calculating Per% we can Find out the Grades by using VLOOKUP Worksheet Function
Type this formula in Cell# K4 =VLOOKUP(J4,$E$16:$F$21,2)
J4 is lookup value
$E$16:$F$21 Range of table_array
2 is Col_Index# of Table_Array
 Lookup_Value

M. Obtain Per% Grade WHAT IS THIS

360 72 A
284 56.8 C Vlookup
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a
432 86.4 A+ column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are
located in a column to the left of the data you want to find.
473 94.6 A+
Syntax
158 31.6 FAIL
VLOOKUP(lookup_value,table_array,col_index_num)
398 79.6 A
Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a
149 29.8 FAIL reference, or a text string.

380 76 A Table_array is the table of information in which data is looked up. Use a reference to a range or a range
name, such as Database or List.
303 60.6 B
Col_index_num is the column number in table_array from which the matching value must be returned.
323 64.6 B

Col_Index is the
Column# of Table_Array

ns

Worksheet Function
 urns a value in the same row from a
KUP when your comparison values are

ex_num)
array. Lookup_value can be a value, a

. Use a reference to a range or a range

the matching value must be returned.

 LOOKUP
Reg# NAME F. NAME T. PHONE ADDRESS
1511119 SAEED ALI 3444455 KARACHI
1511120 RAHIL SHAH KHAN 8978766 LAHORE
1511121 ZAHID ALI NOOR ZAMAN 6767676 KARACHI
1511122 NOMAN BABAR QADIR BHAI 7878888 PINDI
1511123 ASIF KHAN RAFI ULLAH 6676777 HYDERABAD

Type any Rg. No 1511121 FORMULAS USED

NAME ZAHID ALI =LOOKUP(B10,$A$2:$F$7,$B$2:$B$7)
F. NAME NOOR ZAMAN =LOOKUP(B10,$A$2:$F$7,$C$2:$C$7)
T. PHONE 6767676 =LOOKUP(B10,$A$2:$F$7,$D$2:$D$7)
ADDRESS KARACHI =LOOKUP(B10,$A$2:$F$7,$E$2:$E$7)
MARRIED N =LOOKUP(B10,$A$2:$F$7,$F$2:$F$7)
 MARRIED WHAT IS THIS?
Y
Y Lookup
N Returns a value either from a one-row or
Y one-column range or from an array. The
LOOKUP function has two syntax forms:
N
vector and array. The vector form of
LOOKUP looks in a one-row or one-
column range (known as a vector) for a
value and returns a value from the same
USED position in a second one-row or one-
column range. The array form of
7,$B$2:$B$7) LOOKUP looks in the first row or column
7,$C$2:$C$7) of an array for the specified value and
7,$D$2:$D$7) returns a value from the same position in
the last row or column of the array.
7,$E$2:$E$7)
7,$F$2:$F$7)
Important Options for Formatting

Merge & Center

Warp Text
Conditional Formating
Row Height
Column Width
Format Manu
Sort Data
Page setup
Paste Special
Print Setup
 Basic Allowances Allocation By Using If
Syntax
=IF(Basic Salary>=5000,Basic*Over allowances Percentage,Basic*Below allowances Percentage)
=IF(H11>=5000,H11*F14,H11*E14)

PAY ROLL
Basic Salery Over Mr. Riaz Mr. Iqbal Mr.javad Total
Below Over
Basic Salary >>>> 5,000 5,000 6,000 11,000 4,500 21,500

Allowances on Basic
House of rent 70.00% 62.00% 3,720 6,820 3,150 13,690
Conveyance 15.00% 12.00% 720 1,320 675 2,715
Medical 7.50% 6.50% 390 715 338 1,443

Total Allowances 4,830 8,855 4,163 17,848

Gross Salery 10,830 19,855 8,663 39,348

Deduction

Provident Fund 10.00% 9.00% 540 990 1,530

Incom Tax 6.00% 11.00% 0
Lunch Charges 300 400 0

Total Deduction 540 990 0 1,530

Net Salery Payable 10,290 18,865 8,663 37,818

 5000

Average
Sample
7,167 4200 Below
3720 Above

0
Age Calculations
 Find Out Result Using "IF" Command

GRADE SHEET
Marks Max Max
Student Name Roll# Test1 Test2 Test3 Test4 Test5 TOTAL Per% Average Result Grade Remarks
Obtained Score Score
Sana Khan 101 45 67 87 86 35 320 500 64 87 35 71 Pass
M. Ali 102 23 76 68 54 76 297 500 59 76 23 55 Pass
Kamran Saeed 103 34 67 78 75 82 336 500 67 82 34 64 Pass
Nadia Barlas 104 94 89 79 90 88 440 500 88 94 79 88 Pass
Nadeem Syed 105 23 56 74 33 67 253 500 51 74 23 47 Pass
Arif Mustafa 106 36 78 83 93 77 367 500 73 93 36 73 Pass
David 107 38 47 46 59 34 224 500 45 59 34 48 Fail
Furqan Haider 108 56 67 78 87 45 333 500 67 87 45 72 Pass
M. Ali 109 88 89 99 98 89 463 500 93 99 88 94 Pass
Majid Bilal 110 12 14 34 14 24 98 500 20 34 12 19 Fail

120
Test3 Test4
Sana Khan 87 86 100
M. Ali 68 54
80
Kamran Saeed 78 75
Nadia Barlas 79 90 60
Nadeem Syed 74 33
40 Test3
Arif Mustafa 83 93
Test4
David 46 59
20
Furqan Haider 78 87
M. Ali 99 98 0
n li d las ed fa er li lal
Majid Bilal 34 14
ha .A ee ar Sy sta v id id .A Bi
K M Sa B u Da Ha M d
na ra
n
di
a em M an aji
Sa de if M
m Na Na Ar rq
Ka Fu
TEXT Functions
=Range&Range

Wasim Ahmed =C6&D6

=LEFT(Text,Number of Characters)

Wasim Ahmed Wasim

=LEFT(C9,2)

=RIGHT(Text,Number of Characters)

=MID(Text,Start Number,Number of Characters)

 Reg# NAME F. NAME

1511119 SAEED ALI

1511120 RAHIL SHAH KHAN
1511121 ZAHID ALI NOOR ZAMAN
1511122 Naeem Ahmed Waseem
1511123 Waqar Ali Ahmed Ali
1511124 Raja Imran Safeer Ahmed
1511125 Faraz Ahmed Shareef
1511126 NOMAN BABAR QADIR BHAI
1511127 ASIF KHAN RAFI ULLAH
 Visiting Day T. PHONE ADDRESSMARRIED
(empty)
Sunday 3444455 KARACH
Y
Monday 8978766 LAHORY
Friday 6767676 KARACH N
Sunday 5455544 LarkanaY
Tuesday 5524548 KARACH N
Wednesday 6233544 Sukkur Y
Sunday 1221555 LarkanaN
Friday 7878888 PINDI Y
Monday 6676777 HYDERN
Sorting & Filter

Reg# NAME

1511119 SAEED
1511120 RAHIL
1511121 ZAHID ALI
1511122 Naeem Ahmed
1511123 Waqar Ali
1511124 Raja Imran
1511125 Faraz Ahmed
1511126 NOMAN BABAR
1511127 ASIF KHAN
 F. NAME Visiting Day T. PHONE ADDRESS

ALI Sunday 3444455 KARACHI

SHAH KHAN Monday 8978766 LAHORE
NOOR ZAMAN Friday 6767676 KARACHI
Waseem Sunday 5455544 Larkana
Ahmed Ali Tuesday 5524548 KARACHI
Safeer Ahmed Wednesday 6233544 Sukkur
Shareef Sunday 1221555 Larkana
QADIR BHAI Friday 7878888 PINDI
RAFI ULLAH Monday 6676777 HYDERABAD
 MARRIED

Y
Y
N
Y
N
Y
N
Y
N
SZABIST Larkana Campus

GRADE SHEET

Student Name Reg# English Math Urdu Accounting Management Marks ObtainTOTAL Per% Max ScorMax ScoAverage Result Grade by Using If Grade by Using Vlookup Remarks
Waseem 1735181 36 65 45 54 75 275 500
Yasir 1735182 95 35 75 85 65 355 500
Waqar 1735183 63 42 54 25 45 229 500
Rafique 1735184 94 89 79 90 88 440 500
Shoaib 1735185 23 56 74 33 67 253 500
Jaffar 1735186 36 65 45 54 75 275 500
Naeem 1735187 38 47 46 59 34 224 500
Farhan 1735188 56 67 78 87 45 333 500
Shareef 1735189 36 65 45 54 75 275 500
Siraj 1735190 45 65 25 78 95 308 500