Learn How to Transfer Data Between Workbooks Using Named Ranges in Excel

A named range is a group of cells that have a name assigned to them. This name can be used to refer to the cells in formulas, charts, or other features of Excel. For example, if you have a range of cells that contain sales data for January, you can name it JanSales and use it in formulas like =SUM(JanSales) or =AVERAGE(JanSales).

Copying contents of named range from one workbook to another with identical structure means transferring the values and formulas of the cells in the named range to another workbook that has the same layout and format. This can be useful when you want to update or compare data from different sources or periods.

There are different ways to copy contents of named range from one workbook to another with identical structure in excel formula, such as using VBA code, using the Name Manager, or using the Paste Special feature. In this article, we will focus on the last method, which is the simplest and most convenient one.

Procedures

To copy contents of named range from one workbook to another with identical structure in excel formula using the Paste Special feature, follow these steps:

1. Open both workbooks and make sure they have the same structure and format. For example, the named range should be in the same location and have the same name in both workbooks.
2. In the source workbook, select the named range that you want to copy. You can do this by clicking on the name box in the left corner of the formula bar and choosing the name from the drop-down list, or by pressing Ctrl+F3 to open the Name Manager and selecting the name from there.
3. Press Ctrl+C to copy the selected range to the clipboard.
4. Switch to the destination workbook and select the cell where you want to paste the copied range. Make sure the cell is in the same position as the named range in the source workbook.
5. Right-click on the selected cell and choose Paste Special from the context menu. Alternatively, you can click on the Paste button in the Home tab and choose Paste Special from there.
6. In the Paste Special dialog box, choose the option that suits your needs. For example, if you want to paste only the values of the cells, choose Values. If you want to paste the formulas and the formatting, choose Formulas and number formats. If you want to paste the formulas and adjust them to the new location, choose Formulas.
7. Click OK to complete the paste operation. You should see the contents of the named range copied to the destination workbook.

Explanation

To illustrate the process of copying contents of named range from one workbook to another with identical structure in excel formula using the Paste Special feature, let’s use a scenario with real numbers.

Suppose you have two workbooks, Book1 and Book2, that contain sales data for different months. Each workbook has a named range called SalesData that covers the cells from A1 to D5, where A1 contains the month name, A2 to A5 contain the product names, B1 to D1 contain the regions, and B2 to D5 contain the sales amounts. The structure and format of the workbooks are identical, as shown in the following table:

The only difference is that Book1 has the sales data for January, while Book2 has the sales data for February. You want to copy the contents of the named range SalesData from Book1 to Book2, so that you can compare the sales performance of the two months.

To do this, you can follow these steps:

1. Open both workbooks and make sure they have the same structure and format. You can see that the named range SalesData is in the same location and has the same name in both workbooks.
2. In Book1, select the named range SalesData that you want to copy. You can do this by clicking on the name box in the left corner of the formula bar and choosing SalesData from the drop-down list, or by pressing Ctrl+F3 to open the Name Manager and selecting SalesData from there. You should see the cells from A1 to D5 highlighted in Book1.
3. Press Ctrl+C to copy the selected range to the clipboard. You should see a dashed border around the copied range in Book1.
4. Switch to Book2 and select the cell A1, where you want to paste the copied range. Make sure the cell is in the same position as the named range in Book1.
5. Right-click on the selected cell and choose Paste Special from the context menu. Alternatively, you can click on the Paste button in the Home tab and choose Paste Special from there. You should see the Paste Special dialog box appear in Book2.
6. In the Paste Special dialog box, choose the option that suits your needs. For example, if you want to paste only the values of the cells, choose Values. If you want to paste the formulas and the formatting, choose Formulas and number formats. If you want to paste the formulas and adjust them to the new location, choose Formulas. In this case, let’s assume you want to paste the formulas and the formatting, so you choose Formulas and number formats.
7. Click OK to complete the paste operation. You should see the contents of the named range SalesData copied to Book2, with the same formulas and formatting as in Book1. The only difference is that the month name in A1 is February instead of January.

Result

The result of copying contents of named range from one workbook to another with identical structure in excel formula using the Paste Special feature is that you have the same data and calculations in both workbooks, but with different month names. You can now compare the sales performance of the two months by using formulas or charts. For example, you can use the formula =SUM(SalesData) in both workbooks to get the total sales for each month, or you can create a pivot table or a chart to compare the sales by product or by region.