Save content
Have you found this content useful? Use the button above to save it to your profile.
excel-tips-search-dhr
mihailomilovanovic

Excel tip: How to find missing worksheet tabs

by
18th Oct 2013
Save content
Have you found this content useful? Use the button above to save it to your profile.

AccountingWEB.com’s David H Ringstrom offers some detection tips to help you track down worksheets that appear to have gone astray.

Have you ever opened an Excel workbook that you know has several worksheets, only to see a single sheet, with no visible tabs for its companions.

This frustrating situation can come about due to a basic Excel setting that can have the effect of concealing your worksheets in plain sight.

The techniques here will help you restore the missing worksheet tabs, and may also make it easier to navigate around multi-sheet workbooks.

Typically, within Excel workbook displays worksheet tabs along the bottom frame of the open Window, as illustrated below.

dhr-missing

This Show Sheet Tabs setting is reached via different routes, depending on which version you use:

  • Excel 2010/2013: Choose File, Options, and then enable the Show Sheet Tabs setting in the Display Options section of the Advanced options as s.
  • 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. 

dhr-missing-excel-tips

In Excel 2010 and earlier versions, it’s quite easy to arrange a spreadsheet window so that the worksheet tabs don’t appear on the screen, even if the Show Sheet Tabs option is enabled.

If you encounter the situation as illustrated below, you can double-click on the workbook's name to maximise the window and restore your workbook tabs.

If you can’t find the worksheet tabs in Excel 2013 double-click on the words "Microsoft Excel" at the top of the window to maximize Excel's application window.

dhr-missing-excel-tips

Whether you can see the actual tabs, you can also navigate between worksheets using the Ctrl-Page UP and Ctrl-Down keyboard shortcuts. Page Up will jump you to the next sheet on the left, while Down will move you to the right. 

If the Show Sheet Tabs option is enabled, you can also right-click on the navigation arrows in the lower left-hand corner of the screen to navigate between sheets, as shown below. This command calls up a menu that lets you choose any sheet in the workbook.

In Excel 2013 this Activate menu displays all worksheets in a single dialogue box and lets you 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.

dhr-missing-excel-tips

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.

"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By mghilardi
06th Dec 2013 21:40

Good job. Easy to follow.
 

Good job. Easy to follow.

 

Thanks (0)