Effectively Using Named Ranges Simplifies Excel Programming

One of the most underutilized advanced features of Microsoft Excel is Named Ranges.  Properly using Named Ranges makes programming Microsoft Excel much easier.  They make understanding Excel functions, and what they are trying to calculate, more understandable.  Efficiently using Named Ranges in Excel simplifies Excel programming, we suggest that you use them.

  • =SUMIF(AvailableRegions,SelectedRegion,SalesByRegion)

Data Visualization:  Named Ranges allow you to visualize your data/function. They allow you to instantly make sense of the complex function you are reading.  Named ranges are a tool you want in your Excel toolbox; learn to use dynamic named ranges to simplify your Excel consulting work.

 

Named Ranges allow you to visualize your data, named ranges simplify programming.

 

What is a Named Range in Microsoft Excel?

A “Named Range” in Excel is one or more cells, that have formally been given a formal name ( reference ).  A named range can be a single cell, or range of cells.  There are static named ranges and dynamic named ranges.

  1. Static Named Ranges
    1. Static Named Cells
  2. Dynamic Named Ranges

 

A dynamic named range grows and shrinks with the data, and it is usually the preferred method, when naming a continuous range of cells.  That said, it does not take more effort to create.  Simply use one of the new Dynamic Array Functions in Office 365.

 

Expert’s note:  With the use of the new Dynamic Array Functions you name just the first cell of the Spill Range, and you can then use that thought-out file.

  • A Named Range can be a single cell.
  • A Named Range can be a range of connected cells.
  • A Named Range can be a discounted range of cells, on different sheets even.

 

Named Ranges are Most Often Used:

  • Excel Functions
  • VBA/Macros
  • UserForms
  • Conditional Formatting
  • Drop-Downs:
    • Validation Controls, ComboBoxes, & ListBoxes.

 

Excel’s Dynamic Named Ranges allow you to reference your data by name, but they also grow and shrink with the data, such as in an Excel Table.  As such, when possible, use Excel’s Dynamic Named Ranges, and use Excel Tables.

 

Effectively using Named Ranges in Excel Programming greatly simplifies Excel. You can use the older methods such as in this image, or newer methods.


Old School method of creating a Dynamic Named Range in Microsoft Excel.

 

User Friendly Note:  Named Ranges make it easier for the person coming after you, to understand your files and your work.  If you do not use them, you need to start.

 

Excel Experts can do it all:  When possible, use Named Ranges, Excel Tables, Excel VBA, Pivot Tables, Pivot Charts, UserForms, and the new Dynamic Array Functions, together, in your Excel solutions.  Your users will love you.   That is intermediate-level Excel programming.

 

Applying Named Ranges in Excel your Excel programming will make it easier for others to understand. It just makes sense.

 

 

Named Ranges in Functions Simplify Understanding

A Word:  If you look at an Excel Function, and if you see a word being used, such as ‘Sales’, you can be pretty sure they are using named ranges.  The word ‘Sales‘ in the formula is referencing the Sales column in an Excel Table, by name, instead of using $A$12:$Z$500.

You can see that the Sales Table is being referenced, by name, and it instantly makes sense.  Named Ranges simplify Excel programming because they make Excel make sense.

 

Named Ranges Make Excel Cell Ranges/Addresses Make Sense.

Which Location Below Tells you Something about the Location: 

“1245 Adams Street, Riverside, California, 92707”

“Riverside Public Library”

 

 

Which Formulas Makes More Sense to the User?

One is a combination of numbers, letters and other characters, and the other is a recognizable name.  The name just makes much more sense.   Added benefit, named ranges are often dynamic; absolute references are not.

What does this even mean?  (H8:$H$104,H8:$H$104>0)),1)

 

If you learn nothing else from this post, learn to use named ranges in your functions:

There are many ways to use Named Ranges.  But the best use of them is in functions

  • =SORT(UNIQUE(FILTER(H8:$H$104,H8:$H$104>0)),1)
  • =SORT(UNIQUE(FILTER(Regions,Regions>0)))

Other than the Sum, and the range, you do not know what this function is totaling.  Is it sales, expenses, units, or profits?

  • =SUM(R$8:R$20)
  • =SUM(2023Sales).

 

See how using Named Ranges in Excel Programming makes the SumIf Function make more sense?


Pretty clear that the SumIf is looking at the Year Column, and is summing the Amount Column, for matching records. Now if it said SalesAmount, what would make even more sense. but you get the point, names make data make sense.

 

 

 

 

Named Ranges Say Something to the User

How does Effectively using Named Ranges in Excel Programming?  Named Ranges say something extra to the user.  Sure, both methods of programming ultimately do the same thing, but which is easier, one can be dynamic, and one is a named range.  The name describes the data the range contains:  Budget, Forecast, Actual,  Sales, Expenses, Units, FTE, etc.

  • =SUM(Sales, -Expenses)
  • =Sum(QTR1Sales, QTR2Sales, QTR3Sales, QTR4Sales)
  • =SUMIF(AvailableRegions,SelectedRegion,SalesByRegion)

 

Effectively using Named Ranges in your Excel functions allows others to visualize your data.

 

Expert’s Advice:  Simplify your Excel files, and use Named Ranges as much as necessary.  They communicate more effectively, more intuitively.

 

 

 

 

 

What Can you do with Named Ranges – Examples

 

Image of the Name Box in Excel.


Use the Name Box in Excel to create new named ranges, or to reference existing ones.

 

 

Named Ranges used in Excel Functions – Example

The formulas in the example below make sense.  Even if you never see the file, you can see what the formulas are doing.  This is the number one reason to use Named Ranges in Excel.

The named ranges in this example, all mean something.  Amount, Year, Department.  Use Dynamic Named Ranges to really simplify Excel programming.  Either way, as you can see, when effectively used, named ranges can simplify Excel programming.

 

This image shows the SumIf Function, using names, so you can see how this is easy to apply and to understand.


Example of how using names in your advanced Excel functions will quickly allow you to see what the calculation is doing.

 

 

Another Formula Example, Inputs/Variables

How often do you see an Excel formula with a hard-coded value in it?  Say =$H$6*1.035.  That is terrible, never do that!

When working with Excel formulas, make sure to not place hard-coded values in them.  Instead, place these values, in a cell, and name the cell.  You can now reference that cell in your formulas.

  • =H6*1.035.
  • =H6*SalesMarkup.

 

Expert’s Advice:  Place your variables on an Input Sheet.  Give them a name, and when needed, reference that name in your formulas.  When you need to change the amount, change it in the Input cell, and all of your formulas work as normal.

 

In this image we show the method of naming a cell that contains a variable to be used in your calculations.

 

 

Named Ranges in Excel VBA – Example

Using named ranges in your Excel code is one of the nicest things you can do, for those that need to edit it. It is almost one of the most useful things you can do.   Sure, add comments, but also use named ranges, they make understanding your vba that much easier.

 

Image of Excel vba coding. Good VBA code relies on named ranges. Code without them would be messy.

 

 

 

 

Named Ranges in Data Validation Controls – Example

One of the most common uses of dynamic names ranges would be for drop-down lists.  aka Validation Controls, ComboBoxes, and ListBoxes.   Doctor’s Name, Region, Product Category, etc., are the type of items you would see listed in a drop-down list.

Users can make a selection in the Validation Control, and the formulas and code in the file, would reference the selection made.

In the example below, the Validation Control uses a named range that has the # symbol on the end.  This shows that the validation control is referencing the first cell of a Spill Range, based on a dynamic array function.

In an Validation Control, you can use a formula, or you can reference a Named Range.

 

Dynamic Array Functions Note:

Traditional Named Ranges do not have the # symbol associated with them.  You use the # symbol when pointing to a dynamic Spill Range Cell.  Doing so, to an Excel XLookup or SumIf will make the function dynamic, to the adjacent dataset.  Place the function in the first row, it will grow and shrink as needed.

 

The GOOD Stuff: 

That Little ‘#’ Sign makes all the difference.  If you use “=Regions” you will only get the first item in the list.  If you use “=Regions#“ you will get the full, dynamic list.  Your named range here is actually a named cell, the first cell of the Spill Range.

 

 

Image of a Validation Control.


This approach is much easier than the Offset/CountA method. The new Dynamic Array Functions found in Office 35 make all the difference.

 

 

Cascading Validation Controls – Example

Validation Controls are a great method of collecting inputs from a user.  If you want to take this up a notch, use Cascading Validation Controls.  Here, your selection in one list, feeds another list, and so on.

  • In this setup, you make a selection from the first Validation Control (Pick a Country).
    • Your choice populates the second Validation Control ( Pick a State ).
      • Your choice populates the third Validation Control ( Pick a City ).
        • There you make your final selection, and the formulas and macros reference the selection you made.

In this method, we used the Indirect Function, to pull the data, based on a named range.

  • =Indirect(B8)

 

Image of three Cascading Validation Controls.


In this Validation Control example, we use the Indirect Function, we do this to base one Validation Control’s data source on another Validation Control, based on the selection made. A nice additional to a user-friendly file.

 

 

 

 

Named Ranges in Conditional Formatting – Example

Conditional Formatting is a powerful feature in Microsoft Excel.  It is widely used.  With it you can quickly visualize what your data is trying to tell you.

Use Conditional Formatting for example to show patient records that are 15 days past due.  Simple, shows up red.

Use named ranges when you create the conditional formatting, and it will be easier to follow.  See the example below.

Image of Conditionally Formatting, using Named Ranges.


This is an example of how using a Named Range in Conditional Formatting simplifies Excel programming.

 

 

 

Named Ranges in UserForms – Example

Excel UserForms are vba heavy, meaning that there is a lot of VBA code required in order to make them function.  This code should as much as possible, work with named ranges.

Excel UserForms do not use Validation Controls.  Instead, they use ComboBoxes and ListBoxes.  These drop-down lists can be based on a named range as well as cell references.

Like Validation Controls, in Excel UserForms, you can determine if they are restricted to items in the ComboBox List, or if they are free to type anything they want.

UserForms are a powerful user-friendly tool for file manipulation and for controlled data entry.  Named Ranges, used in vba, simply programming of Excel UserForms.  We recommend that you use dynamic named ranges whenever possible.

 

Image of Excel UserForm. Excel UserForms are based on VBA, so they use Named Ranges.

 

 

Dynamic Array Functions and Named Ranges # – Example

The best way to use dynamic named ranges is via the new Dynamic Array Functions, based on Excel Tables, and using the # Symbol.  In this method you name just one cell, the first cell of the Spill Range.  You can reference it anywhere.

The data in that range can grow and shrink as needed, programmatically populating your Validation Control, via a Named Range (cell).

Similarly, you can use the new Unique function to pull a unique list of regions from the Excel Table, and do the same thing with that data.

 

This is very powerful, this is how you might do it.  

  1. On your Lists Tab, use the Transpose Function to pull the Table Headers from your tblSales Table.
  2. Name the cell that contains the dynamic array Transpose Function.
    1. Name just that cell.  Name it ColumnsList.
  3. In your Validation Control, select List and type “=ColumnsList#”
  4. Go to your Validation Control, and select the Column from the list.

 

Example image of how to use the # symbol in your Validation Controls, to make them dynamic.

 

 

 

 

Make Excel Easier use the Name Box

The Name Box should be your friend.  Use it to find the source of the data you are seeking.  Select the name of interest, from the drop-down list, and tada, you are instantly taken their, unless it is hidden/protected.

Excel Experts Note:  If the Dynamic Named Range is created using the Offset/CountA Function Combo, the range will not be listed in the Name Box.  But you can see it in the Name Manager.  Excel has its issues.

 

Closeup image of Excel Name Box. You can use this to create new names, or to activate a named range.

 

 

 

 

 

Conclusion – Effectively using Named Ranges Simplifies Excel Programming

Using Named Ranges in your Excel functions, vba code, Validation Controls, Conditional Formatting, and Pivot Tables will simplify your Excel programming.  An added benefit, it will also make it easier for others to understand what you have done.

The majority of Excel experts use Named Ranges in their Excel files, you should consider doing so as well. When possible, use dynamic named ranges based on the new dynamic array functions, and Excel Tables.

The two examples below clearly show why you would want to use named ranges in your Excel files.  They just make Excel make sense.

This is Clearly Meaningless:

  • =SORT(UNIQUE(FILTER(H8:$H$104,H8:$H$104>0)),1)

This Clearly Means Something:

  • =SORT(UNIQUE(FILTER(Regions,Regions>0)))

=Regions# will return the list of Unique Regions, from the tblSampleData Excel Table, as seen below.

 

Expert’s Closing:  Microsoft Excel should be easy to use.  Using named ranges in Excel, helps to make it so.  Use named ranges in your functions, macros, and Validation Controls, they will simply how you program Excel.

 

Image of our favorite combination of Dynamic Array Functions.


Using dynamic named ranges in your Excel files will simplify your Excel programming. We recommend that you use them as much as possible.

 

 

 

 

Contact Us for Excel Consulting Services

Our team of professional Excel Consultants can teach you how effectively using Named Ranges in Excel Programming.  We can walk you through all of the options.  Our consultants can show you the strengths and weaknesses of each.  We will teach you best practices.

We Offer programming and training services in Microsoft Excel, Access, SQL Server, and Azure.  Free 45-minute Zoom consultations are available to discuss your needs live.

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261