Sometimes you have to deal with an Excel file that has a lot of hidden sheets, and Excel only lets you unhide them one by one, so it can be quite annoying. Here’s an easy trick that lets you forget that you ever had this problem. It requires VBA, but it’s very simple, and you can save it as a personal macro that works for every Excel file you open on the same computer, so you never have to do it again. I’ll explain every step below.

First, make sure you have the Developer tab in the ribbon. If you don’t have it, right-click on any of the other tabs, choose Customize Ribbon and click the Developer check-box:

Choose the Developer tab and click on Record Macro. In the dialog box that appears, give the macro a name, for example “UnhideAllTabs” (remember: no spaces in macro names!), choose “personal Macro Workbook” in the next field, and write a short description in the last field (optional). You also have the option to assign a shortcut to it, but if you do that, make sure that it doesn’t conflict with any other shortcuts that you might use. Click OK.

Click on the Stop symbol, and then on Visual Basic (or Alt+F11) to open the VBA (macro) editor:

Now you should be able to find this empty macro in your personal folder:

All you have to do is to paste in one short line of code:

For Each ws In Sheets: ws.Visible = True: Next

Save the file as either .xlsx (macro-enabled workbook) or .xlsb (binary workbook). Now the macro is stored, and it will be available for all Excel workbooks you open on the same computer.

The final touch is to make it accessible with a click on the Quick Access Toolbar. Click on the little arrow and choose More Commands:

Choose Macros in the field to the left and double-click on the UnhideAllTabs macro. If you want a special symbol for it, you can click on the Modify button and choose a symbol.

Done! From now on, this button will unhide all worksheets in any new or old workbook you open.

If you want to hide multiple sheets again, all you have to do is to press Ctrl and click on all the tabs of the sheets you want to hide, then right-click and choose Hide. Or you can click on the first tab, press Shift, and click on the last tab, and all the tabs in between will be selected.

More easy tricks in Excel:

• How to create a Refresh All button in Excel
• How and Why you should use a Logarithmic Scale in an Excel Diagram
• How to get Week Numbers right in Excel using ISOWEEKNUM
• How to Join Text from Several Cells in Excel using TEXTJOIN

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish