How to Rename a Worksheet in Excel?

By default, Excel names worksheets in this format: Sheet1, Sheet2, Sheet3.

We could leave them as is. However, it would be more ideal to give them a more meaningful name so that when we use them as references in formulas, we can readily pinpoint where the values are taken from.

Also, when you have loads of worksheets in your workbook, other users can immediately locate the sheet they need to go to when the worksheets have descriptive names.

Rules in Naming a Worksheet

Before going into the steps on how to rename worksheets, please take note of the following rules when renaming a worksheet:

It must not be left blank.
It must only have a maximum of 31 characters.
It must not contain any of these characters: / \ ? * : [ ]
- If you want to add a date to be used for sorting later in the worksheet name, you can’t add it in this format: 01/01/2021 Inputs.
- However, you can swap the slash with a dash: 01-01-2021 Inputs.
It must not start (remove the apostrophe if it does) or end with an apostrophe (‘), but you can add the apostrophe in between texts or numbers.
It must not be named “History”. This is a reserved word that Excel uses internally.
It must not have the same name as any of the existing sheets.

Rename a Worksheet in Excel by Double-Clicking the Worksheet

This is probably the default method that most people use when renaming a worksheet:

1. Double-click on the worksheet. The entire worksheet name will be highlighted.

2. Enter the new name and press ENTER.

That’s it! It’s super quick and easy.

Just repeat the same steps to rename other worksheets.

Rename a Worksheet in Excel by Right-Clicking on the Worksheet

If the left button in your mouse has been overused and is not working as it’s supposed to be, double-clicking can already become a difficult task. Luckily, you can still rename a worksheet using the right mouse button.

1. Right-click on the worksheet you want to rename. A pop-up menu will appear. Select Rename.

Right-click on the worksheet and select Rename from the menu that pops up.

2. Type the new name and press ENTER.

That’s it! The worksheet name will be updated if it doesn’t violate any of the naming rules.

Rename a Worksheet in Excel Using Keyboard Shortcuts

If you prefer doing things with keyboard shortcuts, then you will like this next method.

To rename a worksheet:

1. Activate the worksheet you want to rename.

2. Hold the ALT key and press H + O + R.

Steps to rename worksheet using a keyboard shortcut.

The name of the worksheet will be highlighted.

3. Type the new name and press ENTER.

PRO TIPS:

If you need to also rename the next worksheet, just press CTRL + Page Up to activate the next sheet and repeat steps 2 and 3.
If you need to go to the previous sheet, press CTRL + Page Down.

Rename a Worksheet in Excel from the Excel Ribbon

You can also rename a worksheet from the Excel Ribbon.

1. Activate the worksheet you want to rename.

2. From the Home tab, find the Cells section and click the Format button.

3. A drop-down menu will appear. Select Rename Sheet.

4. The current worksheet name will be highlighted.

Type the new name and press ENTER.

Rename a Worksheet in Excel Using a Macro or VBA

If you need to rename all worksheets in your workbook to add a prefix or a suffix, then this method is perfect for you.

IMPORTANT:

Before doing the steps below, please secure a copy of your workbook for backup. Once the macro you added runs, the steps cannot be undone, so it’s better to have a backup just in case.

In my example below, I need to add the year as the prefix to all the worksheets inside the workbook.

Screenshot of the worksheets that I'm working on.

To do this:

1. Press ALT + F11 to open the VBA Editor.

2. Go to the Insert menu and select Module.

3. Next, we’ll insert the appropriate code into your new module.

Copy the following code if you need to add a prefix to your worksheets:

Option Explicit

Sub AddPrefixToWorksheet()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Name = "2020 " & ws.Name
        End If
    Next
End Sub

On the other hand, if you need to add a suffix, use the following code:

Option Explicit

Sub AddPrefixToWorksheet()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Name = ws.Name & “ 2020”
        End If
    Next
End Sub

4. Once you’ve copied the appropriate code, change the text inside the double quotes with the prefix or suffix you want to add.

Update the highlighted text with the appropriate text.

Just remember to leave the double quotation marks as is.

5. Once you’ve added the correct text, you can now run the code.

IMPORTANT:

Please note that the code above only updates the visible worksheets. If you need to update all worksheets inside the workbook, ensure they are all visible before running the code.

To run the code, place the text cursor anywhere inside the code and press F5.

6. That’s it! Close the VBA Editor and go back to your workbook.

You should now see the worksheets updated with the prefix or suffix you have added.

7. If you want to be able to use this macro next time, remember to save the file in .xlsm or .xlsb format.

On the other hand, if you intend to still save it in .xlsx format, when the following prompt appears when you save the workbook, click Yes.

Prompt that appears when you save a .xlsx file containing macro.

Conclusion

Renaming worksheets is an essential task if you intend to make your workbook easy to read and use. I hope the methods listed can help you do this task on the fly. Also, you can check the “How to Delete A Sheet in Excel” article.

How to Rename a Worksheet in Excel?

Table of Contents