How to Display Worksheet Names in Excel for the Web When CELL is Unsupported
Question details
The user needs to retrieve and display worksheet names dynamically in Excel for the Web or Microsoft Teams where the CELL function is not supported.
- Product
- Excel for the Web
- Device & OS
- not provided
- Scenario
- Working in Excel for the Web or sharing workbooks through Microsoft Teams where specific functions behave differently than desktop versions.
- Observed behavior
- The CELL function returns a #VALUE! error, preventing the standard method of extracting worksheet names.
Create a dedicated helper sheet in your workbook to store the references for each worksheet you want to extract the name from.
Extract Worksheet Name Using FORMULATEXT and a Helper Reference
Since the CELL function does not work in Excel for the Web, you can create a direct reference to the target sheet and extract its name using the FORMULATEXT function.
This method involves referencing a cell on the target worksheet from a helper sheet, and then parsing that formula as text to extract the sheet's name.
Note that each worksheet requires its own unique reference formula.
Insert a new worksheet in your Excel workbook to act as your reference helper sheet (e.g., named 'Sheet2').
In the helper sheet, enter a direct formula reference to the sheet whose name you want to retrieve. For example, enter ='Test Sheet'!1:1 in cell A2.
In the cell where you want the worksheet name to be displayed, enter the following formula to parse the text: =SUBSTITUTE(MID(FORMULATEXT(Sheet2!A2),2,FIND("!1:1",FORMULATEXT(Sheet2!A2))-2),"'","")
To get names for other sheets, you cannot just drag the formula. You must create a new reference (e.g., ='Another Sheet'!1:1) in a new cell, and update the FORMULATEXT reference to point to that new cell.
Experience Full Formula Support with WPS Office
Avoid function limitations found in Excel for the Web. WPS Office offers a comprehensive, lightweight, and free alternative with full support for advanced spreadsheet functions like CELL, ensuring your complex workbooks work flawlessly.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Microsoft Excel (.xlsx) file seamlessly.
- 3. Use Standard Functions: Easily use standard formulas like =CELL("filename") to extract worksheet names without encountering the #VALUE! errors seen in web versions.
Frequently Asked Questions
Why does the CELL function return #VALUE! in Excel for the Web?
Excel for the Web and Microsoft Teams operate in a browser environment without traditional local file paths. Because of this, environment-specific functions like CELL("filename") are unsupported and return a #VALUE! error.
Can I apply the FORMULATEXT extraction to all sheets at once?
No, you must create a separate direct reference (like ='Sheet Name'!1:1) for each individual worksheet you want to extract the name from, and then point the FORMULATEXT formula to that specific reference.
Is there a simpler built-in function to get the sheet name online?
Currently, Excel for the Web does not have a dedicated built-in function to return the sheet name directly. Users must rely on workarounds like the FORMULATEXT method or utilize Office Scripts.
