The simplest way to reference another sheet

To pull data from one sheet into another in Excel, type an equals sign, then the sheet name, an exclamation point, and the cell you want. For example, =Sheet2!A1 will display whatever is in cell A1 on Sheet2. You can type this formula into any cell on any other sheet, and it will show that value.

The sheet name is the tab at the bottom of your workbook — the one that says "Sheet1" or "Sales Data" or whatever you named it. If your sheet name has a space in it, wrap the name in single quotes: ='Sales Data'!A1. Excel will not accept the formula without them.

This works the same way whether you are using Excel on your computer, Excel on the web, or Excel through Microsoft 365. The syntax does not change.

Key Takeaways

  • Reference another sheet by typing =SheetName!CellAddress, where SheetName is the tab name and CellAddress is the cell you want, like A1 or B5.
  • If your sheet name contains spaces, wrap it in single quotes: ='Sheet Name'!A1.
  • You can reference a range of cells the same way: =Sheet2!A1:A10 will pull all ten cells at once.
  • Formulas that reference other sheets update automatically when the source data changes, so you do not have to manually copy values.
  • You can link to sheets in a different workbook by including the file path, but the file must stay in the same location or the link will break.

Referencing a range instead of a single cell

If you need multiple cells from another sheet, use a range instead of a single cell address. Type =Sheet2!A1:A10 to pull cells A1 through A10. This works in most functions — you can use it in SUM, AVERAGE, COUNT, or any other formula that accepts a range.

When you reference a range, Excel will display all the values in that range if you paste the formula into a single cell. If you want to see them in separate cells, use a different approach: paste the formula into the first cell, then copy it down. Each row will automatically adjust to the next row on the source sheet.

Linking to sheets in a different workbook

You can reference a sheet in a completely different Excel file. The syntax is longer: =[FilePath]SheetName!CellAddress. For example, ='C:\Users\YourName\Documents\[Budget.xlsx]Sheet1'!A1 will pull data from Sheet1 in a file called Budget.xlsx.

The file path must be exact. If you move the Budget.xlsx file to a different folder, or rename it, Excel will show an error because it cannot find the file anymore. For this reason, most people only use cross-file links when both files will stay in the same location permanently.

If you want to avoid typing the long path, you can open both files, then click and drag from the source cell into the formula bar of the destination file. Excel will write the correct path for you.

What happens when the source data changes

When you link to another sheet, the formula updates automatically every time you open the workbook or every time the source data changes. If Sheet2 cell A1 contains the number 100, and you reference it on Sheet1, Sheet1 will show 100. If someone changes Sheet2 A1 to 150, Sheet1 will change to 150 without you doing anything.

This is different from copying and pasting values, which creates a static snapshot. A formula link stays connected to the source, so it always shows the current data.

Using sheet references inside functions

You can use a sheet reference inside any Excel function. =SUM(Sheet2!A1:A10) will add up all the values in that range on Sheet2. =AVERAGE(Sheet3!B5:B15) will find the average of those cells. =IF(Sheet2!A1>100,"Yes","No") will check if the value on Sheet2 is greater than 100.

The sheet reference works exactly the same way as if you were referencing cells on the current sheet. You can combine it with any function, use it in conditional statements, or nest it inside other formulas. The only rule is that the sheet name and cell address must be separated by an exclamation point.

Avoiding common mistakes with sheet names

The most common error is forgetting the single quotes around a sheet name that contains spaces. If your sheet is called "Q4 Sales" and you type =Q4 Sales!A1, Excel will show an error because it reads "Q4" as a separate thing. Always use ='Q4 Sales'!A1 instead.

Another mistake is using the wrong sheet name. Check the exact spelling of the tab at the bottom of your workbook — if it says "Sheet 2" with a space, that is the name you need to use, not "Sheet2" without a space. Excel is case-insensitive for sheet names, so "sheet2" and "Sheet2" are the same, but the spacing must match exactly.

If you delete a sheet that other sheets are linked to, Excel will show a #REF! error in any formula that referenced it. You can fix this by updating the formula to point to a different sheet, or by undoing the deletion if you catch it right away.

When to use sheet links versus consolidating data

Sheet links are useful when you want to keep data organized across multiple sheets but pull it together in one place. A sales workbook might have one sheet per region, and a summary sheet that links to each region's total. When a region updates its numbers, the summary updates automatically.

If you are working with a lot of data or many sheets, consider whether a pivot table or a consolidation tool might work better. These features are designed to combine data from multiple sources without writing individual formulas. For straightforward cases — pulling one or two values from another sheet — a direct link is the fastest approach.

Frequently Asked Questions

Can I link to a sheet in a different workbook that is not open?

Yes, but you need to type the full file path. Excel does not need the file to be open to create the link. However, if the file is not open when you open your workbook, Excel may ask you to update the link or show a security warning. The link will work once the source file is available again.

What does #REF! error mean?

This error appears when Excel cannot find the sheet or cell you are trying to reference. It usually means the sheet was deleted, the sheet name was spelled wrong, or the file path is broken. Check the sheet name spelling and make sure the source sheet still exists.

Can I reference a sheet from a different workbook without typing the full path?

The easiest way is to open both files, then click the cell you want to reference while typing your formula. Excel will automatically insert the correct file path. You can also copy the formula from the source file and paste it into the destination file, and Excel will adjust the reference.

Do sheet links work the same way in Excel on the web?

Yes, the basic syntax is identical. However, linking to a different workbook works differently in Excel on the web — you cannot reference external files the same way you can on the desktop version. Stick to linking sheets within the same workbook for web-based Excel.

If I copy a formula with a sheet reference to another workbook, will it still work?

No, the reference will break because it is looking for a sheet that does not exist in the new workbook. You will need to update the formula to reference the correct sheet in the new file, or use a different approach like importing the data directly.