How to make sheet tab name equal to cell value in Excel?
When managing workbooks with multiple worksheets, it is often helpful to give each sheet a descriptive name that matches its content. In many cases, you may want the sheet tab name to reflect the value entered in a specific cell—for example, using the project name entered in cell A1 as the sheet’s name. This approach can simplify navigation, make your data structure clearer, and improve overall efficiency when working with large amounts of information or sharing files with colleagues. In this article, we will introduce several practical methods to make a sheet tab name automatically—or conveniently—match a specific cell value in Excel.
Make sheet tab name equal to cell value with VBA code
Make sheet tab name equal to cell value with Kutools for Excel
Make sheet tab name equal to cell value with VBA code
If you want the worksheet tab name to update dynamically and automatically follow the value in a particular cell, you can accomplish this using VBA (Visual Basic for Applications). This method is particularly useful for templates or files where sheet names need to change based on user input—such as naming sheets after departments, dates, or custom categories. While this solution requires a one-time code setup, it is efficient and eliminates manual renaming.
1. Right-click the sheet tab that you want to automatically link to the value of a cell, then choose View Code from the right-click menu. See below:
2. In the opening Microsoft Visual Basic for Applications window, copy and paste the following code into the blank Code window. After pasting, press Alt + Q simultaneously to close the window and return to Excel.
VBA code: Make sheet tab equal to cell value
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230130
On Error Resume Next
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("A1")
ElseIf Not Intersect(Target.Dependents, Range("A1")) Then
ActiveSheet.Name = ActiveSheet.Range("A1")
End If
End SubParameter explanation: In the code, A1 denotes the cell whose value you want to use as the sheet name. You can modify "A1" to reference any other cell (e.g., "B2" or "C5") according to your needs.
Now, whenever the value in the designated cell (such as A1) changes, the worksheet tab name will automatically update to match it. This happens without any further manual steps.
Applicable scenarios: Use this method if you want the sheet name to change immediately and automatically when a specific cell’s content is edited.
Limitation: VBA macros must be enabled in your workbook, and you need to save the file as a macro-enabled workbook (*.xlsm). Changing the referenced cell to a name Excel doesn’t allow (such as special characters or exceeding 31 characters) will trigger errors.
Tips and error reminders:
- Sheet names in Excel cannot exceed 31 characters and may not contain any of the following characters: \ / : ? * [ ]. If the cell value contains invalid characters, the VBA code will display an error.
- If you need to switch the auto-renaming to reflect a different cell, simply amend the cell reference in the code and save the changes.
- To enable macros and ensure the code works, your workbook must be saved in a macro-enabled format (*.xlsm).
- If you want other sheets to behave similarly, repeat the steps for each desired sheet.
Make sheet tab name equal to cell value with Kutools for Excel
For users who prefer not to use VBA or want to batch rename sheets quickly without writing code, Kutools for Excel offers a convenient solution. The Rename Multiple Worksheets utility allows you to rename one or more worksheets according to the values in specific cells. This approach is particularly suited for situations where you need to update several sheet names at once—such as creating monthly reports, team summaries, or when standardizing sheet names across a workbook.
To rename sheet tabs based on cell values in bulk, please follow these steps:
1. Click Kutools Plus > Worksheet > Rename Multiple Worksheets. See screenshot:
2. In the Rename Multiple Worksheets dialog box, configure your desired settings as follows:
Now, the selected worksheets will immediately be renamed to match the corresponding values in the specified range, as demonstrated below.
Notes:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Make sheet tab name equal to cell value with Kutools for 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