How to Pull Data from Another Sheet in Excel

How to Pull Data from Another Sheet in Excel

If your Excel spreadsheet has a lot of data, consider using different sheets to organize them. To pull data from another sheet in Excel, follow this guide.

Excel doesn’t just let you work in one spreadsheet—you can create multiple sheets within the same file. This is useful if you want to keep your data separated. If you were running a business, you could decide to have sales information for each month on separate sheets, for example.

What if you want to use some of the data from one sheet in another, however? You could copy and paste it across, but this can be time-consuming. If you make changes to any of the original data, the data you copied across won’t be updated.

The good news is that it’s not too tricky to use the data from one sheet in another. Here’s how to pull data from another sheet in Excel.

How to Pull Data From Another Sheet in Excel Using Cell References

You can pull data from one Excel sheet to another by using the relevant cell references. This is a simple way to get data from one sheet into another.

To pull data from another sheet by using cell references in Excel:

1. Click in the cell where you want the pulled data to appear.
2. Type = (equals sign) followed by the name of the sheet you want to pull data from. If the name of the sheet is more than one word, enclose the sheet name in single quotes.
3. Type ! followed by the cell reference of the cell you want to pull.
4. Press Enter.
5. The value from your other sheet will now appear in the cell.
6. If you want to pull across more values, select the cell and hold the small square in the bottom-right corner of the cell.
7. Drag down to fill the remaining cells.

There is an alternative method that saves you from having to type in the cell references manually.

To pull data from another cell without typing the cell reference manually:

1. Click in the cell where you want the pulled data to appear.
2. Type = (equals sign) and then open the sheet from which you want to pull data.
3. Click on the cell containing the data that you want to pull across. You’ll see the formula change to include the reference to this cell.
4. Press Enter and the data will be pulled into your cell.

How to Pull Data From Another Sheet in Excel Using VLOOKUP

The method above works well if you’re not planning to do much with your data and just want to put it into a new sheet. However, there are some issues if you start to manipulate the data.

For example, if you sort the data in the July Sales sheet, the names of the sales team will also be rearranged.

However, in the Sales Summary sheet, only the pulled data will change order. The other columns will remain the same, meaning that the sales are no longer aligned with the correct salesperson.

You can get around these issues by using the VLOOKUP function in Excel. Instead of pulling a value directly from a cell, this function pulls a value from a table that is in the same row as a unique identifier, such as the names in our example data. That means that even if the order of the original data changes, the data that is pulled will always remain the same.

To use VLOOKUP to pull data from another sheet in Excel:

1. Click in the cell where you want the pulled data to appear.
2. Type =VLOOKUP( then click on the cell to the left. This will be the reference that the VLOOKUP function will look for.
3. Type a comma, and then click on the sheet that you want to pull data from. Click and drag over the two columns that hold your data.
4. Type another comma, and then type the number of the column that contains the data you want to pull across. In this case, it’s the second column, so we would type 2.
5. Type another comma, then FALSE, then a final closed bracket to complete your formula. This ensures that the function looks for an exact match for your reference.
6. Press Enter. Your data will now appear in your cell.
7. If you want to pull across more values, select the cell and click and hold on the small square in the bottom right-hand corner of the cell.
8. Drag down to fill the remaining cells.
9. Now if you sort the original data, your pulled data will not change, since it is always looking for the data associated with each individual name.

Note that for this method to work, the unique identifiers (in this case, the names) must be in the first column of the range that you select.

Make Excel Work For You

There are hundreds of Excel functions that can take a lot of the grind out of your work and help you to do things quickly and easily. Knowing how to pull data from another sheet in Excel means you can say goodbye to endless copying and pasting.

Functions do have their limitations, however. As mentioned, this method will only work if your identifying data is in the first column. If your data is more complex, you’ll need to look into using other functions such as INDEX and MATCH.

VLOOKUP is a good place to start, however. If you’re having trouble with VLOOKUP, you should be able to troubleshoot VLOOKUP errors in Excel.

Leave a Reply

Leave a Reply