While you can always manually enter the sheet name, it won’t update in case the sheet name is changed. So if you want to get the sheet name, so that it automatically updates when the name is changed, you can use a simple formula in Excel. In this tutorial, I will show you how to get the sheet name in Excel using a simple formula.
Get Sheet Name Using the CELL Function
CELL function in Excel allows you to quickly get information about the cell in which the function is used. This function also allows us to get the entire file name as a result of the formula. Suppose I have an Excel workbook with the sheet name ‘Sales Data’ Below is the formula that I have used in any cells in the ‘Sales Data’ worksheet: As you can see, it gave me the whole address of the file in which I am using this formula. But I needed only the sheet name, not the whole file address, Well, to get the sheet name only, we will have to use this formula along with some other text formulas, so that it can extract only the sheet name. Below is the formula that will give you only the sheet name when you use it in any cell in that sheet: The above formula will give us the sheet name in all scenarios. And the best part is that it would automatically update in case you change the sheet name or the file name. Note that the CELL formula only works if you have saved the workbook. If you haven’t, then it would return a blank (as it has no idea what the workbook path is) Wondering how this formula works? Let me explain! The CELL formula gives us the whole workbook address along with the sheet name at the end. One rule it would always follow is to have the sheet name after the square bracket (]). Knowing this, we can find out the position of the square bracket, and then extract everything after it (which would be the sheet name) And that’s exactly what this formula does. The FIND part of the formula looks for ‘]’ and return it’s position (which is a number that denotes the number of characters after which the square bracket is found) We use this position of the square bracket within the RIGHT formula to extract everything after that square bracket One major issue with the CELL formula is that it’s dynamic. So if you use it in Sheet1 and then go to Sheet2, the formula in Sheet1 would update and show you the name as Sheet2 (despite the formula being on Sheet1). This happens as the CELL formula considers the cell in the active sheet and gives the name for that sheet, no matter where it is in the workbook. A workaround would be to hit the F9 key when you want to update the CELL formula in the active sheet. This will force a recalculation.
Alternative Formula to Get Sheet Name (MID formula)
There are many different ways to do the same thing in Excel. And in this case, there is another formula that works just as well. Instead of the RIGHT function, it uses the MID function. Below is the formula: This formula works similarly to the RIGHT formula, where it first finds the position of the square bracket (using the FIND function). It then uses the MID function to extract everything after the square bracket.
Fetching Sheet Name and Adding Text to it
If you’re building a dashboard, you may want to not just get the name of the worksheet, but also append a text before or after it. For example, if you have a sheet name 2021, you may want to get the result as ‘Summary of 2021’ (and not just the sheet name). This can easily be done by combining the formula we saw above with the text we want before it using the ampersand operator. Below is the formula that will add the text ‘Summary of ‘ before the sheet name: The ampersand operator (&) simply combines the text before the formula with the result of the formula. You can also use the CONCAT or CONCATENATE function instead of an ampersand. Similarly, if you want to add any text after the formula, you can use the same ampersand logic (i.e., have the ampersand after the formula followed by the text that you want to append). So these are two simple formulas that you can use to get the sheet name in Excel. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Rename a Sheet in Excel (4 Easy Ways + Shortcut)How to Insert New Worksheet in Excel (Easy Shortcuts)How to Unhide Sheets in Excel (All In One Go)How to Sort Worksheets in Excel using VBA (alphabetically)Combine Data From Multiple Worksheets into a Single Worksheet in ExcelHow to Compare Two Excel SheetsHow to Group Worksheets in Excel
