WORKING WITH NAMES,

RANGES, FUNCTIONS,
AND LOOKUP
Excel Lesson 3

GEADLITE
Microsoft Excel
No. 1

Pasewark & Pasewark Microsoft Office 2010 Introductory

 Objectives
By the end of this lesson, the student
should be able to:
• Define range names and use names in
formulas
Excel Lesson 3

• Identify the parts of a function.

• Enter formulas with functions.
• Use functions to solve mathematical
problems.
2 Pasewark & Pasewark Microsoft Office 2010 Introductory
2
 Objectives
• Use functions to solve statistical
problems.
• Use functions to solve financial problems.
• Use logical functions to make decisions
with worksheet data.
Excel Lesson 3

• Use functions to insert times and dates in

a worksheet.
• Use text functions to format and display
cell contents.
3 Pasewark & Pasewark Microsoft Office 2010 Introductory
3
 WORKING WITH NAMES AND RANGES

• Range names are meaningful character

strings that you can assign to individual
cells or cell ranges. You can use a range
name practically anywhere you can use a
Excel Lesson 3

cell or range reference. The advantage of

using names comes from the fact that a
name, like Employees, is more meaningful
and less abstract than a reference like
C2:C55.
4 Pasewark & Pasewark Microsoft Office 2010 Introductory
4
 WORKING WITH NAMES AND
RANGES
Also, named ranges are by default absolute, so if
you copy or AutoFill a formula using named
ranges, it will maintain its original cell references.
To define a range name:
Excel Lesson 3

• Select either a cell or cell range

• On the Formulas Ribbon, choose the Define Name
button from the Defined Names group
• To name your range, type a name in the top text field
and click OK. The Scope refers to the parts of the
workbook where your named range will be valid.
5 Pasewark & Pasewark Microsoft Office 2010 Introductory
5
 WORKING WITH NAMES AND
RANGES
Another way to name a cell or range is to:
• Select the cell or range of cells
• Click in the Name Box to the left of the formula bar
and Type the name
Excel Lesson 3

• Press Enter.

Note: Excel will not accept spaces between words in the

names you choose. For example, “newrange” or
“newRange” or “new_Range” would be acceptable, but
“New Range” would not.
6 Pasewark & Pasewark Microsoft Office 2010 Introductory
6
 WHAT IS FUNCTION?
• A function is a predefined formula that
performs calculations using specific values in
a particular order. Also, a Function is a
shorthand way to write an equation that
performs a calculation.
Excel Lesson 3

• A Function is consisted of a built-in formula

that is a shortcut for commonly used
calculation, such as: sum, average, count,
maximum value, and minimum value for a
range of cells.
7 Pasewark & Pasewark Microsoft Office 2010 Introductory
7
 The Parts of A Function
Function has Three Parts:
1. The equal sign identifies the cell contents as a
formula.
2. The function name identifies the operation to be
Excel Lesson 3

performed.
3. The argument is the value the function uses to
perform a calculation

8 Pasewark & Pasewark Microsoft Office 2010 Introductory

8
 ENTERING FORMULAS WITH
FUNCTIONS

To enter a formula with a function, you need to do

the following:
Excel Lesson 3

– Start the formula with an equal sign.

– Select or enter the function you want to use.
– Select or enter the arguments.
– Enter the completed formula.

9 Pasewark & Pasewark Microsoft Office 2010 Introductory

9
 ENTERING FORMULAS WITH
FUNCTIONS
To open the Insert Function dialog box, click the
Insert Function button on the Formula Bar.
Excel Lesson 3

10 Pasewark & Pasewark Microsoft Office 2010 Introductory

10
 ENTERING FORMULAS WITH
FUNCTIONS
• You can also enter a formula with a function
directly in a cell by typing an equal sign, the
function name, and the argument.
Excel Lesson 3

• Formula AutoComplete helps you enter a

formula with a valid function name and
arguments. As you begin to type the
function name, a list of function names
appears below the active cell.
11 Pasewark & Pasewark Microsoft Office 2010 Introductory
11
 TYPES OF FUNCTIONS
Excel Lesson 3

12 Pasewark & Pasewark Microsoft Office 2010 Introductory

12
Excel Lesson 3

13 Pasewark & Pasewark Microsoft Office 2010 Introductory

13
Excel Lesson 3

14 Pasewark & Pasewark Microsoft Office 2010 Introductory

14
Excel Lesson 3

15 Pasewark & Pasewark Microsoft Office 2010 Introductory

15
Excel Lesson 3

16 Pasewark & Pasewark Microsoft Office 2010 Introductory

16
Excel Lesson 3

17 Pasewark & Pasewark Microsoft Office 2010 Introductory

17
Excel Lesson 3

18 Pasewark & Pasewark Microsoft Office 2010 Introductory

18
Excel Lesson 3

19 Pasewark & Pasewark Microsoft Office 2010 Introductory

19
Excel Lesson 3

20 Pasewark & Pasewark Microsoft Office 2010 Introductory

20
Excel Lesson 3

21 Pasewark & Pasewark Microsoft Office 2010 Introductory

21
Excel Lesson 3

22 Pasewark & Pasewark Microsoft Office 2010 Introductory

22
Excel Lesson 3

23 Pasewark & Pasewark Microsoft Office 2010 Introductory

23
Excel Lesson 3

24 Pasewark & Pasewark Microsoft Office 2010 Introductory

24
Excel Lesson 3

25 Pasewark & Pasewark Microsoft Office 2010 Introductory

25
Excel Lesson 3

26 Pasewark & Pasewark Microsoft Office 2010 Introductory

26
Excel Lesson 3

27 Pasewark & Pasewark Microsoft Office 2010 Introductory

27
Excel Lesson 3

28 Pasewark & Pasewark Microsoft Office 2010 Introductory

28
Excel Lesson 3

29 Pasewark & Pasewark Microsoft Office 2010 Introductory

29
 LOOKUP FUNCTION
Excel provides two lookup functions
that you can use to quickly retrieve
information from data in a table. The
Excel Lesson 3

functions are called HLOOKUP (horizontal

lookup) and VLOOKUP (vertical lookup)

30 Pasewark & Pasewark Microsoft Office 2010 Introductory

30
 VLOOKUP
VLOOKUP is a function to lookup up
and retrieve data in a table. The "V" in
VLOOKUP stands for vertical, which
means the data in the table must be
Excel Lesson 3

arranged vertically, with data in rows.

When it finds the value you specified, it
will return a value that is located in the
same row, a specified number of columns
into the table.
31 Pasewark & Pasewark Microsoft Office 2010 Introductory
31
 The arguments for the VLOOKUP function are:
 
The value you are looking for (lookup_value)
The range of cells that make up the table
(table_array)
The number of the column from which to retrieve a
result (column_index)
Excel Lesson 3

The match mode (range_lookup, TRUE = approximate,

FALSE = exact)

Formula: 
=VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup])
32 Pasewark & Pasewark Microsoft Office 2010 Introductory
32
 HLOOKUP
HLOOKUP is similar, but it will look
horizontally across the upper row of your
table, and then retrieve data from a column
further down.
Excel Lesson 3

33 Pasewark & Pasewark Microsoft Office 2010 Introductory

33
Excel Lesson 3

34 Pasewark & Pasewark Microsoft Office 2010 Introductory

34