To reference another tab in Excel, type the sheet name, an exclamation mark and the cell, for example =Sales!B2. If the name has spaces, wrap it in single quotes: =’Q1 Sales’!B2. To show the current tab’s name in a cell, use =TEXTAFTER(CELL(“filename”,A1),”]”) in Excel 365. To use a tab name typed in a cell, use INDIRECT.
Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows and Mac. TEXTAFTER needs Excel 365 or 2024. Last reviewed October 8, 2026.
1. Reference a cell on another tab
- Click the cell where you want the result and type =.
- Click the other sheet’s tab, click the cell you want, and press Enter.
Excel writes the reference for you, such as =Sales!B2 or =’Q1 Sales’!B2. You can use it inside other formulas too, for example =SUM(Sales!B2:B20). More detail is in how to reference a cell from another sheet in Excel.
If you rename the tab later, Excel updates every reference automatically.
2. Show the current tab’s name in a cell
This is useful for report titles that should match the tab name.
Excel 365 and 2024
=TEXTAFTER(CELL("filename",A1),"]")Excel 2021 and older
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)How it works: CELL(“filename”,A1) returns the full path, workbook name and sheet name, such as C:\Reports\[Budget.xlsx]Sales. The rest of the formula keeps only what comes after the closing bracket.
- The workbook must be saved. In a new, unsaved file the formula returns a blank.
- Keep the A1 argument. Without it, CELL returns the name of whichever sheet was last changed, which can show the wrong tab.
- CELL(“filename”) may not return a sheet name in Excel for the web.
3. Use a tab name typed in a cell
If cell A1 holds a sheet name, such as January, this formula reads cell B2 from that sheet:
=INDIRECT("'"&A1&"'!B2")The single quotes inside the double quotes make it work even when the name has spaces. Change A1 to February and the result switches to that sheet. This is handy for a summary sheet with a drop-down list of month tabs.
INDIRECT has two drawbacks. It recalculates every time anything changes, which can slow down big workbooks, and it doesn’t update if you rename a tab, so the formula returns #REF! until you fix the name in A1.
4. List all tab names
No regular worksheet function lists every tab, but an older macro function can do it through a named range:
- Go to Formulas > Define Name.
- Name it SheetNames.
- In Refers to, enter =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) and select OK.
- In Excel 365, type =TRANSPOSE(SheetNames) in a cell. The names spill down the column. In older versions, type =INDEX(SheetNames,ROW(A1)) and copy it down.
- Save the file as an Excel Macro-Enabled Workbook (.xlsm), or the name stops working when you reopen it.
To just count tabs, use =SHEETS(). To get a tab’s position, use =SHEET(“Sales”).
Example: a summary sheet that follows a drop-down
Say you have one tab per month with the total in B20 on each. On a Summary tab:
- In A1, add a drop-down with Data > Data Validation > List, listing the month tab names.
- In B1, enter =INDIRECT(“‘”&A1&”‘!B20”).
- Pick a month in A1, and B1 shows that month’s total.
Because the tab names come from the list, there’s no typing error to cause #REF!.
Troubleshooting
#REF!
The sheet name is misspelled, has a space without quotes, or the tab was deleted. Check the spelling and add single quotes around names with spaces or symbols.
The formula shows the path, not the sheet name
The workbook isn’t saved, or the closing bracket wasn’t found. Save the file and recalculate with F9.
Every sheet shows the same name
The CELL function is missing its second argument. Add ,A1 so each sheet looks at itself.
Frequently asked questions
Can I reference a tab in another workbook?
Yes. Excel adds the file name in square brackets, for example =[Budget.xlsx]Sales!B2. The same trick is used in a VLOOKUP between two sheets.
How do I add the same cell from many tabs?
Use a 3D reference: =SUM(January:December!B2). See how to add totals from different sheets.
How do I find differences between two tabs?
Use a formula such as =Sheet1!A1<>Sheet2!A1 or conditional formatting. See how to compare two sheets in Excel.
Related: How to Insert a Hyperlink in an Excel Cell with Other Text.
Related: how to use XLOOKUP with two sheets.
Related: show tabs in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.