How to Link Two Excel Spreadsheets
You've already worked hard on one spreadsheet, setting up formulas and populating the data. Now you need to use the results from the first spreadsheet to set up another spreadsheet. You have the option of manually entering the data in the new spreadsheet, but what if the results change on the first spreadsheet? Read on and you'll find out how to configure a hyperlink on the second spreadsheet so it brings in the results from the first spreadsheet.
Things You'll Need
- Microsoft Excel 2003, 2007 or 2010
Create a Document with Multiple Spreadsheets
Create and name the first spreadsheet. At the bottom of the worksheet, right-click on the first tab (Sheet 1) and select "Rename." Type a new name. Example: Feb26.
Create and name the second spreadsheet. At the bottom of the worksheet, right-click on the second tab (Sheet 2) and select "Rename." Type a new name. Example: Feb27.
Make sure you are in spreadsheet Feb27. Enter numeric data in A1 and B1. In cell A1, type the number 2. Then in cell B1, type the number 3.
Enter a formula in cell C1 on the Feb27 spreadsheet. Click in the cell C1 and then click "Formula" in the ribbon. Click AutoSum and then select Sum. In the formula field, you should see the formula "=SUM(A1,B1)." Press Enter.
Check to see that the formula functions correctly. If your formula has been entered correctly, you should see a 5 displayed in the C1 cell.
Make a Link from One Spreadsheet to the Other
Navigate to the Feb26 spreadsheet. Scroll to the bottom and click on the Feb26 tab. This will put you on the Feb26 spreadsheet.
Add a link from C1 on Feb26 to C1 on Feb27. On Feb26, click on C1 then click on the "Insert" tab on the ribbon. Select the "Hyperlink" icon.
Specify the cell on the Feb26 spreadsheet. In the formula bar, type: =Feb27!C1
Check the cell. Click the C2 cell. You should now see the value that came from cell C1 in Feb27 displayed in the cell C1 in Feb26; that is, you should see the number 5 in cell C1 Feb26.
Test to see if it's working properly. Navigate to spreadsheet Feb27 by clicking on the tab at the bottom. Change the contents of A1 to 3. You should see the 5 change to a 6. Navigate back to spreadsheet Feb26 by clicking on the Feb26 tab. You should see a 6 displayed in C1 Feb26.
Tips & Warnings
- Don't confuse spreadsheets with workbooks. Workbooks can be composed of one or multiple spreadsheets.