This post is going to show all the ways you can show or hide a sheet tab in Google Sheets.

If you are a Google Sheets user, often or not, you will share them with your colleagues. On top of that, you wish to share the adequate templates only with them.

If there are too many sheet tabs, it becomes a job itself for the users to go through all of them and check which one is for their use.

Moreover, it becomes a headache for you as well, since you don’t want to share all the stuff with them but feel helpless because you don’t know the appropriate way of hiding the tabs from the shared spreadsheet.

Also, the third case can be one where you have too many sheets, and you want to copy the data from the sheet that is at the extreme right to the current sheet. If you rearrange the order of such sheets, it will take a lot of time.

For all these common problems, can there be a solution? Well, of course, there is one! 😎

You can hide the sheets to tackle such problems. Let me explain it a bit!

If you are hiding the other sheets from your example and only keeping the one that is of use to end users, they will be quick to do the task. It, on the other hand, makes you feel safe because now, you don’t have to worry about sensitive content leakage. Moreover, hiding a few sheets can bring the sheet from the extreme right, close to the sheet you want to copy the data.

You can now see how vital hiding and unhiding a sheet can be in Google Sheets. So, hold your seats tight because we will walk you through different methods using which you can hide and (or) unhide individual sheets.

Hide a Sheet by Right Click

Well, hiding tabs from your Google Sheets is not just about restricting specific users from accessing its content of it. You can lock the cells for such a purpose, which will be good enough.

However, in some fashion, hiding sheets fall under the ease of access or navigation. Imagine, fewer sheets mean more convenient users can navigate towards the data! 👍

The primary method to hide a sheet is using the right mouse click. A short, sweet, and simple method that uses the right mouse click to hide a single sheet!

To hide a single sheet using the right mouse click, –

1. Navigate toward the sheet (or tab) that you want to hide. Go to the tab name.
2. Right-click on the tab to see all the possible actions associated with that sheet.
3. Click on the Hide sheet option that appears fourth from below (or seventh from above).

That’s it! Congratulations on successfully hiding a sheet! 🎉

Interestingly, Google Sheets share a pop-up message with you as soon as the sheet is hidden. That message tells you that the sheet is hidden and from where you can access (or unhide) the same. Cool feature, I must say!

💡 Tip: You can also access the options associated with a sheet by clicking on that upside-down triangle that appears at the right of the sheet name.

Hide Multiple Sheets with a Right Click

More often than not, you will end up being in a situation where you want to hide more than one sheet. It becomes a tedious job to go to each sheet, right-click, and hide the same.

Well, a question would already have crossed the mind of many of you reading this post, “is there any way we can hide multiple sheets in a single shot?” Again, there is!

You can select all the tabs that you want to hide and then use the right-click single time to hide them all. See a step-by-step demo below:

1. Select all the sheets that you want to hide. There are two ways of doing so –
   a. If the sheets you want to hide are adjacent, select the first one, hold the Shift button and click on the sheet until which you want to hide all the sheets.
   b. If the sheets you want to hide are not adjacent (meaning, you want to keep the sheets in between), Click select the first one to hide, hold the Ctrl button, and click on the other sheets you want to hide one by one.
2. Right-click on any of the selected sheets to access the options.
3. The fourth from the last option now reads as “Hide sheets.” Click on it, and now all of your selected sheets are hidden.

Notice that, in the first example where you wanted to hide the single sheet, the option was reading as “Hide sheet.” In this example, the same option is reading as “Hide Sheets” because you are now hiding more than one sheet.

📝 Note: The upside-down triangle option also works when you select multiple sheets to hide.

Hide Multiple Sheets Using the Apps Script

The world is full of automation, and people try to eliminate manual efforts to do a task every time. Various tools help you in automating stuff. Google Sheets also has a tool that allows you to automate almost everything (if not everything).

The Apps Script is a JavaScript-based platform developed by Google that allows you to automate the tasks inside Sheets (Other Google Workspaces can also be automated, but that is beyond the scope of this article).

You will now use the same tool to automate the process of hiding the sheets. If you want to know more about it, we have a Beginners Guide to Apps Script that can help you get going.

You can access the Apps Script through the Extensions menu.

Click on it from the menubar, and the third option is for Apps Script. As soon as you click on it, the Apps Script environment will be launched in a new browser tab.

Once the tool opens up in a new browser window, please copy the following code and paste it under the script window. Don’t worry if the code seems alien at the moment. You will get the line-by-line explanation in the section below:

function notToHide(){
   hideSheetsOtherThan('Working');
}
function hideSheetsOtherThan(notToHide) {
  var spreadshts=SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for(var i =0; i < spreadshts.length;i++){
    if(spreadshts[i].getName()!=notToHide){
      spreadshts[i].hideSheet();
    }
  }
}

Explanation

The code consists of two functions. The first function works within the second as an input/parameter.

• The function keyword on the first line of code defines a new function with the name notToHide().
• Then you use the hideSheetsOtherThan() clause to let the system know to hide every sheet other than the one with the name “Working.”
• The third line defines a new function using the same hideSheetsOtherThan(). The notToHide function from the previous section is being used as a functional parameter inside this.
• Then you use the SpreadsheetApp.getActiveSpreadsheet() clause on the new line. Inside it, you use the getSheets() to get all the sheets from your Spreadsheet (provided that you have multiple sheets in it). The result is stored in a variable with a name spreadshts.
• Now, use a for loop that runs starting from 0 until the length of spreadshts variable (meaning, from 0 to the last number of spreadsheets found other than “Working”).
• The if() conditions on the subsequent line check if the name for each sheet found is matching with the sheet name that the notToHide function holds (Remember, it holds the “Working” sheet).
• If the sheet’s name is not “Working,” the following line of code will execute the hideSheet() clause that hides the sheet.
• The process continues until the for loop finds the last sheet with a name other than “Working.” Then it ends, and you see all such sheets are now hidden.

Now, you click on the Run button placed on the Menu bar of the Apps Script to execute this code.

Remember that if you are using this code for the first time, you need to authorize Apps Script through your Google Account to use this tool (Apps Script codes are tools that automate stuff for you) on your spreadsheets.

Once the code executes, you will see an execution log where execution-related steps let you know the progress of the same.

You are through when you see Execution completed inside the log. It means the code is run and implemented successfully on the sheet.

📝 Note: Don’t forget to change the sheet name from “Working” to the one you want to keep unhidden in your spreadsheet, and you will be good to go. That will be the only thing you need to amend in this code to get going.

Hide Sheet with an Add-on

On top of all the options discussed above, one more exciting option is there for hiding the sheets. You must have seen different organizers develop the Add-on tools that can be used on Google Sheets to automate the tasks.

Consider these tools as automation that allows you to do specific tasks with a click if you install them on your system.

One such Add-on from ablebits allows you to hide sheets inside Google Sheets. You will be exploring the same to get this task done. It is simple, convenient, and easy to use.

Firstly, click on the Get Add-ons (a simple plus icon) button that you can find at the extreme right vertical pane in your Google Sheets environment.

As soon as you click on the Get Add-ons button, a new Google Workspace Marketplace window will appear on top of the sheets, where you can see hundreds of add-ons that can be used and installed to make the work faster.

Note that not every add-on is associated with sheets, but most of them are! 😉

In the search box, type Sheets Manager and hit the keyboard Enter button.

The Marketplace will search and show all the add-ons associated with the name. Click on the first one coming from ablebits, as shown in the screenshot above.

On the next screen, click on the Install button to add this one on Sheets. This will install the tool into your Google Sheets workspace.

Now, go to the Extensions menu > navigate towards the Sheets Manager > Click on the Start button from the submenu that emerges as soon as you navigate the Sheets Manager menu.

Now, you can see on the right-hand side that the Sheets Manager window is opened. The first tool option with an eye symbol is the one that allows you to hide the sheets. You can see the name of sheets already popping up there.

Click on the second sheet (sheet name – “Hide This”), and then hold the Shift key and click on the last sheet. This way, sheets starting from second to fifth will be selected.

Then click on the eye, and the selected sheets will be hidden. That’s it! As easy as it looks! 😎

Once you hide these sheets, they will be grayed out, and the sheet with the name “Working” will be active.

That’s how you can hide sheets using the ablebits Add-on.

Unhiding Sheets

Until now, you have been exploring the ways to hide the tabs in Google Sheets. You learned around four of them to get the task done.

However, unhiding sheets is equally essential. Imagine a situation where you hide those tabs for convenience and now want to make amendments to the tabs hidden but don’t know the way to unhide. It will be a situation nobody wants themselves to be in.

Therefore, this section introduces you to different ways of unhiding tabs from Google Sheets.

Unhide a Sheet with the View Menu

Remember, you were using the right-click to hide a single or multiple sheets by now. But once the sheet is hidden, there is no way you can right-click on the same. So, how can you unhide it (or them) now?

Well, there is a way, and it is not hard to unhide the sheets. You can use the View menu to unhide those hidden sheets. Let’s explore this way of unhiding sheets.

1. Navigate towards the View menu and click on it to expand the list of available options inside.
2. The fourth option from the top is for Hidden sheets. Click on it to expand the submenu inside.
3. As soon as you expand the Hidden sheets submenu, you will see “Show Sheet (Sheet represents the sheet name that you have hidden).” Click on the “Show Hide This” option to unhide the sheet with the name “Hide This.”

Have you noticed that the Hidden sheets submenu shows the number of sheets hidden inside the current view? In this case, it shows that four sheets are hidden. That is really cool because it gives you a piece of upfront information.

Moreover, you can only unhide one sheet at a time through the View menu. There is no facility to unhide all of them at once.

Unhide a Sheet with All Sheets Button

The other way of unhiding a sheet is using the All Sheets button. This button is a less known facility. I mean, it is always there, but you seldom recognize the same.

You must have seen the three horizontal lines before the first active sheet in your workspace, that is nothing but the All Sheets button, and we are going to use the same to unhide one sheet at a time.

1. Navigate towards the All Sheets button at the bottom left, between the Add Sheets (plus icon) and the first active sheet. Click on the All Sheets button.
2. You will see a list of available sheets inside. Click on any one which is grayed out, and it will be unhiding in the view.

That’s it! Easy Peasy, Right?

Just in two clicks, you were able to unhide a sheet. But again, note that you can only unhide one sheet at a time using the All Sheets button. In the next section, you will see an alternative that tackles this problem successfully and allows you to unhide all the sheets in a single go.

Note: Have a look at the checkmark in front of the sheet name. It lets you know that the sheet is active and unhidden in the current view.

Unhide All Sheets with the Apps Script

Until now, the two options you explored are the ones where you can unhide one sheet at a time. Imagine a situation where you are dealing with around 20 or more sheets to unhide.

If you go by the current approach, it will take you a lot of time to unhide them all one by one. Besides, the frustration of doing the repeating task will again be a thing constantly hitting you.

Is there any way you can unhide all these sheets in a single go? Well, who says NO? There, albeit, is a way! Using the Apps Script! 😎

You can write a small piece of code that can simultaneously unhide all the sheets. Automating things with Apps Script not only saves time, but also cost.

Again navigate towards the Extensions menu, and click on the third option from the top, Apps Script, to launch the tool into a new browser tab.

function unhideSheets() {
  var spreadsht = SpreadsheetApp.getActiveSpreadsheet();
  var sheetsUnhide = spreadsht.getSheets();
  
  for(var i =0; i<sheetsUnhide.length; i++){
    sheetsUnhide[i].showSheet();
  }
}

Please copy the code above and paste it inside the script. As you might have guessed right, the code will allow you to unhide all the hidden sheets. Let’s break the code down line-by-line.

Explanation

• You use the function keyword on the first line to define one with the name unhideSheets. This will be the function that can hold the entire code that automates the process of unhiding.
• On the subsequent line, you use the SpreadsheetApp.getActiveSpreadsheet() clause to access the active Google Sheet. This will be the spreadsheet from which you want to unhide the tabs. The result is stored in a variable named spreadsht.
• The following variable with the name unhideSheets holds all the sheet (tab) names that are present inside the active spreadsheet. You use the getSheets() clause applied on the spreadsht variable defined in the previous step.
• Now, write a for loop starting from zero to the length of sheetsUnhide variable (length gives a number of sheets inside that variable). It runs through until the last working sheet.
• Finally, use the showSheet() clause on each sheet of sheetsUnhide to (you might have guessed it already! 😉) show all the hidden sheets.

Click on the Run button to execute this piece of code and see the magic!

You can see the sheets are now active or unhide in the current spreadsheet. Just a tiny piece of code can save you a lot of time when you are in such situations.

Unhide Sheets with an Add-on

You have seen the ablebits Sheets Manager add-on that allows you to hide all the sheets (tabs) in Google Sheets. The same tool can be used to unhide the sheets as well.

Launch the ablebits Sheets Manager by navigating to Extensions > Sheets Manager > Start.

You can see all tabs in the spreadsheet. The “Working” sheet is active, and other sheets are grayed out. Meaning they are hidden.

Please select all the grayed-out sheets (the method to select them all in a single shot is shared in the first part of this article).

Then, you click on the eye symbol that unhides them all.

This is one quick way to unhide all the sheets in a single shot.

Conclusions

• You can use the right-mouse click to hide a single sheet. Go to the sheet, right-click on the sheet name, and select the Hide sheet option. You can also use the upside-down triangle symbol to get this done.
• The same method can be used to hide multiple sheets. Firstly select all the sheets you want to hide. Then right, click on anyone, and click on the Hide sheets option.
• You can write a small script (code) that can automatically hide all the sheets apart from the one you want to see in the spreadsheet.
• The ablebits Sheets Manager is a tool that allows you to both hide and unhide single or multiple sheets all at once.
• To unhide sheets one by one, you can go to the View menu, then Hidden sheets, and unhide them one by one.
• Another way to unhide sheets one by one is through the All Sheets (a button with three horizontal lines) button. The button is placed in between Add Sheets and the first active sheet.
• You can use the Apps Script to write a small piece of code that can unhide all the hidden sheets in a single shot.

The article ends here as we have covered almost every known way to hide or unhide sheets. Is there any other way to hide or unhide sheets that you know but have not discussed here?

Let me know in the comment section below. Until the next time, ciao! 😄