Prezi is an interactive zooming presentation

### Present Remotely

Send the link below via email or IM

• Invited audience members will follow you as you navigate and present
• People invited to a presentation do not need a Prezi account
• This link expires 10 minutes after you close the presentation

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

You can change this under Settings & Account at any time.

# Excel Training

Excel for beginners
by

## Lee Hawthorn

on 18 July 2013

Report abuse

#### Transcript of Excel Training

Our itinerary
We've arrived
The basics - quick re-cap
Formulas & Functions
Named Ranges
Text Functions
Lookup Functions
Cell Validation
Dynamic Cell Validation
Tables
Condtional Formatting
SUMIFS
Pivot Tables
A file is a workbook

A worksheet is a page in the workbook

Think of the columns as columns of a table

Think of the rows as the rows of a table
Excel
- One big calculator
App basics
The Ribbon
Commands on the ribbon bars
The formula bar (very important !)
Change this setting !
This allows you to go to a cell reference by double clicking
Exercise 1
Open Excel

Familiarise yourself with formula bar / ribbon

Try copy/paste with the keyboard

We'll change a setting that will help you no end when creating formulas
Formulas and Functions
This is a formula
This is a function
Functions make it easy for us to write formulas
i.e. C10+C9+C8+C7 or SUM(C7:C10)

Functions always end with () and most of the them take a number of arguments i.e. AVERAGE(C7:C10)
Functions & Formulas
Many functions can be used in one formula....
Understanding functions and how to combine them to get the required result is the key to unlocking Excel power
Function tips
Check the formula when things don't work
Select an element of the formulas and press F9 to calculate that element
More function tips
Use \$ to make absolute references i.e. when you copy the cell the address will be fixed

Without \$ the formulas are relative i.e. when you copy the cell the address will move too
Exercise 2
Open the workbook Exercise 2
and adjust the formula in cell C8 so that you can copy/paste into the other cells. You should be calculating X*Y
Named Ranges
Named Ranges allow you to assign a name to cell(s)

You can then use these names in formulas rather than the cryptic cell references

=Sum(A1:B12)

=Sum(LastYear)

See demo
More Named Range Tips
Copy/Paste
Clipboard based copy/paste to copy existing cells
Keyboard short-cut :
CRTL+C = Copy,
CTRL+P = Paste

Copy formula as value :
CTRL + C (copy)
ALT, E, S, V

Copy formula only (retains format)
CTRL + C (copy)
ALT, E, S, F
Try and use these shortcuts as it will save loads of time.
Before long you'll be doing this without thinking.
With a table of data we can
quickly assign names using
the columns of the table.

Press CTRL+SHIFT+F3 - Learn the short-cut

Give it a try now with Exercise 3
Create the names and sum Sales.
The big benefit of this is that if the table of data grows you can
quickly expand the named ranges - select / CTRL+SHIFT+F3
Any formulas will be reset to the revised range.
Other Name Tips
We can refer to relative ranges when setting up names
Note we've removed the \$ from column and row
Functions Galore
Text Manipulation

=LEN(A1) This gives us the length of the cell in characters

=LEFT(A1,3) This gives us the left 3 characters of a cell

=RIGHT(A1,3) Guess what this does?

=MID(A1,3,2) This gives us characters from the middle of a cell, starting from the 3rd character return the next 2 characters

300-5650-1000
301-56450-1000
302-343-1000

Can anyone guess how we can extract the left segment?
Really testing you for the middle? (use your imagination)
(see Exercise 4)
Text Manipulation
1. Establish the length
2. Identify aspects of the text that are consistently sized
3. Deduct the number of characters from step 2 from the length.
4. Extract the middle segment using the starting point and the width from step 3.

It takes practice to build the solution so don't worry if you didn't think of it.
We can fix rows or columns
independently

F4 is the keyboard short-cut
More text functions
Lower(A1) turns all characters to the lower case
"Lee Hawthorn" into "lee hawthorn"

Upper(A1) turns all characters to the upper case
"Lee Hawthorn" into "LEE HAWTHORN"

Proper(A1) Capitalises the first letter in each word
"lee hawthorn" into "Lee Hawthorn"

Counta(A1:A10) Counts the number of non-empty cells

Find(Text to find, Place to look, optional start number)
Finds characters in a cell and returns the start position (see demo)
Lookup functions
VLOOKUP (Vertical lookup)

We use lookup like you read a contents page of a book.

The content page is our lookup table.

We know the chapter we want. What we're missing is the page number.
VLOOKUP(Chapter, Content Page, page number column, false)

This function is powerful as it allows you
to build calculations without having to
merge data together.
VLOOKUP Example
=VLOOKUP(B2,Category!A2:B5,2,FALSE)
The Product Category Name
is held in the second column
The Problems with VLOOKUP
The column being returned has to be on the left

Large lookup tables are slow to calculate

Sometimes better to use :

Offset() & Match()

Offset()
Offset() is the worst named function in Excel yet one of the most powerful.
Think about writing a function such as
SUM(A1:B10)

In the background Excel is selecting cells A1 to B10

You don't see the screen change as this is happening in memory (behind the scene)

Offset() allows us to manipulate the cells Excel selects in the background (behind the scene)

Match()
Match works similar to VLOOKUP except it returns the position of the value rather than the value

=match(Lookup value, Lookup column, Match Type)

Normally the Match Type is 0 which returns an exact match
1
Offset Example
Offset ( Start Cell,
Number of rows down,
Number of columns across,
Width of selection
Height of selection)
Formulas combined :
Offset(Category!\$A\$1,
MATCH(B2,Category!\$A\$2:\$A\$5,0)
,1,1,1)
Offset & Match are some of the most
powerful functions

Using them in combination requires a little creative thinking....

consist of multiple functions so try
and get used to thinking of the calculation in parts.
Don't worry if your struggling as it can take practice to get thinking in this way. Have a go at Exercise 5.
HLOOKUP

Same as VLOOKUP except Horizontal lookup
Transposed
Lets take a break from functions...
When creating a model sometimes it's good to
limit what can be input in a cell

Excel gives us 'Cell Validation' for this purpose
Data Validation
It's best to work out what you want in your list first.
Drop Down List
Create a named range for this data

Can you remember the keyboard
short-cut? CTRL+SHIFT+?
Data Validation
Dynamic Data Validation
Remember the Offset() function from earlier?
Offset ( Start Cell,
Number of rows down,
Number of columns across,
Width of selection
Height of selection)
We can use a formula to specify the name
range. Using the Offset function we can
make the range dynamic.

Give it a go....remember the counta function from earlier?
Sometimes the items we want in the list grows
Tables...not what you think
See demo
Conditional Formatting
This allows us to identify certain cells by applying special formats such as a bold font or yellow background

It is very powerful. We can use a formula as well as explicit text.

See demo and then try Exercise 7
Back to functions

SUMIFS
This is a powerful function that allows you to sum numbers according to multiple criteria

i.e. Sum Sales if Product = Brakes

See the demo and then have a go at Exercise 8
Pivot Tables
Pivot Tables allow you to aggregate, group, filter, and transform data without having to resort to complex functions.

See Demo

Then have a go at exercise 9
Here's what we covered :
The basics
Named ranges
Text functions
Lookup functions
Cell Validation
Tables
Conditional formatting
SumIFs
Pivot Tables
Full transcript