Categories
Device

How to show and hide the worksheet tabs in excel

How to delete hidden worksheets in Excel?

Perhaps you have a workbook contains many hidden worksheets which aren’t useful, and they take up so many bits, now you want to delete all of the trashy hidden worksheets. How could you quickly delete them?

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Delete hidden worksheets with VBA code

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

The following VBA code will delete all hidden worksheets within a workbook.

1. Open the workbook you want to delete the hidden worksheet.

2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, then copy and paste the following code into the Module:

Sub deletehidden()
Dim a As Integer
a = 1
While a <= worksheets="" count="" span="">
If Not Worksheets(a).Visible Then
Worksheets(a).Delete
Else
a = a + 1
End If
Wend
End Sub

3. Click button to execute the code, and a warning window will pop out for each hidden worksheet to make sure that you really want to delete it. See screenshot:

4. Then click Delete. And all of the hidden worksheets have been deleted.

Delete hidden worksheets with Inspect Document function

With the Inspect Document function of Excel, you can delete the hidden worksheets quickly and easily.

1. If you are using Excel 2010/2013, you can click File > Info > Check for Issues > Inspect Document, see screenshot:

If you have Excel 2007, you can click Office Button on the top left corner, and then click Prepare > Inspect Document. See screenshot:

2. In the Document Inspector dialog box, click Inspect button.

3. And the hidden worksheets will be inspected, then click Remove All button to delete the hidden worksheets of the active workbook.

Delete hidden worksheets with Kutools for Excel

If you want to delete the hidden worksheets quickly, you can use Kutools for Excel.

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

After installing Kutools for Excel, you can do as follows:

1. Go to the ribbon, and click Enterprise > Worksheet Tools > Delete All Hidden Sheets, see screenshot:

2. Then a prompt box pops out to remind you if you really want to delete all hidden worksheets. See screenshot:

3. Click Yes, and another prompt box will appear to remind you how many hidden worksheets have been deleted. See screenshot:

Microsoft Excel files are workbooks that can contain individual worksheets within them. You can learn more about the difference between workbooks and worksheets here. You can navigate between individual worksheets in your workbook by clicking the tabs at the bottom of the window. But it is possible for these tabs to be hidden by adjusting a setting in Excel Options.

Our guide below will show you what you need to do when you can’t see your tabs in Excel 2013, but need to switch to another worksheet.

Where are my Worksheet Tabs in Excel 2013?

The steps in this article will assume that you have a workbook open in Microsoft Excel 2013 that contains multiple worksheets, but that you do not see the tabs at the bottom of the window.

    Open Microsoft Excel 2013.

How to show and hide the worksheet tabs in excel

How to show and hide the worksheet tabs in excel

How to show and hide the worksheet tabs in excel

How to show and hide the worksheet tabs in excel

If you followed the steps above, only to find that Excel 2013 was already configured to show sheet tabs, then it is possible that your Excel window is minimized or has been manually sized. You can double-click the workbook name at the top of the window to maximize the window, which should bring your worksheet tabs into view.

How to show and hide the worksheet tabs in excel

If only some of your worksheet tabs are visible in your Excel 2013 workbook, then it is possible that individual sheets have been hidden. Learn how to unhide a worksheet in Excel.

Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.

After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.

His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.

Lori Kaufman
How to show and hide the worksheet tabs in excelLori Kaufman
Writer

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

How to show and hide the worksheet tabs in excel

If you’re short on screen space, you may want to hide parts of the Excel window, such as the ribbon and the worksheet tabs. We’ve already shown you how to hide the ribbon, so here we’ll show you how to hide the tabs.

To begin, click the “File” tab.

How to show and hide the worksheet tabs in excel

On the backstage screen, click “Options” in list on the left.

How to show and hide the worksheet tabs in excel

On the “Excel Options” dialog box, click “Advanced” in the list of items on the left.

How to show and hide the worksheet tabs in excel

Scroll down to the “Display options for this workbook” section (not the “Display” section) and select the “Show sheet tabs” check box so there is NO check mark in the box.

How to show and hide the worksheet tabs in excel

Click “OK” to accept the change and close the “Excel Options” dialog box.

How to show and hide the worksheet tabs in excel

You can also hide other items in Excel such as cells, rows, and columns and comments, formulas, and gridlines.

  • › How to Add Images to Questions in Google Forms
  • › How to Delete Your Photos on Facebook
  • › Razer Thinks You Want a PC Gaming Headset That Vibrates
  • › How to Get a Link to a Photo or Video on Instagram
  • › The Fix for Slow AMD Processors on Windows 11 Is Here

How to show and hide the worksheet tabs in excel Lori Kaufman
Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business.
Read Full Bio »

This article provides several methods to unhide all hidden sheets & worksheets in Excel step by step.

  1. Unhide all hidden worksheets one by one in Excel
  2. Unhide all hidden worksheets by VBA code
  3. Unhide all hidden worksheets by Kutools for Excel with one click
  4. Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature
  5. Unhide all very hidden worksheets by Kutools for Excel

Unhide all hidden worksheets one by one in Excel

We can apply the Unhide Sheet feature to unhide a hidden worksheet at a time in Excel. Please do as follows:

1. Click Home > Format > Hide & Unhide > Unhide Sheet. See screenshot:
Note: You can also right click any one of sheet names in the Sheet tab bar, and select Unhide from the context menu.

How to show and hide the worksheet tabs in excel How to show and hide the worksheet tabs in excel

2. Now all hidden worksheets are listed in the Unhide dialog box. Please select the worksheet you want to show, and then click the OK button.
How to show and hide the worksheet tabs in excel

3. Then the selected hidden sheet is displayed. Repeat above Step 2 to show all unhide worksheets one by one.

Note: Unhide all hidden worksheets is not as simple as it can be while there are hundreds of hidden sheets need to be unhide. You may have to click the mouse for hundreds of times for repeating the operations. If you would like to get an easier way, please make it according to the procedures that the following methods provides.

Toggle Hidden Worksheet Visibility – One click to unhide all hidden sheets, and one click to restore hidden

With the handy toggle button of Worksheets provided by Kutools for Excel, you can one click to show all hidden worksheets in the active workbook, and click it again to toggle all hidden worksheets to be invisible. Full Feature Free Trial 30-day!

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Unhide all hidden worksheets by VBA code

The following short VBA code also can help you display all of the hidden sheets at the same time.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

3. Press the F5 key to run this macro. And the hidden sheets will be displayed at once.

Unhide all hidden worksheets by Kutools for Excel with one click

Kutools for Excel’s Unhide All Hidden Sheets feature could help you display all of the hidden sheets easily with only one click.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Click Kutools > Show / Hide > Unhide All Hidden Sheets, see screenshot:
How to show and hide the worksheet tabs in excel

Then all hidden sheets are shown at once. See screenshot:

Note: The number displaying after the function indicates the total number of hidden worksheets in your workbook. If there is no hidden worksheet, the function will turn gray and cannot be clicked.

With this handy Unhide All Hidden Sheets feature, you can display all hidden worksheets with only one click in Excel. Have a Free Trial!

Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature

Kutools for Excel also provides a handy Worksheets (Toggle Hidden Worksheets Visibility) feature to show or hide all hidden worksheets with only one click in the active workbook.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Under Kutools tab, please click the Worksheet button to display all hidden worksheets, and click the Worksheets button again to restore hidden status.

Worksheets toggles worksheets visibility. One click to display all hidden sheets, and another click to hide them again. Have a Free Trial!

Unhide all very hidden worksheets by Kutools for Excel

Sometimes above methods cannot unhide the hidden worksheets. That’s because these worksheets are very hidden. In this situation, you can apply the Hide/Unhide Workbooks and Sheets feature of Kutools for Excel to quickly unhide them.

Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Click Kutools > Show/Hide > Hide/Unhide Workbooks and Sheets.
How to show and hide the worksheet tabs in excel

2. In the Hide/Unhide Workbooks and Sheets dialog, please click to select the active workbook, and click the Unhide all button in the Sheets section. See screenshot.
How to show and hide the worksheet tabs in excel

Now all hidden worksheets including the very hidden ones are displaying in bulk. Please close the dialog as you need.

Hide/Unhide Workbooks and Sheets hide or unhide multiple opening workbooks and their worksheets in bulk, including the very hidden ones. Have a Free Trial!

You can hide any worksheet to remove it from view. The data in hidden worksheets is not visible, but it can still be referenced from other worksheets and workbooks, and you can easily unhide hidden worksheets as needed.

Right-click the sheet tab you want to hide, or any visible sheet if you want to unhide sheets.

On the menu that appears, do one of the following:

To hide the sheet, select Hide.

To unhide hidden sheets, select them in the Unhide dialog that appears, and then select OK.

How to show and hide the worksheet tabs in excel

Notes: To select multiple sheets do either of these:

Press and hold CTRL, then click the items to select them.

Press and hold SHIFT, then use the up and down arrow keys to adjust your selection.

These steps work for Microsoft 365 subscribers, as do the steps on the tab below that matches your operating system. For perpetual Office versions, see the tabs below for steps supported by your specific version of Excel.

Hide or unhide a worksheet

Note: The screen shots in this article were taken in Excel 2016. If you have a different version your view might be slightly different, but unless otherwise noted, the functionality is the same.

Select the worksheets that you want to hide.

How to select worksheets

Click the sheet tab.

If you don’t see the tab that you want, click the scrolling buttons to the left of the sheet tabs to display the tab, and then click the tab.

Two or more adjacent sheets

Click the tab for the first sheet. Then hold down Shift while you click the tab for the last sheet that you want to select.

Two or more nonadjacent sheets

Click the tab for the first sheet. Then hold down Ctrl while you click the tabs of the other sheets that you want to select.

All sheets in a workbook

Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

Tip: When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.

On the Home tab, in the Cells group, click Format > Visibility > Hide & Unhide > Hide Sheet.

How to show and hide the worksheet tabs in excel

To unhide worksheets, follow the same steps, but select Unhide. You’ll be presented with a dialog box listing which sheets are hidden, so select the ones you want to unhide.

How to show and hide the worksheet tabs in excel

Note: Worksheets hidden by VBA code have the property xlSheetVeryHidden; the Unhide command will not display those hidden sheets. If you are using a workbook that contains VBA code and you encounter problems with hidden worksheets, contact the workbook owner for more information.

Hide or unhide a workbook window

On the View tab, in the Window group, click Hide or Unhide.

On a Mac, this is under the Window menu in the file menu above the ribbon.

How to show and hide the worksheet tabs in excel

When you Unhide a workbook, select from the list in the Unhide dialog box.

How to show and hide the worksheet tabs in excel

If Unhide is unavailable, the workbook does not contain hidden workbook windows.

When you exit Excel, you will be asked if you want to save changes to the hidden workbook window. Click Yes if you want the workbook window to be the same as you left it (hidden or unhidden), the next time that you open the workbook.

Hide or display workbook windows on the Windows taskbar

Excel 2013 introduced the Single Document Interface, where each workbook opens in its own window.

Click File > Options.

For Excel 2007, click the Microsoft Office Button , then Excel Options.

Then click Advanced > Display > clear or select the Show all windows in the Taskbar check box.

Hide or unhide a worksheet

Select the worksheets that you want to hide.

How to select worksheets

Click the sheet tab.

If you don’t see the tab that you want, click the scrolling buttons to the left of the sheet tabs to display the tab, and then click the tab.

Two or more adjacent sheets

Click the tab for the first sheet. Then hold down Shift while you click the tab for the last sheet that you want to select.

Two or more nonadjacent sheets

Click the tab for the first sheet. Then hold down Command while you click the tabs of the other sheets that you want to select.

All sheets in a workbook

Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

On the Home tab, click Format > under Visibility > Hide & Unhide > Hide Sheet.

To unhide worksheets, follow the same steps, but select Unhide. The Unhide dialog box displays a list of hidden sheets, so select the ones you want to unhide and then select OK.

How to show and hide the worksheet tabs in excel

Hide or unhide a workbook window

Click the Window menu, click Hide or Unhide.

When you Unhide a workbook, select from the list of hidden workbooks in the Unhide dialog box.

How to show and hide the worksheet tabs in excel

If Unhide is unavailable, the workbook does not contain hidden workbook windows.

When you exit Excel, you will be asked if you want to save changes to the hidden workbook window. Click Yes if you want the workbook window to be the same as you left it (hidden or unhidden) the next time that you open the workbook.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Lori Kaufman
How to show and hide the worksheet tabs in excelLori Kaufman
Writer

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

How to show and hide the worksheet tabs in excel

If you use a worksheet in your Excel workbook to hold values or formulas referenced on other worksheets, you may not want that worksheet visible on the tab bar at the bottom of the Excel window. The good news is you can easily hide worksheets in Excel.

You can even hide an entire workbook, although we’re not sure why you would want to do that. However, we’ll show you how to hide worksheets and workbooks and show them again.

Hide Worksheets/Tabs

To hide a worksheet (the tabs at the bottom of the Excel window), right-click on the tab you want to hide and select “Hide” from the popup menu. The tab is removed, but not permanently deleted.

How to show and hide the worksheet tabs in excel

You cannot hide all the worksheets in a workbook. If you try to hide the last visible worksheet, you’ll see the following warning.

How to show and hide the worksheet tabs in excel

To unhide a worksheet, right-click on any tab still showing and select “Unhide” from the popup menu.

How to show and hide the worksheet tabs in excel

On the “Unhide” dialog box that displays, select the worksheet you want to unhide and click “OK”.

NOTE: You can only unhide one worksheet at a time.

How to show and hide the worksheet tabs in excel

If you’ve hidden the worksheet tabs to make more room to view your worksheet data, you can unhide hidden worksheets using a different method. Make sure the “Home” tab on the ribbon is active. In the “Cells” group, click “Format”. Select “Hide & Unhide” under “Visibility”, and select “Unhide Sheet” from the submenu.

How to show and hide the worksheet tabs in excel

The “Unhide” dialog box displays. Select the worksheet you want to unhide and click “OK”, as described above.

Hide an Entire Workbook

To hide an entire workbook, click the “View” tab.

How to show and hide the worksheet tabs in excel

In the “Window” section of the “View” tab, click “Hide”.

How to show and hide the worksheet tabs in excel

The Excel window stays open but the grid area becomes blank. To unhide the the workbook, click “Unhide” in the “Window” section of the “View” tab.

How to show and hide the worksheet tabs in excel

The “Unhide” dialog box displays listing the filenames for the currently open workbooks. If you have hidden other entire workbooks that are not current open, they are not listed in this dialog.

When you hide an entire workbook and save the file, the next time you open the workbook, it’s still hidden. It will remain hidden until you unhide it.

How to show and hide the worksheet tabs in excel

When you hide an entire workbook and save the file, the next time you open the workbook, it’s still hidden. It will remain hidden until you unhide it.

NOTE: Any worksheets/tabs that were hidden when you hid the entire workbook are still hidden when you unhide the workbook.

  • › The Best Gaming Keyboards of 2021: Be on Top of Your Game
  • › Why Sublime Text Is Great For Writers, Not Just Programmers
  • › What Is a ULED TV, and How Is It Different?
  • › Why Professionals Will Actually Want a 2021 MacBook Pro
  • › How to Add Images to Questions in Google Forms

How to show and hide the worksheet tabs in excel Lori Kaufman
Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business.
Read Full Bio »

If you can’t see the worksheet tabs at the bottom of your Excel workbook, browse the table below to find the potential cause and solution.

Note: The image in this article are from Excel 2016. Your view might be slightly different if you have a different version, but the functionality is the same (unless otherwise noted).

The window sizing is keeping the tabs hidden.

If you restore multiple windows in Excel, ensure that the windows are not overlapping. Perhaps the top of an Excel window is covering the worksheet tabs of another window.

The status bar has been moved all the way up to the Formula Bar.

Tabs can also disappear if your computer screen resolution is higher than that of the person who last saved the workbook.

Try maximizing the window to reveal the tabs. Simply double-click the window title bar.

If you still don’t see the tabs, click View > Arrange All > Tiled > OK.

The Show sheet tabs setting is turned off.

First ensure that the Show sheet tabs is enabled. To do this,

For all other Excel versions, click File > Options > Advanced—in under Display options for this workbook—and then ensure that there is a check in the Show sheet tabs box.

The horizontal scroll bar obscures the tabs.

Hover the mouse pointer at the edge of the scrollbar until you see the double-headed arrow (see the figure). Click-and-drag the arrow to the right, until you see the complete tab name and any other tabs.

The worksheet itself is hidden.

To unhide a worksheet, right-click on any visible tab and then click Unhide. In the Unhide dialog box, click the sheet you want to unhide and then click OK.

How to show and hide the worksheet tabs in excel

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Is there a way to prevent users from unhiding “hidden sheets” in an excel file – without using VBA?

or to put it in other words, can the “Format/Sheet/Unhide” be disabled for specific worksheets?

Here is a non-VBA way to do this. I am not sure if this is optimum, but it seems to produce results without much effort. And it doesn’t use VBA, just the VBA Editor.

Step 1: Right click on the tab you want to hide and select view code option

Step 2: In the properties window for that sheet, set “visibility” as 2 – xlSheetVeryHidden

How to show and hide the worksheet tabs in excel

Step 3: Now right click on the sheet name in project explorer area and select VBA Project properties

How to show and hide the worksheet tabs in excel

Step 4: Go to “Protection” tab and check “Lock” project

Step 5: and set password for protection, click ok

Step 6: when someone tries to open the VBA Code for that sheet to make the worksheet tab unhidden (visible), Excel prompts for a password

This trick is very handy when you are sharing workbooks with others and afraid that they may ruin the calculations or data.

At the bottom of your worksheets are a series of tabs. If you don’t use them or rename them, then they probably have names like Sheet1, Sheet2, Sheet3. By navigating between these tabs you can have multiple spreadsheets in the same file.

But if you never use those tabs, or if you are accidentally clicking on them, then you might want to hide them. Our guide below will show you how to change a setting in Excel so that the worksheet tabs at the bottom of your workbook are always hidden.

How to Hide Worksheet Tabs in Excel

The steps in this article were performed in the Excel for Office 365 version of the program, but will work in earlier versions of Excel as well. Note that this only changes the setting for the file that is currently open. It won’t affect any other workbooks that you open in Excel.

Step 1: Open a file in Excel.

Step 2: Select the File tab at the top-left of the window.

How to show and hide the worksheet tabs in excel

Step 3: Choose the Options button at the bottom-left corner of the window.

How to show and hide the worksheet tabs in excel

Step 4: Click the Advanced tab at the left side of the Excel Options window.

How to show and hide the worksheet tabs in excel

Step 5: Scroll down to the Display options for this workbook section, click the box to the left of Show sheet tabs to clear the check mark, then click the OK button at the bottom of the window.

How to show and hide the worksheet tabs in excel

Tired of Excel automatically turning Web page addresses into clickable links? Learn how to disable automatic hyperlinking in Excel so that this stops happening for other files that you open in Excel in the future.