What To Do When Worksheet Tabs Go Missing

84

It can be disconcerting when you open an Excel workbook that has several worksheets, but you only see single worksheet. If this happens, your "missing" worksheets may be hiding in plain sight due to a simple Excel setting. In addition to restoring vanished worksheet tabs, I'll also describe a couple of techniques for navigating workbooks easier, as well as other ways to find hidden worksheets.

Typically, within an Excel workbook you'll see worksheet tabs along the bottom of the screen, but it's also possible to hide the worksheet tabs, as shown in Figure 1. To manage this setting:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

Two different circumstances can cause this. One is clicking Full Screen on the View tab, but pressing the Escape key should restore the menu. The second is an Excel macro you ran (perhaps unknowlingly) has hidden the menu interface known as the Ribbon. To restore it, open Excel, and then press Alt-F11 to launch the Visual Basic Editor. Within the VBE choose View, and then Immediate Window. Copy and paste this line of code into the Immediate window and then press Enter:

Application.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",true)"

Make sure to copy and paste the text exactly as shown above. Let me know how it goes.

You're a saint! You just saved me from pulling all of my hair out! Not sure what happened or how it happened in the first place but I'm just glad to have my dear ol' tabs back. Thank you, David!

One of Excel's many nuances slipped by you. I'm glad you found my article helpful!

Hi,
I have lost several worksheets, they were all labelled individually. My main concern is that I can still see the first tab, but cannot find the others despite your steps. Any thoughts?

In Excel press Alt-F11 to launch the Visual Basic Editor. On the View menu choose Project Explorer, which will provide a listing of your open workbooks along the left-hand side of the screen. If necessary double-click on the workbook in question to expand its objects, and if necessary double-click on Excel objects. This will show you all worksheets in the workbook. If you see worksheets listed that you can't view in the Excel interface, choose View, and Properties window. Click on a hidden sheet and change its Visible property to xlSheetVisible. If you do not see the sheets in question in the Project Explorer then the sheets have been deleted and are not recoverable at this point unless you have a back-up copy of the workbook.

Hi David,

Thanks for your help, but they are gone forever :(

I'm sorry to hear that, but at least now you know unequivocally. I cringe at the the number of spreadsheets I've lost or wrecked over the years. The only saving grace for me is reconstruction goes much faster then the initial build-out. Good luck recovering from your situation.

Thanks!

You're welcome! I am happy I could help!

I just got a new PC with very high resolution. I've figured out how to set it so that the ribbons and cells are look fine, but the tabs are still very very small. How can I set their font size?

You can't control this from within Excel, instead you have to change a setting in Windows to change the size of your scroll bars. This page explains how: http://www.asap-utilities.com/...

Thanks, that did it ... I really appreciate you taking the time to research and respond, even when not an Office issue.

Happy to help. It's one of those rare bits of trivia that I do get asked infrequently, so it's helpful to keep the answer top of mind. I never like being stumped with regard to anything Excel related. :-)

OMG - you saved my day. THANKS A LOT. :)

I'm so glad to hear that. Thank you!

Hi! I'm using excel 2007 and cannot view my other tabs, despite these steps. I can see the other tabs when I type alt+f11, but when I click on the others, I am brought to the original page. Any suggestions?

Try the steps in this article to make sure that they haven't been set to xlSheetVeryHidden:

http://www.accountingweb.com/t...

Also, on the Review tab, make sure that the workbook isn't protected. If Protect Workbook has an orange background then the structure of the workbook is locked and would prevent you from unhiding hidden worksheets.

how this sheet fit to window
i tried bt it didnt wrk

A user has clicked on the Review tab, chosen Protect Workbook, and selected the Window option. You'll have to choose Unprotect Workbook on the Review tab and provide the password if prompted.

THANK YOU - Peace

I'm happy that you're happy. Thank you for the feedback!

I need help to fix my Bi-weekly employees schedule. When I print it out the Column with the Staff members is missing in the second week. How can I fix this?
Thank you!

Choose Page Break Preview on the View menu in Excel 2007 and later. I suspect that your Page Setup settings are including one or more columns on the left-hand side of the worksheet, but your print range is excluding the column in question. In Page Break Preview you can drag the edges of your print range to include the missing area.

Thanks a million for these tips!

I appreciate your feedback!

Or View-> Arrange all -> Tiled -> [OK]

Thanks for the great addition to my article! It's easy to get tunnel vision in Excel and overlook alternatives to given situations.

HI,

I am not able to see hide/unhide properties on my XL for a sheet. I am trying to hide a sheet from vba but it is erroring out. I checked the issue from UI, but I dont see hide,unhide,tabcolor all such properties of excel sheet are diabled..please help

on a sheet in xl, right click and then looking at properties I see only Protect Sheet' and 'view code' as active. Rest everything is disabled

The structure of the workbooks is protected. In Excel 2007 and later click Protect Workbook on the Review tab. You may be prompted to provide a password. In Excel 2003 and earlier choose Tools, Protection, Unprotect Workbook. As you noted, you cannot unhide sheets in a protected workbook.

Thanks for reply. I am using ecel 2013, and there is some problem suddenly xl sheets properties are going disabled. So sheet.visible=xlvisible/xlhidden is throwing error.

As I said above " In Excel 2007 and later click Protect Workbook on the Review tab. You may be prompted to provide a password.". Excel 2007 and later includes Excel 2013. You will not get past this error until you unprotect the workbook in the fashion I've described.

But How to overcome this problem with VBA Macros? I am trying to acheive this unhide/hide functionality through VB as per our business requirements. I have some 25 times this hide/unhide in my code which is working fine and I am able to switch in between sheets. But at certain lines of code..I can see this hide/unhide is erroring. So I just thought of checking the xl, and there I caught this disabling issue. Yes my XL is password protected.

Your VBA macro will have to unprotect the workbook before any sheet hiding/unhiding and then reprotect the workbook back again. Or you can protect the workbook in such a fashion that your macro can act on the workbook but users cannot. Spend a few minutes on Google. This is a common problem with widely documented solutions.

Pages