In Microsoft Excel, renaming a worksheet can help you organize and identify your data more effectively. This tutorial will guide you through various methods to rename a single worksheet, or rename multiple worksheets simultaneously, and provide you with essential rules for naming worksheets in Excel.

Rename a worksheet Double-clicking the sheet tab Right-clicking the sheet tab Using a keyboard shortcut Rename multiple worksheets Customizable batch renaming for specific/all sheets VBA method for renaming all sheets Rules for naming worksheets in Excel

Video: Rename sheets in Excel

Rename a worksheet in Excel

There are three easy ways to rename a sheet in Excel. Let's dive into each of these methods.

Rename a worksheet by double-clicking the sheet tab

1. Double-click on the worksheet tab you want to rename to activate the name editing mode.
2. Enter the desired name for the worksheet and press Enter.
3. 

Rename a worksheet by right-clicking the sheet tab

1. Right-click on the tab of the worksheet you want to rename.
2. From the drop-down menu that appears, select Rename to activate the name editing mode.
3. Type in your new name and press Enter.
4. 

Rename a worksheet by using a keyboard shortcut

1. Select the worksheet tab you want to rename.
2. Press Alt + H + O + R in sequence to activate the name editing mode.
3. Type in your new name and press Enter.

Rename multiple worksheets in Excel

In the following section, we will discuss two efficient methods for renaming multiple selected worksheets, or all worksheets in a workbook at once. Whether you're looking for a customizable renaming approach or a straightforward solution, we have you covered.

Batch rename specific/all worksheets with a versatile tool

With Kutools for Excel's Rename Worksheets feature, renaming multiple worksheets in the active workbook simultaneously becomes a breeze. You can choose the worksheets to rename and either add a prefix/suffix or replace the original names altogether. For these modifications, you can utilize an input value, values from a specified range, or a value from a specific cell in each selected worksheet.

After installing Kutools for Excel, navigate to the Kutools Plus tab, and select Worksheet > Rename Worksheets. In the dialog box that appears, do as follows:

1. Select the worksheets you wish to rename.
2. Choose the desired renaming options.
3. Select the name source. If you select the From an input box option, you should enter a value in the input box.
4. 

Result

The selected sheets are instantly renamed based on your chosen renaming options.

Rename all worksheets with VBA (Complicated)

In this section, we will introduce two VBA codes to either add a prefix/suffix to all worksheet names or rename all worksheets based on a specific cell's value in each worksheet.

Step 1: Create a new module

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
2. Click Insert > Module to create a new module.
3. 

Step 2: Copy VBA code to module window

Copy either of the below VBA codes and paste it to the opened Module window according to your needs.

• VBA code 1: Add prefix/suffix to all sheet names

• Sub renameSheetsWithPrefixSuffix()
  'Update by ExtendOffice
      Dim xWs As Worksheet
      Dim xPrefix As String
      Dim xSuffix As String
      xPrefix = "MyPre_" 'Replace "MyPre_" with your desired prefix.
      xSuffix = "_MySuf" 'Replace "_MySuf" with your desired suffix.
      On Error Resume Next
      For Each xWs In Worksheets
          xWs.Name = xPrefix & xWs.Name & xSuffix 'This snippet adds both prefix and suffix to sheet names. Adjust as needed.
      Next xWs
  End Sub

• Note:

  • In this code, to add a prefix and suffix to all sheet names, you should replace "MyPre_" and "_MySuf" on the 6th and 7th lines with your desired prefix and suffix respectively.
  • To only add a prefix, modify the 10th line of code to be xWs.Name = xPrefix & xWs.Name.
  • To only add a suffix, modify the 10th line of code to be xWs.Name = xWs.Name & xSuffix.
• VBA code 2: Rename all sheets based on specified cell's value in each sheet

• Sub renameSheetsBasedOnCellValue()
  'Update by ExtendOffice
      Dim xWs As Worksheet
      Dim xRgAddress As String
      xRgAddress = "A1" 'Replace "A1" with your target cell address.
      On Error Resume Next
      For Each xWs In Worksheets
          xWs.Name = xWs.Range(xRgAddress).Value
      Next xWs
  End Sub

• Note: In this VBA code, remember to change "A1" on the 5th line to the actual cell address that contains the new name for each worksheet. Also, ensure that the specified cell (in this case A1) on each sheet contains a value. Otherwise, running this VBA code will lead to a run-time error '1004'.

Step 3: Run the VBA code

In the Module window, press F5 or click the button to execute the pasted code.

Result

• Result of VBA code 1: “Pre_” and “_Suf” are added as a prefix and suffix respectively to all sheet names.
• 
• Result of VBA code 2: All sheets are renamed based on the value in cell A1 of each sheet, which are “Test1”, “Test2”, and “Test3”, respectively.
• 

Rules for naming worksheets in Excel

While renaming your worksheets, there are a few rules you need to follow:

• Worksheet names must be unique within a workbook.
• Worksheet names must not exceed 31 characters.
• Worksheet names must not be blank.
• Worksheet names must not contain the characters: \ / ? : * [ ].
• Worksheet names must not begin or end with an apostrophe ('), although it can be used somewhere in the middle of the name.
• Worksheet names must not be History as it's reserved for internal use by Excel.

By following this tutorial, you can effectively rename worksheets in Excel to better organize and manage your data. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.

Related articles

• How to copy sheet and rename automatically in Excel?
• In Excel, how could you copy a worksheet and rename it automatically without typing the name as usual? In this article, I will talk about how to copy a sheet one time or multiple times and give them specific names at once.