Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Workaround for Multiple Rows of Worksheet Tabs

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Workaround for Multiple Rows of Worksheet Tabs.

At the bottom of Excel's program window you can see tabs that represent the various worksheets in your workbook. (If you don't see the tabs, you need to choose Tools | Options | View tab and make sure the Sheet Tabs check box is selected.) You may wonder if there is a way to have multiple rows of the sheet tabs displayed, for those workbooks that have many worksheets in them.

Unfortunately, Excel provides no intrinsic way to change the number of rows of worksheet tabs displayed; it appears that the limit of a single row is hard-coded into the program. There are a couple of workarounds you can try, however.

  • Use shorter worksheet names. Shorter names allows more tabs to be automatically displayed horizontally.
  • Click the divider bar between the tab area and the horizontal scroll bar, dragging it to the right. The scroll bar gets narrower, and the space allotted to the tabs becomes wider.

If your tabs still won't all fit on the screen, right-click on the navigation controls just to the left of the tabs. You should see a list of sheet names you can select. If there are too many sheet names to display in that way (more than 15), select the More Sheets option, and Excel displays the Activate dialog box, from which you can select a sheet name.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3009) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Workaround for Multiple Rows of Worksheet Tabs.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Comments for this tip:

Dave    12 Mar 2015, 18:20
The Hyperlink idea is kind of cool, just a lot of work.

As the author notes, you can right click the left of the tabs and get a combo box with all of them showing. I'm on 2013 and don't see the 15 limit he references though - the list has a scroll bar to the right, but better than most things MS, the focus is correct and my mouse scroll wheel works instantly - so, right click move the wheel and I can see any and all sheets to select from - very fast - so much so that I'm no longer concerned about MS not giving us multiple rows for tabs (I agree it's lame though). If we want to speak lame, though not germane to this question, what about tabs for multiple work books - seriously as much Excel is a staple in business they don't anticipate that need and you have to buy a 3rd party app?
Ron Jeremias    30 Jan 2015, 08:59
I just switched to a newer version of Excel. I have an older workbook with a large number of spreadsheets that I named a, b, c, etc. and I was able to see all of my tabs. But in the newer version of Excel the tabs seem to have a minimum width of about 5 characters, even when the tab name has only one character. Thus, fewer tabs are visible. Is there a fix for this?
Gilbert Dupuis    25 Dec 2014, 19:54
Hi

I have over 30 tabs and I like good names for them. I solved my navigation problem by creating a new sheet (INDEX)in witch I have created hyperlinks to every sheet (Insert menu-Hyperlinks). These hyperlnks are very flexible in setting.

On every sheet, I froze the first column and copy (rows 1 to 200) an hyperlink to the INDEX sheet. So this column is always visible and with two clicks I could select the next sheet I want to go to.

And since the hyperlinks are in cells, I could arrange them, move them in whatever disposition suits me.

Snap shot available.

Good day
Christian    15 Aug 2014, 11:48
Pretty lame, Microsoft...pretty lame.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 4+5 (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.