8 Ways to Paste Range Names in Excel

The strange thing about names in Excel is that, despite being one of its most beneficial features, many people think they’re silly or geeky. The issue is that few people are familiar with the fundamentals of Excel Names. This article will not only show you how to construct a named range in Excel but also how to take advantage of this feature by pasting them into formulas. Today we will show you how to paste Range Names in Excel.

📁  Download Excel File

Download this file to practice with the article.

What is Named Range and How to Create It?

Names are frequently used to refer to individuals, things, and locations in daily life. For instance, you would say “Washington, DC” rather than “the city lying at latitude 38.9072° N and longitude 77.0369° W.” Similarly, you can refer to a single cell or a range of cells in Excel by name rather than by cell reference by giving them names. Now you can just use the name you gave it rather than the cell reference (such as B6 or B6:B10). You can also use the name in formulas. Suppose you have named the range of cells B6:B10 “Dairy”. So, in a formula, instead of writing this:

you can write this:

The practice of naming ranges of cells comes in handy when you are dealing with large sets of data. This gives you a short approach to selecting a data range.

But how can you create a Named Range? There are a couple of ways you can create a Named Range. We will show you the easiest of those methods.

⬇️⬇️ STEPS ⬇️⬇️

• First, without selecting headers, choose the range you want to give a name.

• Enter the name of the selection in the Name Box to the left of the Formula Bar.

That’s it. Your range in the selection has a name now. Repeat the process for every range of cells.

Learn to Paste Range Names in Excel with These 8 Methods

Now that you know how to create a named range, we will show you how to paste the range names in 8 different ways in Excel. We will use a dataset of a shopping list where the price of the Computer components is listed. We named the component list “Components” and the prices “Price”. For most of the cases, we will use the built-in Formula features of Excel, and in the last method, we will demonstrate how you can achieve the same goal using a VBA script.

If you are ever in need of pasting the location of your range cells or cell references, this method will guide you through the process.

⬇️⬇️ STEPS ⬇️⬇️

• First, we have already named the columns as shown above.
• Next, select your target cell where you want to paste the cell references of your data. We selected B16. Then, from the Formulas tab, go to the Defined Names Then, from Use in Formula dropdown, select the Paste Names option.

• Subsequently, the Paste Name dialogue box will appear. Here, select Paste List.

Thus, you will be given a list of range names and their locations, which will include the worksheet name and cell ranges.

Assume, you want to copy and paste an entire column in another location. You do not have to copy the column and paste it. You can paste the named range, and the process will be much easier. In our example, suppose we bought components for another PC with a different price point. So, the component list will remain the same except for the prices. So we’d like to paste the same components into another table.

⬇️⬇️ STEPS ⬇️⬇️

• First, we named the range like before. Then select your target cell where you want to paste the entire “Components” Column. We selected E8. Then, from the Formulas tab, go to the Defined Names Then, from Use in Formula dropdown, select the Paste Names option.

• Subsequently, the Paste Name dialogue box will appear. Notice, every range of columns you named is listed here. Select the column name you want to paste. We selected “Components”.

• Upon pressing OK, you will see a formula like this appear on the selector cell.

• Press Enter and the whole components column from the previous table will paste here.

If your data set is in tabular form and there are a lot of columns, naming every column individually will get tiresome. However, you can name every column in one swoop using Create from Selection.

⬇️⬇️ STEPS ⬇️⬇️

• To select the entire data table, including headers, press Ctrl+A.
• Then, from the Formulas tab, go to the Defined Names group and select Create from Selection.

• Subsequently, the Create Names from Selection window will appear. Examine the options for headers. We only chose the Top row because the header is in the top row. You can select both if you have headers in both the top row and the left column. Similarly, if your data is organized in such a way that the headers are only in the left column, only select the Left Column option.

• Press OK which will create Named Ranges for every column you have in your data table.

Column C became “Price”.

Now that you know how to name a range of cells, let’s learn how to use the name in a formula without using the cell references. We will calculate the total price using the named range.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell where you want to display the total money you spent. We selected C15 and write this formula in the Formula Bar.

• Then, from the Formulas tab, go to the Defined Names Then, from Use in Formula dropdown, select the Paste Names option.

• Subsequently, the Paste Name dialogue box will appear. Select the name of the range you want to calculate. We selected “Price”.Then, press OK.

Thus, the formula in Formula Bar will look like this.

• Press Enter and the summation of the prices of every component will be done.

You can also paste the named range from the Use in Formula list.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell where you want to display the total money you spent. We selected C15 and write this formula in the Formula Bar.

• Then, from the Formulas tab, go to the Defined Names Then, from Use in Formula dropdown, select “Price”.

Thus, the formula in Formula Bar will look like this.

• Press Enter and the summation of the prices of every component will be done.

If going to the Formula tabs and pasting names seems cumbersome, you can use Formula Assistant to use the named range in the formula.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell where you want to display the total money you spent. We selected C15 and write this formula in the Formula Bar.

• As you begin to type the name of the range, the names will first appear. Press the TAB key after choosing the range’s name.

Thus, the formula in Formula Bar will look like this (method 5 (2) is the name of our sheet here).

• Press Enter and the summation of the prices of every component will be done.

Suppose you already made the calculation using the cell references. You can also name the formula in this case.

⬇️⬇️ STEPS ⬇️⬇️

• First, in Cell C15, type this formula.

• Pressing Enter will get you the total price.
• Now, from Formulas tab, go to the Defined Names group and select the Define Name drop-down, and finally select Apply Names.

• Subsequently, the Apply Name dialogue box will appear. Select the name of the range you calculated. We selected “Price”.Then, press OK.

Following that, the price range in the formula will be replaced by the range name “Price.”

We can also use VBA code to automate the process to paste range names in another location in Excel. To write VBA code we need to enable the Developer tab in our Ribbon. Find here how to access the Developer tab.

⬇️⬇️ STEPS ⬇️⬇️

• Select the Developer Tab and click Visual Basic at the left corner of the Ribbon.

• A window (Microsoft Visual Basic for Applications) will open, in which we will enter our code to paste the named range. Go to Insert and click Module.

• I have already included the code so that you may copy and paste it into your module.

Sub pasteNamedRange()

    Range("Components").Copy
    [E8].PasteSpecial xlPasteValues
    [E8].PasteSpecial xlPasteFormats

End Sub

• Finally, press F5 to execute the code.

📄 Important Notes

📌 If you create a Named Range using the Name Box, it will remain the same for every workbook in your Excel file.

📌 The pasted Named Range will work like a dynamic array, meaning you cannot change anything in the pasted column.

📌 If the Names you specify in the Named Range have space in between words, Excel automatically puts an underscore in exchange for space.

📝 Takeaways from This Article

🖊️ First, we explain the definition of Named Range and how users can create them.

🖊️ Next, we simplified the process of pasting Named Ranges from the Formula tab.

🖊️ We also demonstrated how users can paste Named Ranges into the formula.

🖊️ Last but not least, we created a VBA code to make the process much cooler.

Conclusion

We have come to the conclusion of our article. I hope that after reading this article, you can now create and paste Range Names in Excel. If you have any questions, feel free to comment below.  I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.