Excel reference to another sheet or workbook (external reference)

This concise guide demonstrates how to efficiently utilize external references in Excel, enabling seamless data integration across worksheets and workbooks. Learn how to link data for automatic updates and streamline your calculations.

When working with Excel, accessing data from other sheets or files is often necessary. This is easily achieved using external cell references, also known as links.

An external reference in Excel points to a cell or range outside the current worksheet. The key advantage? When the referenced data changes, the linked cell automatically updates.

While similar to standard cell references, external references have crucial distinctions. This tutorial covers various reference types, providing step-by-step instructions, visuals, and formula examples.

Referencing Another Sheet

To reference cells on another sheet within the same workbook, precede the cell address with the sheet name and an exclamation mark (!).

Individual Cell: Sheet_name!Cell_address (e.g., Sheet2!A1)

Range of Cells: Sheet_name!First_cell:Last_cell (e.g., Sheet2!A1:A10)

Note: Sheet names with spaces or special characters require single quotes. For example, 'Project Milestones'!A1.

A formula multiplying cell A1 in the 'Project Milestones' sheet by 10 would be: ='Project Milestones'!A1*10

Creating a Sheet Reference

Manually typing references is inefficient and prone to errors. A more effective method involves directly selecting the target cells:

1. Begin typing a formula in the destination cell or formula bar.
2. Navigate to the source sheet and select the desired cell(s). Excel automatically inserts the correct reference.
3. Press Enter to finalize the formula.

For instance, to calculate 19% VAT on sales figures in a 'Sales' sheet, within a 'VAT' sheet:

1. Start with =19%* in cell B2 of the 'VAT' sheet.
2. Select cell B2 in the 'Sales' sheet. Excel will input the reference (see image below).
3. Press Enter.

Note: Excel defaults to relative references. Copying the formula down column B will automatically adjust cell references for each row.

Similarly, referencing a range is done by selecting multiple cells. To sum sales (B2:B5) from the 'Sales' sheet: =SUM(Sales!B2:B5)

Referencing Another Workbook

External references to other workbooks vary depending on whether the source workbook is open or closed.

Open Workbook

When the source workbook is open, the reference includes the workbook name (in square brackets, including extension), sheet name, exclamation mark, and cell reference.

Example: [Sales.xlsx]Jan!B2:B5 (sums cells with =SUM([Sales.xlsx]Jan!B2:B5))

Closed Workbook

For closed workbooks, include the full file path: =SUM('D:\Reports\[Sales.xlsx]Jan!B2:B5)

Components: File Path, Workbook Name (in brackets), Sheet Name, Cell Reference.

Quotes are needed for names with spaces or special characters. For example: =SUM('[Year budget.xlsx]Jan'!B2:B5)

Creating a Workbook Reference

Similar to sheet references, selecting cells in another workbook automatically creates the reference:

Note: Excel uses absolute references by default. Remove dollar signs ($) for relative or mixed references when copying formulas. Issues may arise if workbooks are open in separate Excel instances.

Referencing Defined Names

Using defined names simplifies external references.

Creating a Name

Select the cells, go to Formulas > Define Name, or press Ctrl F3. Enter a name (no spaces) and verify the range in the Refers to field.

Referencing a Name

Same Workbook (Workbook-level name): =SUM(Jan_sales)

Same Workbook (Worksheet-level name): =SUM(Jan!Jan_sales)

Different Workbook (Workbook-level name): =SUM(Sales.xlsx!Jan_sales)

Different Workbook (Worksheet-level name): =SUM([Sales.xlsx]Jan!Jan_sales)

Closed Workbook: =SUM('C:\Documents\Sales.xlsx'!Jan_sales)

Creating a Name Reference

To insert a name reference:

1. Begin typing the formula.
2. Navigate to the sheet (if necessary).
3. Press F3, select the name, and click OK.
4. Complete the formula.

Mastering external references empowers you to leverage data across multiple sheets and workbooks effectively.

The above is the detailed content of Excel reference to another sheet or workbook (external reference). For more information, please follow other related articles on the PHP Chinese website!