7 Ways to Rename a Sheet in Microsoft Excel

2021-09-25

Keeping your spreadsheet organized is important!

A well organized spreadsheet can make a big difference for someone looking at it for the first time.

This can lead to less time required to comprehend and use the file properly with less errors.

Naming sheets appropriately is an integral part of a properly organized spreadsheet.

In this post, I’ll show you 7 ways you can use to rename the sheets in your workbooks.

Video Tutorial

Rules for Sheet Names

There are limits to what you can name a sheet.

If you try to name a sheet History, then you will get the above warning message letting you know it is a reserved name and can’t be used.

This isn’t the only limitation. There are some other conventions and characters that aren’t allowed when naming a sheet.

Here is a list of all the rules you will need to follow when naming your sheets.

  • A sheet can’t be named History.
  • A sheet name can’t be left blank.
  • Sheet names can’t be longer than 31 character.
  • Sheet names can’t contain any of these characters [ ] / \ ? * :
  • Sheet names can’t begin or end with an apostrophe character.

Fortunately, these rules aren’t extremely restrictive and most users won’t ever have any issues. Usually the only rule that anyone comes up against is the maximum length of 31 characters.

Keep your sheet names short and you won’t have any issues!

Rename a Sheet Using the Right Click Menu

When you right click on any of the sheet tabs in a workbook, there are a variety of options available including the ability to rename a sheet from this menu.

Right click on the sheet you would like to rename then select Rename from the options.

The current sheet name will become highlighted and you can type your new name and press the Enter key to confirm.

Pros

  • This is easy to perform and only requires two mouse clicks once you know about it.

Cons

  • You need to discover the right click menu exists before you can use it!

Rename a Sheet Using a Double Left Click

This is the quickest and easiest way of them all.

Once you learn about this mouse trick to rename your sheets, it will likely be your preferred method.

Double left click on the sheet tab which you would like to rename.

Pros

  • The quickest way to rename a sheet.
  • Double click to rename is a fairly common mouse shortcut across various tools.

Cons

  • None.

Rename a Sheet from the Home Tab

It’s a bit hidden, but there is a command in the Home tab for renaming a sheet.

Make sure the sheet you want to rename is the active sheet ➜ go to the Home tab ➜ click on Format in the Cell section ➜ select Rename Sheet from the options.

Unfortunately, this command is too hidden to be of any use. It’s not very intuitive that it is grouped in a button labelled Format.

Pros

  • None

Cons

  • Not easily discovered.

Rename a Sheet Using a Keyboard Shortcut

Unfortunately, there isn’t a dedicated keyboard shortcut for renaming a sheet.

But we can take advantage of the Alt hot key shortcuts.

Alt + H + O + R will get you to the Rename Sheet command in the Home tab.

Pressing the Alt key will activate the hot keys in the ribbon. Then pressing H will select the Home tab. Pressing O will select the Format command and then pressing R will select the Rename Sheets option from the Format command.

Pros

  • You can use the keyboard to rename a sheet.

Cons

  • Hard to remember.

Rename a Sheet Using the Accessibility Menu

This will only work if you haven’t already renamed the sheet from the default name of Sheet1, Sheet2, Sheet3 etc…

Having unnamed sheets represents an accessibility issue because anyone that relies on a screen reader won’t be able to tell what is in a sheet based on those generic default sheet names.

Unnamed sheets will get flagged as an accessibility issue when you run the accessibility checker and you will then be able to rename them.

Go to the Review tab and click on the Check Accessibility command.

This will run a check on the entire workbook and a window pane will open up with the inspection results.

There is a sections called Default Sheet Names which can be expanded to show all the sheets which have not been renamed.

Click on the downward chevron icon to the right of any sheet name then choose Rename Sheet from the options.

This will highlight the sheet name and you can start typing your new name and press Enter when done.

Pros

  • Good as a final check if you have created a large workbook with dozens of sheets.

Cons

  • Hidden sheets do not show up in the inspection results.
  • Not a practical way of renaming sheets.

Rename a Sheet Using the Navigation Pane

A great feature of Microsoft Word is the navigation pane which allows you to see all the headings and subheadings in your word document. You can click on any of the headings and instantly be taken to its location in your document.

Excel also now has a very similar navigation pane feature. The Excel Navigation Pane will show you a list of various objects like sheets, pivot tables, named ranges etc. You can click on any the the listed objects and Excel will take you to its location in the workbook.

Open the Navigation Pane by going to the View tab and clicking on the Navigation button in the Show section of the ribbon.

This will open up the Navigation pane which lists all the sheets in the workbook. Right click on any of the sheets and choose Rename from the menu options.

This will open up Rename input box where you can type your new name. Press the Ok button to change the name.

This might be a handy option if you’ve already got the navigation pane open because you are already using it to navigate. Otherwise, it’s not worth the effort of opening the navigation pane just to rename a sheet.

One thing to note about this method is it’s the only manual method which you can use rename a sheet that’s not active.

Pros

  • You can rename a sheet that is not the active sheet.

Cons

  • Need to have the navigation pane open.

Rename a Sheet Using VBA

If you want to rename your sheets programmatically, then VBA might be the solution for you.

Suppose you wanted to change all the sheet names in your workbook to upper case. Using the previous methods would require a lot of tedious work as you would have to rename each sheet individually.

This is where a programmatic solution is best!

Sub UpperCaseSheets()

For i = 1 To Worksheets.Count
    Sheets(i).Name = UCase(Sheets(i).Name)
Next i

End Sub

Press Alt + F11 to open the visual basic editor then go to the Insert tab and select Module from the options.

Paste in the above code into the module window.

The above code will loop through each sheet in the workbook and change the sheet name to upper case.

This will even rename any sheets that are hidden without needing to unhide them first.

You can run this code from Excel by going to the Developer tab and selecting the Macro command then pressing the Run button.

Pros

  • You can rename sheets without selecting them first.
  • You can rename all sheets with one click.
  • You can rename any hidden sheets in the workbook without unhiding them.

Cons

  • You need to be comfortable with VBA.
  • You will need to save the workbook in the macro enabled xlsm format.

Rename a Sheet Using Office Scripts

Office scripts is another way you can automate task in Excel, but it’s only available in business and enterprise versions of Excel online.

Open Excel Online and go to the Automate tab and click on New Script.

function main(workbook: ExcelScript.Workbook) {
	// get all worksheet objects into ws
	let ws = workbook.getWorksheets();
	
	//loop through each sheet in ws and change the name to uppercase
	for (let i = 0; i < ws.length; i++) {
		ws[i].setName(ws[i].getName().toUpperCase());
	}

};

This will open up the Code Editor where you can paste in the above code. Press the Run button to execute the code.

The above code will get all the worksheets in the workbook and loop through each and rename it upper case text.

This will make quick work of all the sheets in the workbook, including any hidden sheets.

Pros

  • You can rename sheets without selecting them first.
  • You can rename all sheets with one click.
  • You can rename hidden sheets without unhiding them first.

Cons

  • You need to be comfortable with the TypeScript programming language.
  • Office Scripts are only available in Excel online business and enterprise plans.

Conclusions

Renaming sheets is a task you will have to do again and again in Excel.

There are a lot of easy options for this!

There are even ways to automate this for situations that require naming or renaming a large number of sheets.

Do you have a method for renaming your sheets that’s not mentioned? Let me know in the comments!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest