In Microsoft Excel, it is a common task to refer to cells in other worksheets or even in different Excel files. At first, this may seem a bit daunting and confusing, but once you understand how it works, it is not that difficult.
In this article, we will see how to reference another sheet in the same Excel file and how to reference a different Excel file. We will also cover things like how to reference a range of cells in a function, how to make things simpler with defined names, and how to use VLOOKUP for dynamic references.
How to reference another sheet in the same Excel file
A basic cell reference is written as the letter of the column followed by the row number.
So cell reference B3 refers to the cell at the intersection of column B and row 3.
When referring to cells in other sheets, this cell reference is preceded by the name of the other sheet. For example, below is a reference to cell B3 on a sheet called "January."
B3 = January!
The exclamation point (!) Separates the name of the cell's address sheet.
If the sheet name contains spaces, you must enclose the name in single quotes in the reference.
= January sales B3
To create these references, you can write them directly in the cell. However, it is easier and more reliable to let Excel write the reference for you.
Type an equal sign (=) in a cell, click on the Sheet tab, and then click on the cell you want to cross-reference.
In doing so, Excel writes the reference in the formula bar.
Press Enter to complete the formula.
How to reference another Excel file
You can refer to cells in another workbook using the same method. Just make sure you have the other Excel file open before you start writing the formula.
Type an equal sign (=), change to the other file, and then click on the cell of the file you want to reference. Press Enter when finished.
The complete cross-reference contains the other name of the workbook in square brackets, followed by the name of the sheet and the cell number.
= (Chicago.xlsx) January! B3
If the file or sheet name contains spaces, you must include the file reference (including square brackets) in single quotes.
= ‘(New York.xlsx) January’! B3
In this example, you can see the dollar sign ($) between the address of the cell. This is an absolute cell reference (Learn more about absolute cell references).
When referring to cells and ranges in different Excel files, the references are made absolute by default. If necessary, you can change it to a relative reference.
If you look at the formula when the referenced workbook is closed, it will contain the full path to that file.
Although creating references to other workbooks is simple, they are more susceptible to problems. Users who create or rename folders and move files can break these references and cause errors.
Keeping the data in a workbook, if possible, is more reliable.
How to cross references of a range of cells in a function
Referencing a single cell is quite useful. But you may want to write a function (such as SUM) that refers 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 same way as you did in the previous examples.
In the following example, a SUM function adds the values of the B2: B6 range in a worksheet called Sales.
= SUM (Sales! B2: B6))
How to use defined names for simple cross references
In Excel, you can name a cell or a range of cells. This is more significant than the direction of a cell or a range when looking back. If you use many references in your spreadsheet, naming them can make it much easier to see what you have done.
Better yet, this name is unique to all worksheets of that Excel file.
For example, we could call a cell “Chicago Total” and then the cross reference would say:
= ChicagoTotal
This is a more significant alternative to a standard reference like this:
= Sales! B2
It is easy to create a defined name. Start by selecting the cell or range of cells you want to name.
Click the Name box in the upper left corner, type the name you want to assign, and then press Enter.
When creating defined names, spaces cannot be used. Therefore, in this example, the words have been joined in the name and separated by an uppercase letter. You can also separate words with characters such as a hyphen (-) or underscore (_).
Excel also has a Name Manager that facilitates the monitoring of these names in the future. Click Formulas> Name Manager. In the Name Manager window, you can see a list of all the names defined in the workbook, where they are and what values they currently store.
You can then use the buttons located at the top to edit and delete these defined names.
How to format data as a table
When working with an extensive list of related data, the use of the Format function as an Excel table can simplify the way in which the data in it is referenced.
Take the following simple chart.
This could be formatted as a table.
Click on a cell in the list, switch to the "Start" tab, click on the "Format as table" button, and then select a style.
Confirm that the cell range is correct and that your table has headers.
In the "Design" tab you can assign a meaningful name to your table.
Then, if we needed to add Chicago sales, we could refer to the table by name (of any sheet), followed by a square bracket (() to see a list of the table's columns.
Select the column by double clicking on it in the list and enter a closing bracket. The resulting formula would be something like this:
= Sum (Sales (Chicago))
You can see how tables can make reference data for aggregation functions such as SUM and AVERAGE easier than standard sheet references.
This table is small for demonstration purposes. The larger the table and the more sheets you have in a workbook, the more benefits you will see.
How to use the VLOOKUP function for dynamic references
The references used so far in the examples have been set to a specific cell or a range of cells. That is great and is often enough for your needs.
However, what happens if the cell you are referring to has the potential to change when new rows are inserted, or if someone orders the list?
In these scenarios, it cannot be guaranteed that the desired value will remain in the same cell that was initially referenced.
An alternative in these scenarios is to use a search function in Excel to find the value in a list. This makes it more durable against changes in the blade.
In the following example, we use the VLOOKUP function to search for an employee on another sheet by their employee ID and then return their start date.
Below is an example of the list of employees.
The VLOOKUP function looks down 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 cell A2 of the list shown above and returns the joined date of column 4 (fourth column of the table).
= VLOOKUP (A2, Employees! A: E, 4, FALSE)
Below is an illustration of how this formula searches the list and returns the correct information.
The good thing about this VLOOKUP about the previous examples is that the employee will be found even if the list changes order.