How To Pull Data From Another Sheet in Excel

Do you need to pull data from another sheet in Excel?

This is a simple task users encounter when managing multiple sheets in a single Excel workbook. Excel offers several simple functions that can streamline this process. With just a few clicks or formulas, you can easily access and update data between sheets.

Let's show you how to pull data from another sheet in Excel.

Pulling Data Using Direct Cell References

You can pull data from another sheet in the same worksheet using Direct Cell Reference. Here's how to reference data step by step:

1. Open the Excel file: Open the Excel file with multiple sheets. For this example, let's use the file with two sheets: "Products" and "Sales."

2. Go to the destination sheet: Navigate to the sheet where you want to pull data. For instance, go to the "Products" sheet to pull data from the "Sales" sheet.

3. Select the destination cell: Click on the cell where you want the data to appear. For example, click cell D1 in the "Product" sheet.

4. Type the formula: In the selected cell, type an equal sign (=), followed by the source sheet's name, an exclamation point (!), and the cell reference from the source sheet.

Example: To pull the Revenue data from "Sales" to "Products," type =Sales!C1:C5 into cell D1.

5. Press Enter: Once you press Enter, Excel will pull the data from column C in the "Sales" sheet and display it in column D in the "Products" sheet.

Pulling Data Using the VLOOKUP Function

The VLOOKUP function helps retrieve data based on a matching value. Learn how to use it below:

Scenario:

You have two sheets:

• "Products" with product details (Product ID, Product Name, and Price).

• "Sales" with sales data (Product ID and Quantity Sold). You want to pull the Product Name from the "Products" sheet into the "Sales" sheet based on the Product ID.

Steps:

1. Open the Excel file: Open the Excel file containing both sheets ("Products" and "Sales").

2. Go to the destination sheet: Navigate to the sheet where you want to pull data. In this example, go to the Sales sheet.

3. Select the destination cell: Click on the cell where you want the pulled data to appear. For example, click cell D2 in the Sales sheet, where you want to display the Product Name based on the Product ID.

4. Enter the VLOOKUP formula: In the selected cell, type the following VLOOKUP formula:

=VLOOKUP(A2,Products!A:B,2,FALSE)

Here’s what each part of the formula means:

• A2: This is the lookup value, i.e., the Product ID in the Sales sheet.

• Products!A:B: This is the table array, i.e., the range of cells from the Products sheet where Excel will look for data. In this case, it looks in columns A and B (Product ID and Product Name).

• 2: This is the column index number in the table array from which to return the value. Since Product Name is in the second column, the index is 2.

• FALSE: This ensures an exact match for the Product ID.

5. Press Enter: Once you press Enter, Excel will pull the Product Name corresponding to the Product ID from the Products sheet and display it in cell C2 of the Sales sheet.

6. Drag the formula down: To apply the same formula for all rows, use the fill handle by clicking and dragging down from the corner of cell C2. This will update the formula for each row, pulling the correct Product Name based on the Product ID.

Retrieving Data Using the INDEX and Match Function

The INDEX and MATCH combination is more flexible than VLOOKUP. It allows you to search for a value in any column (not restricted to the first column) and pull data based on more complex criteria.

Scenario:

You have two sheets:

• "Products" with columns: Product ID (Column A), Product Name (Column B), and Price (Column C).

• "Sales" with columns: Product ID (Column A) and Quantity Sold (Column B). You want to pull the Product Name from the Products sheet based on the Product ID in the Sales sheet.

Steps:

1. Open the Excel file: Open the Excel file containing the "Products" and "Sales" sheets.

2. Go to the destination sheet: Navigate to the Sales sheet, where you want to pull data. Click on the cell where you want the pulled data to appear. For this example, let’s say you want to pull the Product Name for each Product ID. So, select C2 in the Sales sheet.

3. Start by writing the MATCH formula: The MATCH function finds the position of a value in a column. In this case, it will find the row where the Product ID in the Sales sheet matches the Product ID in the Products sheet. In cell D2 of the Sales sheet, type the following formula:

=MATCH(A2,Products!A:A,0)

• A2: The lookup value (Product ID in the Sales sheet).

• Products!A

  : The column in the Products sheet where you want to find the match (Product ID column).

• 0: This ensures an exact match.

4. Press Enter. This will return the row number where the Product ID in A2 of the Sales sheet matches the Product ID in the Products sheet.

5. Combine MATCH with INDEX: The INDEX function returns the value from a specified row and column in a range. You will use MATCH to find the row, and INDEX will use this information to return the corresponding Product Name. In cell C2 of the Sales sheet, combine the Excel functions with this formula:

=INDEX(Products!B:B,MATCH(A2,Products!A:A,0))

Here’s what each part means:

• Products!B: This is the column you want to pull data from (in this case, the Product Name column from the Products sheet).

• MATCH(A2,Products!A,0): This looks for the Product ID in the Products sheet and returns the corresponding row number.

6. Press Enter: Press Enter, and Excel will display the Product Name corresponding to the Product ID in A2 of the Sales sheet.

7. Drag the formula down: Click on the fill handle in the corner of C2 and drag it down to apply the formula to all rows. This will pull the Product Name for each Product ID in the Sales sheet.

Data Import Through Power Query

Power Query is useful when pulling and manipulating large datasets from multiple sheets or workbooks. It’s a dynamic way to update your data without manually copying and pasting.

1. Open your Excel file: Open the Excel file with multiple sheets, including the "Products" and "Sales" sheets.

2. Go to the Data tab: Click on the Data tab in the Excel ribbon.

3. Select Get Data from Workbook (for the same workbook): If you're pulling data from the same workbook, select Get Data → From Other Sources → Blank Query.

4. Open Power Query Editor: This will open the Power Query Editor. Here, you will see a new window with the query editor interface.

5. Pull data from the source sheet: In the Power Query Editor, go to Home → Advanced Editor. Type the following formula to load data from the "Products" sheet:

= Excel.CurrentWorkbook(){[Name="Products"]}[Content]

6. Load data into Power Query: Press Done to load the data from the "Products" sheet into the Power Query Editor.

7. Transform or filter data (Optional): Power Query allows you to filter or transform data before loading it. For example, you can filter by product category, remove unnecessary columns, or rename headers. If no changes are needed, move to the next step.

8. Close & load to Excel: Once the data is loaded in Power Query, click Close & Load → Close & Load To. Choose where you want to load the data. You can load it as a new sheet or add it to the existing sheet (like "Sales").

9. Load data into the sales sheet: Choose to load the data into the Sales sheet (or any other sheet you want) as a table or a connection.

10. Link data: Now that your data is loaded into the "Sales" sheet, whenever the source (the "Products" sheet) changes, you can refresh the data in the "Sales" sheet automatically. Simply right-click on the table in the "Sales" sheet and choose Refresh to update the data.

How To Combine Data From Multiple Sheets With Consolidation

You can combine data from multiple worksheets into one using the Consolidate feature in Excel. This method is useful when you have similar data (like sales figures, expenses, or inventory) spread across different worksheets and need to combine them into a summary sheet. Here’s how you can do it step by step:

Scenario:

You have multiple worksheets (e.g., "Sheet1," "Sheet2," "Sheet3") containing sales data, and you want to combine this data into one summary sheet.

Steps:

1. Open your workbook: Open the workbook containing all the sheets you want to consolidate.

2. Go to the consolidation sheet: Create or select the sheet to consolidate the data. This will be your summary sheet (e.g., “Summary”).

3. Select the consolidation tool: Go to the Data tab in the Excel ribbon. In the Data Tools group, click on Consolidate.

4. Choose the consolidation function: In the Consolidate dialog box, choose the function you want to use to combine your data.

   • Sum: Adds the values together (common for financial data).

   • Average: Takes the average of the values.

   • Count: Counts the number of entries.

   • Other options include Max, Min, Product, etc.

5. Add the data ranges from each sheet: Click the range selector icon in the Reference box. Switch to the first worksheet (e.g., "Sheet1"), select the data range (e.g., A1), and click Add. Repeat the process for all other worksheets (e.g., "Sheet2" and "Sheet3"). After selecting each range, click Add to include it in the consolidation.

   Tip: Ensure the data structure (e.g., column headers and formats) is the same across all sheets for a successful consolidation.

6. Click OK: Once you've added all ranges and selected your options, click OK. Excel will consolidate the data from the multiple worksheets and display it in your summary sheet.

Final Thoughts

Pulling data from another Excel sheet can be easily done using various methods like direct cell references, VLOOKUP, INDEX-MATCH, Power Query, or the Consolidate feature. Each method offers flexibility depending on your specific needs. So, take your time to master the best option.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ

1. Can I pull data from multiple sheets at once in Excel?
Yes, you can use the Consolidate feature or Power Query to pull data from multiple sheets. These tools allow you to combine data from different worksheets into one summary sheet.

2. How do I update pulled data when the source sheet changes?
If you use formulas like VLOOKUP or INDEX-MATCH, the data updates automatically when the source changes. For Power Query, simply right-click the data table and select Refresh.

3. Is there a way to pull data from another workbook?
Yes, you can use the same methods like direct cell references or VLOOKUP, but include the file path in the formula (e.g., '[WorkbookName.xlsx]SheetName'). Make sure both workbooks are accessible so the links function properly.

Related Articles

How to Insert Bullets in Excel

How to Capitalize in Excel

How to Print Gridlines in Excel