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:

**=SUM(B6:B10)**

you can write this:

**=SUM(Dairy)**

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.

**Method 1**

### 1. Applying Paste List Option

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.

**Method 2**

### 2. Utilizing Paste Names Feature

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.

**=Components**

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

**Method 3**

### 3. Creating Names from Selection

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

**Method 3**

### 4. Pasting Named Range in Formula

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

**=SUM(**

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

**=SUM(Price)**

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

**Method 4**

### 5. Pasting Named Range from Use in Formula list

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

**=SUM(**

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

**=SUM(Price)**

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

**Method 5**

### 6. Using Formula Assistant

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

**=SUM(**

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

**=SUM(‘Method 5 (2)’!Price)**

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

**Method 6**

### 7. Applying Name to Formulas

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.

**=SUM(C6:C14)**

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

**Method 8**

### 8. Applying VBA Code

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.