## Simplify Excel Formulas Using Named Ranges

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:

To define a name for a cell range:

**STEP 1: Select a group of cells. In the example below, A2:A23 is 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).

(Click here to download my Excel Workbook.)

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

(Click here to download my Excel Workbook.)

I then went on to create 3 named ranges:

1. School_Name (A3:A153);

2. School_Level (B3:B153); and

3. Suspensions (C3:C153).

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:

I’ll share some of my results with you:

I used the SUMIF function to answer this question.

The exact formula I used was "=SUMIF(School_Level, "MIDDLE SCHOOL", Suspensions)"

- How many students were suspended in the 2013-2014 School Year?
- How many students attending High Schools were suspended in the 2013-2014 School Year?
- How many students attending Middle Schools were suspended in the 2013-2014 School Year?
- How many students attending Elementary Schools were suspended in the 2013-2014 School Year?
- 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:

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?

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 Open Data Initiative.***