How to match the cell value with sheet tab name or vice versa in Excel?
In day-to-day work with Excel, you may encounter situations where you need the cell value to reflect the name of the worksheet tab, or you wish to automatically rename a worksheet tab based on the value in a specific cell. Ensuring that the worksheet name and a cell’s content correspond can be especially useful for better data organization, creating dynamic reports, or managing workbooks with multiple sheets, such as monthly reports or project tracking. This article introduces practical methods to easily establish a link between sheet tab names and cell values, or to perform the reverse operation.
Match the cell value with sheet tab name with formula
Match the cell value with sheet tab name with Kutools for Excel
Name the sheet tab with cell value by VBA code
Name the sheet tab with cell value by Kutools for Excel
Match the cell value with sheet tab name with formula
Linking the name of your worksheet tab to a cell value can help keep your spreadsheets organized and easy to navigate, especially when working with dynamic reports or summary sheets. Excel provides a way to display the sheet tab name inside a cell by using a formula, so you can always reference or check which sheet you are working on directly from within the worksheet.
1. Select a blank cell where you want to display the current sheet tab name, for example, B1. Enter the following formula into this cell and press the Enter key:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
After confirming the formula, the cell will immediately display the current worksheet's tab name. Keep in mind that this formula works only if the workbook has been saved at least once, since the CELL("filename") function relies on the saved file path. If you see a #VALUE! error, try saving your file first.
This solution is particularly suitable for users who prefer a formula-based approach and do not want to rely on add-ins or macro code. However, it is limited to displaying the current sheet tab name—it does not automatically update other sheet tabs, nor does it change sheet names based on cell content.
Practical tip: If you want this reference on every sheet, you can copy the formula cell and paste it into the same position on each worksheet.
Error reminder: If you move or rename the worksheet, the formula updates automatically, but if you copy the worksheet, double-check the formula as the CELL function might reference incorrect file paths until the workbook is re-saved.
Easily insert tab name in a specific cell, header or footer in worksheet:
With the Kutools for Excel 's Insert Workbook Information utility, you can conveniently insert the active tab name into a specific cell within your worksheet with just a few clicks. In addition to the sheet name, this tool allows you to quickly reference other information such as workbook file name, file path, user name, and more—either in cells, headers, or footers as needed, enhancing documentation clarity and record keeping. Click for details.
Download Kutools for Excel Now! (30-day free trial)
Match the cell value with sheet tab name with Kutools for Excel
If you find formulas complex or hard to remember, a dedicated add-in can simplify the process. Kutools for Excel offers the Insert Workbook Information utility, allowing you to match and display the worksheet tab name in a specified cell with a user-friendly dialog. This approach is highly recommended if you frequently need to reference tab names or other document info, or prefer a click-based method without formula memorization.
1. Select the cell where you would like the sheet tab name to appear. Next, go to the Kutools Plus tab, click Workbook, and then select Insert Workbook Information.
2. In the Insert Workbook Information dialog box, select Worksheet name in the Information section. Make sure that Range is selected in the Insert at section, and click OK to confirm. See screenshot below:
You should now see the worksheet tab name instantly populated in the chosen cell. This method is error-free and especially useful when working with multiple sheets and organizing large workbooks, as you can quickly repeat the process as needed.
Advantages: It eliminates formula errors, works regardless of workbook save status, and provides more options for extracting workbook info.
Note: This method requires Kutools for Excel to be installed.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Name the sheet tab with cell value by VBA code
Sometimes you may want to automate the process of renaming a worksheet tab so that it always matches a specific cell’s content, which could be helpful for monthly report sheets, dynamically named sheets, or inventory sheets. VBA (Visual Basic for Applications) provides a way to accomplish this efficiently.
1. Press Alt + F11 together to open the Microsoft Visual Basic for Applications editor.
2. In the editor window, select Insert > Module. Copy and paste the following VBA code into the module window:
VBA code: name the sheet tab with cell value
Sub TabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End SubNote: In this code, A1 refers to the cell whose value will be used as the worksheet tab name. You can adjust this reference to any other cell as needed, for example, use Range("B2").Value if you want to use cell B2.
3. Press the F5 key or click the Run button to execute the code, and your worksheet tab will be renamed immediately using the specified cell’s content.
Practical tip: If your sheet name contains invalid characters (such as < > : " / \ | ? *), you may encounter an error. In such cases, ensure the cell value is a valid sheet name. Sheet names cannot be empty, exceed 31 characters, or duplicate other sheet names.
Error reminder: Always save your workbook before running new VBA code to avoid potential data loss if an unexpected error occurs.
VBA-based methods are suitable for users comfortable with macros and those who wish to automate renaming tasks. However, these require macro-enabled workbooks and basic familiarity with the VBA editor.
Name the sheet tab with cell value by Kutools for Excel
For those seeking a faster, non-coding solution, Kutools for Excel's Rename Worksheets utility enables you to rename one or multiple worksheet tabs directly using the values from cells. It's especially practical in scenarios where you need to batch rename worksheets (for example, renaming each sheet with the content of cell A1 on each sheet).
1. Select the cell whose content you wish to use as the new worksheet name. Then, click Kutools Plus > Worksheet > Rename Worksheets.
2. In the Rename Multiple Worksheets dialog, check the worksheet(s) to rename in the Worksheets box. Select Replace original sheet name in the Rename Options section, then choose From specific range or Rename worksheets with specific cell as appropriate. Click OK.
3. After clicking OK, a dialog box will appear confirming how many worksheet names have been updated. Simply click OK to finish.
Now, as shown in the screenshot, the worksheet tab has been renamed according to the chosen cell value. This approach minimizes error and saves time, especially useful for batch renaming or regular updates.
Note: Ensure that the cell used for naming does not contain invalid characters or exceed the length limit for worksheet names in Excel.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Match the cell value with sheet tab name or vice versa in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in