Worksheet Tabs are missing/hidden in MS Excel! How to Fix?

Worksheet Tabs are missing/hidden in MS Excel! How to Fix?

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 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 3: Excel windows sometimes get arranged such that workbook tabs aren't visible.

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-knownxlSheetVeryHidden 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