Microsoft Excel Basics
Copyright of Shell International B.V. CONFIDENTIAL 1
HSSE
There are no scheduled
drills/alarms today.
Copyright of Shell International B.V. CONFIDENTIAL
HSSE
Copyright of Shell International B.V. CONFIDENTIAL 3
Excel Basics - Functions
Combining Text Creating Nested Functions
• &, Concatenate, Concat, Textjoin • Understanding how to create nested / multiple
formula in one cell
Extracting Text
• Left, Mid, Right Lookup and Reference
• Find, Search • Vlookup, Hlookup
• Len, Replace, Substitute • Match, Index
• Vlookup+Choose
Logical Functions
• If, Iferror, Ifs, Ifna
• And, Or, Not
• IS family
Copyright of Shell International B.V. CONFIDENTIAL 4
Excel Basics – Summaries and Visualization
Pivot Tables
• Summarize data thru tables
• Filter vs Slicer
• Charts and visualization
Data Analysis
• Power BI
Copyright of Shell International B.V. CONFIDENTIAL 5
Combining Text
01
&, CONCATENATE, CONCAT, TEXTJOIN
Copyright of Shell International B.V. CONFIDENTIAL 6
Combining text in a cell ( &, CONCATENATE )
Use “ & “ to combine text in multiple cells into one
Copyright of Shell International B.V. CONFIDENTIAL 7
Combining text in a cell ( &, CONCATENATE )
CONCATENATE() is the functional version of the “&”
Copyright of Shell International B.V. CONFIDENTIAL 8
Combining text in a cell ( ? )
It is too tedious to select the cells individually,
is there an alternative?
Copyright of Shell International B.V. CONFIDENTIAL 9
Combining text in a cell ( CONCAT )
In Excel 2016, CONCAT() replaced CONCATENATE(),
It accepts ranges of cells (vectors) as an input.
NOTE: CONCATENATE() is still present in EXCEL
Copyright of Shell International B.V. CONFIDENTIAL 10
Combining text in a cell ( ? )
What if we want to add spaces?
Copyright of Shell International B.V. CONFIDENTIAL 11
Combining text in a cell ( TEXTJOIN )
TEXTJOIN() is the upgraded version of CONCAT(),
It is CONCAT() with an added functionality of placing delimiters
Copyright of Shell International B.V. CONFIDENTIAL 12
Copyright of Shell International B.V. CONFIDENTIAL 13
END OF 01
QUESTIONS?
Copyright of Shell International B.V. CONFIDENTIAL 14
CHALLENGE EXERCISE!
Copyright of Shell International B.V. CONFIDENTIAL 15
Challenge – Combining Texts
In “1”, create a new column containing the full name of the individuals
Copyright of Shell International B.V. CONFIDENTIAL 16
Extracting/Editing Text
02
LEFT, MID, RIGHT, LEN, SEARCH, FIND, REPLACE, SUBSTITUTE
Copyright of Shell International B.V. CONFIDENTIAL 17
Extracting/Editing Text ( LEFT, MID, RIGHT )
LEFT() gives the characters of a text starting from the beginning of the
string until how many characters you specify
RIGHT(), similar to LEFT(), but starts from the end of the string.
MID() starts from anywhere you specify then functions just like LEFT().
Copyright of Shell International B.V. CONFIDENTIAL 18
Extracting/Editing Text ( LEN )
LEN() outputs the number of characters in a cell
Copyright of Shell International B.V. CONFIDENTIAL 19
Extracting/Editing Text ( FIND, SEARCH )
FIND() and SEARCH() gives the position a character in a text from the
start of the string.
Difference?
SEARCH() is “case-insensitive” and able to read “wild-cards”
Copyright of Shell International B.V. CONFIDENTIAL 20
Copyright of Shell International B.V. CONFIDENTIAL 21
Extracting/Editing Text ( REPLACE, SUBSTITUTE )
REPLACE() and SUBSTITUTE() changes a character/set of characters
with replacement you specify
If you know the character/s to be changed, use SUBSTITUTE()
If you know the position of the character/s to be changed, use
REPLACE()
Copyright of Shell International B.V. CONFIDENTIAL 22
END OF 02
QUESTIONS?
Copyright of Shell International B.V. CONFIDENTIAL 23
NESTING FUNCTIONS
2.5
Creating Nested function in one Cell
Copyright of Shell International B.V. CONFIDENTIAL 24
Nesting Functions
Nested Functions are functions that are combined in one cell to get one
value
It is good practice to create separately the constituents of a function then
combine everything in one cell
DIVIDE and CONQUER
Copyright of Shell International B.V. CONFIDENTIAL 25
END OF 2.5
QUESTIONS?
Copyright of Shell International B.V. CONFIDENTIAL 26
CHALLENGE EXERCISE!
Copyright of Shell International B.V. CONFIDENTIAL 27
Challenge Exercise – Extracting from Texts
1. Go to google and look for the city of Wales with the longest name.
Count how many consonants are in its name?
2. In the tongue twister “Peter piper picked a peck of pickled peppers...”,
the last occurrence of the word “peppers” is what occurrence?
Copyright of Shell International B.V. CONFIDENTIAL 28
Logical functions
03
LOGICALS and The Family of “IS”
OR, AND
IF, IFS, IFERROR, IFNA
Copyright of Shell International B.V. CONFIDENTIAL 29
Logical functions ( Logical )
A logical is a formula using logical operators and is answerable by TRUE
or FALSE
Logical Operators: {=, >, <}
Copyright of Shell International B.V. CONFIDENTIAL 30
Logical functions ( “IS” Family )
Typing “=IS” in a cell shows a list of available pre-set logic tests in excel.
Copyright of Shell International B.V. CONFIDENTIAL 31
Logical functions ( OR, AND )
OR() and AND() returns either a TRUE or a FALSE depending if logical
expressions inside them has been met or not
AND OR RESULT
AT LEAST ONE CONDITION IS
ALL CONDITIONS ARE TRUE TRUE
TRUE
AT LEAST ONE CONDITION IS FALSE ALL CONDITIONS ARE FALSE FALSE
Copyright of Shell International B.V. CONFIDENTIAL 32
Logical functions ( IF )
IF() follows the algorithm below
=IF(Logical Test, [output value if true], [output value if false])
Copyright of Shell International B.V. CONFIDENTIAL 33
Logical functions ( IFS )
IFS() is given multiple logical conditions each with its own corresponding
values if true and returns the value of the first condition that is met
(returns “#N/A” if none is met)
Copyright of Shell International B.V. CONFIDENTIAL 34
Logical functions ( IFERROR, IFNA)
IFERROR() and IFNA() outputs a value/text if the expression within it
comes up as an error.
Difference?
IFNA is specific to the error “#N/A”, so go with IFERROR()
Copyright of Shell International B.V. CONFIDENTIAL 35
Copyright of Shell International B.V. CONFIDENTIAL 36
END OF 03
QUESTIONS?
Copyright of Shell International B.V. CONFIDENTIAL 37
CHALLENGE EXERCISE!
Copyright of Shell International B.V. CONFIDENTIAL 38
Logical Functions
If “=“ is the logical operator for “Equals”/”is equal to”, what is the
logical Operator for “Not Equal”/”is NOT equal to”?
Copyright of Shell International B.V. CONFIDENTIAL 39
Lookups
04
VLOOKUP, HLOOKUP, CHOOSE, INDEX, MATCH
Copyright of Shell International B.V. CONFIDENTIAL 40
Lookup functions ( VLOOKUP, HLOOKUP )
VLOOKUP() matches target value to the leftmost column of a reference
table and outputs the value corresponding to the row in the specified
column
Copyright of Shell International B.V. CONFIDENTIAL 41
Lookup functions ( VLOOKUP, HLOOKUP )
HLOOKUP() matches target value to the top-most row of a reference
table and outputs the value corresponding to the column in the specified
row
Copyright of Shell International B.V. CONFIDENTIAL 42
Lookup functions ( CHOOSE )
CHOOSE() returns a value from a list/vector using a given position or
index
Copyright of Shell International B.V. CONFIDENTIAL 43
Lookup functions ( LOOKUP + CHOOSE )
Use CHOOSE() in combination with VLOOKUP()/HLOOKUP() by
letting CHOOSE() be the reference list/table/vector
Copyright of Shell International B.V. CONFIDENTIAL 44
Lookup functions ( INDEX, MATCH )
INDEX() retrieves values at a given location in a list or table
Copyright of Shell International B.V. CONFIDENTIAL 45
Lookup functions ( INDEX, MATCH )
MATCH() provides the numeric position of an item in a list
Copyright of Shell International B.V. CONFIDENTIAL 46
Lookup functions ( INDEX + MATCH )
INDEX() + MATCH()
Copyright of Shell International B.V. CONFIDENTIAL 47
Copyright of Shell International B.V. CONFIDENTIAL 48
Lookup functions ( INDEX + MATCH + MATCH = PROFIT! )
INDEX() has a third argument, column assignment, which could take on
another MATCH() to fully automate your lookup
Copyright of Shell International B.V. CONFIDENTIAL 49
END OF 04
QUESTIONS?
Copyright of Shell International B.V. CONFIDENTIAL 50
CHALLENGE EXERCISE!
Copyright of Shell International B.V. CONFIDENTIAL 51
Challenge Exercise – Lookups
In the excel provided, to which team does the Person with the highest sale
in terms of total value belong?
Copyright of Shell International B.V. CONFIDENTIAL 52
Pivot Tables
05
Summarising data
Copyright of Shell International B.V. CONFIDENTIAL 53
Pivot tables
Summarize data using pivot table
Copyright of Shell International B.V. CONFIDENTIAL 54
Pivot tables – Building your table
Copyright of Shell International B.V. CONFIDENTIAL 55
Pivot tables – Introducing Slicers
Slicers are the friendlier version of the filters
Copyright of Shell International B.V. CONFIDENTIAL 56
END OF 05
QUESTIONS?
Copyright of Shell International B.V. CONFIDENTIAL 57
CHALLENGE EXERCISE!
Copyright of Shell International B.V. CONFIDENTIAL 58
Challenge Exercise – Pivot
In the same exercise as lookup, which team has the biggest total sale in
terms of value?
Copyright of Shell International B.V. CONFIDENTIAL 59
END OF SESSION
The only stupid question is the only question that isn’t asked - Ramon
Bautista
Copyright of Shell International B.V. 60