** Download the accompanying Excel Workbook  **

A named range is a feature in Excel that allows you to easily reference individual cells or groups of cells within a workbook using a descriptive name. Using named ranges can save you time when writing complex formulas and make your formulas easier to understand. 

To define a name for a cell range:

​STEP 1: Select a group of cells. In the example below, cells A2:A23 are selected.

STEP 2: Click on the Name Box.

STEP 3: Type a name in the ‘Name Box,’ and click enter.
​In the example, I created a named range called 'School_Level.'

​Now you have created a named range called 'School_Level' that you can use in formulas throughout your workbook!

​​Let's look at a more detailed example:
 
I created an Excel Workbook that contains information about suspensions in select schools within the Philadelphia School District for the 2013-2014 School Year. The dataset has three variables: School Name (The name of the school); School Level (Specifies whether the school is an elementary, middle, high, or career and technical high school); and Total Number of Suspensions (The total number of suspensions reported at each school during the 2013-2014 school year).

I then went on to create 3 named ranges:
    1. School_Name (A3:A153);
    2. School_Level (B3:B153); and
​    3. Suspensions (C3:C153).

From there, I used Excel's named ranges feature to answer the following questions:

1. How many students were suspended in the 2013-2014 School Year?

2. How many students attending High Schools were suspended in the 2013-2014 School Year?

3. How many students attending Middle Schools were suspended in the 2013-2014 School Year?

4. How many students attending Elementary Schools were suspended in the 2013-2014 School Year?

5. How many Elementary Schools suspended more than 50 students in the 2013-2014 School Year? 

I’ll share some of my results with you:
 
How many students attending Middle Schools were suspended in the 2013-2014 School Year? 
I used the SUMIF function to answer this question.
The exact formula I used was "=SUMIF(School_Level, "MIDDLE SCHOOL", Suspensions)"
 
Translation: Sum the values in the named range 'Suspensions,' where the corresponding School_Level is 'Middle School.'

How many Elementary Schools suspended more than 50 students in the 2013-2014 School Year? 
I used the COUNTIFS function to answer this question.
The exact formula I used was "=COUNTIFS(School_Level, "Elementary School", Suspensions, ">50")"
 
Translation: Count how many Elementary Schools suspended more than 50 students in the 2013-2014 School Year. ​

TIPS:

• Names cannot contain any spaces. (Consider using underscores (_) or periods (.) as separators. Example: School_Level)

• The first character of a name must be a letter.

• Name lengths cannot exceed 255 characters.

• Excel does not distinguish between UPPERCASE and lowercase characters in names.

• Click the drop-down arrow next to the Name Box to see a list of named ranges in a Workbook.

• To edit a named range:

1. Click Insert>Name>Define

2. In the list, click on the named range you want to change;

3. Click Ok, and then close the Name Manager.

Named ranges are a powerful feature that can be used to simplify formulas. How do you use named ranges?

** Data used in this post were retrieved from the School District of Philadelphia’s District Performance Office Data Archive on Suspensions. Note: Not all schools were included in the analysis. **