When working with multiple Excel workbooks, you'll often find yourself needing to reference numbers from one workbook in a separate workbook. Luckily Excel has plenty of built-in tools to accomplish this exact thing.
Contents:
How to Link Workbooks Together in Excel
Let's go step by step and write a formula that includes data from two separate workbooks.
By creating a formula that incorporates cells from two different workbooks, we are effectively linking them. Any changes to the second workbook, will be reflected in the first.
1. Open both workbooks:
Start Excel and open both workbooks that you want to link together. For this example, we will use "Total Sales.xlsx" and "West Sales.xlsx".
2. Select the workbook for the formula
Decide which workbook you want to input your formula into. For this example, let's use "Total Sales.xlsx".
3. Select the cell for the formula: Click on the cell where you want to enter the formula that adds a number from the other workbook. Let's use cell C3 for this example.
4. Start writing the formula: In cell B3, type =SUM(
5. Switch to the other workbook: Without closing the parentheses or pressing Enter, click on the other workbook. Your formula from the first workbook will still be open and editable.
6. Select the cell to link: In your second workbook click on the cell or cells that you want to link to the first workbook. For this example, let's use cell C3:C9.
7. Finish the formula: Excel will automatically insert the correct reference to the cell or cell range in your second workbook. Press Enter to complete the formula. It should look something like:
= SUM('[West Sales.xlsx]Monthly Sales'!$C$3:$C$9)
Now you have linked and summed the cells from "West Sales.xlsx" to the cell C3 in "Total Sales.xlsx". Now, whenever the value of any cells in the range C3:C9 on the "West Sales.xlsx" changes, the value in C3 of "Total Sales.xlsx" will automatically update to reflect that change.
Formula to Reference a Cell in Another Workbook
Let's take a closer look at how the formula works and its syntax.
= '[Book1.xlsx]Sheet1'!A1In this formula we are referencing the cell A1 in Sheet1 of Book1.xlsx.
The square brackets [ ] around the workbook name allow Excel to know that we're referring to a different workbook.
This is followed by the sheet name containing the cell you're referencing.
The exclamation mark ! separates the workbook and sheet names from the cell reference which act just like a typical cell reference.
Sometimes single quotation marks ' ' are used when a workbook name or worksheet name contains spaces or non-alphanumeric characters.