When working on a workbook, you may need to take data from other cells. It comes very handy with the reference formula to pull data from a cell, column or row.
The cross-workbook reference formula makes the work much easier, especially for the business and company. You can modify or update the data in a workbook and instantly get the changes in your other workbook.
When extracting data from another workbook, Microsoft has many powerful formulas and functions to give the user an advantage.
Though many people don’t know there’s a way to reference other files in a workbook, some of them find it dreadful.
In this article, I’ll explain the most effortless way to reference other files in a workbook to make your work more comfortable. So, let’s begin.
You may also like to read how to add borders in Microsoft Excel
How to Reference Other Files from Same Sheet in MS Excel
By referencing the cell address within the worksheet, users can print the same data in another location. The formula is very straightforward, and you can print multiple cell data using the same technique.
If you are new to the referencing formula, read the procedures below to reference other files in the same worksheet.
Here are the steps to reference other files from the same sheet in Excel:
- Launch Microsoft Excel.
- Open your workbook.
- Go to the preferred sheet.
- Select a cell.
- Type =[Cell_Address] formula in the box and click Enter.
Here, Cell_Address is referencing the data’s location (Example: =A3). If you want to reference multiple cells within the same sheet, follow the procedures below.
Here are the methods to reference multiple files from the same worksheet in MS Excel:
- Go to the worksheet from MS Excel.
- Click on a cell.
- Type =[Cell_Address] formula in the cell.
- Drag to a direction to print the data from a row or column.
If you want to reference the row or column at once, consider reading the steps below.
Here is the other process to reference multiple files from the same worksheet in MS Excel:
- Navigate to the worksheet.
- Press on a cell.
- Type =[FIrst_Cell: Last_Cell].
- Click the Enter button.
The first cell indicates the starting address of the data, and the last cell is the location where it will end. Example: =A2:A11. Here, A2 is the starting cell, and A11 refers to the last cell.
However, if you need to reference other files from another worksheet within the same workbook, follow the steps from the next passage.
Related content you should read how to add borders in Microsoft Excel.
How to Reference Other Files from Another Worksheet in Excel
You can reference other files from another worksheet using the Excel formula. It allows the user to print data from single or multiple cells from another worksheet within the same workbook.
The following procedures will guide you with the effortless way to reference files from another worksheet in Excel.
Here are the ways to reference other files from another worksheet in MS Excel:
- Open Excel application.
- Go to your workbook.
- Click on the cell from a worksheet.
- Type = and navigate to the other sheet from the worksheet tab at the bottom.
- Press on the cell you want to refer to.
- Click the Enter button.
It will automatically reference the files from another worksheet within the same workbook. Follow the steps below to reference multiple cells from another worksheet.
Here are the procedures to reference multiple files from another worksheet in Excel:
- Go to your workbook from the Excel application.
- Press on the preferred cell from a worksheet.
- Type = and navigate to the other sheet from the worksheet tab at the bottom.
- Click on the cell you want to refer to.
- Press the Enter button.
- Proceed to the previous worksheet.
- Drag the cell to print the data from a row or column.
But, there’s an additional way to reference multiple cells from another worksheet within the same workbook. Read the instructions below carefully to apply them to your worksheet.
Here is the additional way to reference multiple files from another worksheet in MS Excel:
- Launch MS Excel.
- Navigate to the worksheet.
- Click on the preferred cell.
- Type =Sheet_Name!First_cell: Last_cell and click on Enter.
The Sheet_Name refers to the name of the wheat from where you want to collect the data. The first and last cell refers to the cell address of other sheets.
If you want to reference other files from another workbook, the following procedure will help to achieve that.
Some more guide on how to autofill dates in MS Excel.
How to Reference Other Files from Another Open Workbook in Excel
Users can apply the Excel formula to a cell to print a file from a source workbook when they are open. Also, you can drag to select multiple cells without using the formula.
It’s alright if you don’t know the appropriate procedure. Read the steps below to reference files from one workbook to another.
Here are the methods to reference other files from another open workbook in Excel:
- Open both workbooks with Microsoft Excel.
- Click on the cell from a workbook.
- Type = and switch to another workbook.
- Select the cell you want to refer to.
- Click on Enter.
In order to reference multiple cells, follow the instructions below to complete it quickly.
Here are the steps to reference multiple files from another open workbook in Excel:
- Launch MS Excel and navigate to the workbook.
- Select the cell.
- Type = and go to the other workbook.
- Click on a cell and drag the complete row or column you want to refer to.
- Press the Enter button.
This process will print the selected cell’s data to your workbook. Also, there’s an additional process to refer to multiple files from another workbook. Consider reading the following instructions to learn about them.
Here is the additional way to reference multiple files from another open workbook in MS Excel:
- Go to your workbook from MS Excel.
- Select a cell.
- Type =[Workbook_Name]Sheet_Name!Cell_Address formula in the box.
- Press the Enter button.
Here, Workbook_Name indicates the target workbook file name. Sheet_Name and Cell_Address refer to the file’s exact sheet name and cell location, respectively.
However, if you close the other workbook, you may encounter some problems. The following passage will help you to overcome the difficulties.
Check out the easiest way for how to lock or unlock cells in Excel.
How to Reference Other Files from Another Closed Workbook in Excel
References from other files don’t work correctly when the source workbook is closed. Excel fails to identify the location of the file and cell. You need to provide the file location in the Excel formula to remove the problem.
The process may seem a bit tricky. But following the instructions below will help you to refer to other files from closed workbooks.
Here are the methods to reference files from another closed workbook in Excel:
- Open Microsoft Excel.
- Go to your worksheet.
- Select a cell where you want to start.
- Type ‘=[File_Path][File_Name][Sheet_Name]’![Cell_Address]
- Press the Enter button.
In this step, File_Path is the location of the Excel file, and File_Name indicates the workbook’s name. Sheet_Name and Cell_Address are the exact names of the sheet and cell, respectively.
However, moving the file to another folder can create problems again, and Excel will start showing error messages. You’ll find the absolute solution to this problem in the following passage.
How to Fix the Changing File Location Issue on References in Excel
When you reference other files from a source file to a new workbook, Excel connects the two workbooks. Any possible changes are updated with the interconnection. But, whenever the file location changes, Excel gets confused and identifies the data as harmful.
To prevent this issue, you need to provide the new location of the Excel workbook file. Here’s how to do it.
Here are the steps to update the reference file location in MS Excel:
- Open the workbook from MS Excel.
- Click on Update when Excel shows the prompt window.
- Press on Edit Links.
- Click on Change Sources.
- Navigate to the workbook file location.
- Select the file and click on OK.
- Close the Edit Links.
Your workbook will now update with the new file location. If you move the file once again, follow the same procedures again.
Frequently Asked Questions
How do you reference other files in Excel?
To reference other files in Microsoft Excel, double-click on the selected cell, type = (Equal sign) and select the cell.
Can Excel pull data from another Excel file?
Yes, Microsoft Excel can pull data from another Excel file. You need to use the reference formula to make it happen.
Does changing the data in the Excel workbook change in other workbooks?
Yes, the data changes instantly in a workbook when they are changed from the source workbook.
Conclusions
Referencing files from cells, sheets or workbooks to another is very useful in maintaining a calculation. Still, many people work manually to input the data, which wastes a lot of time.
This article has provided straightforward methods to reference other files from every possible location, and I believe it has helped you a lot.
Don’t forget to share your experience in the comment box below.
Cheers!
