How to extract data from another worksheet in Excel?

How to use cell references to pull data from another worksheet in Excel

You can use related cell references to pull data from one Excel worksheet to another. This is a simple way to get data from one worksheet to another.

To use cell references in Excel to extract data from another worksheet:

1. Click the cell where you want the extracted data to appear.
2. Type = (equal sign), followed by the name of the worksheet from which you want to extract data. If the worksheet name is longer than one word, enclose the worksheet name in single quotes.
   
3. Enter ! Followed by the cell reference of the cell to be pulled.
   
4. Press Enter.
5. Values ​​from other worksheets will now appear in the cells.
   
6. If you want to pull out more values, select the cell and hold down the small square in the lower right corner of the cell.
   
7. Drag down to fill the remaining cells.
   

There is an alternative that saves you from having to enter the cell reference manually.

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

1. Click the cell where you want the extracted data to appear.
2. Type = (equal sign) and open the worksheet from which you want to extract data.
3. Click on the cell containing the data you want to extract. You will see the formula change to include a reference to this cell.
   
4. Press Enter key and the data will be pulled into your cell.
   

How to use VLOOKUP to extract data from another worksheet in Excel

If you don’t plan to do much with the data and just want to into a new worksheet, the above method works well. However, if you start manipulating the data, some problems arise.

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

However, in the Sales Summary table, only the extracted data changes the order. The other columns will remain the same, meaning sales will no longer be aligned with the correct salesperson.

You can solve these problems by using the VLOOKUP function in Excel. Rather than pulling the value directly from the cell, this function pulls the value from the table that is in the same row as the unique identifier, such as a name in our example data. This means that even if the order of the original data changes, the pulled data will always remain the same.

To use VLOOKUP to extract data from another worksheet in Excel:

1. Click on the cell where you want to display the extracted data.
2. Type =VLOOKUP( Then click the cell on the left. This will be the reference that the VLOOKUP function will look for.
   
3. Type a comma, Then click the worksheet you want to extract the data from. Click and drag the two columns that contain the data.
   
4. Type another comma and then type the number of the column that contains the data you want to extract .In this case, it's the second column, so we enter 2.
5. Type another comma, then FALSE, and then the final closing bracket to complete your formula. This ensures that the function finds an exact match for your reference.
   
6. Press Enter. Your data will now appear in your in the cell.
7. If you want to pull out more values, select the cell and click and hold the small square in the lower right corner of the cell.
   
8. Towards Drag down to fill the remaining cells.
   
9. Now, if you sort the original data, the data you extract won't change because it will always look for each individual The data associated with the name.

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

Make Excel work for you

There are hundreds of Excel functions to help you get your work done quickly and easily. Knowing how to extract data from another sheet in Excel means you can say goodbye to endless copying and Paste.

However, the function does have its limitations. As mentioned before, this method only works if your identification data is in the first column. If your data is more complex, you will need to consider using another Functions such as INDEX and MATCH.

The above is the detailed content of How to extract data from another worksheet in Excel?. For more information, please follow other related articles on the PHP Chinese website!