How to Reference Data from Another Sheet or Workbook

    AsmiBy Excel 4 Mins Read

    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.

    Referenced Value

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

    Referencing

    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 ‘=’.

    Select Range for Referencing

    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.
      Insert Link Excel
    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.
      Embedded link text to display excel
    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.
      Excel charts
    4. Right-click on your chart and select Select Data.
      Select Data for Chart
    5. Select the arrow icon next to the Select data range section.
      Select Range while creating chart
    6. Open the source worksheet/ workbook and click on the range you wish to reference.
      Select Range
    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.
      Object Excel
    2. Switch to Create from File tab.
    3. Click on the Browse button and select your file.
      Browse for file Excel objects
    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.
      Excel PivotTable
    4. In the Create PivotTable window, select the box next to Use an external data source > Choose connection.
      Create PivotTable
    5. Head to the Tables tab.
    6. From the window, locate your workbook then select the table you wish to reference data from.
      Create PivotTable Excel
    7. Select Open > OK.

    Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

    Add A Comment