In Microsoft Excel, referencing cells in other worksheets, or even in other Excel files, is a common task. This may seem a bit intimidating and confusing at first, but once you understand how it works, it's not that hard.
This article explains how to reference another sheet in the same Excel file and how to point to another Excel file. We will also look at topics such as referring to a range of cells in a function, simplifying the use of defined names, and using VLOOKUP for dynamic references.
Referencing another sheet in the same Excel file [1
9659005] A basic cell reference is written as a column letter followed by the row number.
The cell reference B3 thus refers to the cell at the intersection of column B and row 3.
When referring to cells in other sheets, the cell name is preceded by the name of the other sheet. For example, here's a reference to cell B3 in a sheet name "January".
= January! B3
The exclamation mark (!) Separates the sheet name from the cell address.
If the sheet name contains spaces, then you must enclose the name with single quotes in the reference.
= & # 39; January Sales & # 39 ;! B3
To create these references, you can enter them directly into the cell. However, it is easier and more reliable to let Excel have the reference for you.
Enter an equal sign (=) in a cell, click the Sheet tab, and then click the cell you want to refer to.
Excel writes the reference for you in the formula bar.
Press Enter to complete the formula.
Referencing another Excel file
You can use the same method to reference cells in another workbook. Make sure the other Excel file is open before you start typing the formula.
Enter an equal sign (=), go to the other file, and then click the cell in the file you want to refer to. Press Enter when you're done.
The completed cross-reference contains the name of the other workbook in square brackets, followed by the sheet name and the cell number.
= [Chicago.xlsx] January! B3
If the file or sheet name contains spaces, then you must enclose the file reference (including the square brackets) in single quotes.
= & # 39; [New York.xlsx] January & # 39 ;! B3
In this example, $ ($) is displayed below the cell address. This is an absolute cell reference (more about absolute cell references).
When referencing cells and ranges in different Excel files, the references are set as absolute references by default. If necessary, you can change this to a relative reference.
If you look at the formula while the referenced workbook is closed, it contains the entire path to that file.
Although creating references to other workbooks is easy, they are more prone to problems. Users who create or rename folders and move files can override these links and cause errors.
If possible, storing data in a workbook is more reliable.
Cross Reference to a Cell Range in a Function
References to a single cell is useful enough. However, you might want to write a function (for example, SUM) that points to a range of cells in another worksheet or workbook.
Start the function as usual, and then click on the sheet and cell range in the previous examples.
In the following example, a SUM function sums the values from the range B2: B6 in a worksheet named Sales.
= SUM (Sales! B2: B6)
Using defined names for simple cross-references
In Excel, you can name a cell or range of cells. This makes more sense than a cell or range address when you look at them. If you use a lot of references in your spreadsheet, naming these links makes it easier for you to understand what you've done.
This name is unique even for all worksheets in this Excel file.
For example, we could have a cell & # 39; ChicagoTotal & # 39; and then the cross-reference would be:
This is a more useful alternative to a standard reference like this:
= Sales! B2
It's easy to create a distinguished name. Start by selecting the cell or range of cells you want to name.
Click in the name box in the upper-left corner, enter the name you want to assign, and press Enter.
Can not use spaces when creating defined names. Therefore, in this example, the words were put together in the name and separated by a capital letter. You can also use words like a hyphen (-) or an underscore (_) to separate words.
Excel also has a name manager that makes it easier to monitor these names in the future. Click Formulas> Name Manager. In the Name Manager window you will see a list of all defined names in the workbook, where they are located and what values they currently store.
 You can then use the buttons above to edit and delete these defined names.
Formatting Data as a Table
When you work with a large list of related data, you can simplify the Format As Table feature in Excel to reference data in it.
Take the following simple table.
These can be formatted as a spreadsheet.
Click a cell Go to the Home tab of the list, click the Format as Table button, and then select a style.
Make sure the range of cells is correct and your table contains headers.
You can then assign a meaningful name to your table on the Design tab.
If we then had to sum up Chicago's sales, we could refer to the spreadsheet with their name (from any sheet), followed by a square bracket ([)todisplayalistoftablecolumns
Select the column Double-click it in the list and enter a closing square bracket. The resulting formula would look something like this:
= SUM (Sales [Chicago])
You can see how tables make referencing data easier for aggregation functions such as SUM and AVERAGE than standard sheet references.
This table is small for demonstration purposes. The larger the table and the more leaves you have in a workbook, the more benefits you will see.
Using the VLOOKUP function for dynamic references
The references used in the previous examples have all been corrected for a particular cell or range of cells. That's great and often enough for your needs.
But what can change if the cell you refer to is changed as new rows are inserted, or if someone else sorts the list?
You can not guarantee in these scenarios. The desired value is still in the same cell that you originally referenced.
In these scenarios, an alternative is to use a search function in Excel to find the value in a list. This makes it more durable against changes to the worksheet.
In the following example, we use the VLOOKUP feature to look up an employee by using his employee ID on another worksheet and then returning his start date.
Below is the example list of employees.
The VLOOKUP function searches the first column of a table and then returns information from a specified column to the right.
The following VLOOKUP function searches for the employee ID entered in the above list in cell A2 returns the associated date from column 4 (fourth column of the table).
= VLOOKUP (A2, Employees! A: E, 4, FALSE)
The following shows how this formula searches the list and returns the correct information.
The big thing about this VLOOKUP over the previous examples is that the employee does this is also found when the list is in the specified order changes.
Note: VLOOKUP is an incredibly useful formula, and we've only scratched the surface of the value in this article. For more information on using VLOOKUP, see our article on this topic. In this article about Computergaga (also written by Alan Murray) you will learn even more tricks for use.
In this article, we looked at several ways to cross-reference between Excel worksheets and workbooks. Choose the approach that is right for your job and with which you can work well.