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.

Download Workbook


7 Ways to Paste Range Names in Excel

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.

how to paste range names in excel


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. 

how to paste range names in excel

paste list

how to paste range names in excel

Step-01:
➤Firstly, make two columns Range Name and Position for pasting the list of the named ranges and their location.

paste list

➤Select the output cell E5
➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown>>Paste Names Option

paste list

After that, the Paste Name Wizard will pop up.
➤Select the Paste List Option.

how to paste range names in excel

Result:
Finally, you will get the list of the range names and their corresponding location containing the sheet name and the cell ranges.

paste list

Read more: How to Edit Named Range 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.

how to paste range names in excel

Step-01:
➤Select the output cell F4
➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown>>Paste Names Option

paste names

After that, the Paste Name Wizard will pop up.
➤Select the name of the range name sales1.
➤Press OK

paste names

Then, the following formula will appear in the cell F4

=sales1

➤Press ENTER

paste names

Result:
In this way, you will be able to paste the range name sales1 in Column F.

how to paste range names in excel

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.


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.

how to paste range names in excel

Step-01:
➤Select the output cell D12
➤Type the name of the function in this cell.

=SUM(

using paste names in a formula

➤Go to Formulas Tab>>Defined Names Group>>Use in Formula Dropdown>>Paste Names Option

using paste names in a formula

After that, the Paste Name Wizard will pop up.
➤Select the name of the range name sales2.
➤Press OK

how to paste range names in excel

Then, the name of the range name will appear inside the function

=SUM(sales2

➤Press ENTER

using paste names in a formula

Result:
Finally, you will get the Sum of Sales in cell D12.

how to paste range names 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.

how to paste range names in excel

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.

use in formula list

 

Then, the name of the range name will appear inside the function

=SUM(sales3

➤Press ENTER

use in formula list

Result:
Afterward, you will get the Sum of Sales in cell D12.

how to paste range names in excel


Similar Readings:


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.

how to paste range names in excel

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

using formula assistance

Then, the name of the range name will appear inside the function

=SUM(sales4

➤Press ENTER

using formula assistance

Result:
Finally, you will get the Sum of Sales in cell D12.

how to paste range names in excel


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 Apply Names Option.

how to paste range names in excel

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

using apply names

Step-01:
➤Select the output cell D12
➤Go to Formulas Tab>>Defined Names Group>>Define Name Dropdown>>Apply Names Option

using apply names

After that, the Apply Names Wizard will pop up.
➤Select the name of the range name sales5.
➤Press OK

using apply names

Result:
After that, the range of sales in the formula will be replaced with the range name sales5.

how to paste range names 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 besides the Sales Person column in the second table.

To do this here we will use a VBA code.

how to paste range names in excel

Step-01:
➤Go to Developer Tab>>Visual Basic Option

VBA code

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

how to paste range names in excel

After that, a Module will be created.

VBA code

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.

VBA code

➤Press F5

Result:
In this way, you will be able to paste the range name sales6 in Column F.

how to paste range names in excel


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.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo