Excel Reference to another sheet

There may be situations where, in Excel, you have to retrieve data from another worksheet or even another Excel file. The solution is to link these two worksheets within the same workbook or across different workbooks using what’s called a cell reference.

An external Excel reference in Excel refers to a reference made to any cell or range of cells not located in the current worksheet. The main benefit of using an Excel external reference is that when changes are made in the referenced cell(s) located at another worksheet, it will automatically update the value obtained by the external cell reference.

Despite similarities existing between regular references and external references in Excel, there are significant differences between them. This tutorial will go through the fundamentals and provide a step-by-step guide with screenshots and examples of external references.

How to reference another sheet in Excel

To create a reference to a cell or range of cells located in a different worksheet within the same workbook in Excel, use the worksheet name followed by an exclamation mark (!) before specifying the cell address.

In essence, when referencing another worksheet in Excel, follow this format:

Reference to an individual cell:

Sheet_name!Cell_address

For instance, to point to cell A1 in Sheet2, enter Sheet2!A1.

Reference to a range of cells:

Sheet_name!First_cell:Last_cell

For example, to reference cells A1 to A10 in Sheet2, input Sheet2!A1:A10.

It’s important to note that if the worksheet name contains spaces or non-alphabetical characters, enclose it in single quotation marks. For instance, referencing cell A1 in a worksheet named “Project Milestones” should be written as:

‘Project Milestones’!A1

How to create an Excel reference

You can create a reference to cells in another worksheet by following these steps to let Excel handle the syntax efficiently.
● Start typing your formula in the destination cell or formula bar.
● When you reach the point of adding a reference to another worksheet, switch to that sheet.
● Select the cell or group of cells that you want to refer to.
● Finish your formula and press the Enter key on your keyboard.
● For example, if you want to calculate the Value Added Tax (19%) for each product in sheet VAT which refers to sales figures in Sales:
● Begin typing =19%*in cell B2 on sheet VAT.
● Move over to sheet Sales and click cell B2. Excel will insert an external reference into this cell.

Press Enter to finalize the formula.

Remember: If you happen to use this method when referencing another sheet, Excel will automatically add a relative reference (without a $ sign). In case you copy the formula down column B on the VAT sheet, these references will adjust for each row accordingly.

Similarly, one may refer to a range of cells from another sheet. The process is similar; what is needed only is multiple selections of cells on the source worksheet. For instance, finding the total sales in cells B2:B5 on the Sales Sheet would be done using the following formula:

This is how we link the Sheet with Excel reference.