When working with more than one sheet, it’s a good idea to give those sheets meaningful names. Doing so improves efficiency, from the simple task of clicking the right sheet to referencing sheets in VBA procedures—those names help us get things done easier and quicker. Most of us pick up a naming method and stick with it, never realizing there’s more than one way to name Excel sheets. In this article, we’ll discuss the rules for naming sheets. Then, I’ll show you five ways to rename sheets—depending on what you’re doing, using a little-known method that can save you a bit of time.
SEE: Software Installation Policy (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. There’s no demonstration file because you won’t need one. Excel for the web supports all five methods for renaming sheets.
Rules for naming sheets in Excel
By default, Excel names sheets using the Sheetx format where x is a consecutive number, as shown in Figure A.
As you add more sheets, x increases accordingly. I recommend that you give sheets a meaningful name if you’re working with more than a few sheets. Even if you don’t use VBA, a meaningful name helps guide you to right sheet instantly. Meaningful names also help when referencing sheets in functions and formula, VBA, and more.
Like most object names, sheet naming has a few rules:
- You can’t use a reserved word. If you try to use one of Microsoft Excel’s reserved words, Excel will reject the name and tell you why. Find a new name; there’s no way around this.
- A sheet must have a name, even if you leave the default name.
- Sheet names are limited to 31 characters.
- There are a few special characters—[ ] / \ ? * :—that you can’t include in a sheet name.
- Don’t start or end a sheet name with the apostrophe character (‘).
These rules aren’t so bad; you’ll find them easy to respect. Now, let’s look at a few ways to rename sheets.
Perhaps the easiest way is also the most intuitive. Simply double-click the sheet name right on the tab. Doing so will highlight the name in edit mode. At this point, you simply write over the name and press Enter. You’re probably already familiar with this routine because Windows lets you rename files this way in File Explorer.
Another quick method for putting the sheet name in edit mode is to right-click it. In the resulting submenu, choose Rename, as shown in Figure B. Excel will highlight the current name in edit mode at which time you can overwrite the current name. The right-click is about as intuitive as the double-click, but it requires more clicks.
3. The Excel Home tab
The Home tab offers a way into the sheet tab, but most users will never find it on their own. First, click the Home tab and then click Format in the Cell group. Select Rename Sheet, shown in Figure C, from the resulting menu. Doing so will highlight the current sheet name in edit mode at which time you can overwrite the sheet name.
Knowing the Rename Sheet command is available on the Format dropdown is the key—most users don’t know about it and won’t go looking for it there. Using one of the first two methods, I can’t think of a good reason for them to bother.
4. Accessibility check
Users requiring an accessibility reader won’t get much from Sheet1, Sheet2 and so on. If you don’t rename sheets for yourself, that’s fine. However, if you don’t know who might end up using your workbook, run the Check Accessibility feature in the Accessibility group on the Review tab. When Excel returns the results, look for the Default Sheet Names section. Click the dropdown arrow and choose Rename Sheet, as shown in Figure D, which will highlight the sheet name in edit mode. If a sheet has already been renamed from the default, Check Accessibility won’t list the sheet.
This method isn’t particularly intuitive, and you won’t want to use it for renaming sheets specifically. However, it’s good to know that the checker will catch unnamed sheets for you. Hidden sheets won’t show up in the list either, but there’s less of a need to rename hidden sheets than the sheets users interact with.
SEE: Why Microsoft Lists is the new Excel (TechRepublic)
5. Excel’s new Navigation pane
Some Microsoft 365 users have the new Navigation pane. To display it, click the View tab and check the Navigation pane option in the Show group. If you don’t see that option, you don’t have it yet, but should soon.
Excel’s Navigation pane shows a list of objects, sheet names, PivotTables, and more. Click an item to go straight to it. Or right-click a sheet name to highlight it in edit mode so you can rename it. This isn’t a great feature for renaming a sheet; there are easier ways. But if you happen to already be in the Navigation pane, it’s quicker than closing the pane and using one of the easier methods.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays