If you are looking for some of the easiest ways to paste range names in Excel, then you are in the right place. Pasting range names will make your work easier in Excel like in making a data table or in applying formulas etc.
Let’s get started with the main article.
Paste Range Names in Excel: 7 Ways
We will use the following data table of Sales Records of a company for demonstrating the ways of pasting range names in Excel.
For creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.
Method-1: Using Paste List Option to Paste the List of Defined Named Ranges
Here, we have named the three ranges of the three columns (Product, Sales Person, Sales) with the names product, person, and sales respectively. In this method, we will show the way to paste a list of these range names easily.Â
Step-01:
➤Firstly, make two columns Range Name and Position for pasting the list of the named ranges and their location.
➤Select the output cell E5
➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown>>Paste Names Option
After that, the Paste Name Wizard will pop up.
➤Select the Paste List Option.
Result:
Finally, you will get the list of the range names and their corresponding location containing the sheet name and the cell ranges.
Read More: How to Display Named Range Contents in Excel
Method-2: Using Paste Names Option to Paste Range Names
Here, we have two tables; one has the Product column and the Sales column and another has the Sales Person column. We have named the range of the Sales column as sales1 and now we want to paste this range besides the Sales Person column in the second table.
To do this here we will use the Paste Names Option.
Step-01:
➤Select the output cell F4
➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown>>Paste Names Option
After that, the Paste Name Wizard will pop up.
➤Select the name of the range name sales1.
➤Press OK
Then, the following formula will appear in the cell F4
=sales1➤Press ENTER
Result:
In this way, you will be able to paste the range name sales1 in Column F.
Things to Remember
The range name pasted here will work as a Dynamic Array and you cannot edit or delete an individual cell in this array.
Read More: How to Delete Named Range in Excel
Method-3: Pasting a Range Name in a Formula
Suppose, you have named the range in the column Sales with sales2. Now, you want to get the sum of sales by using the SUM function and paste the range name in this function to have the result.
Step-01:
➤Select the output cell D12
➤Type the name of the function in this cell.
=SUM(
➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown>>Paste Names Option
After that, the Paste Name Wizard will pop up.
➤Select the name of the range name sales2.
➤Press OK
Then, the name of the range name will appear inside the function
=SUM(sales2➤Press ENTER
Result:
Finally, you will get the Sum of Sales in cell D12.
Read More: How to Name a Column in Excel
Method-4: Using Use in Formula List to Paste Range Name in a Formula
You can paste the range name sales3 in the formula from the Use in Formula list to get the sum of sales.
Step-01:
➤Select the output cell D12
➤Type the name of the function in this cell.
=SUM(➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown
➤Select the range name sales3 from the options in the Use in Formula list.
Then, the name of the range name will appear inside the function
=SUM(sales3➤Press ENTER
Result:
Afterward, you will get the Sum of Sales in cell D12.
Method-5: Using Formula Assistance to Paste Range Name in a Formula
You can use Formula Assistance to paste the range name and get the Sum of Sales easily.
Step-01:
➤Select the output cell D12
➤Type the name of the function in this cell.
=SUM(➤After that, start typing the name of the range, and then a list of suggestions will appear
➤Select the name of the range from the list and press the TAB key
Then, the name of the range name will appear inside the function
=SUM(sales4➤Press ENTER
Result:
Finally, you will get the Sum of Sales in cell D12.
Method-6: Using Apply Name to formulas
Suppose, you have already got the Sum of Sales by using the SUM function and referring to the range of Sales manually instead of using the range name. Now, you can change the range of sales to the name of this range sales5 by using the Apply Names Option.
Here, we can see that we have got the Sum of sales by using the following formula
=SUM(D5:D11)D5:D11 is the range of sales and now we will replace it with the name of this range(sales5).
Step-01:
➤Select the output cell D12
➤Go to Formulas Tab>>Defined Names Group>>Define Name Dropdown>>Apply Names Option
After that, the Apply Names Wizard will pop up.
➤Select the name of the range name sales5.
➤Press OK
Result:
After that, the range of sales in the formula will be replaced with the range name sales5.
Read More: How to Edit Named Range in Excel
Method-7: Using VBA Code
In this section, We have named the range of the Sales column as sales6 and now we want to paste this range beside the Sales Person column in the second table.
To do this here we will use a VBA code.
Step-01:
➤Go to Developer Tab>>Visual Basic Option
Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option
After that, a Module will be created.
Step-02:
➤Write the following code
Sub pasterng()
Range("sales6").Copy
[F4].PasteSpecial xlPasteValues
[F4].PasteSpecial xlPasteFormats
End SubHere, sales6 is the range name and we will copy it and then we will paste the values with the format in the cell range F4.
➤Press F5
Result:
In this way, you will be able to paste the range name sales6 in Column F.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Download Practice Workbook
Conclusion
In this article, we tried to cover the easiest ways to paste range names in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.
