How to reference cells between Microsoft Excel spreadsheets

In Microsoft Excel, it is common to refer to cells from other worksheets or even from different Excel files. It might seem a bit intimidating and confusing at first, but once you understand how it works, it’s not that difficult.

This article will show you how to reference another sheet in the same Excel file and how to reference another Excel file. We also cover topics like referencing a set of cells in a function, simplifying defined names, and using VLOOKUP for dynamic references.

The base cell reference is written as a column letter followed by the row number.

So the cell reference B3 refers to the cell at the intersection of column B and row 3.

When cells in other sheets are referenced, that cell reference is prefixed with the name of the other sheet. For example, below is a reference to cell B3 on a sheet named “January”.

=January!B3

The exclamation point (!) separates the sheet name from the cell address.

If the sheet name contains spaces, you must enclose it in single quotes in the reference.

=’January Sales’!B3

To create these references, you can type them directly into 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 the Sheet tab, and then click the cell you want to cross.

As you do this, Excel writes the reference for you in the formula bar.

File reference in the formula

Press Enter to complete the formula.

You can reference cells from another workbook using the same method. Just make sure the other Excel file is 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 done.

The completed cross-reference will contain the name of the other workbook in square brackets, followed by the sheet name and cell number.

=[Chicago.xlsx]January!B3

If the file or sheet name contains spaces, you must enclose the file reference (including the square brackets) in single quotes.

='[New York.xlsx]January’!B3

Formula that references another manual

In this example, you see dollar signs ($) next to the mobile address. This is an absolute cell reference (Learn more about absolute cell references).

By default, when referencing cells and ranges in different Excel files, the references are made absolute. You can replace them with a relative reference if needed.

If you look at the formula when the referenced workbook is closed, it contains the full path to that file.

Full path of workbook in formula

While it’s easy to create references to other manuals, these are more likely to cause problems. Users who create or rename folders and move files can break these references and cause errors.

It is more reliable to store the data in a single workbook whenever possible.

Single cell referencing is very useful. But you can also write a function (like SUM) that references a range of cells in another worksheet or notebook.

Start the function as usual, then click on the sheet and on the range of cells – just like in the previous examples.

In the following example, a SUM function sums the values ​​in the range B2:B6 on a worksheet named Sales.

=SUM(Revenue!B2:B6)

Return receipt by sum

In Excel, you can assign a name to a cell or a range of cells. This is more meaningful than a cell or range address when you look at it. 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 for all worksheets in this Excel file.

For example, we could name a cell “ChicagoTotal” and the cross-reference would then be:

=Chicago total

It’s a more descriptive alternative to a standard reference like this:

=Revenue!B2

It’s easy to create a distinguished name. Start by selecting the cell or range of cells that you want to name.

Click in the “Name” field in the upper left corner, type the name you want to assign, then press the “Enter” key.

Define a name in Excel

Do not use spaces when creating unique names. Therefore, in this example, the words in the name have been joined and separated by a capital letter. You can also separate words with characters such as hyphen (-) or underscore (_).

Excel also has a name manager that makes it easy to keep track of those names in the future. Click Formulas > Name Manager. In the name management window you can see a list of all the names defined in the workbook, where they are located and what values ​​they are currently storing.

Name Manager for managing defined names

You can then use the buttons above to edit and delete these defined names.

If you are working with a long list of related data, using Excel’s Format as Table feature can make it easier to refer to it.

Take the following simple table.

Small list of product sales dates

It could be formatted as a table.

Click a cell in the list, go to the Home tab, click the Format As Table button, and then choose a style.

Format a range as a table

Confirm that the cell range is correct and that your table has headings.

Confirm the range to use for the table

You can then give your table a meaningful name under the “Design” tab.

Give your Excel spreadsheet a name

If we then totaled the Chicago sales, we could refer to the table by name (of each sheet) followed by a square bracket ([) to see a list of the table’s columns.

Using structured references in formulas

Select the column by double-clicking 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 can make referencing data for aggregate functions like SUM and AVERAGE easier than standard sheet references.

This table is small for demonstration purposes. The bigger the spreadsheet and the more sheets you have in a workbook, the more benefits you will see.

The references used in the examples so far have all been attached to a specific cell or range of cells. This is fine and often sufficient for your needs.

However, what if the cell you are referring to might change as new rows are inserted or someone sorts the list?

In these scenarios, you cannot guarantee that the value you want is still in the same cell that you originally referenced it.

An alternative in these scenarios is to use a search function in Excel to find the value in a list. This makes it more resilient to changes in the sheet.

In the example below, we use the VLOOKUP function to look up an employee on another sheet based on their employee number and then return their start date.

Below is the list of employees as an example.

List of employees

The VLOOKUP function examines the first column of an array and then returns information from a specified column on the right.

The following VLOOKUP function finds the employee ID number entered in cell A2 of the list above and returns the join date from column 4 (fourth column of the table).

=VLOOKUP(A2,Employee!A:E,4,FALSE)

The VLOOKUP function

Below is an illustration of how this formula will search the list and return the correct information.

The VLOOKUP function and how it works

The advantage of this VLOOKUP over the previous examples is that the employee is found even if the order of the list changes.

note : VLOOKUP is an incredibly useful formula, and we’ve only scratched the surface of its value in this article. You can learn more about using VLOOKUP by reading our article on the subject.

In this article, we looked at the different ways to cross-reference Excel spreadsheets and workbooks. Choose the approach that suits your job and that you are comfortable with.

https://www.howtogeek.com/426633/how-to-cross-reference-cells-between-microsoft-excel-spreadsheets/

  How to calculate percentage in MS Excel