askvity

How Do I Extract Data From Different Sheets in Excel?

Published in Excel Data Extraction 5 mins read

You can extract data from different sheets in Excel by using formulas that reference cells in other sheets. Here's a breakdown of how to do it, covering both within the same workbook and from different workbooks:

Extracting Data from Different Sheets Within the Same Workbook

This is the most common scenario. You'll use the following structure in your formula:

=SheetName!CellReference

  • =: Starts the formula.
  • SheetName: The exact name of the sheet containing the data you want to extract. If the sheet name contains spaces or special characters, enclose it in single quotes (e.g., 'Sheet 1'!A1).
  • !: The exclamation mark separates the sheet name from the cell reference.
  • CellReference: The cell containing the data you want to extract (e.g., A1, B25, C10:C20).

Example:

To extract the value from cell B5 on a sheet named "SalesData", you would enter the following formula in the destination cell:

=SalesData!B5

If "Sales Data" (with a space) was the sheet name, the formula would be:

='Sales Data'!B5

Steps:

  1. Open the Excel workbook.
  2. Navigate to the sheet where you want to display the extracted data.
  3. Select the cell where you want to enter the formula.
  4. Type = to begin the formula.
  5. Type the name of the sheet containing the data, followed by an exclamation mark. (e.g., Sheet2!)
  6. Type the cell reference you want to extract. (e.g., Sheet2!A1)
  7. Press Enter. The value from the specified cell in the other sheet will now appear in the current cell.

Extracting Data from Different Sheets in Different Workbooks

Extracting data from different workbooks follows a similar principle, but the formula structure is slightly different and requires the full file path (or the workbook to be open).

=[WorkbookName]SheetName!CellReference or '[FilePath/WorkbookName]SheetName'!CellReference

  • =: Starts the formula.
  • [WorkbookName]: The name of the workbook containing the data, enclosed in square brackets. If the workbook is not open, you'll need the full file path.
  • SheetName: The name of the sheet within the workbook.
  • !: Separates the sheet name from the cell reference.
  • CellReference: The cell containing the data.

Example (Workbook Open):

If you want to extract data from cell C3 on the "Summary" sheet in a workbook named "Budget.xlsx" (and "Budget.xlsx" is currently open), you would use:

=[Budget.xlsx]Summary!C3

Example (Workbook Closed):

If "Budget.xlsx" is not open, you'll need the full file path. For example, if "Budget.xlsx" is located in C:\Documents\ExcelFiles\, the formula would be:

='C:\Documents\ExcelFiles\[Budget.xlsx]Summary'!C3

Steps (Workbook Open):

  1. Open both Excel workbooks (the source and the destination). This is crucial for a dynamic link.
  2. Navigate to the destination workbook and the sheet where you want the data.
  3. Select the cell where you want to display the extracted data.
  4. Type = to begin the formula.
  5. Type [ followed by the name of the source workbook, then ] (e.g., [Budget.xlsx]).
  6. Type the name of the sheet in the source workbook, followed by ! (e.g., [Budget.xlsx]Summary!).
  7. Type the cell reference you want to extract (e.g., [Budget.xlsx]Summary!C3).
  8. Press Enter.

Steps (Workbook Closed):

  1. Open the destination workbook.
  2. Navigate to the sheet where you want the data.
  3. Select the cell where you want to display the extracted data.
  4. Type = to begin the formula.
  5. Type ' followed by the full file path and workbook name inside square brackets, then ] (e.g., 'C:\Documents\ExcelFiles\[Budget.xlsx]').
  6. Type the name of the sheet in the source workbook, followed by ! (e.g., 'C:\Documents\ExcelFiles\[Budget.xlsx]Summary!').
  7. Type the cell reference you want to extract (e.g., 'C:\Documents\ExcelFiles\[Budget.xlsx]Summary!C3).
  8. Press Enter.

Important Considerations:

  • Updating Links: When the source workbook is closed, Excel may prompt you to update links when you open the destination workbook. Click "Update" to refresh the data.
  • File Paths: Ensure the file path is correct when referencing closed workbooks. If you move or rename the source workbook, the link will break.
  • Security: Excel may display a security warning about external data connections. You may need to adjust your security settings to allow these connections.
  • Error Handling: If you encounter errors (e.g., #REF!, #VALUE!), double-check the sheet name, workbook name, file path, and cell reference.

Related Articles