What To Do When Excel Worksheet Tabs Go Missing

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.

 

You may like these other stories...

More and more businesses are adopting the cloud in order to take advantage of benefits such as greater efficiency, increased productivity and lower costs. Companies in general are flocking to the cloud for email hosting,...
A great way to increase business for your accounting firm is to remain at the front of your clients' minds all throughout the year. One of the best ways to do that is through an email newsletter. Perhaps you send out a...
Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...

Already a member? log in here.

Upcoming CPE Webinars

Oct 9In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards.
Oct 15This webinar presents the requirements of AU-C 600, Audits of Group Financial Statements (Including the Work of Component Auditors).
Oct 21Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience’s communication style.
Oct 23Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.