How to Reference Another Sheet in Excel

To make an Excel reference another sheet, type the sheet name, an exclamation mark (!) and the cell: =Prices!B3 means "cell B3 on the Prices sheet". The safest way is to let Excel write it: type =, click the other tab, click the cell and press Enter. This guide covers single cells, names with spaces, lookups against another tab, totals across many tabs and links to other files.

The examples use a workbook with an Orders sheet, a Prices sheet, three monthly sheets (Jan, Feb, Mar), a Last Year sheet and a Summary sheet.

How to make an Excel reference another sheet#

A workbook is the Excel file. It holds one or more worksheets (sheets), each with a tab at the bottom. A sheet reference points at a cell on a different sheet:

=Prices!B3

• Prices is the sheet name.
• ! separates the sheet from the cell.
• B3 is the cell on that sheet.

You don't have to type it. To put the Notebook price into D2 on Orders:

1. Click D2 on the Orders sheet and type =.
2. Click the Prices tab at the bottom.
3. Click B3 and press Enter.

Excel writes =Prices!B3 and takes you back to Orders. Press Enter straight after clicking the cell. If you click the Orders tab first, Excel adds Orders! to the formula instead.

Sheet names with spaces need single quotes#

If a sheet name has a space or a special character, put single quotes (apostrophes) around it:

=B1-'Last Year'!B5

The quotes go around the sheet name only, before the !. Without them, =B1-Last Year!B5 gives an error. When you click the tab instead of typing, Excel adds the quotes for you, which is one more reason to click. Short names without spaces, like Jan or Prices, are quicker to type and read.

Use a range from another sheet in a function#

Any function that takes a range can take one from another sheet. Write the sheet name once, before the range:

=SUM(Jan!B2:B4)

The most useful case is a lookup against a price list on its own tab. On Orders, this finds the product in B2 in the price list on Prices and returns its price:

=VLOOKUP(B2,Prices!$A$2:$B$7,2,FALSE)

Lock the range with $ signs before you fill down, or the price list slides down a row each time and the lower rows show #N/A. To build it by clicking, type =VLOOKUP(B2,, click the Prices tab, drag over A2:B7, press F4 (on a Mac laptop, Fn+F4, or type the $ signs), then finish the formula. The VLOOKUP function guide explains the other three parts.

Conditional sums work the same way. On a Report sheet, =SUMIFS(Sales!E:E,Sales!A:A,"Q2",Sales!B:B,"West") adds up Q2 West sales from the Sales sheet. That formula is a common partner to a pivot table, as our guide to pivot table subtotals, filters and % of total shows.

Add up the same cell across many sheets#

When several sheets have the same layout, you can total one cell across all of them. Each monthly sheet has its total in B5:

On Summary, add three sheet references:

=Jan!B5+Feb!B5+Mar!B5

The result is 93,400. For a long run of sheets, a 3D reference is shorter. It covers the first sheet, the last sheet and every sheet between them in the tab order:

=SUM(Jan:Mar!B5)

Be careful with 3D references: a new sheet dragged between Jan and Mar is included in the total, and moving Jan or Mar changes which sheets are covered. Then compare with last year, which is on a sheet with a space in its name: =B1-'Last Year'!B5 gives 93,400 − 81,500 = 11,900.

For a summary that groups and totals data without writing a reference per cell, a pivot table is often quicker.

Reference a cell in another workbook#

To point at a different Excel file, the reference also includes the file name in square brackets:

=[Budget.xlsx]Summary!$B$3

Build it the same way: type =, switch to the other open file, click the cell and press Enter. When the other file is closed, Excel shows the full folder path, such as ='C:\Reports\[Budget.xlsx]Summary'!$B$3. These links break if the file is moved or renamed. To check or fix them, use Data > Edit Links (called Workbook Links in newer versions of Microsoft 365).

What happens when you rename, move or delete a sheet#

Excel keeps references working in most cases:

• Rename a sheet: every formula updates by itself. Rename Jan to January and =Jan!B5 becomes =January!B5, with the same result.

• Move a sheet to a new place in the tab order: normal references keep working. Only 3D references can change.

• Delete a sheet: formulas that used it show #REF!, which means the cell no longer exists. Undo with Ctrl+Z (Mac: ⌘Z) straight away if that wasn't what you meant.

• Type a sheet name that doesn't exist: Excel can't find the sheet and opens a file window called Update Values, because it thinks you mean another workbook. Click Cancel and check the spelling of the tab name.

When you total data from another sheet that has filters on, SUM still counts the hidden rows. Use the SUBTOTAL function to add only the visible ones.

Switch between sheets quickly#

Moving between tabs with the keyboard saves time when you build cross-sheet formulas:

To rename a sheet, double-click its tab, type the new name and press Enter.

Practise it#

Cross-sheet formulas feel natural once you have clicked through a few. ExcelReady's worksheets and cross-sheet references lesson uses the workbook from this guide: switch tabs, click a price on the Prices sheet into a formula, look up every price with VLOOKUP, add a Summary sheet, total three monthly tabs, rename a tab and compare with 'Last Year'. The exercise on this page practises the same price lookup on a single sheet; try the multi-sheet version in the lesson.