Simplify Excel Formulas Using Named Ranges
** 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:
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:
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. **
