How to Pull Data from Another Sheet in Excel (Easily)

Got your data sitting in one sheet, and now you want to pull the same to another Excel sheet.

Don’t tell me you’ve resorted to copying and pasting it between both the sheets manually.

Because if you’re doing so, you’re not making the best use of the new and dynamic Excel.

While you’d have to spend a great deal of time pulling data manually, plus you’ll have to update it every time the data in the source sheet changes. And obviously, let’s not forget the big risk you’re running of manual errors (in moving data between sheets).

To help all of these problems, you can simply add links to the source data in your sheet. How to do that?🤔

Download your free practice workbook for this guide here and join me along until the end of this guide.

Table of Contents

Pull data from another sheet in Excel

How do you pull data from another sheet in Excel? It’s so easy you’ll love it.

For instance, I have some data here in Cell B2 of Sheet2 of my book.

To pull it to another sheet (Sheet1):

Step 1) Go to the destination cell of Sheet1 (where you want the data pulled).

Step 2) Insert an equal to (=) sign.

Step 3) Write the cell reference for the cell to be pulled from the other sheet as follows:

So, for Cell B2 from Sheet2, this becomes:

Step 4) Press Enter.

And Cell B2 from Sheet 2 will be linked. This brings the data from this cell to Sheet1.

Pro Tip!

This format of sheet reference only works if the Sheet Name doesn’t have any non-numeric or space characters in it.

But if the Sheet Name has any such characters like ‘Sheet 2’ has a space character then this name has to be enclosed in single quotation marks as follows:

Apart from manually entering the sheet reference, another approach that I find preferable is to let Excel directly pick up the cell reference.

Step 1) Go to the destination cell.

Step 2) Enter the equal to (=) sign.

Step 3) Go to the source sheet (from where the cell is to be linked). Sheet2 in our case.

Step 4) In the source sheet, go to the cell to be linked. Cell B2 in our case.

See in the formula bar how Excel automatically picks up the sheet and cell reference of the designated cell.🤯

Step 5) Stay on the same source sheet and press Enter.

There you go! Excel pulls the data from Cell B2 of Sheet2.

Just like a single cell, you can also pull multiple cells (or a cell range) from another sheet by selecting the cell range to be pulled.

Like here, I have pulled the data in Cells B2:D4 from Sheet2:

This is how you can pull data from any other sheet of your Excel workbook.

Best Part: The data will automatically update in the destination sheet as you update the source sheet.🤩

For example, if I change the data in Cell B2 of Sheet2:

The changes will be automatically updated in Sheet1 (the destination sheet):

No worries if the data changes time, all your records will update in real-time with absolutely no manual data entry errors.

Pull data from a sheet of another workbook in Excel

Just like above, you can also pull data from another workbook in Excel.💡

For example, I want to pull data in Cell B2 of Sheet2 of Book2 (the sourcebook) to Book1 (the destination book).

To do that:

Step 1) Go to the destination book.

Step 2) Activate the cell where you want the data pulled.

Step 3) Enter an equal to (=) sign in this cell.

Step 4) Toggle to the sourcebook.

Step 5) Navigate to the cell from where you want the data pulled.

Step 6) Press Enter.

The activated cell in the source workbook will automatically create a link for the navigated cell and pull data from it.😎

Now as you make any changes to the source cell, the destination cell will be automatically updated.

However, make sure both the workbooks are active at the time of making changes for the destination cell to be automatically updated.

If either of the workbooks is closed, the changes will not be automatically adapted.

Pro Tip!

You can also write the reference to another book manually using the below format:

‘[WorkbookName]SheetName’!CellReference

However, this method runs the risk of making manual errors as you will manually type in the name of the workbook and then the sheet.

It’s better to use the option to toggle to the relevant sheet and let Excel pick up the reference from another workbook automatically.

Pull data from another sheet or book based on a cell value

Once you’ve understood the science of pulling data from another sheet or workbook by simply referencing cells, let me show you how you can leverage the same to pull data from another sheet or workbook based on a cell value.

Except for the case where you only want to add a cell reference to another sheet or workbook to create a direct reference, the method is more handy when you want to write up a formula to fetch the relevant values from data placed in a different sheet or different workbook 🚀

And this we’ll do using the all-time dynamic VLOOKUP function of Excel.

In one of my sheets, I have the names and other information for a list of my contacts maintained.

I want to fetch the addresses for these contacts too. However the address dataset for these contacts are populated in another Excel spreadsheet 💭

To automate the process of pulling addresses for all these contacts, I will use VLOOKUP as follows:

Step 1) Write the following formula for VLOOKUP.

First argument of the VLOOKUP syntax refers to the lookup value (the name of contact).

Step 2) As the second argument, refer the lookup array where the value is to be looked up. Since our lookup array sits in another sheet, go to that sheet and drag the selection box around those cells.

Step 3) Stay on the same sheet (where the lookup value is located).

Step 4) Type in the col_index_num from where you want the value to be returned. Since addresses are in the second column, this will be 2 for us.

Step 5) Change the cell reference for the lookup array into an absolute reference by taking your cursor to it (in the formula) and pressing the F4 key.

Step 6) Add 0 (as we want an exact match) as the match mode argument and close the formula.

Step 7) Press enter to see VLOOKUP extract data from another sheet ⚒

Step 8) Drag this formula down the whole list to have the same extracted for all contacts.

How smooth is that?

You can similarly set up a VLOOKUP formula for that to pull data based on a cell value from other sheets.

And if the data to be looked up resides in another workbook instead, just change the reference to the lookup array by going to the relevant sheet from that workbook and selecting the relevant cells.

VLOOKUP will fetch data from the other book for you as seamlessly as it ordinarily does 🤝

However, something you must take note of is that if you make any changes to the source workbook, the fetched values in the destination workbook won’t update until both the books are simultaneously opened.

Conclusion

This explains how you can directly link data from other sheets and other workbooks into your sheet in Microsoft Excel.

It not only rids you of the need to update the data whenever there are changes to the source data but also makes the data entry free of manual copy-pasting errors.

If you enjoyed learning to link data across sheets in Excel, you’d enjoy learning the following Excel tutorials by Spreadsheeto, too.

• How to Reference Tab & Sheet Names in Excel Formulas
• How to Duplicate a Sheet in Excel (3 Methods)
• How to Hide Worksheets and Tabs in Excel (With Password)