Table of Contents
Excel is an incredibly powerful tool for business professionals. It allows users to quickly manage, organize, and analyze data. One of the most powerful features of Excel is its ability to reference tab names in formulas. This feature allows users to create complex formulas that refer to data on multiple sheets. By referencing tab names in formulas, users can easily link data between different sheets and consolidate data from multiple sources. This makes it easier to make quick decisions based on accurate information. Referencing tab names in formulas also helps prevent errors, as incorrect data is easily identifiable and can be corrected quickly. As such, the ability to reference tab names in formulas is an incredibly important feature of Excel.
You can use the following formula in Excel to reference the current tab name (i.e. “sheet” name) in a formula:
=TEXTAFTER(CELL("filename"), "]")
This formula will return the current tab name.
For example, it may return Sheet1.
The following example shows how to use this formula in practice.
Fortunately this is easy to do using a simple formula in Excel and the following example shows how to do so.
Example: How to Reference Tab Name in Formula in Excel
Suppose we have the following Excel workbook with five sheets:
Suppose we are currently working in Sheet3 and we would like to create a formula to return the name of this current sheet.
We can type the following formula into cell A1 to do so:
=TEXTAFTER(CELL("filename"), "]")
The following screenshot shows how to use this formula in practice:
Notice that the formula returns Sheet3, which is the name of the sheet currently in use.
How This Formula Works
Recall the formula that we used to return the active sheet name:
=TEXTAFTER(CELL("filename"), "]")
This formula first uses CELL(“filename”) to extract the entire file path for the Excel workbook.
For example, if we only typed CELL(“filename”) in cell A1 then this would return the entire file path:
Next, we use the TEXTAFTER function to extract all text from this file path after the closing bracket ( ] ) in the file path.
This returns just the sheet name, which turns out to be Sheet3.
Note: You can find the complete documentation for the TEXTAFTER function in Excel .