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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Want to make fast work of moving from one worksheet to another? Here's how to do the task when you have a lot of ...
Discover MoreSorting worksheet tabs can be done by using a macro. This tip provides a macro that accomplishes this task, but it also ...
Discover MoreExcel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll ...
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