19

Some days I'm working with extremely large Excel files.  They have too many sheets that can't be shown in one sheet tab. (see image)

too many sheets in an Excel file

So, I'm wondering whether or not we can show Excel sheet on more than 1 row. Many tries with Google don't help much.


Many tries but I still can't make Excel show the sheet bar the way I want (≥ 2 rows), so I'm working with some VBA Scripts kopischke suggested.


FYI, right click on |< << >> >| button on the bottom-right corner show you a list of up to 15 sheets.  For more, click on View more in the list appeared.

  • 1
    Another option is to make a list of the sheets, eg in the first sheet, with hyperlinks to the actual sheets... so you create your own list. :) HTH – user452833 May 28 '15 at 16:04
  • Can you hide a sheet but still get to it using a hyperlink? – posfan12 Aug 13 '16 at 4:39
  • I just tried and was unable to get it to work. – posfan12 Aug 13 '16 at 4:46
  • Is there an up to date answer to this in 2020? – user56834 May 17 at 5:10
  • @user56834 : 2020-05-19 now and I'm still using Office 2016, no update :) my Friend with Office_365 still must view excel sheet list on ONLY 1 row too :) – Luke May 19 at 9:05
17

Another work around that might not be immediately obvious is that you can right click on the little arrows in the bottom left corner of the window that you use to scroll left and right on the sheets. Doing so opens a vertical list sheets with the option to display more.

Microsoft Excel 2010:

enter image description here

Microsoft Excel 2013:

enter image description here

| improve this answer | |
  • 3
    Good one – never noticed that (as you said: not exactly obvious). – kopischke Oct 28 '11 at 10:45
  • I knew about it, but it just displays 15 sheets in that list. and to kopischke : view more take we to a scroll list of all sheet. – Luke Oct 28 '11 at 12:30
11

Excel will only display one row of sheet tabs, I’m afraid. If space runs scarce, you have the following options to display more:

  • resize the tab area (by dragging the handle separating it from the horizontal scrollbar), and / or
  • rename your sheets to have a shorter name, so that more sheets show (by removing the “Sheet” part, say, making the tab names “1”, “2” etc.), and / or
  • hide sheets (right click on their tab to get the option; right clicking on a visible tab will get you an option to see all hidden sheets and show them again) if there are some you do not need.

Finally, there are VBA scripts out there claiming to build a menu of all sheets in the workbook, which might solve your problem (untested).

| improve this answer | |
  • It was made for Excel 2002 and 2003. What are the chances of it working today? – posfan12 Aug 4 '16 at 23:14
  • Just tested the script in Excel 2016. It works. I just would rather have a dockable window pane than a drop-down menu. – posfan12 Aug 13 '16 at 4:26
2

Another workaround: use Kutools for Excel (free to try with no limitation in 30 days).

  1. Click Kutools > Navigation. See screenshot:

enter image description here

  1. Then you can see a Navigation Pane shown at the left of the sheet, click Sheets tab, and in the Workbooks list select the workbook whose sheet tabs you want to view, then in the Worksheets list, you can view all the sheet tabs.

enter image description here

If the sheet tabs are too many to display all in the Worksheets list, you can shorten the workbooks list with moving cursor over the bottom border of Workbooks list, and then drag it up when the cursor becomes.

enter image description here

The Vertical scrollbar will come out automatically when the sheet tabs can’t be shown fully.

Click here for more details on Navigation Pane.

| improve this answer | |
  • I like this option the best. Unfortunately, if you share your worksheet, the person you share it with does not benefit from having it too. Unlike a VBA script. Also, I wonder if when you purchase a license, it is limited to particular versions of Excel. – posfan12 Aug 4 '16 at 23:10
1

Excel doesn't natively support multi-row sheet tabs. There are other ways to view more sheets, as outlined in other answers here, but no way to view multiple rows of sheet tabs.

However, there is a commercial Excel add-in called FormulaDesk Navigator that can display multiple rows of sheet tabs as well as vertical sheet tabs and other features:

Multi-row sheet tabs

Vertical sheet tabs

Disclosure: This is my product. I don't wish to self-promote, but it's the only way to accomplish what the question asks.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.