An Introduction to

MicroSoft Excel

A Nervous Rex !

Ctrl CCtrl V

???

Focus Areas

Shortcuts Functions VLOOKUP

Shortcuts

General

Ctrl X : Cut Ctrl S : Save

Ctrl F : Find Ctrl A : Select All

Ctrl Z : Undo Ctrl Y : Redo

Ctrl B : BoldCtrl Home : Move to First Cell

Alt I R : Insert Row Alt I C : Insert Column

Alt + : Auto SumAlt D F F : Filter Ctrl - : Delete

Specialized

Focus Areas

Shortcuts Functions VLOOKUP

Functions

Error Checking

#NULL!#DIV/0!#VALUE!#REF!#NAME?#NUM!#N/A

Standard problem: error check

1 25 1 25 25

2 30 2 30 30

3 #N/A 3 #N/A

4 15 4 15 15

total #N/A total #N/A 70

The error messes up computations Can now find the total despite the bad data

Before After

IFERROR (Operation , Value if error is detected)

=IFERROR (A1, “ ”)(=A1)

Functions

Standard problem: error check

1 25 1 25 25

2 30 2 30 30

3 #N/A 3 #N/A

4 15 4 15 15

total #N/A total #N/A 70

The error messes up computations Can now find the total despite the bad data

=A1 IFERROR(A1,” ”)

Before After

Functions

BA

Focus Areas

Shortcuts Functions VLOOKUP

VLOOKUP

Vertical Look Up

• What to find • Where to find it• Only find that which matches exactly• Where to send it once it is found

Team City

KCS Chennai

KRK Kolkata

RJR Jaipur

GPRS Pune

DED Delhi

RIB Bangalore

Which City does Team GPRS belong to ?

A B

12345

6

77

8

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Value we are trying to find

Table of data we are looking in to find the match

The column where the value will be found

Should always be "0"

VLOOKUP

Team City

KCS Chennai

KRK Kolkata

RJR Jaipur

GPRS Pune

DED Delhi

RIB Bangalore

A B

12345

6

77

8

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Value we are trying to find

Table of data we are looking in to find the match

The column where the value will be found

Should always be "0"

GPRS

A1 : B8

2

= vlookup(“GPRS”, A1:B8,2,0)

= vlookup(“GPRS”, $A$1:$B$8,2,0)

VLOOKUP Watch outs

• Use $ sign to lock reference

= vlookup($A1, $A$1:$B$8,2,0)

• Keep an eye on the column number

= vlookup($A1, $A$1:$B$8,2,0)