How to Reference Data from Another Sheet or Workbook

Data referencing is one of the features that differentiate Excel from the sea of spreadsheet programs. Take, for instance, you’re performing calculations on a sheet of paper. You would not want to show all the rough work on the final sheet with the finalized data.

Data referencing is exactly that where you can extract data from a different sheet or workbook to your final sheet. I cannot stress this enough when I say Excel has made it really convenient by allowing referencing across different workbooks. This has greatly improved the presentation of your document without compromising calculations. 

Referencing is not only used to import data from the destination cells but professionals mostly utilize it to reference data from another sheet to create Charts, Objects, and PivotTables. In this article we’ve gone through all of the methods you can reference data, so keep reading!

Reference Data to Cells

Referencing data from a different worksheet or a workbook works exactly like how you would reference data from a cell in the current sheet. The difference is that you need to specify the name of the sheet and the range. You can simplify this process by naming your range and your worksheet. This way you will leave less room for error.

In this example, we’ll be referencing a cell from a different workbook. You can either use a formula in the format ='[(name of workbook).xlsx](sheet name)'!(referenced cell/range).

I personally find this method tedious as I need to be extra weary of all the syntax errors I could make while entering the formula. So instead, I first open both, the source and destination workbooks.  In the destination workbook, I select the cell I want to insert my reference in and enter ‘=’.

Then, I open the source workbook and simply click on the cell with the data I wish to reference. This will automatically insert the reference in our destination workbook. How cool is that?

Use Links to Reference Data

You can also use links in your spreadsheet if you want to reference the entire worksheet or workbook. Excel will display a link any user can click to access the other worksheet until it’s unprotected. In addition, you can also set a different display name to make your link look more presentable.

1. Open your destination worksheet and head to the Insert tab.
2. In the Links section, click Link.
   
3. Browse your file and select it.
4. If you wish to change the display name, enter your new display name next to Text to display.
   
5. Click OK.

Reference Data from a Different Source In Charts

It isn’t necessary to have the data in your current worksheet in order to create a chart in Excel. You can have the values in either a different worksheet in the workbook or even on a completely different workbook.

I usually use referencing to select values in a different worksheet while creating a chart in an empty worksheet. This way when I print my chart, I do not have to go through the extra hassle of hiding the columns with the data I used for referencing.

1. Open both the source and destination workbooks.
2. In the destination worksheet, head to the Insert tab.
3. Select a chart of your choice in the Charts section.
   
4. Right-click on your chart and select Select Data.
   
5. Select the arrow icon next to the Select data range section.
   
6. Open the source worksheet/ workbook and click on the range you wish to reference.
   
7. Click OK in the window.

Place Workbooks as Objects

You can place external workbooks as icons in your worksheet. When a user double-clicks on the icon, the workbook will open, allowing them to access the data in the embedded workbook.

1. Head to the Insert tab and select Objects.
   
2. Switch to Create from File tab.
3. Click on the Browse button and select your file.
   
4. Check the box next to Link to file or Display as icon, depending on how you want to insert your object.
5. Click OK.

Reference Data in PivotTables

Pivot Tables are great for preparing a final summary of a data table. If you have a data range with a huge number of rows and columns, it wouldn’t make sense for you to embed the PivotTable in your current worksheet. Excel allows you to refer to all tables from open workbooks to create PivotTables.

1. Open your source and destination workbooks.
2. Head to the Insert tab.
3. Click PivotTable from the Tables section.
   
4. In the Create PivotTable window, select the box next to Use an external data source > Choose connection.
   
5. Head to the Tables tab.
6. From the window, locate your workbook then select the table you wish to reference data from.
   
7. Select Open > OK.