Written by Allen Wyatt (last updated September 21, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Thomas has a workbook in which the worksheet names are long, averaging about 22 characters or so. This makes the worksheet tabs quite wide, and Excel shows the entire name in the tabs. Of course, this makes it difficult to navigate from sheet to sheet using the tabs. Thomas wonders if there is a way to specify a maximum width for the worksheet tabs so he can see more tabs in the workbook at one time.
The short answer is that no, there is no way to specify a maximum width. Even if there was, it wouldn't help if your worksheets all started with similar characters, such as "Joint Committee." That is 15 characters, so shortening from the 22 average that Thomas has would result in worksheet tabs that would be difficult to tell apart one from another.
The only solution is to shorten the name of the worksheet tabs. For instance, remove any leading characters that are common to all the worksheets, or use acronyms or abbreviations. You could create a macro to handle some of this, but the real utility of such a macro would depend (1) on how many times you might need to run it and (2) if the macro could check the shortened worksheet names for duplicates.
In situations where you cannot rename worksheets for some reason (perhaps some sort of corporate rules run amok), remember that you can right-click on the left/right arrows to the left of the first worksheet tab. This displays the Activate dialog box, which shows you all the worksheet tab names and allows you to select which one you want to display.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13938) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Excel allows you to protect your worksheets easily, and that includes if you need to protect only a single worksheet out ...
Discover MoreIf you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. ...
Discover MoreSorting worksheet tabs can be done by using a macro. This tip provides a macro that accomplishes this task, but it also ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-09-28 04:43:27
Hans Hallebeek
Maybe a little cumbersome; but depending how you set it up you could do it either with macros (VBA) or with lookup table in a (let’s call it admin) sheet
Using the formula that placed all sheet names in for example each A1 cell of each sheet, this list with eg a 5-6 letter code which is the actual sheet name and next to the lookup column you place and additional description for that sheet:
Column A Column B
JC_001. Joint Committee etc .
A data validation and hyperlink to select the worksheet
Hope my explanation makes sense
2024-09-27 04:20:50
Alex Blakenburg
@J. Woolley ðŸ‘
2024-09-26 14:52:54
J. Woolley
@Alex Blakenburg
Thank you for describing the View > Navigation pane. It also lists hidden sheets, which are not included in the Activate dialog mentioned in the Tip. Right-click a sheet to Rename, Delete, Hide, or Show (unhide) it. When you pick a sheet, its contiguous used range(s) and any named range(s) can be selected (see Figure 1 below) . You can also move the pane around the screen and resize it (see Figure 2 below) . This was all new to me.
For related discussion, see: https://excelribbon.tips.net/T007094
Figure 1.
Figure 2.
2024-09-25 09:23:07
Alex Blakenburg
@Don - appreciate the feedback ðŸ™
2024-09-23 10:51:08
Don
Alex Blakenburg - thank you for sharing your tip on the Navigation pane. I learned something today!
2024-09-21 09:45:37
Don Small
For large workbooks, I usually create a "Home" worksheet containing the name and a link to each worksheet in the workbook. This allows me to use up to the maximum length of tab entries (31 characters). Next, I add a link to each worksheet at top left to taking me back to "Home" where I can easily navigate to another worksheet. If you already have numerous worksheets, you can automate the creation of this index. Do a search on something like "Automatically Create Index in Excel" for resources.
2024-09-21 07:54:30
Alex Blakenburg
The right click option to view the worksheets dialogue box mentioned above will show the most sheets in a single view but in MS365 a close 2nd is to open the Navigation pane (View > Navigation or Alt+W+K). To maximise the size of this pane you could make sure the formula bar is in its default compact size and collapse the ribbon (Ctrl+F1 or the Double click on one of the ribbon tabs)
2024-09-21 07:36:46
jamies
Partial solution approach -
have a sheet with the 1 row per sheet - use (Name) set when the sheet was created
descriptive names of the worksheets -
actual sheet tab id - User set Name
link to the sheet
any relevant usage notes, or a link to a descriptive notes sheet.
Setup a simple script to update that :
add rows for any sheets not already in the list - actual sheet name, and link
and a colour fill warning for any sheets that are no longer present !
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments