What To Do When Worksheet Tabs Go Missing

Columnist
Share this content
85

 

By David Ringstrom, CPA
 
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:
  • Excel 2010/2013: As shown in Figure 2, choose File, Options, and then enable the Show Sheet Tabs setting in the Display Options section of the Advanced options.
  • Excel 2007: Click the Office button, choose Excel Options, and then then enable the Show Sheet Tabs setting in the Display Options section of the Advanced options.
  • Excel 2003 and earlier: Choose Tools, Options, Display, and then Show Sheet Tabs.
  • Excel 2011 for Mac: Choose Excel, Preferences, View, and then Show Sheet Tabs. 
Figure 1: You may encounter workbooks where the worksheet tabs aren't visible.
 
Figure 2: The Show Sheet Tabs setting determines whether or not worksheet tabs are visible.
 
Another scenario you may encounter is shown in Figure 3. In Excel 2010 and earlier, it's relatively easy to inadvertently arrange a spreadsheet window so that the worksheet tabs aren't present on the screen, even if the Show Sheet Tabs option is enabled. If this occurs, as shown in the figure, you can double-click on the workbook's name to maximize the window and restore your workbook tabs. In Excel 2013 if you can't see the worksheet tabs, simply double-click on the words "Microsoft Excel" at the top of the window to maximize Excel's application window.
 
Figure 3: Excel windows sometimes get arranged such that workbook tabs aren't visible.
 
Regardless of whether you can see the actual tabs, you can use a keyboard shortcut to navigate between worksheets within a workbook. To do so, press Ctrl-Page Up to activate the adjacent worksheet to the left or press Ctrl-Page Down to activate the next worksheet to the right.
 
Assuming that the Show Sheet Tabs option is enabled, another way to navigate within a workbook is to right-click on the navigation arrows in the lower left-hand corner of the screen, as shown in Figure 4. This displays a hidden menu from which you can select any visible worksheet in the workbook. Excel 2013 offers some helpful improvements to this Activate menu, as all worksheets are displayed in a single dialog box, and you can select a worksheet by typing the first letter of a sheet name. In Excel 2010 and earlier, the Activate menu initially displays up to 16 worksheets, requiring you to choose More Sheets to display an expanded list. Further, in Excel 2010 and earlier you must choose a desired sheet name with your mouse, as the menu cannot be accessed by way of keystrokes as is possible in Excel 2013.
 
Figure 4: Right-click on the navigation arrows in any version of Excel to display a list of worksheets.
 
In other cases, your worksheet tabs may be present, but a worksheet still appears to be missing. If so, the first place to check is to see if the worksheet has been hidden. In Excel 2007 and later, you can right-click on any worksheet tab and choose Unhide. If the Unhide command is disabled, there most likely aren't any hidden worksheets in the workbook, but there's a way you can determine this categorically.
 
You can also access the Unhide Sheet command through Excel's main menu:
  • Excel 2007 and later: On the Home tab, choose Format, Hide and Unhide, and then Unhide Sheet.
  • Excel 2003 and earlier: Choose Format, Sheet, and then Unhide.
  • Excel 2011 for Mac: From the main menu, choose Format, Sheet, and then Unhide. The Format command on the Home tab of the ribbon doesn't allow you to unhide worksheets.
If the Unhide sheet is disabled, you can't necessarily assume that there are no hidden worksheets within a workbook. Savvy users rely on a little-known xlSheetVeryHidden setting to hide worksheets in plain sight.
 
Read more articles by David Ringstrom. 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

Replies

Please login or register to join the discussion.

hi i lost home, insert, formulas and all tabs....how could i get back?

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