Named ranges

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which is a named range?

A B2:B30, a block of cell addresses

B Selected_Year, a readable name given to a cell

2. Where do you type a name to give a selected cell its name quickly?

A The Name Box, left of the formula bar

B The sheet tab at the bottom

3. Which formula uses a named range called Sales?

A =SUM(Sales)

B =SUM("Sales")

4. Why use a name like Selected_Year instead of a cell address like $B$2?

A The formula is easier to read and understand

B The formula will always calculate faster

5. Which is a valid name for a named range?

A Selected Year

B Selected_Year

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=AVERAGE(Maths_Marks)		AFormulas > Define Name > Name: Selected_Year > Refers to: =Sheet1!$B$2
2=SUM(Sales)*Tax_Rate		BNames cannot contain spaces. Use an underscore instead, such as Selected_Year.
3The Name Box, next to the formula bar, is where you type a name for the cells you have selected, then press Enter		CMultiplies the total of the range named Sales by the value in the cell named Tax_Rate
4Define Name on the Formulas tab creates a named range and lets you set what cells it refers to		DThe name is in quotation marks, so it is treated as text. Type the name without quotes: =SUM(Maths_Marks).
5Name: Selected Year		EFinds the mean of a range that has been given the name Maths_Marks, instead of typing cell addresses
6=SUM("Maths_Marks")		FSelect B2:B9, click the Name Box, type Maths_Marks, press Enter

Definitions

Named range

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=_______(MathsMarks)

Formulas > ___________

Name Box: RainDays =COUNTIF(________,">0")

Select B2:B30, type Prices in the ________

Named ranges: Region, Sales =SUMIF(Region,"North",_____)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Select cells B2:B30

B Click the Name Box

C Type Sales and press Tab

Question 2

A Name: Selected Year

B Refers to: =Sheet1!$B$2

C Use it: =Selected_Year+1

Question 3

A Cells B2:B30 are named Marks

B In cell E2 type a formula

C =AVERAGE("Marks")

Question 4

A Insert tab > Define Name

B Name: Prices

C Refers to: =Shop!$C$2:$C$9

Question 5

A Name: Max_Temp

B Refers to: =Weather!$B$2:$B$8

C In cell F2 type:

D =MAX(MaxTemp)

Answer Key

Named ranges

Section A: Quick Questions

B — Selected_Year, a readable name given to a cell

A — The Name Box, left of the formula bar

A — =SUM(Sales)

A — The formula is easier to read and understand

B — Selected_Year

Section B: Match the Pairs

=AVERAGE(Maths_Marks) → Finds the mean of a range that has been given the name Maths_Marks, instead of typing cell addresses

=SUM(Sales)*Tax_Rate → Multiplies the total of the range named Sales by the value in the cell named Tax_Rate

The Name Box, next to the formula bar, is where you type a name for the cells you have selected, then press Enter → Select B2:B9, click the Name Box, type Maths_Marks, press Enter

Define Name on the Formulas tab creates a named range and lets you set what cells it refers to → Formulas > Define Name > Name: Selected_Year > Refers to: =Sheet1!$B$2

Name: Selected Year → Names cannot contain spaces. Use an underscore instead, such as Selected_Year.

=SUM("Maths_Marks") → The name is in quotation marks, so it is treated as text. Type the name without quotes: =SUM(Maths_Marks).

Section C: Definitions

Named range — A readable name given to a cell or range, like Selected_Year, which formulas and charts can use instead of cell addresses such as B2:B30.

Section D: Fill in the Blanks

=AVERAGE(MathsMarks)

Formulas > Define Name

Name Box: RainDays
=COUNTIF(RainDays,">0")

Select B2:B30, type Prices in the Name Box

Named ranges: Region, Sales
=SUMIF(Region,"North",Sales)

Section E: Spot the Error

C — A name typed in the Name Box is only saved when you press Enter. Pressing Tab does not create the named range. It should be: Type Sales and press Enter.

A — Named ranges cannot contain spaces. Use an underscore instead: Selected_Year.

C — Quote marks turn Marks into plain text, so the formula does not use the named range. Write the name without quotes: =AVERAGE(Marks).

A — Define Name is on the Formulas tab, not the Insert tab. It should be: Formulas tab > Define Name.

D — The name must be spelled exactly as defined. MaxTemp does not exist, so Excel shows #NAME?. It should be =MAX(Max_Temp).