Free All-in-One Office Suite with PDF Editor
Edit Word, Excel, and PPT for FREE.
Read, edit, and convert PDFs with the powerful PDF toolkit.
Microsoft-like interface, easy to use.
Windows • MacOS • Linux • iOS • Android
Catalog
How to Make References to Other Files in Excel with Formula
When calculating data in Excel, you may often find yourself in a situation when you need to pull data from another worksheet or even from a different Excel file. Can you do that? Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.
External reference in Excel is a reference to a cell or a range of cells outside the current worksheet. The main benefit of using an Excel external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.
Simple Methods to Reference Cells in Other Sheets
1. Using "= reference a cell
Step 1 Click the cell in which you want to enter the formula.
Step 2 To reference cell A2, you can enter "=A2" in a different cell. This will display the value of cell A2 in the referenced cell.
Step 3 To reference a range of cells, such as A2 to A6, you can enter "=A2:A6" in a different cell. This will display the values of the specified range in the referenced cell.’
2. Using a cell reference across multiple sheets
Step 1 Choose the cell where you wish to enter the formula.
Step 2 Enter = (equal sign) in the formula bar
Step 3 Enter! followed by the cell reference of the cell to be pulled. Then press Enter
The value from the other sheet will now be shown in the cell.
Advanced Techniques for Pulling Data from Another Sheet
VLOOKUP Function: Definition and Use Cases
Definition:
The VLOOKUP function in Excel is a powerful tool for searching and retrieving specific information from a table or range of data. It stands for "vertical lookup" and is primarily used to find a value in the leftmost column of a table and return a corresponding value from a specified column.
Use Cases:
In "Sheet 1," we have a table with a "Code " column, and in "Sheet 2," we have the corresponding "Product ID" column. The goal is to populate the "ID" column in "Sheet 1" with data from "Sheet 2" based on the matching "Code."
Our aim is to copy data from the second worksheet's "ID" column and paste it into the first worksheet's "ID" column. This will be done for each individual Product.
To retrieve values from another worksheet, use the following steps:
Cell C2 should be selected and clicked.
Insert the following formula: =VLOOKUP(B2,Sheet 2!$D$2:$E$6,2,0)
By clicking and dragging the small "+" button at the bottom-right of the cell, you may move the formula down to the other cells in the column.
INDEX and MATCH Functions: Advantages over VLOOKUP
The INDEX and MATCH functions offer several advantages over the VLOOKUP function in Excel. Here are some key benefits:
Flexibility in retrieving data in both vertical and horizontal directions
Ability to use multiple lookup criteria for more precise data retrieval
Non-sequential lookup capability, not limited to leftmost column
Effective handling of dynamic ranges, adjusting formulas automatically
Better error handling, providing alternative results or error messages when lookup value is not found
Example:
To refer to a cell or range of cells in another worksheet in the same workbook, use the worksheet name followed by an exclamation point before the cell address.
To refer to cell A1 in Sheet2, for example, type Sheet2!A1.
Using INDIRECT for Dynamic Sheet Referencing
INDIRECT attempts to analyze text as a worksheet reference. This enables the creation of formulae that concatenate a reference as text and utilize the resultant text as a valid reference.
Referencing to Other Files Workbook in Excel
When creating external references to another workbook in Excel, there are two scenarios to consider: referencing to an open workbook and referencing a closed workbook.
External reference to an open workbook:
Format: [Workbook_name]Sheet_name!Cell_address
Example: =SUM([Sales.xlsx]Jan!B2:B5)
Usage: Use this format when the source workbook is open. Include the workbook name in square brackets, followed by the sheet name and the referenced cell or range of cells.
External reference to a closed workbook:
Format: Drive_path[Workbook_name]Sheet_name!Cell_address
Example: =SUM(D:\Reports[Sales.xlsx]Jan!B2:B5)
Usage: Use this format when the source workbook is closed. Specify the complete path to the workbook, including the drive letter and folder location.
Tips for Efficient Data Pulling in Excel
Keyboard Shortcuts to Ease the Process
Copy selected cells: Ctrl+C - Copies selected cells to the clipboard.
Cut selected cells: Ctrl+X - Cuts selected cells and places them on the clipboard.
Paste copied or cut cells: Ctrl+V - Pastes copied or cut cells from the clipboard.
Fill down: Ctrl+D - Copies content from the cell above and fills it down.
Fill right: Ctrl+R - Copies content from the cell to the left and fills it right.
AutoSum: Alt+= - Inserts the SUM function for the selected range.
Flash Fill: Ctrl+E - Automatically fills data based on established pattern.
Filter: Ctrl+Shift+L - Applies or removes filter from the selected range.
Go to Special: Ctrl+G, then press S - Opens "Go To Special" dialog for selecting specific cell types.
Sort: Alt+D, then press S - Opens Sort dialog to specify sorting options.
Frequently Asked Questions
Can I Pull Data from a Sheet in a Different Workbook?
Yes, you can pull data from a sheet in a different workbook by using external references or linking formulas. This allows you to access and retrieve data from another workbook seamlessly.
How Can I Combine Data from Multiple Sheets into One?
You can combine data from multiple sheets into one by using consolidation techniques such as using the Consolidate feature, writing formulas to reference data from different sheets, or using Power Query to merge and transform data.
What Is the Role of the INDIRECT Function in Pulling Data from Another Sheet?
INDIRECT function is a tool that enables you to maintain a fixed reference to a specific cell, cell range, or cell from another sheet. It ensures that the references remain unchanged, even if you modify the structure of your spreadsheet by adding or deleting rows or columns. This provides greater flexibility and accuracy when working with dynamic data and formulas in Excel, allowing you to confidently retrieve and manipulate information without worrying about reference changes
What's the Difference Between the VLOOKUP, INDEX, and MATCH Functions in Excel?
VLOOKUP:
Retrieves a value based on a specific criteria.
Searches for the value in the leftmost column of a table.
Provides a simple lookup solution.
INDEX:
Retrieves a value based on specified row and column numbers.
Allows for more flexible data retrieval from anywhere within a table.
MATCH:
Finds the position of a value within a range or column.
Useful for determining the relative position of a value in a dataset.
Summary
In summary, this guide provides step-by-step instructions on how to efficiently pull data from another sheet in Excel. By utilizing functions like VLOOKUP, INDEX-MATCH, and INDIRECT, users can retrieve data accurately and save time. The article also covers techniques for referencing cells within and across multiple sheets, along with helpful keyboard shortcuts for faster data pulling.
With the use of WPS Office, users can seamlessly apply these methods and enhance their data management experience. Say goodbye to manual copying and pasting and embrace the efficiency of WPS Office for streamlined data retrieval.
Also Read:
- 1. How to make Excel Formula Sheet Name in Excel
- 2. How to Make an Assignment Spreadsheet in WPS Office (Make Your Life More Efficient)
- 3. How to save files to My Cloud Files?
- 4. How to Use Excel Formula IF Cell Contains Text in Excel
- 5. Create formula with structured references in WPS Office Excel
- 6. How to make First Name and Last Name in Excel Using Formula