How to Cross Reference Cells Between Microsoft Excel Worksheets.
Contents
In Microsoft Excel, it is a common task to reference cells in other worksheets or even in different Excel files. At first, this can seem a bit overwhelming and confusing, but once you understand how it works, it’s 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’ll also cover things like how to reference a range of cells in a function, how to keep things simple 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 column letter followed by the row number.
So cell reference B3 refers to the cell at the intersection of column B and row 3.
When cells on other sheets are referenced, this cell reference is preceded by the name of the other sheet. For example, below is a reference to cell B3 in the name of a sheet “January”.
=January!B3
The exclamation point (!) separates the sheet name from the cell address.
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 type them directly in the cell. However, it’s easier and more reliable to let Excel write the reference for you.
Type an equal sign (=) in a cell, click the Sheet tab, and then click the cell you want to cross-reference.
When you do this, Excel writes the reference for you in the formula bar.
Press Enter to complete the formula.
How to reference another Excel file
You can reference cells in another workbook using the same method. Just make sure you have the other Excel file open before you start typing the formula.
Type an equal sign (=), switch to the other file, and then click the cell in that file that you want to reference. Press Enter when you’re done.
The full cross reference contains the other workbook name in square brackets, followed by the sheet name and cell number.
=January!B3
If the file or sheet name contains spaces, you must enclose the file reference (including the square brackets) in single quotes.
=’January’!B3
In this example, you can see dollar signs ($) between the cell address. This is an absolute cell reference (Learn more about absolute cell references).
When referencing cells and ranges in different Excel files, the references are made absolute by default. You can change this to a relative reference if you need to.
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 easy, 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 reference a range of cells in a function
Referencing a single cell is quite useful. But you may want to write a function (like SUM) that references a range of cells in another worksheet or workbook.
Launch the function as usual, and then click the sheet and cell range, the same way you did in the previous examples.
In the following example, a SUM function sums the values in the range B2:B6 in a worksheet named Sales.
=SUM(Sales!B2:B6)
How to use defined names for simple cross references
In Excel, you can assign a name to a cell or range of cells. This is more meaningful than a cell or range address when you look at them. If you use a lot of references in your spreadsheet, naming those references can make it much easier to see what you’ve done.
Even better, this name is unique to all the worksheets in that Excel file.
For example, we could name a cell ‘ChicagoTotal’ and then the cross reference would read:
=ChicagoTotal
This is a more meaningful 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 in the Name box in the upper left corner, type the name you want to assign, and then press Enter.
When creating defined names, you cannot use spaces. Therefore, in this example, the words were joined in the name and separated by a capital letter. You can also separate words with characters such as a hyphen (-) or an underscore (_).
Excel also has a Name Manager that makes it easy to monitor 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 at the top to edit and delete these defined names.
How to format data as a table
When working with a large list of related data, using Excel’s Format As Table feature can simplify the way you reference the data in it.
Take the following simple table.
This could be in the form of a table.
Click on a cell in the list, switch to the “Home” tab, click the “Format as Table” button, and then select a style.
Confirm that the cell range is correct and that your table has headers.
You can then give your table a meaningful name from the “Design” tab.
Then, if we needed to add up the sales for Chicago, we could refer to the table by its name (from any sheet), followed by a square bracket ()
You can see how tables can make data referencing easier for aggregation functions like SUM and AVERAGE than standard sheet references.
This table is small for demonstration purposes. The bigger the table and the more sheets you have in a book, the more benefits you will see.
How to use the VLOOKUP function for dynamic references
All references used in the examples so far have been fixed to a specific cell or range of cells. That’s great, and often enough for your needs.
However, what if the cell you are referencing has the potential to change when new rows are inserted, or
