**Journey to Excel paradise**

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.

Select the table (including headers)

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?

What about the right 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()

This is getting more advanced....

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)

We'll learn more about this later. For now we're going to use it to return the cell referred to by MATCH

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....

Many advanced calculations in Excel

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