Data In Excel

hello and welcome back to tips and

timesavers I'm Danny rocks in today's

lesson I'm going to show you how to

create name ranges in Excel and then use

those name ranges in an excel formula in

my opinion there are three reasons to

use named ranges in Excel formulas

number one they're easier to write in my

opinion number two they're easy to

remember and number three and I think

most important they're easier to explain

to a colleague or to a client now I'm

going to use the same Excel workbook

that my friend Alan Friedman created

that we've been using in the series to

analyze inventory in the previous lesson

I showed you how to create a formula to

calculate gross profit dollars how to

create a formula to calculate gross

profit percentage by the end of this

lesson you will have learned how to

create a named range and call it sales

create a named range and call it caused

the goods sold and instead of using cell

references in your formula our formula

will read equals gross profit minus cost

of goods sold all right let me use

another worksheet to demonstrate how

this all works I have a fictitious

company over here and I have sales for

last year sales for this year for each

division each cell over here that

contains a shaded background will

contain a formula I'm going to write the

formula so that it will not make a cell

reference it will refer is that to a

name range so over here in this cell in

cell b6 teen I want to be able to total

up or sum all of these values I want to

create a name for these values called

last year so the first step is to select

the cells that contain the values or

will contain the values that you wish to

name so there now select it and I'd like

to use this method of creating the name

range because it will work in all

versions of Excel just click in the name

box and then type your name

now as you type your name bear two

things in mind it must begin with a

letter you're named range must begin

with a letter and it cannot contain any

spaces so I began with the letter

capital L and I did not include any

spaces and remember to hit the enter key

so now let's test this out let's click

away from that range and come back here

to the name box this had dropped down

and any name ranges that I have for the

workbook will be listed there so you see

it automatically selects that name range

now let's use this in a formula I'm

going to use the sum function and I'm

using Excel 2010 here I really like this

feature in Excel 2007 Excel 2010 called

function or formula autocomplete so if I

begin typing in you see the more I type

in it narrows down the list of possible

name ranges or formulas press the tab

key and it supplies the full name right

parenthesis click OK and now my formula

is using a named range all right let's

create a named range called this year

and then use it in a formula first

select the cells that are the range of

cells that you wish to name next in all

versions of Excel click in the name box

type your name beginning with a letter

do not use any spaces and remember to

use the enter key to accept it we can

test it out in this case I'm going to go

to a completely different worksheet in

the workbook come over here and then

select that name range that we just go

select oh just names or just create it

and there it is let's use it in a

formula now presuming that you're using

an older version of Excel may need Excel

2003 I'm going to show you a different

technique that will work in all versions

so equals some and the left parenthesis

at this point if you use the f3 keyboard

shortcut it brings up the pasted names

dialog box so all of the name ranges

that we have in the workbook are listed

here so in this case I select the name

range that I wish to you

is this year and then finish it off with

the right parentheses and there you go

so the formulas are much easier to

create much easier to remember much

easier to explain okay now I want to

create a formula for the variance so if

I'm using cell references equal and what

I would do is point to the cell in this

row that contains this year the operator

will be the minus symbol and then point

to a cell in this row which will point

to last year sales so that's perfectly

fine it works but again it's just a

little bit harder to remember you have

to go back and say oh yeah I'm pointing

to this year - last year so let's use

named ranges in here this time I'll do

equal and I will use that f3 keyboard

shortcut to bring up the pace name so I

want to point to this year the operator

is the - I'll use f3 and select last

year click make sure that I hit enter

and now I have my name range and of

course I can use any of the techniques

that I've demonstrated before to copy

this down remember this little keyboard

shortcut ctrl + tilde so this is going

to show all of the formulas so you see

how we're using the name ranges the name

ranges over here and that is a toggle if

you wish to see how that's done using

the ribbon formulas tab on the ribbon

formula auditing show the formulas and

again it is a toggle all right now let's

create another name range I'm going to

show you a different technique for naor

creating a name range this time instead

of merely selecting the values and then

going up into the name box I'm going to

begin by selecting a cell that contains

the label that I want to use as the name

for this range of cells formulas tab on

the ribbon define names and I want to

create from the selection and notice in

the screen tip that there is a keyboard

shortcut ctrl + Shift + f3 so if you use

in Excel 2003 or earlier ctrl + Shift +

f3 will work I'm going to click on this

come

and notice that it opens up a dialog box

so I want to create names from the

values meaning the name for the range is

the value that's in in this case the top

row variants click OK and now let's test

it out let's go over to another

worksheet and from the drop down I want

to select variants there you go

now I think you can see that there is

another advantage of creating name

ranges and that there are navigational

tools so in a very large workbook with

lots of worksheets and lots of rows and

columns

having a name range makes it very easy

to go to that name range now let's use

the sum function to total up the

variance equals sum and I'll use the

formula autocomplete so I hit the tab to

open up the left parenthesis and start

typing var and then move down tab right

parenthesis and there you go alright so

now we have last year this year and

variance now using our named ranges

let's calculate the percentage of

variance I'm going to show you a

slightly different technique so the

formula for percentage variance will be

equal the variance divided by last year

but we're not going to use cell

references and I'm going to show you a

slightly different technique if I want

to be able to create the formula and

have it copied down to all the cells in

a range select the cells select the

range of cells which will contain the

formulas first then begin your formula

equals and I'm going to use the f3

keyboard shortcut for my pace names

variants ok the operator is division f3

and I want to select I'm sorry last year

click OK and now here's your gotcha step

remember that I selected the cells which

will contain the formula copied all the

way down so when you do that use the

keyboard shortcut ctrl enter so now the

formula is

is in all of the cells in that range use

that control enter control tilde and now

you can see it's the same formula in

each cell so in my opinion it's so much

easier to use named ranges an additional

advantage to using name ranges is it

makes it very easy to create an

executive summary so for example over

here I'd like to see the total for last

year this year the average the high and

the low value well I can very simply

come over here and say equal sum and for

last year here's the function

autocomplete tab right parentheses there

it is for this year equals sum and this

time I'm going to use the f3 key board

shortcut paste names for this year

click OK right parenthesis and there you

go so you see I can create a formula

without being tied to having that

formula create it directly adjacent to

the the value so it's a very easy way to

create that executive formula all right

now I'm going to come back here and I'm

going to show you how to use this or

apply this to our inventory analysis now

I'm going to just do this briefly I'm

going to continue this in another lesson

I like to point out that the old style

of creating labels and I affectionately

call this the old IBM Selectric style of

label creation I mean no label and in

this case three cells a better way to do

that is to use wrapping the text for

example over here to type in one cell

and use wrapping text and I also

recommend that when you're doing that

that you take advantage of vertical

alignment so that you get rid of this

unnecessary blank row so remember how we

use the create names from selection so

in this case I want to be able to select

the label plus the values for a named

range and now that they're selected I

could either if I'm using Excel 2007

2010 come over here and say create names

from selection but in this case I'm

going to use that keyboard shortcut

ctrl + Shift + f3 I want to use this

label sales which is in the top row as

my name click OK and now let's just

delete this here and I'll write my

formula this time I'm going to write it

up here equals some left parenthesis

sales and there you go

so now I have used the formula reference

let's do the same thing over here for

the cost of goods sold select the label

select the values this time I'm going to

come up here I'm going to put in the

name range so you know works in so many

different ways actually if I'm going to

do that I don't want to select the label

move glad I quote that so just the

values come back here now to see of GS

remember to hit enter and now let's use

this in a formula in the next lesson I'm

going to show you how to substitute name

ranges where you have cell references in

there so now I want to do equals sum

left parenthesis C o G s and then right

parenthesis and there you go now for my

gross profit dollars I'm going to use

the formula over here and I'm going to

make my selection I'm going to say

equals sales - co g s so you see I like

that formula autocomplete remember to

use ctrl enter and now you have that

formula that has been put into the cells

so you see the difference between using

the name range the difference that you

have between using cell references so if

you want to download this worksheet you

can go over onto the FK Co website WWF k

co com go into the resources tab and

then select seminar handouts and it will

be this first one for inventory analysis

and if you want to learn more about

Excel I invite you or any other office

product I invite you to visit my online

secure shopping website and I'll look

for you in the next lesson