Can Excel pull data from another sheet or cell? Yes, and it’s fairly straightforward. You can use it simply by using cell references, using advanced menu items, or using a LOOKUP function. This guide will cover each method with step-by-step instructions and screenshots. Read on to learn more.
Table of Contents
In Short: The Easiest Way to Pull Data From Another Sheet in Excel
All you have to do is use a cell reference that contains the sheet name to pull data from a different sheet.
In an empty cell, type:
=Sheetname!Cellrefference
and substitute the Sheetname parameter for the sheet name and the cell reference parameter for the cell reference. For example, if you wanted to pull data from a sheet named Sheet1 and from the cell reference A2, you would simply type the following into an empty cell:
=Sheet1!A2
How to Get Data From Another Sheet in Excel Using Cell References
This is one of the simplest ways of pulling data in, as you only have to use a simple formula to pull data from another sheet or cell. You can specify one cell as the ‘Master’ cell, and the other cells can pull the values from that one cell.
To demonstrate this, we have two worksheets: Sheet1 is the one from which we wish to copy the data, and Sheet2Â is where we will add the data. Here is how to pull data from a sheet to another:
- Open the workbook and navigate to the worksheet where you want to insert the data.
- Click on the cell where you want to add the formula and add an Equals (=)Â sign to initiate a formula.
- Enter the sheet name from which you want to pull the data. Make sure the spellings match the sheet’s name. If there is a space in the sheet’s name, enclose it in quotation marks. In this case, we are going to write Sheet1.
- Add an Exclamation Mark (!)Â symbol to specify that the referenced cell is in a different worksheet.
- Write the reference in the master sheet where you want to get data. In this case, we are going to write it as A2.
- Press the Enter button to execute the formula and pull data from another sheet in Excel.
The values from the specified cells will now be displayed in the cell. If you’re looking to pull data for multiple values, then click and select the cell containing the formula and click and drag the square towards the lower right corner of the cell. Drag it to fill the rest of the cells and let go of the cursor.
Before we look at the other ways to pull data from another sheet, let’s settle the debate between Sheets and Excel and discuss which one is better for you to use in 2024.
How to Pull Data From Another Sheet in Excel Based on Cell Value
Suppose you want to pull data from a different sheet based on criteria. In that case, you can use Excel’s Advanced Filter feature. Here is how to use Excel to populate data from another sheet based on the cell value:
- Open Excel and head to the worksheet where you want to pull data from another sheet based on criteria.
- Select any empty cell and enter the cell value you wish to look for. We want to look for Amanda Fisher in the Name column in this example. Make sure that the header matches the name of the cell.
- With the criteria value defined, click on Data in the main toolbar and then click on Advanced in the Sort & Filter section.
- This will open the Advanced Filter window. Here click the Up arrow to add the values for the cell ranges. Here is a guide on what to select for all the options:
- Action: Here, you can define what to do with the filtered data. You can choose to filter the data in the location of the original table, or you can copy the filtered values to a different location. For this example, we will choose the Copy to another location option.
- List Range: This parameter is the cell range that contains the data you wish to filter. In this case, the data is in another worksheet and is written as Sheet1!$A$1:$B$11.
- Criteria Range: In this parameter, you can add the cell range for the cell value you want to use to filter the data. The function will check for an exact match if you enter a specific value directly into the textbox. You can also add the address containing the criteria value. In this case, the cell range is Sheet2!!$F$1:$F$2.
- Copy To: This parameter is only available if you choose the Copy to another location option in the Action section. Here, you can specify the destination cells where you wish to paste the filtered data. In this example, we want to paste the data to Sheet2!$A$1:$B$11.
- With the parameters for the function selected, click the OK button to execute the function.
The filtered data will start to populate the cells defined in the function.
Do you use Google Sheets and Microsoft Excel for managing spreadsheets? If the answer is yes, then check out how to convert Sheets to Excel and vice versa
How to Insert Data From Another Sheet in Excel Using VLOOKUP
This method for pulling data from another sheet in Excel is very similar to the previous one, but it uses the VLOOKUP formula instead of the built-in Excel function. This method also allows you to paste the values directly into the spreadsheet. Here is how to insert data from another sheet in Excel using VLOOKUP:
- Open the worksheet where you want to apply the VLOOKUP formula and click on the cell where you want to execute the formula to get data from another sheet.
- Write the initial part of the VLOOKUP formula in the cell, which is =VLOOKUP(.
- Write the first parameter, the value you wish to find in the other worksheet. In this case, it’s the corresponding Name value, which is A2. Add a comma to separate the parameters.
- Write the second parameter, the range from which you want to look up the value. In this case, the range is in a different spreadsheet, so first, we have to enter the sheet’s name and enter an Exclamation Mark (!). Then, enter the cell range in the sheet. The complete parameter, in this case, becomes Sheet1!A1:B11. Add another comma to separate the parameters.
- Now, add the third parameter, which defines the column number from which you wish to get the value. In this case, it is column 2.
- Finally, add the last parameter, defining whether you want an exact match. We wish to return the exact value, so we write 0 here.
- Add a bracket to close off the formula, and press Enter to execute it.
Note: This method will also work if you use HLOOKUPÂ or XLOOKUP.
Frequently Asked Questions
How Do I Automatically Pull Data From One Sheet to Another?
There are various ways in which you can pull data from another sheet. One of these is by using the VLOOKUP formula, where you can specify the value you want to look for and the cell range where you want the values to appear. The formula also automatically recalculates, meaning if any values are updated, they are going to be updated for the other spreadsheet as well.
How Do I Link Data From Sheet1 to Sheet2 in Excel?
The easiest way to link sheets is by creating a simple formula and adding the reference to the other worksheet. Write an Equals (=) sign to start the formula and add the sheet’s name. Now, add an Exclamation Mark (!) to specify the sheet’s name and then add the cell range you wish to pull the value.
Wrapping Up
Now we’ve shown you how to pull data from another sheet in Excel in all the possible ways. Now that we have discussed all the ways in which you can pull data from another sheet in Excel, here is a guide on how to reference another sheet in Google Sheets.
If you found this guide useful and you’d like to support our site, consider using one of the templates from our premium library. You can use the code SSP to save 50% on any purchases you decide to make.
Related:
