Setting named ranges in Excel is a very handy feature. However, if you have many names to set from a worksheet, this can take a considerable amount of time. You may think to automat this with some VBA code, but before you do, you should check out a feature from Excel that allows you to set multiple named ranges at the same time. This is a very handy feature and can save you a lot of time.
Check out the video below to see how this works.
Setting multiple named ranges is fast and straight-forward. You begin by having data like this:
And say you wanted to set the Value column to have the names of the Data column as their named range? For example, B2 could be named “Item_1”, B3 could be named “Item_2”, etc. How would you go about doing that?
Set Multiple Named Ranges at the Same Time
The simple solution to this is to use a built-in feature by Excel called “Create from Selection” under the Formulas tab within the Named Ranges group.
Simply select the data that has the names along with the values you want to apply those names to, then click on the Formulas tab, then “Create From Selection.” Excel will automatically figure out what names you’d like to apply. Here is a gif illustrating this:
You’ll see that the named ranges have been applied to the values in the table. This is a nice way to set a bunch of named ranges quickly in Excel.