How to Rename Multiple Sheets in Excel (With Easy Steps)

While working on Excel most of the time we create multiple sheets and it becomes necessary to rename the sheets. It’s quite hazardous to rename each sheet separately. Instead, we can change the names of the sheets at once. In this article, we will show the procedure to rename multiple sheets in Excel.

How to Rename Multiple Sheets in Excel: Step-by-Step Procedures

We can rename multiple sheets instantly by using a simple Visual Basics for Applications (VBA) code. It can reduce a lot of hard work and time. We are showing the steps for the method below.

Step 1: Insert Multiple Sheets

• First, for renaming multiple sheets we have to insert multiple sheets in the workbook.
• For example, we have created three separate sheets whose names we want to change at once. The sheets contain some data on products and their price.

In Sheet1

Again in Sheet2

And in Sheet3

Step 2: Open VBA Window

• Now we have to open the VBA window. We can open the window by selecting the Developer tab > Visual Basic.

• We can also use the keyboard shortcut Alt + F11 to open the VBA window.
• Once the VBA window opens, from VBAProject we select our active sheet and right-click on it.
• Next, select Insert > Module.

• Consequently, a VBA code window will pop up.

Step 3: Write VBA Code

• In the window write the VBA code given below.

Sub ChangeWorkSheetName()
Dim R_N_G As Range
Dim W_R_N_G As Range
On Error Resume Next
xTitleId = "Rename Sheets"""
newName = Application.InputBox("Name", xTitleId, "", Type:=2)
For k = 1 To Application.Sheets.Count
Application.Sheets(k).Name = newName & k
Next
End Sub

Step 4: Run the Code

• Now, it’s time to run the code. All we need to do is click on the Run button from the menu bar.

• Afterward, a Rename Sheets box will appear. We need to type the new name (Product-Price-Sheet) for our sheets.

• Then, select OK and close the VBA window.
• Finally, come back to the workbook and we will see all the names of our sheets changed with the new name and a suffix number.
• In our case, the name is Product-Price-Sheet1, Product-Price-Sheet2, Product-Price-Sheet3.
• Hurrah! We have successfully renamed multiple sheets at once.

6 Effective Methods to Rename One Sheet at a Time in Excel

So far we have changed multiple sheet names at once. We can change the sheet names individually following several methods. In this section, we will discuss steps for 6 effective and simple methods to rename one sheet at a time.

1. Rename the Sheet by Double Clicking on the Sheet Tab

The sheet name can be changed by double-clicking on the sheet name. The steps for doing that are given below.

• First, double-click (left) on the sheet name from the sheet name tab at the bottom.
• Then, type the new name (Double Click).
• Afterward, hit Enter.

• Finally, we can see the sheet name changed.

2. Use the Rename Option from the Menu in Excel

We can also change the name of the sheet from the context menu. The steps for that are given below.

• First, select the sheet from the sheet tab at the bottom and right-click on it.
• Consequently, the context menu will appear and we select Rename from the option.

• Now, type the new name which is Rename Option.
• Further, press Enter.
• Finally, we can see the sheet name changed.

3. Rename Sheet with Format Option

The sheet name can be changed by using the Format option. We are showing the steps for the method.

• From the Home tab in the ribbon, select the Format option and choose Rename Sheet.

• Later on, type the new name (Format Option) for the sheet and hit Enter.
• Bravo! We will see the sheet name changed instantly.

4. Apply the Keyboard Shortcut to Rename a Sheet

We can rename the sheet by applying a keyboard shortcut. The steps for doing that are given below.

• First, open the sheet whose name we want to change.
• Now, press the keyboard shortcut Alt + O + H + R.

• Then, type the new name (Keyboard Shortcut) for the sheet.
• Finally, hit Enter and we will see the sheet name changed.

Read More: How to Rename a Sheet without Using Mouse in Excel

5. Rename a Sheet Using Excel Accessibility Menu

Renaming a sheet can be done using the Excel Accessibility menu. We are showing the steps below.

• At the start, open the sheet to change its name.
• After that, go to Review of the ribbon and select Check Accessibility.

• Consequently, the Accessibility tab will show up and we choose the Rename Sheet from the options.

• Then, type the new name (Accessibility Menu) and hit Enter.
• Finally, we can see the sheet name changed.

6. Use the Navigation Pane to Rename One Sheet at a Time

Another way to rename a sheet is to use the Navigation Pane. The steps to rename the sheet for this method are given below.

• First, we open the sheet to change its name and select Navigation from the View tab in the ribbon.

• Instantly, a Navigation tab will open.
• Then, we select the sheet from the list whose name we want to change and right-click on it.
• Select Rename from the options.

• Finally, type the new name (Using Navigation Pane) for the sheet and select OK.

• Hurrah! We have successfully renamed the sheet.

What Are the Rules for Renaming Multiple Sheets in Excel?

• The sheet name can’t be more than 31 characters.
• We can’t give the same name to different sheets.
• The sheet name can’t be Blank.
• We can’t start or end the sheet name with apostrophes (‘) but we can use it in the middle of the name.
• A few characters ([ ] \ / ? : * ) can’t be used in the sheet name.
• We can’t name a sheet History as it is internally used by Excel.

Download Practice Workbook

You can download the practice workbook from here.

For each sheet,

Conclusion

Renaming sheets come in handy while working in Excel. If the number of sheets is large and we want to change the names, it’s hazardous to change each sheet name individually. In this article, we have shown the method to rename multiple sheets at once in Excel. Hope you will benefit from this article. If you have any queries or suggestions, please let us know by commenting.

<< Go Back to Rename Worksheet | Worksheets | Learn Excel