Using Formulas and Functions
- 6/15/2010
- Understanding Formulas and Cell References in Excel
- Creating Simple Cell Formulas
- Assigning Names to Groups of Cells
- Using Names in Formulas
- Creating a Formula That References Values in an Excel Table
- Creating Formulas That Reference Cells in Other Workbooks
- Summing a Group of Cells Without Using a Formula
- Creating a Summary Formula
- Summing with Subtotals and Grand Totals
- Exploring the Excel Function Library
- Using the IF Function
- Checking Formula References
- Debugging Your Formulas
Creating Formulas That Reference Cells in Other Workbooks
One of the strengths of Excel is that you aren’t limited to using cells from the current workbook in your formulas. If you want, you can use data from any other workbook in your calculations. For example, you might have a workbook in which you track monthly advertising sales for your newsletter. If you want to create a new workbook to summarize all income and expenses for your publication, you can do so. By letting you create formulas that reference cells from more than one workbook, Excel makes it easy for you to organize your workbooks so that each workbook holds data about a specific subject. Not only can you find the data easily, you can reference it anywhere else.
After you create links between workbooks, you can have Excel update your calculation if the data in the linked cell changes. You can also change the cell to which you linked, or if the workbook with the cell to which you linked has been moved or deleted, you can delete the link and have Excel store the last value from the calculation.
Use Cells from Other Workbooks in a Formula
Open the workbook with the cell you want to reference in your formula.
In the workbook where you want to create the formula, click the View tab.
Click Arrange All.
Select the Tiled option.
Click OK.
Click the cell where you want to create the formula.
Type = followed by the first part of the formula.
Select the cells with the values you want to use in the formula.
Press Enter.
Break Links to Other Workbooks and Convert to Values
Click the cell that contains the formula you want to edit.
Select the part of the formula representing the link you want to break.
Press F9.
Press Enter.
Refresh Links
Click the Data tab.
In the Connections group, click Refresh All.
Change Links to Different Workbooks
Click the Data tab.
In the Connections group, click Edit Links.
Click the link you want to change.
Click Change Source.
Click the workbook with the new cell to which you want to link.
Click OK.
Select the sheet from which to update values.
Click OK.
Click Close.
){kind=link}
){kind=link}