Excel notes

What is M.S excel ?

M.S Excel is a calculation based software. M.S Excel is a spreadsheet programme. It was
develop by "DAN BARKCLIN" in 1987.
Use of this software like:- Maintain data entry, Graphing tools, Pivot table, Calculation, etc.
There are limited columns and rows in ms excel
Columns :- 16384
Rows :- 1048576

How to open M.S Excel ?

WINDOW + R - RUN COMMAND - EXCEL – OK

Top left - Quick Access tool bar

Top middle - Save work space

Ribbon display option - ctrl+F1

Minimize - window + down key
Top right
Maximize - window + upper key
Close - window + M

Name box :- Show cell address.

Formula bar :- Show Formula & Text.

different between formula and function :-

Formula
1 It is start (=) symbol.
2 After (=) we give a cell address.
3 Example:- [=C6+D6+E6 enter]
 function
1 It is also start (=) symbols.
2 After (=) we give a particular name.
3 Example:- [=sum(C6,D6,E6) enter]

Date function
Insert today date [=Today()enter] 1/3/2023
Insert yesterday date [=Today()-1 enter] 1/2/2023
Insert tommorow date [=Today()+1 enter] 1/4/2023
Insert date & time [=now() enter] 1/3/2023 17:22
Insert date number [=N(select date) enter] 44928

Shortcut Key of Date & Time :-

Insert date [ctrl + ;] 6/20/2022
Insert Time [ctrl + shift + :] 12:29 PM

text function

Insert short date [=Text(select date,"dd") enter] 03

Insert short week name [=Text(select date,"ddd") enter] Tue
Insert full week name [=Text(select date,"dddd") enter] Tuesday

Insert short month [=Text(select date,"mm") enter ] 01

Insert short month name [=Text(select date,"mmm") enter] Jan
Insert full month name [=Text(selsect date,"mmmm") enter] January

Insert short year [=Text(select date,"yy") enter] 23

Insert full year [=Text(select date,"yyy") enter] 2023
Insert full year [=Text(select date,"yyyy") enter] 2023
 Date of Total
Date
[Link].
Current date
Name (CD)
birth
(DOB)
day Year Month Day Year
(TD) (Y) (M) (D) (Year's) of
1 Ram 1/3/2023 3/3/2003 7246 19 10 2 19year's
2 Mohan 1/3/2023 4/25/2001 7923 21 8 9 21year's
3 Sohan 1/3/2023 8/8/2000 8183 22 4 27 22year's
4 Kiran 1/3/2023 5/23/2008 5338 14 7 12 14year's
5 Suman 1/3/2023 1/19/2005 6558 17 11 14 17year's

birth

function
Current date [=today() enter]
Total day [=current date - date of birth enter]
Year [=year(total day)-1900 enter]
Month [=month(total day)-1 enter]
Day [=day(total day) enter]
year [=year(total day)-1900&"year's" enter]

Datedif function
Current Date of Total live
[Link]. Name date birth days Year Month Day
1 Ram 1/3/2023 8/28/2000 8163 22 4 6
2 Mohan 1/3/2023 12/2/1997 9163 25 1 1
3 Suman 1/3/2023 3/8/1995 10163 27 9 26
 4 Ajay 1/3/2023 6/11/1992 11163 30 6 23
5 Simran 1/3/2023 9/15/1989 12163 33 3 19

function
Current date [=today() enter]
Total live day [=datedif(date of birth,current date,"d")
enter]
Month [=datedif(date of birth,current date,"m") enter]
Year [=datedif(date of birth,current date,"y") enter]
Month [=datedif(date of birth,current date,"ym") enter]
Day [=datedif(date of birth,current date,"md") enter]

Gross salary
Basic GROSS NET Actual
E/code E/name Dept Region salary DA(5%) TA(6%) HRA(8%) SALARY PF(12%) PTAX(12%) SALARY BOUNS Incentive salary

E001 Ram sale east 22750 1138 1365 1820 27073 3249 3249 20575 1000 1500 23075

E002 Mohan pur west 21324 1066 1279 1706 25376 3045 3045 19285 1000 1500 21785

E003 Soni acct south 22908 1145 1374 1833 27261 3271 3271 20718 1000 1500 23218

E004 Kiran admin north 23177 1159 1391 1854 27581 3310 3310 20961 1000 1500 23461

E005 Ajay sale south 22962 1148 1378 1837 27325 3279 3279 20767 1000 1500 23267

E006 Manish sale south 20712 1036 1243 1657 24647 2958 2958 18732 1000 1500 21232

E007 Vijay pur east 23822 1191 1429 1906 28348 3402 3402 21545 1000 1500 24045

E008 Ajay acct west 21457 1073 1287 1717 25534 3064 3064 19406 1000 1500 21906

E009 Vivek admin east 20853 1043 1251 1668 24815 2978 2978 18859 1000 1500 21359

E010 Anish acct north 24295 1215 1458 1944 28911 3469 3469 21972 1000 1500 24472

formula
 For DA(5%) [=basic salary*5% enter]
For TA(6%) [=basic salary*6% enter]
For HRA(8%) [=basic salary*8% enter]
For Gross salary [=basic salary+DA+TA+HRA enter]
For PF(12%) [=salary*12% enter]
For PTAX(12%) [= salary*12% enter]
For net salary [= salary-PF-PTAX enter]
For Annual salary [=net salary+bouns+incentive
enter]

Attendance sheet
DATE 1/1 1/2 1/3 1/4 1/8 1/9 1/10 1/15 1/27 1/28 1/29 1/30
E/code E/name Dept Region Sat Sun Mon Tue Sat Sun Mon Sat Thu Fri Sat Sun
E001 Ram Sale East P Sun P P A Sun P P P A A Sun
E002 Anish Pur West A Sun A A A Sun A P P A A Sun
E003 Manish Sale West P Sun P P P Sun P P P P A Sun
E004 Shivam Sale West P Sun P P P Sun P P P P A Sun
E005 Karan Sale East P Sun P P P Sun P P P P P Sun
E006 Kartik Pur South L Sun L L L Sun L P P L P Sun
E007 Varun Admin South CL Sun CL CL L Sun CL L L L P Sun
E008 Tarun Admin East HL Sun HL HL CL Sun HL P P CL L Sun
E009 Lucky Pur North HL Sun HL HL HL Sun HL P P HL P Sun
E010 Ravi Acct North CL Sun CL CL HL Sun CL P P HL P Sun

1. Type 1/1 then enter then CTRL+SHIFT+F Click on "Date" then select "short date function"
ok.
2. For week name [=text(date,"ddd")enter]
3. Select all data then all border and fill attendence sheet (like:- Present,Absent,Leave,Half
leave,Causal leave).
4. How to attendence fill? Click on "Conditional formatting" then click on "Text that contain"
then fill "P" then select color then ok.
5. Do again some rules for "A","L","CL","hl","sun".

marksheet

Father's Total
[Link]. Name name Address Maths Science English Hindi S.S.T S.K.T No. Min Max Large Small Average Per(%)

1 Karan Mohan Noida 96 90 89 92 95 87 549 87 96 95 89 91.5 91.5

2 Nikhil Ajay Delhi 79 88 55 56 47 86 411 47 88 86 55 68.5 68.5

3 sana Vijay Delhi 86 81 60 89 90 86 492 60 90 89 81 82 82

4 pooja shyam Noida 87 37 38 68 64 48 342 37 87 68 38 57 57

5 payal Manish Noida 60 77 73 68 75 78 431 60 78 77 68 71.83333 71.83333

6 Sohil Ravi Delhi 22 30 29 32 27 28 168 22 32 30 27 28 28

7 vansh lala Delhi 45 20 38 20 40 57 220 20 57 45 20 36.66667 36.66667

function
Total No. [=Sum(select all subject) enter]
Min [=Min(select all subject) enter]
Max [=Max(select all subject) enter]
Large [=Large(select all subject,2) enter]
Small [=Small(select all subject,2) enter]
Average [=Average(select all subject) enter]
Per(%) [=Total no./Total subject) enter]

If condition

introduction

If (Single criteria) - If condition is ture then print ture value otherwise print false value.
IF And (Multiple criteria) - If all condition are ture then print ture value otherwise false value.
IF Or (Multiple criteria ) - If multiple condition any one condition ture then print ture value.
IF Not (Opposite of if conditions ) - If condition ture then print false value otherwise ture value.
 example
Salary Salary (IF Salary (IF Salary (IF
E/code E/name Dept Region (IF) And) OR) NOT)

E001 Ram Sale East 5000 5000 5000 bye

E002 Manish sale East 5000 3000 5000 hello

E003 Kartik Sale South 5000 3000 5000 hello

E004 Sumit Acct East 5000 3000 5000 hello

E005 Anish Admin North 3000 3000 3000 hello

function
For "IF"condition [=if(Name="ram",salary,if(Dept="sale",salary,if(Region="east",salary,other employee
salary)))enter]
For "IF And" condition [=if(and(Name="ram",Dept="Sale",Region="east"),salary,other employee
salary)enter]
For "IF OR" condition [=if(or(Name="ram",Dept="sale",Region="east"),salary,other employee
salary)enter]
For "IF Not" condition" [=if(not(Name="ram"),"Hello","bye")enter]

Advance marksheet
Tot
S.N Father' Addre Mat Scien Engli Hin S.S. S.K. al Mi Ma Lar Sma Averag Resu Gra Positi Master
o. Name s name ss hs ce sh di T T No. n x ge ll e Per(%) lt de on Result
Mr.
Moha
1 Karan n lal Noida 96 90 89 92 95 87 549 87 96 95 89 91.5 91.5 pass A 1st pass
 Mr.
Ajay
2 Nikhil Verma Delhi 79 88 55 56 47 86 411 47 88 86 55 68.5 68.5 pass D 4th pass
Mr.
Shiva Vijay
3 m Singh Delhi 86 81 60 89 90 86 492 60 90 89 81 82 82 pass B 2nd pass
Mr.
Soona Chand
4 m an Noida 87 37 38 68 64 48 342 37 87 68 38 57 57 pass pass pass pass
Mr.
Manis 71.833 71.833
5 Kartik h Noida 60 77 73 68 75 78 431 60 78 77 68 33 33 pass C 3rd pass

Mr.
6 Sohil Ravi Delhi 22 30 29 32 27 28 168 22 32 30 27 28 28 fail fail fail fail
Mr.
Sande 36.666 36.666 compartm
7 Vikash ep Delhi 45 20 38 20 40 57 220 20 57 45 20 67 67 pass pass pass ent

function
Total No. [=Sum(select all subject) enter]
Min [=Min(select all subject) enter]
Max [=Max(select all subject) enter]
Large [=Large(select all subject,2) enter]
Small [=Small(select all subject,2) enter]
Average [=Average(select all subject) enter]
Per(%) [=Total no./Total subject) enter]
Result [=if(per>33,"pass","fail") enter]
Grade [=if(per>90,"A",if(per>80,"B",if(per>70,"C",if(per>60,"D",if(per>33,"pass","fail"))))) enter]
Position [=if(per>90,"1st",if(per>80,"2nd",if(per>70,"3rd",if(per>60,"4th",if(per>33,"pass","fail"))))) enter]
Master Result [=if(countif(select all subject,">33")=6,"pass",if(countif(select all
subject,"<33")>=3,"fail","compartment")) enter]

Auto attendance sheet

Create dropdown jan

First
date 1/1

Last date 1/31

 1 1
/ 1/ 1/ 1/ 1/ 1/ / 1/ 1/
1 2 3 4 5 6 7 30 31
Tot Tot
al al
Tota Tot Tot Hal Cau
S T T l al al f sal
E/c E/na Regi De S u M u W h Fr Su M Pres Lea Abs Lea Lea
ode me on pt at n on e ed u i n on ent ve ent ve ve
E00 Eas
1 Ram Sale t P L P L P L L 13 13 0 0 0
E00 We
2 Anish Pur st P A P A P A A 9 0 13 0 0
E00 Manis
We
3 h Sale st A A A A A A A 3 0 23 0 0
E00 Shiva
We
4 m Sale st P P P P P P P 18 0 0 0 0
E00 Eas
5 Karan Sale t L P L P L P P 9 10 3 0 0
E00 So C
6 Kartik Pur uth L CL L CL L L CL 3 10 0 0 13
E00 Ad So C C C
7 Varun min uth L CL L CL CL L CL 3 0 0 0 23
E00 Ad Eas H
8 Tarun min t P HL P HL P L HL 9 0 0 12 0
E00 No
9 Lucky Pur rth P P P P P P P 18 0 0 0 0
E01 Acc No H H H
0 Ravi t rth L P L P L P P 12 0 0 10 0

function
For First date [=datevalue(dropdown&1)enter Then CTRL+SHIFT+F click on date option then select 3/14 ok
For Last date [=Eomonth(first date,0)enter Then CTRL+SHIFT+F click on date option then select 3/14 ok
First date in attendence sheet [=select first date then enter
Seond date in attendence sheet [=select 1/1<last date fix 1time,select 1/1+1,"")enter Then CTRL+SHIFT+F click
on date option then select 3/14 ok
For week name [=text(select 1/1,"ddd")enter]
For total present [=countif(select first attendance range,"P") enter]
For total Absent [=countif(select first attendance range,"A") enter]

For total Leave [=countif(select first attendance range,"L") enter]

For total Half leave [=countif(select first attendance range,"hl") enter]
For total Causal Leave [=countif(select first attendance range,"cl") enter]

steps
1. Create a dropdown of (jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec)
2. Insert first date and last date
3. First date in attendence sheet [=select first date then enter
4. Seond date in attendence sheet [=select 1/1<last date fix 1time,select 1/1+1,"")enter Then CTRL+SHIFT+F
click
on date option then select "short date format" ok
5. For week name [=text(select 1/1,"ddd")enter]
6. Select all data then all border and fill attendence sheet (like:- Present,Absent,Leave,Half leave,Causal
leave).
7. How to attendence fill? Click on "Conditional formatting" then click on "Text that contain" then fill "P" then
select color then ok.
8. Do again some rules for "A","L","CL","hl".
9. How to Sunday highlights? Click on "Conditional fomatting" then "New Rules" then select "Use a formula to
determine which cells to format".
then click on below of "Format values where this fomula is true:"K$34="sun" then click on format then select
any one color then ok then ok.
10. How to find out ("P","A","L","CL","HL")? [=countif(select first attendance range,"P") enter]
11. Do again same function use for ("A","CL","HL","L").

Count series
For count only number [=count(range) enter]
For count number and text [=countA(range) enter]
For Count only text [=countA(range)-count(range) enter]
For find only blank cell [=countblanck(range) enter]
Find the any particular certeria [=countif(range,"text") enter]
To find the character in the selected range [=countif(range,"??") enter]
To find charcter & text start, end, and middle [=countif(range,"*word*") enter]
To find character & text start [=countif(range,"word*") enter]
To find character & text end [=countif(range,"*word") enter]
For greater than [=countif(range,">50") enter]
For greater than and equal [=countif(range,">=50") enter]
For less than [=countif(range,">50") enter]
For less than and equal [=countif(range,"<=50") enter]
For every number except 50 [=countif(range,"<>50") enter]
To find the multiple certeria [=countifs(certeria range 1,certeria 1,certeria range2,certeria 2, certeria range
3,centeria 3)enter]

Sum series
For total number [=sum(range)enter]

For multiply number [=sumproduct(array1, array2)enter]

For greater than [=sumif(range,">50",range)enter]

For less than [=sumif(range,"<50",range)enter]

For greater than equal [=sumif(range,">=50",range)enter]

For less than equal [=sumif(range,"<=50",range)enter]

For equal [=sumif(range,"=50",range)enter]

For every number expect 50 [=sumif(range,"<>50",range)enter]

To find the multiple certeria [=sumifs(range,certeria 1,certeeria range 1,certeria 2,creteria range2,certeria
3,certeria range3)enter]

Manipulating text

formula

Example:- Rahul sharma

For connecting two different word. [=First name&Last name enter]

For connecting two different word with space. [=First name&"space"&Last name enter]
For connecting two different word with symbols. [First name&"symbols"&Last name enter]

function
 Example:- Rahul Sharma

For connecting two different word. [=concatnate(first name,last name) enter]

For connecting two different word with space. [=concatnate(first name,"space",last name)
enter]

For connnecting two different word with symboles. [ =concatnate(first

name,"symbols",last name) enter]

find first name

Example:- Rahul sharma

For find first name [=left(full name,numering) enter]

For find first name [=left(full name,find("space",full name,numering)) enter]
For find first name [=left(full name,len(full name)-find("space",full name,numering)) enter]

find last name

Example:- Rahul Sharma

 For find last name [=Right(full name,numering) enter]
For find last name [=Right(full name,find("space",full name,numering) enter]
For find last name [=Right(full name,len(full name)-find("space",full name,numering)) enter]

find first name,middle name,last name

Example:- Manish kumar sharma

For find first name [=Trm(left(substitute(full name,"space",Rept("space",10)),10)) enter]

For find middle name [=Mid(full name,find("space",full name)+1,find("space",full
name,find("space",full name)+1)-find("space",full name)-1) enter]

For find last name [=Trim(right(substitute(full name,"space",Rept("space",10)),10)) enter]

cases

Example:- "The quick brown fox jumps over the lazy dogs."
For Upper case [=Upper() enter]
For lower case [=lower() enter]
For capitlize each word [=proper() enter]
For second Capitlize each word [=upper(left(full name,1))&lower(right(full nmae,len(full name)-1)) enter]
For Toggle case [=lower(left(text)&uppper(right(text,len(text)-1)) enter] - first word small letter and another
word is capital letter.

find first
name

Example :- Vijay Sharma

 For first name [=left(full name,numering) enter]
For first name [=left(full name,find("space",full name,numering)) enter]
For first name [=left(full name,len(full name)-find("space",full name,numering)) enter]

Last name
For last name [=right(full name,numering) enter]
For last name [=right(full name,find("space",full name,numering)) enter]
For last name [=right(full name,len(full nmae)-find("space",full nmae,numering)) enter]

find first name / middle name / last name

Example :- Vijay Kumar Sharma

For first name [=Trim(left(substitute(full name,"space",rept("space",10)),10)) enter]

For middle name [=mid(full name,find("space",full name)+1,find("space",full name,find("space",full
name)+1)-find("space",full name)-1) enter]
For last name [=Trim(right(substitute(full name,"space",rept("space",10)),10)) enter]

To find a text in between the word [=find("character",text,numering) enter]

To find a text in between the word [=search("character",text,numering) enter]
To rept the same text [=rept(text,numering) enter]
To find the length of the sentence or word [=len(text) enter]
To replace the unnessasary space in between the text [=trim(text) enter]

substitute function

To substitute the text or replace the text. [=substitute(text,"old text","new text") enter]
 additionall use of substitute function

Example :- The quick brown fox jumps over the lazy doogs.

For replace word [=substitute(text,"Fox","wolf") enter]

For revome spacing [=substitute(text,"space","") enter]
For remove space particular area [=substitute(text,"space","",5) enter]
For add symbols in pergraph [=substitute(text,"space","***") enter]
Example

56kg
90kg
80kg
35kg
25kg
10kg

For this wait sum [=sum(substitute(select range,"kg","")+0)&"kg" then ctrl+shift+enter

Financial function
PV Loan of amount 200000

Rate Rate of interest 12%

Nper Duration of loan 2 years

PMT With interest

PPMT Without interest

IPMT Only interest

Weekly / 365 Weekly divided by 365 EMI.

monthly / 12 Monthly divided by 12 EMI.

Yearly / 1 Yearly divided by 1 EMI.

Half Yearly /
2 Half Yearly divided by 2 EMI.

Quaertly / 4 Quaertly divided by 4 EMI.

PMT - calcute the payment for a loan based on constant payment and constant rate of interest .

PMT Function [ =pmt(rate/12,nper*12,-pv) enter]

PPMT - Use for installment without rate of interest .

PPMT Function [ =ppmt(rate/12,1,nper*12,-pv) enter]

IPMT - Use for find out total interest paid in a month without installment money

IPMT function [ =ipmt(rate/12,1,nper*12,-pv) enter]

PV - To find out (present value) amount of loan

PV Function [=pv(rate/12,nper*12,pmt) enter]

Rate - To find out rate of interest.

Rate function [=rate(nper*12,pmt,-pv)*12 enter]

Nper - To find out how many installment will be payable.

Nper function [=nper(rate/12,pmt,-pv) enter]

FV - To find out future value.

FV Function [ =fv(rate/12,nper*12,pmt) enter

Simple interest - To find out simple rate of interest

simple interest function [ =pv*rate*nper enter]

Compound interest - To find out compound rate of interest.

Compound interest function [=pv*power(1+rate,nper)-pv enter]

Total payable amount - To find out total payable

amount

Total payable amount function [=nper*pv enter]

monthly
Customer
name Ram kumar

Date of loan 1/3/2023

Amount of
loan 200000

rate of interest 12%

duration of
loan 2 years
Per month
EMI 9,415

[Link] Opening balance Rate of interest Installment Closing balance

1 200,000 2,000 9,415 192,585

2 192,585 1,926 9,415 185,096

3 185,096 1,851 9,415 177,533

4 177,533 1,775 9,415 169,893

5 169,893 1,699 9,415 162,178

6 162,178 1,622 9,415 154,385

7 154,385 1,544 9,415 146,514

8 146,514 1,465 9,415 138,564

9 138,564 1,386 9,415 130,535

10 130,535 1,305 9,415 122,426

11 122,426 1,224 9,415 114,235

12 114,235 1,142 9,415 105,963

13 105,963 1,060 9,415 97,608

14 97,608 976 9,415 89,169

15 89,169 892 9,415 80,646

16 80,646 806 9,415 72,038

17 72,038 720 9,415 63,344

18 63,344 633 9,415 54,563

19 54,563 546 9,415 45,694

20 45,694 457 9,415 36,736

21 36,736 367 9,415 27,688

22 27,688 277 9,415 18,551

23 18,551 186 9,415 9,321

24 9,321 93 9,415 0

For EMI [=pmt(rate/12,nper*12,-pv)enter]

For Opening balance [=Amount of loan then enter]
For Rate of interest [=opening bal*rate fix 1time/12 then enter]
For Installment [=EMI fix 1time then enter]
For Closing Balance [=Opening bal.+Rate of int.+Installment+Closing bal. then enter]

Hide show
E/code pro1 pro2 pro3 pro4 pro5
E001 139 150 136 132 133
E002 136 124 127 116 106
E003 111 146 143 149 103
E004 120 131 143 127 125
E005 108 125 139 103 130
E006 147 104 101 131 109
E007 138 118 104 107 144
E008 131 130 120 122 115
E009 142 148 127 103 148

Hide Table show

Hide
colunms show

Hide rows show

Hide cell show

rules

For hide table [No remove dollar symbols]

For hide colunms [Only remove first dollar symbol]
For hide rows [Only remove second dollar symbol]
For hide cell [Remove all dollar symbols]

steps :-
1. Create a dropdown of "Show/Hide".

2. Select dropdown cell.

3. How to Hide? Click on "Conditional formatting" then click on "New rules" then select "Use a formula to
determine which cells to format"

click on below of "Format values where this fomula is true" :- =$N$21 then click on "format" then click on
"custom" then press tree time ;;;

then ok then ok. (Note:- This format only for Hide table).

[Link] again same format "Hide row","Hide columns","Hide cell" (Note :- only change Dollar symbols like:- follow
above rules).

Logical function

two types of logical function

index match
Find name by position. Find position by name

Gross
E/code E/name Region Dept Basic tA DA HRA Salary
E001 Ram West Sale 21583 1079.15 1294.98 1726.64 25683.77
E002 Karan East Pur 23070 1153.5 1384.2 1845.6 27453.3
E003 Varun South Acct 23213 1160.65 1392.78 1857.04 27623.47
E004 Ajay East Sale 23165 1158.25 1389.9 1853.2 27566.35
E005 Manish West Sale 21197 1059.85 1271.82 1695.76 25224.43
E006 Anish North Admin 21292 1064.6 1277.52 1703.36 25337.48
 index
function

INDEX VARUN Function:- [=index(range,position)enter]

Function:- [=index(range,blank cell)

index with blank cell 6 Anish Note:-(Type position in blank cell)

Function:- [=index(range,dropdown)enter]
index with dropdown 3 Varun Note:- (Create dropdown of n number)

match
function

Function:- [=match("name",range,0)enter]
Match 1

Function:- [=match(select blank

Match with blank cell Ajay 4 cell,range,0)enter]
Note:-(Type name in blank cell)

dropdown Function:- [=match(select

Match with dropdown,range,0)enter]
dropdown Varun 3 Note:-(Create dropdown of e/name)

index with
match

E003 Varun

Function:- [=index(e/name,match(dropdown,e/code,0))enter]
Note:-(crate a dropdown of E/code range)

index with double match

 Gross
E/code E/name Region Dept Basic tA DA HRA Salary
E001 Ram West Sale 21583 1079.15 1294.98 1726.64 25683.77
E002 Karan East Pur 23070 1153.5 1384.2 1845.6 27453.3
E003 Varun South Acct 23213 1160.65 1392.78 1857.04 27623.47
E004 Ajay East Sale 23165 1158.25 1389.9 1853.2 27566.35
E005 Manish West Sale 21197 1059.85 1271.82 1695.76 25224.43
E006 Anish North Admin 21292 1064.6 1277.52 1703.36 25337.48

Gross
E/code E/name Region Dept Basic tA DA HRA Salary
E001 Ram West Sale 21583 1079.15 1294.98 1726.64 25683.77
E003 Varun South Acct 23213 1160.65 1392.78 1857.04 27623.47
E005 Manish West Sale 21197 1059.85 1271.82 1695.76 25224.43
E006 Anish North Admin 21292 1064.6 1277.52 1703.36 25337.48

Function:- [=index(database fix 1time,match(lookup value fix 3time,first vertical range fix
1time,0),match(lookup value fix 2time,first horizontal range,0))enter]

Note:- Copy E/code,E/name,Regioon,Dept,DA,HRA,TA,Gross salary Then Copy E/code like:-

(E002,E005,E006)etc.

index with dropdown

Gross
E/code E/name Region Dept Basic tA DA HRA Salary
E004 Ajay East Sale 23165 1158.25 1389.9 1853.2 27566.35

Function:- [=index(database fix 1time,match(lookup value fix 3time,first veritcal range fix
1time,0),match(lookup value fix 2time,first horizontal range fix 1time,0))enter]

Note:- Copy E/code,E/name,Region,Dept,DA,HRA,TA,Gross salary then Create a dropdown of

e/code range

index with multi certeria

 Item Size Color Price Item Shirt
Jeans Medium Blue Rs. 987 Size Large
Shirt Large White Rs. 750 Color White
Skrit Small Pink Rs. 673 Price 750
Top Small Yellow Rs. 729
Crop
top Large Red Rs. 838

Note:- Copy Item,Size,Clor,Price then Click on paste option then transpose paste and Copy
Shirt,Large,White then click on paste option then traspose paste

Function:- [=index(price range fix 1time,match(1(Lookup value1=Itme range)*(Lookup

value2=Size range)*(Lookup value3=Color range),0))CTRL+SHIFT+ENTER

lookups

introduction of vlookup

Lookups function:- The LOOKUP function in Excel can Perfrom the simplest types of Vertical Horizontal lookups.
There are two forms of LOOKUP in Excel: Vector and Array.

Vector Form :- The Vector form looks for a value in a specified column or row.
Syntax:- [ =Lookup(lookup_value,lookup_vector,result_vector) enter]

use of lookup function

Same use of lookup function are :
1. You can find the exact or appropariate match by using the lookup function.
2. User can search for data both vertically (columns) and horizontally (rows).
3. It is simpler to use and does not require selecting the entire table.
types of lookups
Simple lookup
V-lookup :- vertical lookup - (Find data form vartical database).
H-lookup :- Horizontal lookup - (Find data form horizontal database).
Offset function

vlookup

The VLOOKUP function in excel is a powerful function used to lookup data in a table organized vedrtically. It looks
down the left column of a range to find a value.
Syntax :- [ =vlookup(lookup_value,table_array,col_index_number,[range_lookupvalue]) enter]
Lookup_value :- Whose according data is to be find
Table_array :- Database
Col_index_number :- column from which to take data.
Ture/False :- 0/1 :- (0 for false value & exact match) and (1 for ture value & Approx match).

parts of
vlookups
1. Simple Vlookup
2. vlookup with curly bracers
3. Vlookup with match
4. Vlookup with dropdown
5. Vlookup with choose
6. Choose with Vlookup
7. Vlookup with sum
8. Vlookup with multisheet
9. Vlookup with indirect function
10. Double Vlookup format
11. Double Vlookup with double match
12. Vlookup with columns

hlookup
The HLOOKUP function look up for a value in the first row of a given range and gives a value in the same
column from a row that you specified .
Syntax :- [ =Hlookup(lookup_value,table_array,row_index_number,[range_lookup]) enter]
Lookup_value :- Whose according data is to be find.
Table_array :- database
Row_index_number :- Row from which to take data
Ture/Fulse :- 0/1 :- (0 for fulse value & exact match) and (1 for ture value & approx match).

parts of
hlookups
1. Simple Hlookup
2. Hlookup with match
3. Hloookup with row

offset
Returns a reference to a range that is a specified number of rows and columns
from a cell or range of cells.
The reference that is returned can be a single cell or a range of cells. You can specify the number of
rows and

the number of columns to be returned.

Syntax:- [=offset(refrence,row,col)]

parts of
offset
1. Simple Offset Function
2. Offset With Double Match

Vlookup
Gross
E/code E/name Region Dept Basic DA TA HRA salary
E001 Ram East Sale 20204 1010.2 1212.24 1616.32 24042.76
E002 Manish West Pur 24603 1230.15 1476.18 1968.24 29277.57
E003 Mohan East Sale 21814 1090.7 1308.84 1745.12 25958.66
E004 Sumit East Pur 21142 1057.1 1268.52 1691.36 25158.98
E005 Karan West Pur 20391 1019.55 1223.46 1631.28 24265.29
E006 Vikas South Sale 21681 1084.05 1300.86 1734.48 25800.39
E007 Arjun South Acct 20213 1010.65 1212.78 1617.04 24053.47
E008 Sanjay North Admin 24357 1217.85 1461.42 1948.56 28984.83
E009 Anish North Acct 23459 1172.95 1407.54 1876.72 27916.21
E010 Sandeep South Admin 21898 1094.9 1313.88 1751.84 26058.62

simple vlookup
Gross
E/code E/name Region Dept Basic DA TA HRA salary
E001 Ram
E003 Mohan
E006 Vikas
E008 Sanjay
Copy E/code,E/name,Region,Dept,DA,HRA,Gross salary then copy E/code
like(E001,E004,E006,E008)etc.
Function:- [=vlookup(lookup value fix 1time,table array fix 1time,2,0)enter]

vlookup with curly bracers

Copy E/code,E/name,Region,Dept,DA,TA,HRA,Gross they copy E/code like:(E002,E004,E007)etc.

Function:- [=vlookup(lookup value fix 3time,table array fix 1time,{2,3,4,5,6,7,8,9},0)CTRL+SHIFT+ENTER]

vlookup with match

Copy E/code,E/name,Region,Dept,DA,TA,HRA,Gross then Copy E/code like:(E001,E004,E007)etc.

Function:- [=vlookup(lookup value fix 3time,table array fix 1time,match(lookup vale fix 2time,first horizontal
range fix 1 time,0),0)enter]

vlookup with dropdown

create a dropdown of E/code

Copy E/code,E/name,Region,Dept,Basic,TA,DA,HRA,Gross salary

Function:- [=vlookup(lookup value fix 3time,table array fix 1time,match(lookup value fix
2time,first horizontal range fix 1time,0),0)enter]

vlookup with column

Copy E/code,E/name,Region,Dept,Basic,TA,DA,HRA,Gross salary then E/code like:(E001,E003,E005)etc.
Function:- [=vlookup(lookup value fix 3time,table array fix 1time,columns(select any one black cell the press
shift+: key fix 1time)+1,0)enter]

vlookup with indirect function

india China
Brand name of
Brand name of
product QTY Rate
product QTY Rate
Microsoft Excel 69 40072
Microsoft Excel 41 72882
Microsoft
Microsoft
Powerpoint 50 44518
Powerpoint 58 73027
Microsoft Access 69 51967
Microsoft Access 57 71268
Microsoft Outlook 60 41670
Microsoft Outlook 55 67872
Microsoft word 73 46084
Microsoft word 44 63294
Winword 7 79 67771
Winword 7 40 60320

usa
Brand name of product QTY Rate
Microsoft Excel 47 162684
Microsoft Powerpoint 33 156214
Microsoft Access 40 188630
Microsoft Outlook 34 186323
Microsoft word 41 164719
Winword 7 45 163645

Blank cell
Brand name of product india QTY Rate
Microsoft Access 69 51967

Note:- select india data &click on name box type INDIA then enter
select china data &click on name box type CHINA then enter
select USA data &click on name box type USA then enter

copy Brand name, QTY, Rate

function:- [=vlookup(lookup value,indirect(select black cell),2,0)

double vlookup format

Date of
E/code Name Dept City Joining
E001 Ram Manager Delhi 1/3/2023
E002 Karan Peon Delhi 1/3/2023
E003 Nikhil Manager Delhi 1/3/2023
E004 Ajay Peon Delhi 1/3/2023
E005 Vikash Manager Delhi 1/3/2023
E006
E/code Anish Manager Delhi
Salary 1/3/2023
Name
E001 Phone Salary Region
7000
Ram
E007 6789054321 7000match
it's not North
E005
Karan 7648376482 12000
10000 South
E008
Nikhil 4638428278 it's not match
12000 North
Ajay 9719093947 7000 South
Vikash 7723272882 12000 East
Anish 2637268816 10000 North

Copy E/code and Salary then copy any e/code like:(E001,E003,E006)etc.

Function:- =iferror(vlookup(vlookup(lookup value fix 3time,first table array fix 1time,2,0),second table array
fix 1time,
match(lookup value fix 2time,select first horizontal range of second table array fix 1time,0),0),"it's not
match")enter]
 double vlookup with double match

Product Bill Product

Bill No. Name Unit Price No. Name Unit Price
B020 Computer 5pcs. 73693 B087 Light 12pcs. 37103
B021 CPU 7pcs. 80506 B088 Light 10pcs. 42507
B022 Computer 15pcs. 96423 B089 Fan 5pcs. 49775
B023 Keyboard 8pcs. 96648 B090 Light 50pcs. 18491
B024 Mouse 15pcs. 85240 B091 A.C 75pcs. 22407
B025 Mouse 30pcs. 54648 B092 Light 45pcs. 21263
B026 CPU 10pcs. 52957 B093 Fan 30pcs. 20404
B027 Keyboard 40pcs. 63701 B094 A.C 4pcs. 32506
B028 Printer 10pcs. 61043 B095 Fan 55pcs. 19144
B029 Computer 29pcs. 59605 B096 A.C 2pcs. 22467

Product
Bill No. Name Unit Price
B021 CPU 7pcs. 80506
B024 Mouse 15pcs. 85240
B090 Light 50pcs. 18491
B092 Light 45pcs. 21263

Note:- Copy Bill no,Product name,Unit,Price paste Then Copy Bill no. like:-
(B021,B024,B090,B092)etc.

Function:- [=iferror(vlookup(lookup value fix 3time,first table array fix 1time,match(lookup

value fix 2time,select first horizontal range in first table fix1time,0),0),

Vlookup(lookup value fix 3time,second table array fix 1time,match(lookup value fix 2time,select first
horizontal range in second table,0),0)
 choose with vlookup & vlookup with sum

Name Sale Sum 1

Ranjeet 52 Max 2
Ravi 56 Min 3
Karan 54 Average 4
Varun 74 Count 5
Shivam 92
Manish 68
Anish 88
Sumit 86
Amit 83
Lalit 94

Note:- Create a dropdown of (sum,max,min,average,count)

Function:- [=choose(vlookup(lookup value,table
array,2,0),sum(range),max(range),min(range),average(range),count(range))enter]

vlookup with choose

Years Grade Role Saving
2002 B Manager 11938
2005 A Peon 17953
2006 A Manager 13277
2009 B Annylist 14620
 2011 C Manager 15921 Years 2009
2016 B Annylist 17265 Grade B
2019 C Peon 19563 Role Annylist
2022 A Annylist 11506
Saving 14620

Note:- Copy (years,Grade,Role,Saving) then click on paste option then transpose paste do again copy
(2009,B,Annylist) then transpose paste
Function:- [=vlookup(lookup value1&lookup value2&lookup value3,choose({1,2},Select years
range&Select grade range&Select role range,Select saving range),
2,0)CTRL+SHIFT+ENTER

Hlookup
E/code E001 E002 E003 E004 E005 E006 E007 E008 E009 E010
Sandee
E/name Ram Manish Mohan Sumit Karan Vikas Arjun Sanjay Anish p
Region East West East East West South South North North South
Dept Sale Pur Sale Pur Pur Sale Acct Admin Acct Admin
Basic 20204 24603 21814 21142 20391 21681 20213 24357 23459 21898
1230.1 1019.5 1084.0 1010.6 1217.8 1172.9
DA 1010.2 5 1090.7 1057.1 5 5 5 5 5 1094.9
1212.2 1476.1 1308.8 1268.5 1223.4 1300.8 1212.7 1461.4 1407.5 1313.8
TA 4 8 4 2 6 6 8 2 4 8
1616.3 1968.2 1745.1 1691.3 1631.2 1734.4 1617.0 1948.5 1876.7 1751.8
HRA 2 4 2 6 8 8 4 6 2 4
Gross 24042. 29277. 25958. 25158. 24265. 25800. 24053. 28984. 27916. 26058.
salary 76 57 66 98 29 39 47 83 21 62

SIMPLE HLOOKUP
E/code E001 E003 E006
E/name Ram Mohan Vikas
Region
Dept
Basic
DA
TA
HRA
Gross
salary

Note :- Copy (E/code,E/name,Region,Dept,etc) paste then copy e/code like:-

(E001,E003,E007)etc.
Function :- [=Hlookup(lookup value fix 2time,table array fix 1time,2,0)enter]

hlookup with match

Note :- Copy (E/code,E/name,Region,Dept,etc) paste then copy e/code like:-

(E001,E003,E007)etc.
Function :- [=Hlookup(lookup value fix 2time,table array fix 1time,match(lookup value fix 3time,
Select first vertical range,0),0)enter]

hlookup with rows

Note :- Copy (E/code,E/name,Region,Dept,etc) paste then copy e/code like:-(E001,E003,E007)etc.

Function :- [=Hlookup(lookup value fix 2time,table array fix 1time,rows(select any one cell then press
Shift+:
key fix 1time)+1,0)enter]

Offset
DAY JANUARY FEBRUARY MARCH APRIL MAY
MONDAY 70 40 19 50 37
TUESDAY 23 76 82 46 47
WEDNESDAY 96 78 85 91 66
THUSRDAY 50 11 94 58 71
FRIDAY 79 14 67 35 42
SATURDAY 56 62 38 84 98
SUNDAY 78 66 40 95 99

DAY JANUARY
TUESDAY 23
SATURDAY 56

Note:- Copy Day or January then copy Monday, Saturday etc.

Function:- [=offset(reference,row,col)enter]

OFFSET WITH DOUBLE MATCH

ITEM 2016 2017 2018 2019 2020

LCD 28516 16330 43799 19729 24279
MONITOR 33613 26542 29421 31634 31110
CPU 13595 27779 15518 10331 32644
A/C 12811 10257 15442 40779 11733
REFRIDGERATOR 42906 17877 23500 13342 43275
FAN 22245 17888 25056 16796 24876
AIR COOLER 13341 12603 21582 47170 48081
TELEVISION 26132 38748 39631 20810 10455

ITEM 2016 2017

LCD 28516 16330

Note:- Copy item or 2018etc.

Function:- [=offset(reference,match(lookup value,lookup array,0)-1,match(lookup value,lookup array,0)-
1) enter]

Menu bar
Home tab
The Home menu is the second menu in the Excel menu bar. The home ribbon item
include options for fomatting font, color, conditional formatting,
filter,number type,etc. It is also used to apply formats to cell in a worksheet.

cut
Remove the selection and put it on the clipboard so you can paste it somewhere else. (CTRL+X).

copy
There are two types of copy 1. Copy and 2. Copy as picture.
Put a copy of the selection on the clipboard so you can paste it somewhere else. (CTRL+C).

Paste
Add content on the clipboard to your document. (CTRL+V).
There are many types of paste :-
1. Paste:- Add content on the clipboard to your document.
2. Formulas:- To paste just the formula and not the formmating of the original cell.
3. Formulas & Number formatting:- To paste just the formula, and the number formatting (for
example:percent format, currency format, and so on).
4. Keep source formatting:- This option retains formatting that was applied to the copies text. Any styles
defination that is associated with the
copied text is copied to the destination document.
5. No border:- On a worksheet, select the cell or range of the cells that you want to remove a border from.
6. Keep source column widths:- Keep source column width worksheet as well as you can paste it
somewhere else.
7. Transpose:- Transpose reorients the content of the copied cells when pasting. Data in rows is paste into
columns . And data in columns is paste into rows.
8. Values:- Paste values will paste the values only of the copied range without formulas and formatting. This
allows us to extract the numbers
or text form cells.
9. Value & Number formatting:- If you are copying values to another range and want to keep the original
number formats such as the currency
symbols or the number of decimal places, press Ctrl+Alt+V, and then U to paste values and number
formats.
10. Values & source formatting:- If you are copying values to another range and want to keep the original
number format with formatting.
11. Formatting:- This paste option only paste formatting another area.
12. Paste Link:- The Excel paste link shortcut is a quick and easy way to copy and paste links between cells.
By using this shortcut, you can
save yourself time and effort when working with data in Excel.
13. Picture:- you can use the Copy as Picture function to quickly copy and paste range or chart.
14. Linked picture:- You can make a Linked Picture using Copy and a Paste Special option. Simply select the
cell or cells you want to view
in the Linked Picture,Copy and, in Paste Special, select Linked Picture. This will create a Linked Picture of
the cell(s) you just copied.

Format Painter
Like the look of a particular selection? You can apply that look to other content in the document.
(CTRL+SHIFT+C) then (CTRL+SHIFT+V).

Font style font size

Pick a new font for your document. Change the size of your text.

Increare font size Decrease font size

make your text a big bigger. (CTRL+SHIFT+>) Make your text a bit smaller. (CTRL+SHIFT+<)

Bold Italic underline

Make your text Bold. Italicize your text Underline your text

There are many types of Borders:-

1. Bottom Border:- Apply bottom boreder to the curently selected cells.
2. Top Border:- Apply Top border to the curently selected cells.
3. Left Border:- Apply Left border to the curently selected cells.
4. Right Border:- Apply Right border to the curently selected cells.
5. No Border:- Apply No border to the curently selected cells.
6. All Border:- Apply all border to the curently selected cells.
7. Outside Border:- Apply Outside border to the curently selected cells.
[Link] Border:- Apply thick border to the curently selected cells.
9. Bottom Double Border:- Apply bottom double border to the curently cells.
10. Thick Bottom border:- Apply thick bottom border to the curently cells.
11. Top and Bottom Border:- Apply top and bottom border to the curently cells.
12. Top and Thick Bottom Border:- Apply top and thick bottom border to the curently cells.
13. Top and Double Bottom Border:- Apply top and double bootom border to the curently cells.
14. Draw Border:- Draw outside border to the curently cells.
15. Draw Border Grid:- Apply draw border grid to the curently selected cells.
16. Erasar Border:- Apply Eraser border to the curently selected cells.
17. Line Color:- Choose any one border color then draw border .
18. Style Border:- Choose any one border style then draw border.

fill color
Color the background of cells to make them stand out.

text color
Change the color of your text.

Alignments
Top Align:- Align text to the top.
Middle Align:- Align text so that it is centered between the top and bottom of the cell.
Bottom Align:- Align text to the bottom.
Align Left:- Align text to the left.
Align Center:- Center your content.
Align Right:- Align your content to the right.

orientation
Roatat your text diagonally and vertically. This is a great way to label narrow columns.
 indent
There are two types of indent :- 1. Increase Indent 2. Decrease Indent
1. Increase Indent:- Move your content closer to the cell border.
2. Decrease Indent:- Move your content farther away form the cell border.

wrap text
Wrap extra long text into multiple lines .

merge
There are four types of Merge:-
1. Merge & Center:- Combine and center the content of the selected cell in a new large cell.
2. Merge Across:- Merge select cell in the same row into one large cell.
3. Merge Cell:- Merge the selected cell into one cell.
[Link] Cell:- Split the current cell into multiple cell.

number format
Choose the format for your cells such us percentage, date ,time, currencyetc.

accounting number format

Formats as Dollar, Euro, Chines, or other currency.
percentage style

Format as a percentage. (CTRL+SHIFT+%).

comma style
Format with a thousands saparator.
 decimal
There are two types of decimal:-
[Link] Decimal:- Show more decimal places for a more precise value.
2. Decrease Decimal:- Show fewer decimal places.

conditional
formatting

hightlight cell rules

Hightlight cell rules is a premade type of conditional formating in Excel used to change the appearance of
cells in a range
based on on you specified condition.

Greater than ➔ Highlight greater than number.

Less than ➔ Highlight less than number.

Between ➔ Highlight between number.

Equal to ➔ Highlight Equal number.

Text that contain ➔The "Text That Contains..." Highlight Cell Rule will highlight a cell
with one of the appearance options based on a part
of the cell value containing your specified value. The specified value is typically text, but also works with
a numerical value. In this example, the specified value will be "row"

A Date occurring ➔ Highlight date like :- yesterday, today, tommorow etc.

Duplicate value ➔ Highlight duplicate and uniqe value .

Top and bottom rules ➔ Top/Bottom Rules are premade types of conditional formatting
in Excel used to change the appearance of cells in
a range based on your specified conditions

Top 10 item ➔ Highlight top 10 numbers.

Top 10% ➔ Highlight top 10% number.

Bottom 10 item ➔ Highlight 10 bottom item number.

Above average ➔ Highlight above average number.

Below average ➔ Highlight below average number.

Data bars ➔ A data bar is a type of conditional formatting that creates a visual effect in the
cells of your database that correspond to the contents of the cell. A cell with a longer bar
represents a larger value, while a cell with a shorter bar represents a smaller value

Color scale ➔ Color scales can help you understand data distribution and variation, such
as investment returns over time. Cells large the cell values are compared to the other values in
the range. Note: Icon Sets can be used together with other conditional formatting rules

New rule ➔ Formula – you can now specify the formatting criteria using a logical formula in
Excel for the web. This rule type gives you the added flexibility of formatting a range based on the
result of a function or evaluate data in cells outside the selected range

Clear rule ➔ Clear Rules function can help you quickly and easily remove the conditional
formatting in selected range and entire worksheets. To delete the selected range conditional
formatting, please do as this: 1. Select the range that you want to remove the conditional formatting.

Manage rule ➔ Create, edit, delete, and view all conditional formatting rules in the workbook by using
the conditional formatting rules manages.

Format as Table ➔ Quickly convert a range of cell to a table with its own style

Cell style ➔ A colorful style is a great way to make important data stand out on the sheet.

Insert ➔ Insert new cells, columns, rows and sheets to your workbook.

Delete ➔ Delete cells, columns, rows and sheets form your workbook.

Format ➔

Row Height:- Incerase row height according to you.

Autofit Row Height:- Autofit row height.
Column Width:- Increase column height according to you.
Autofit column width:- Autofit column width.
Default column width:- Normal column width.
Hide & Unhide:- Hide & Unhide columns, rows, sheets.
Rename sheet:- change name your sheet.
Move and copy sheet:- Copy sheet.
Tab color:- Give any one color in tab color.
Protect sheet:- Prevent unwanted change form others by limiting their ability to edit.
Lock cell:- Lock the selected cell to keep people form making change to them. You must turn on
protect sheet in order for this feature to work.
Format cell:- Datasheet formatting (CTRL+1) or (CTRL+SHIFT+F).
Sum ➔ Automatically add a quick calculation to your worksheet, such as a sum, count, min, max,
average, [Link] (ALT+=)

Fill ➔

1. Down:- Fill same number down. (CTRL+D).

2. Up:- Fill same number up.
3. Right:- Fill same number right. (CTRL+R).
4. Left:- Fill same number left.
5. Series:- Fill Series is a helpful feature in Excel that improves efficiency in creating spreadsheets. It
allows instant filling of
values in selected cells based on the intervals, type, units and step values specified by the user.
6. Justify:- An interesting tool within Excel is the Fill Justify. It allows you to select text from several
rows and merge them
in to one cell. So if you have data that gets downloaded in to separate rows and want to join them up
in to one sentence,
then the Excel´s Fill Justify option is your savior
7. Flash fill:- Automatically fill in values. Enter a couple of a examples you you want as output and
keep the active cell
in the column you want filed in. (CTRL+E).

Sort and filter ➔

1. Sort A to Z:- Lowest to highest.

2. Sort Z to A:- Highest to lowest.
3. Custom sort:- Choose more option, such as sorting by multiple columns or rows, and case-sensitive
sorts.
4. Filter:- Trun on filtering for the selected cells. Then, click the arrow in the column header to narrow
down the data.
5. Clear:- Clear the filter and sort state form the current range of data.
6. Reapply:- Reapply the filter and sort on the current range so that change so that changes you've
made are included. (CTRL+ALT+L).

Find and Select ➔

1. Find:- Find text or other content in the document.

2. Replace:- Search for text you'd like to change. And replace it with something else.
3. Go to:- What's faster than scrolling? Jumping. Go to lets you jump right to a specific page, line,
footnote, comment or other place
in your document.
4. Formulas:- Go to formulas.
5. Comment:- Go to comments.
6. Conditional formatting:- Go to conditional formating.
7. Data validation:- Go to data validation.
 8. Select Object:- Select object including ink, shapes and text areas.
9. Selection pane:- see a list of all your object.

INSERT TAB
The Insert tab contains various items that you may want to insert into a
document. These items include such things as
tables, word art, hyperlinks, symbols, charts, signature line, date & time, shapes, header, footer, text
boxes, links, boxes, equations and so on.

Pivot table ➔ Easily arrange and summarize complex data in a Pivot table. FYI:-You can double click a
value to see which detailed values make up the summarized total.

Recommended pivot table ➔ Want as to recommend Pivot tables that summarize your complex data?
Click this button to get a customized set of Pivot table that we think will best suuit your data.

Table ➔ Create a table to organized and analyze related data. Table make it easy to sort, filter, and
format data within a sheet. (CTRL+T).

Picture ➔ Insert picture form your computer or form other computers that you're connected to.

Online picture ➔ Find and insert picture form a variety of online sourcesss.

Shapes ➔ Insert ready-mode shapes, such as circle, squarre, and arrows, etc.

SmartArt ➔ Insert a smart art graphic to visually communicate information. Smart art graphic range
form graphical lists and process diagrams to more complex graphics, such as Venn diagrams and
organization charts.

Screenshot ➔ Quickly add a snapshot of any window that's open on your desktop to your document.

Store ➔ Exploer app in the office store.

My app ➔ Insert an app into your document and use the web to enhance your work.

Recommended chart ➔ Want as to recommend a good chart to showcase your data? Select data in your
worksheet nd click this button to get a customized set of charts that we think will fit best with your data.

Column chart ➔ Use this chart type to visually values across a few categories. Click the arrow to see the
different type of column charts available and pause the pointer on the icons to see a preview in your
document.

Line Chart ➔ Use this type to show trend over time (year, month, or days) or categories. Click the arrow
to see the different type of line charts available and pause the pointer on the icons to see a preview in
your document.
Pie and Donut chart ➔ Use this chart type to show proportions of a whole. Use it when the total of your
number is 100%. Click the arrow to see the different type of line charts availbale and pause the pointer
on the icons to see a preview in your document.

Bar chart ➔ Use this chart type to visually compare values across a few categories when the chart
shows duration or the category text is long.

Area chart ➔ Use this chart type to show trends over time (year, days, months) or categories. Use it to
highlight the magnitude of change over time.

Scatter ➔ Use this chart type to show the relationship between sets of values.

Surface and Stock chart ➔ Click the arrow to see the differenty type of stock, surface, or radar charts
available and pause the pointer on the icons to see a preview in your document.

Combo chart ➔ Use this type to highlight different types of information. Use it when the range of values
in the chart varies widely or you have mixed types of data.

Pivot chart ➔ Use Pivot charts to graphically summarize data and explore complicate data.

Line Sparkline ➔ Sparkline are mini charts placed in single cell, each representing a row of data in your
selection.

Column Sparkline ➔ Spearkline are mini charts placed in single cell, each representing a row of data in
your selection.

Win & loss ➔ Sparkline are mini charts placed in single cell, each representing a row of data in your
selection.

Slicer ➔ Use a slicer to filter data visually. Slicer make it faster and easier to filter tables, Pivot tables,
Pivotcharts, and cube functions.

Timeline ➔ Use a timeline filter dates interactively. Timeline make it faster and easier to select time
periods in order to filter PivotCharpts, PivotTable, and cube functions.

Hyperlink ➔ Create a link in your document for quick access to webpages and files. Hyperlink can also
take you to places in your document.

Text box ➔ Draw a text box anywhere.

Header & footer ➔ The content of the header and footer repeats at the top and bottom of each printed
page. This is useful for showcasing info such file name, date and time.

Word art ➔ Add some artistic flair to your document using a Wordart text box.

Signature line ➔ Insert a signature line that specifies the individual who must sign.

Object ➔ Embedded obects are document or other files you have inserted into this document. Instead
of having separate files, sometimes it's easier to keep them all embedded in a document.

Equation ➔ Add common mathematical equcation to your document, such as the area of a circle or the
quadratic formula. You can also build your own equcations using the library structures.
Symbol ➔ Add symbol that are not on your keyboard. Choose form a variety of options including
mathematical, currency, and copyright symbols.

Page layout tab

The Page Layout Tab holds all the options that allow you to arrange your
document pages just the way you want them. You can set margins, apply themes, control of
page orientation and size, add sections and line breaks, display line numbers, and set paragraph
indentation and lines.

Themes ➔ Pick a new them to give your document instant style and just the right personality.

Color ➔ Change the color.

Font ➔ Change the Font.

Effects ➔ Quickly change the general look of objects in your document.

Margins ➔

Set page margins. There are tree types of margins.

1. Normal 2. Narrow
[Link]

Orientation ➔

Change page orientation.

1. Protrait 2.
Landscape 2 LANSCAPE

Size ➔ Set page size . There are many types of page size like:- Letter, Legal, A4, A3etc.

Print area ➔ Set print area.

Break ➔ Add a page break.

Background ➔ Add a background image.

Print title ➔ Choose rows and columns you'd like to repeat on each printed page, such as those with
labels or headers.

Width ➔ Shrink the width of yoour printout to fit a contain number of pages.

Height ➔ Shrink the height of your printout to fit a certain number of pages.

Scale ➔ Stretch or shrink your printout to a percentage of its actual size.

Gridline ➔ View and unview gridlines.

Heading ➔View and unview heading.

Bring forward ➔ Bring the selected object forward one label so that it's hidden behind fewer objects.

Send backward ➔ Send the selected object back one label so that it's hidden behind more objects.

Selection pane ➔ See a list of all your objects.

Align ➔ Change the placement of your selected obejects on the page.

Group ➔ Join object together to move and format them as if they were a single object.

Rotate ➔ Rotate and flip the selected object.

Formulas
The formula tab is used to insert functions, outline the name, produce the name,
review the formula, etc. In the ribbon, the Formulas tab has vital and most helpful functions to
form dynamic reports. It contains Function Library, Defined Names, Formula Auditing, and Calculation.

Name manager ➔ Create, edit, delete, and find all the names used in the workbook.

Define name ➔ Define and apply names.

Use in formula ➔ Choose a name used in this workbook and insert it into the current formula.

Create from selection ➔ Automatically generate names form the selected cells.

Trace presedent ➔ Show arrow that indicate which cells affect the value of the currently selected cell.

Trace dependents ➔ Show arrow that indicate which cell are affected by the value of the currently
selected cell.

Remove arrows ➔ Remove the arrows drawn by trace precedents or trace dependents.

Show arrows ➔ Display the formula in each cell oinstead of the resulting value. (CTRL+~).

Error checking ➔Check for common erros that occur when using formulas.

Evaluate formula ➔ Debug a complex formula. Evaluating each part of the formula individually.
Stepping though the formula part by part can help you verify it's calculation corrently.

Watch window ➔Add cell to the watch window list to keep an eye on their values as you update other
parts of the sheet.

Calculation options ➔ Choose to calculation formulas automatically and manually.

Calculate now ➔Calculate the entrie workbook now. You only need to use this if automatic calculation
is turned off. (F9)

Calculate sheet ➔ Calculate the active sheet now. You only need to use this if automatic calculation is
turned off. (SHIFT+F9).
 Data tab
Data validation ➔ Pick form a list of rule to limit the type of data that can be entered in a cell.

Consolidate ➔ Summarize data form separate ranges, consolidate the results in a single output range.

What if analysis ➔ Try out version values for the formulas in your sheet using Senario manager, Goal
seek, and Data Table.

Senerio manager ➔ Create different group of values or scenarios, and switch between then.

Goal seek ➔ Find the right input for the value you want.

Data table ➔ See the result of multiple inputs at the same time.

Group ➔ Group rows or columns, or automatically create an outline. (SHIFT+ALT+RIGHT).

Ungroup ➔ Ungroup a range of cells that were previously grouped. (SHIFT+ALT+LEFT).

Subtotal ➔ Quickly calculate rows of related data by interesting subtotals and totals.

Review tab
Under this tab, Excel offers tools to check your document. Search some words in a dictionary, find
synonyms or translate a word to verify the spelling.

Spelling ➔ Lets as check your spelling. (F7).

Research ➔ Want to explore something further? Use source, such as dictionaries, encyclopedias aand
translation services to get the info you need.

Thesaurs ➔ At a loss for word? Let us suggest another way to say what yoou mean.

New comment ➔ Add a note about tjis part of the document.

Delete ➔ Delete the selected comment.

Previous ➔ Jump to the previous comment.

Next ➔ Jump to the next comment.

Show / Hide comment ➔ Show or hide the comment on the active cell.

Show all Comment ➔ Display all comment in the sheet.

Show ink ➔ Show or hide ink on the sheet.

Protect sheet ➔ Prevent unwanted changes form others bgy limiting their ability to edit.

Protect workbook ➔ Keep other form making structural change to your workbook, such as moving
deleting or adding sheets.
Share workbook ➔ Share your workbook so that others can work in it at the same time.

Protect and Share workbook ➔ Share the workbook and protect change tracking with a passwoad.

Allow user to edit range ➔ Set up password protection on ranges, and choose peope who can edit those
range.

Trace Changes ➔ Keep track of change made to this document.

View
The View tab enables you to switch between Normal or Master Page, and Single
Page or Two-Page Spread views.

Normal ➔ See your document in normal view.

Page break Preview ➔ See where the page break will appear when your document is printed.

Page layout ➔ See how your printed document will look.

Custom view ➔ Save your current display and print setting as a custom view that you can quickly apply
in the future.

Ruler ➔ Show ruler next to your documen.

Gridlines ➔ Explain the page layout tab.

Formula bar ➔ Show the formula bar so you can see formulas in cells.

Heading ➔ Explain the page layout tab.

Zoom ➔ Zoom to the level that's right for you.

Zoom to selection ➔ Zoom the sheet so the selected range of cells files the entire.

New window ➔ Open a second window for your document so you can work in different places at the
same time.

Arrange all ➔ Stack your open windows so you can see all of them at once.

Freeze pane ➔

1. Freeze pane:- Keep row and columns visible while the rest of the worksheet scrolls (based on
current selection).
2. Freeze Top Row:- Keep the top rang visible while scrolling through the rest of the worksheet.
3. Freeze First Column:- Keep the first column visible while scrolling through the rest of the
worksheet.

Split ➔ Divide the window into different panes that each scroll separately.

Hide ➔ Hide the current window.

Unhide ➔ Unhide any windows hidden by the hide window feature.

View side by side ➔ Instead of switching back and forth between workbooks, view them side by side. It
makes comparing them easier.

Synchronous Scrolling ➔ Scroll two document at the same time.

Reset window ➔ Place the document you're comparing side by side so they share the screen equally.

Switch window ➔ Quickly switch to another open excel window.

View Macros ➔Click to view, record or pause a macro. (ALT+F8)