9 Tips to Hide and Show Sheets in Google Sheets

If you’re a Google Sheets user, chances are that you often share them with your colleagues. Moreover, you want to share only the relevant templates with them. However, if you have too many sheets, it becomes quite a task for users to browse through all of them and find the one that suits them.

Furthermore, it can become a headache for you as well, because you don’t want to share everything with them, but you feel helpless as you don’t know how to properly hide the tabs in the shared spreadsheet. And another scenario could be when you have too many sheets, and you want to copy the data from the rightmost sheet to the current sheet. If you rearrange the order of these sheets, it would take a lot of time.

For all these common problems, is there a solution? Of course, there is one! 😎

You can hide the sheets to solve these problems. Let me explain a bit!

If you hide the other sheets in your example and keep only the one that is useful for the end users, they will be able to perform their task more quickly. Moreover, this puts you at ease because you no longer have to worry about sensitive content leaks. Plus, hiding a few sheets can bring the rightmost sheet closer to the sheet from which you want to copy the data.

Now you can see how hiding and unhiding a sheet can be important in Google Sheets. So fasten your seatbelts as we present to you different methods to hide and/or unhide individual sheets.

Hide a Sheet using Right-Click

Well, hiding tabs in Google Sheets is not just about restricting access to their content. You could lock cells for that purpose, which is enough.

However, generally speaking, hiding sheets makes accessing or navigating easier. Imagine, fewer sheets means users can access data more easily! 👍

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

To hide a single sheet using the right-click:

1. Go to the tab (or the tab) that you want to hide. Right-click on the tab to display all possible actions associated with that sheet.
2. Click on the Hide sheet option that appears fourth from the bottom (or seventh from the top).

And voila! Congratulations, you have successfully hidden a sheet! 🎉

Interesting, isn’t it? Google Sheets displays a contextual message as soon as the sheet is hidden. This message tells you that the sheet is hidden and instructs you on how to access it (or unhide it). A nice feature, I must say!

💡 Tip: You can also access the options associated with a sheet by clicking on the inverted triangle that appears to the right of the sheet name.

Hide Multiple Sheets using Right-Click

Very often, you will find yourself in a situation where you want to hide more than one sheet. It becomes tedious to go to each sheet, right-click, and hide it one by one.

Well, a question must have already crossed the minds of many of you reading this article: “Is it possible to hide multiple sheets at once?” Well, yes, there is a solution!

You can select all the sheets you want to hide, and then use the right-click to hide all of them at once. Here’s a step-by-step demonstration:

1. Select all the sheets you want to hide. There are two ways to do this – a. If the sheets you want to hide are adjacent, select the first one, hold down the Shift key, and click on the sheet till which you want to hide all the sheets. b. If the sheets you want to hide are not adjacent (i.e., if you want to keep the intermediate sheets), click on the first sheet to hide, hold down the Ctrl key, 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 option from the bottom of the list is now “Hide sheets”. Click on it, and all the selected sheets are now hidden.

Note that, in the first example where you wanted to hide a single sheet, the option read “Hide sheet”. In this example, that same option reads “Hide sheets” because you are now hiding more than one sheet.

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

Hide Multiple Sheets using Apps Script

The world is full of automation, and people are trying to eliminate manual efforts every chance they get. Various tools help you automate tasks. Google Sheets also has a tool that allows you to automate almost everything (if not everything).

Apps Script is a JavaScript-based platform developed by Google that allows you to automate tasks in Sheets (other Google workspaces can also be automated, but that’s beyond the scope of this article).

You will now use the same tool to automate the process of hiding sheets. If you want to learn more, we have a beginner’s guide to Apps Script that can help you get started.

You can access Apps Script via the Extensions menu.

Click on it in the menu bar, and the third option is for Apps Script. As soon as you click on it, the Apps Script environment will open in a new browser tab.

Once the tool opens in a new browser window, copy the following code and paste it below the script window. Don’t worry if the code looks unfamiliar to you at the moment. You will get line-by-line explanations in the next section:

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 as input/parameter to the second function.

• The first line of code uses the function keyword to define a new function with the name notToHide().
• Next, you use the hideSheetsOtherThan() clause to tell the system to hide all sheets except the one named “Working”.
• The third line defines a new function using hideSheetsOtherThan(). The notToHide function from the previous section is used as a functional parameter inside it.
• Next, you use the SpreadsheetApp.getActiveSpreadsheet() clause on the next line. Inside it, you use getSheets() to get all the sheets in your spreadsheet (provided you have multiple sheets in it). The result is stored in a variable named spreadshts.
• Now, use a for loop that runs from 0 to the length of spreadshts (i.e., from 0 to the last sheet number other than “Working”).
• The if() conditions on the next line check if the name of each sheet found matches the sheet name that the notToHide function contains (Remember, it contains the “Working” sheet).
• If the sheet name is not “Working”, the next line of code executes the hideSheet() clause, which 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 that all those sheets are now hidden.

Now click on the “Run” button placed on the Apps Script menu bar to run this code.

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

Once the code is executed, you will see an execution log where the execution steps let you know the progress of it.

When you see “Execution completed” in the log, it means the code has executed and been successfully implemented on the sheet.

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

Hide a Sheet with an Add-on

In addition to all the options discussed above, there is another interesting option for hiding sheets. You have probably seen different developers develop add-on tools that can be used on Google Sheets to automate tasks.

Consider these tools as an automation that allows you to perform specific tasks in one click if you install them on your system.

One of these Ablebits add-ons allows you to hide sheets in Google Sheets. You are going to explore this tool to accomplish this task. It’s simple, convenient, and easy to use.

First, click on the “Get add-ons” button (a simple plus sign) that you can find in the vertical panel on the right side of your Google Sheets environment.

As soon as you click on the “Get add-ons” button, a new “Google Workspace Marketplace” window will appear above the sheets, where you can see hundreds of add-ons that can be used and installed to speed up your work.

Note that not all add-ons are related to sheets, but most of them are! 😉

In the search box, type “Sheets Manager” and press the Enter key on the keyboard.

The marketplace will search and display all the add-ons associated with that name. Click on the first Ablebits add-on, as shown in the screenshot above.

On the next screen, click on the “Install” button to add it to the sheets. This will install the tool in your Google Sheets environment.

Now go to the “Extensions” menu > go to “Sheets Manager” > click on the “Start” button in the submenu that appears as soon as you navigate to the “Sheets Manager” menu.

Now, you can see on the right side that the “Sheets Manager” window is open. The first tool option with an eye symbol is the one that allows you to hide sheets. You can see the sheet names already appearing there.

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

Then, click on the eye, and the selected sheets will be hidden. And there you have it! As simple as that! 😎

Once you have hidden those sheets, they will appear in gray, and the sheet with the name “Working” will be active.

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

Unhide Sheets

So far, you have explored ways to hide tabs in Google Sheets. You have discovered four of them to accomplish this task.

However, it is equally important to be able to show hidden sheets. Imagine a situation where you hid those tabs for convenience and now want to make changes to the hidden tabs but don’t know how to show them again. No one wants to be in such a situation.

Therefore, this section presents different methods to unhide tabs in Google Sheets.

Unhide a Sheet using the View Menu

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

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

1. Go to the “View” menu and click on it to display the list of available options.
2. The fourth option from the top is “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 name of the sheet you hid). Click on the “Show sheet” option to unhide the sheet with the name “Hide this.”

Did you notice that the “Hidden sheets” submenu shows the number of hidden sheets in the current view? In this case, it shows that four sheets are hidden. This is really handy as it gives you immediate information.

Also, you can only unhide one sheet at a time via the View menu. There is no option to unhide all of them at once.

Unhide a Sheet using the All Sheets button

The other way to unhide a sheet is by using the “All sheets” button. This button is a less known feature. I mean, it’s always there, but it’s rarely recognized.

You have probably seen the three horizontal lines in front of the first active sheet in your workspace, that’s nothing but the “All sheets” button, and we are going to use it to unhide a sheet at a time.

1. Go to the “All sheets” button at the bottom left, between “Add sheets” (plus icon) and the first active sheet. Click on the “All sheets” button.
2. You will see a list of available sheets. Click on the one that appears grayed out, and it will be unhidden in the view.

And there you have it! Easy, isn’t it?

In just two clicks, you have managed 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 successfully solves this problem and allows you to unhide all sheets at once.

Note: Look for the checkmark in front of the sheet name. This lets you know if the sheet is active and unhidden in the current view.

Unhide all Sheets using Apps Script

So far, the two options you have explored are the ones where you can unhide one sheet at a time. Imagine a situation where you’re working with a dozen sheets or more to unhide.

If you follow the current approach, it will take a lot of time to unhide one sheet at a time. Plus, the frustration of doing a repetitive task will always be present.

Is there a way to unhide all those sheets at once? Well, who says NO? There is one! By using Apps Script! 😎

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

Once again, go to the “Extensions” menu and click on the third option from the top, “Apps Script,” to launch the tool in a new browser tab.

Copy the code below and paste it into the script. As you might have guessed, the code allows you to unhide all hidden sheets. Let’s break down the code line by line:

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

Explanation

• You use the function keyword on the first line to define a function named unhideSheets(). This function will contain all the code that automates the unhiding process.
• On the next line, you use the SpreadsheetApp.getActiveSpreadsheet() clause to access the active spreadsheet. This is the spreadsheet from which you want to unhide the tabs. The result is stored in a variable named spreadsht.
• The next variable named sheetsUnhide contains all the sheets (tabs) inside the active spreadsheet. You use the getSheets() clause applied to the spreadsht variable defined in the previous step.
• Now, you write a for loop that runs from 0 to the length of sheetsUnhide (i.e., from 0 to the number of sheets inside that variable). It runs until the last worksheet.
• Finally, you use the showSheet() clause on each sheet of sheetsUnhide to (you probably guessed it already! 😉) show all the hidden sheets.

Click on the “Run” button to run this piece of code and see the magic happen!

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

Unhide Sheets with an Add-on

You have seen the Ablebits “Sheets Manager” add-on that allows you to hide and unhide single or multiple sheets in one go. The same tool can also be used to unhide sheets.

Launch the “Sheets Manager” add-on by going to the “Extensions” menu > “Sheets Manager” > “Start”.

You can see all the sheets. The “Working” sheet is active, and the other sheets are grayed out, indicating that they are hidden.

Select all the grayed out sheets (the method to select them all at once is explained in the first part of this article).

Then, click on the eye symbol to unhide all of them.

This is a quick way to unhide all sheets at once.

Conclusions

You can use the right-click to hide a single sheet. Go to the sheet, right-click on the sheet name, and select “Hide sheet” option. You can also use the inverted triangle symbol to accomplish this task.

The same method can be used to hide multiple sheets. First, select all the sheets you want to hide. Then, right-click on any of them and click on the “Hide sheets” option.

You can write a small script (code) that can automatically hide all sheets except the one you want to see in the spreadsheet.

Ablebits’ Sheets Manager is a tool that allows you to both hide and unhide single or multiple sheets at once.

To unhide sheets one by one, you can go to the “View” menu, then go to “Hidden sheets” and unhide them one by one.

Another way to unhide sheets one by one is by using the “All sheets” button (a button with three horizontal lines). The button is placed between “Add sheets” and the first active sheet.

You can use Apps Script to write a small piece of code that can unhide all hidden sheets at once.

You have seen Ablebits’ Sheets Manager add-on that allows you to both hide and unhide single or multiple sheets at once.

The article concludes here, as we have covered almost all the known ways to hide or unhide sheets. Do you know any other way to hide or unhide sheets that you know but hasn’t been discussed here?

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

---

This article was written by the team at bolamarketing.com, drawing from their expertise and experience in the field of Google Sheets. For more tips and tricks, visit our website Crawlan.com.