top of page

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 :

  1. Give greater readability to your formulas

  2. Can reuse references without having to remember where the cell / range is found

  3. Can move more quickly when typing formulas by hitting tab after typing out a few letters

  4. Can load the reference into power query


Named Ranges - Template

How to Set Up and use a Named Range


Setting up a named ranges is fairly simple…

navigate to Formulas > Define Name


Named Ranges - How to set up

Here, you can select the name of your named range - note that excel has a few rules for naming conventions:


Named Ranges - Error

Once you define your name, you can then set the Scope of the named range.


Named Ranges - Set the scope

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


Named Ranges - Create multiple named ranges

Excel will then ask you to confirm where the you want the names to be created from.




56 views

Related Posts

See All

Comments


bottom of page