Using multiple worksheets in an Excel workbook is an easy way to keep your data organized. For example, you can have different sheets for different regions, or for different months/year, or different projects, etc.
And when working with multiple sheets, you may sometimes need to change their names.
In this Excel tutorial, I will show you some simple ways to rename a sheet in Excel (including a keyboard shortcut).
And in case you want to rename all the sheets as one go (such as adding a prefix or suffix to all the sheets), check out the section on using VBA to do this (it’s not complicated).
So let’s get started!
This Tutorial Covers:
Rename Sheets with a Simple Double Click
Using a mouse is the easiest way to change the name of a sheet in Excel.
Below are the steps to rename a sheet using the mouse double-click:
- Place your cursor over the sheet tab that you want to rename
- Double-click on it. This will put the sheet name in the edit mode
- Enter the name of the sheet that you want
- Hit the Enter key (or click anywhere in the worksheet)
If you want to rename multiple sheets, you need to do these above steps for all the sheets.
Also, if you want to add a prefix/suffix to the sheet name, you the arrow keys to move the cursor when you’re in the edit mode in the tab. Once you have the cursor where you want to add the text, enter in manually.
Rename Sheets with a Keyboard Shortcut
If you prefer using the keyboard and don’t want to switch to a mouse just to rename a sheet, you can also use the following keyboard shortcut (for Windows).
ALT + O + H + R
You need to press these keys one after the other in a succession.
While this is not one of the easy keyboard shortcuts, once you get used to it, you may find it a better way to rename sheets in Excel.
Rename Sheets Using the Format Option in the Ribbon
You can also access the option to rename sheets through the Excel ribbon:
- Click the Home tab
- In the Cell group, click on the ‘Format’ option
- Click on the Rename Sheet option. This will get the sheet name into edit mode
- Enter the name that you want for the sheet
This method would only allow you to change the name of the current sheet. To change the name of some other sheet, you first need to activate it and then use the above steps.
While this method exists, I always prefer using the mouse double-click technique to change the name of the sheet.
Rename Sheets Using VBA – Add Prefix/Suffix to All Sheet Names in One Go
Sometimes, you may have a need to add a prefix or a suffix to all the sheets in your workbook.
While you can do this manually (using the methods shown above) if you only have a few worksheets. But in case you have a lot many, this could be tedious and error-prone.
In such a case, you can use a simple VBA code in the immediate window to get this done in a snap.
Below is the VBA code that will add the prefix “2020 – ‘ in front of all the sheet names:
For Each sh In Worksheets: sh.Name = "2020 - " & sh.Name: Next sh
The above code uses a VBA loop (the For Next loop) to go through each sheet in the workbook and change the name by adding the specified prefix.
Below are the steps to use this VBA macro code in Excel:
- Right-click on any of the sheet tabs
- Click on View Code. This will open the Visual Basic Editor backend
- In the VB Editor window, click on the ‘View’ option in the menu and then click on Immediate Window. This will make the immediate window show up. You don’t need to do this in case the immediate window is already visible.
- Copy and paste the above line of code in the immediate window
- Place the cursor at the end of the line
- Hit the Enter key
As soon as you hit the Enter key, the code is executed and it adds the specified prefix in all the sheet names.
Note that this will rename all the sheets – visible as well as hidden.
Similarly, if you want to add a suffix to all the sheet names, you can use the below code:
For Each sh In Worksheets: sh.Name = sh.Name & " - 2020": Next sh
You can even go a step further and also change the name of some specific sheets (let’s say only the ones where the text 2020 appears). This can easily be done by editing the code.
Sheet Naming Rules in Excel
When renaming a sheet in Excel, there are some naming rules you need to follow:
- A sheet name can be a maximum of 31 characters in length.
- You can not leave a sheet name blank.
- You can not have two sheets of the same name.
- You can not begin or end the sheet name with an apostrophe (‘). However, you can use it in the middle somewhere in the sheet name
- You can not use the following characters while renaming the sheet in Excel: [ ] \ / ? : *
- This means that you can’t have a sheet name with a date that uses a forward slash. For example, 01/01/2020 isn’t a valid sheet name but 01-01-2020 is valid.
- You can not name the sheet History (as it’s used internally by Excel)
I hope you found this Excel tutorial useful.
You may also like the following Excel tips and tutorials: