Give it a Name - Named Ranges
Named ranges are one of my favorite features in excel…
yet I rarely see them being used properly.
In this article, we’re going to talk about what exactly they are, and how you can use them.
Follow along with this lesson by downloading the practice file
What are Named Ranges?
Named Ranges allow you to convert references from a format using columns & rows (eg: C4)…
to one that has a custom name (eg: Start_Date).
This adds a ton of value, as you :
Give greater readability to your formulas
Can reuse references without having to remember where the cell / range is found
Can move more quickly when typing formulas by hitting tab after typing out a few letters
Can load the reference into power query
How to Set Up and use a Named Range
Setting up a named ranges is fairly simple…
navigate to Formulas > Define Name
Here, you can select the name of your named range - note that excel has a few rules for naming conventions:
Once you define your name, you can then set the Scope of the named range.
This is helpful for example when you have multiple dashboards, and want to set a named range for the Start Date on each file.
Creating a Named Range from a list
Sometimes, you’ll have a number of inputs, and you’ll want each of those converted to a named range.
This is extra useful when you have an inputs tab with multiple values that you want to later reference as a named range.
To create multiple named ranges in just one click of a button, simply highlight the selection, and click “Create from Selection”.
Excel will then ask you to confirm where the you want the names to be created from.