How to Paste Range Names in Excel (7 Ways)

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

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

➤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

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

➤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

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

➤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

➤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

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 Sub

Here, 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.

Further Readings

• How to Find a Named Range in Excel
• How to Navigate to a Named Range in Excel
• How to Name a Group of Cells in Excel
• How to Change Scope of Named Range in Excel
• How to Delete All Named Ranges in Excel

<< Go Back to Named Range | Excel Formulas | Learn Excel