ExcelInsider Team
Hiding sheets from certain users in Google Sheets is often necessary when you are sharing your spreadsheet with others and don’t want them to access confidential information. Although Google Sheets does not have any built-in tools to hide sheets from certain users, there are some effective methods that you can follow to achieve this.
To quickly hide a tab in Google Sheets from certain users, follow the steps below:
➤ Open your Google Sheets file.
➤ Right-click on the tab that you wish to hide
➤ From the context menu, select Hide Sheet. Now your desired sheet tab is hidden.
➤ Go to the Share option and generate a shareable link with view-only permission.
In this article, we will learn 3 simple and effective methods for hiding sheets from certain users in Google Sheets. Follow them properly.
Hide Sheets and Share with View Only Permission
This is the fastest and easiest method of hiding sheets from unauthorized users. However, users can still access the hidden sheets by simply downloading them. In the following sample dataset, we have 3 tabs containing information about employee attendance, product data and monthly budget. We want to hide the Monthly budget tab and share the rest of the spreadsheet with other users. Follow the steps below.
Steps:
➤ Open the spreadsheet file, head to the Monthly Budget tab and right click on it.
➤ From the context menu, click on Hide Sheet. Now, you can see that the tab is no longer visible.
➤ Lastly, go to the Share option and generate a link with Viewer permission.
Note:
Viewers accessing the sheet from a shareable link can still view the names of the hidden sheets by heading to View >> Hidden sheets. However, they can not unhide or directly access the content of the sheets. To do that, they would need to manually download them.
Hide Sheets by Using the IMPORTRANGE Function
Although a bit complex, this method is much more effective at hiding sheets from certain users than the first method. The IMPORTRANGE function of Google Sheets is used to transfer specific sheets or a range of data onto a new spreadsheet while keeping certain information hidden. It also allows for dynamic updates, meaning any changes made to the original file are automatically updated in the new shared file. We will use the same dataset and hide the Monthly Budget tab from the user. Follow the steps below to do it properly.
Steps:
➤ Open the main spreadsheet file containing all tabs and copy its URL from the browser.
Note:
No need to copy the entire URL. Only copy the part between d/ and /edit
➤ Now, create a new spreadsheet titled Shared File. In the A1 cell type the formula:
=IMPORTRANGE(“1UOJoBXxsuuS3suzTCsP-N1FIwAws2rt5z9QvQlUc0pc”,”Employee Attendance!A1:Z1000″)
Note:
“1UOJoBXxsuuS3suzTCsP-N1FIwAws2rt5z9QvQlUc0pc” is the URL that we copied from the main spreadsheet file. And, “Employee Attendance!A1:Z1000” indicates the sheet name and range of the cell to import.
➤ Then, create a new sheet within the same spreadsheet and in the A1 cell, put the formula:
=IMPORTRANGE(“1UOJoBXxsuuS3suzTCsP-N1FIwAws2rt5z9QvQlUc0pc”,”Product Data!A1:Z1000″).
Note:
This formula will help us to import all information on Product Data from the main sheet to the new one.
➤Then, go to the Share option, generate a shareable link with your permission preference and click on Done. Now you have a separate spreadsheet file that you can share with certain users, which does not contain the Monthly Budget tab.
Hide Sheets by Creating Separate Spreadsheets for Different Users
While time-consuming, this is one of the more effective methods of hiding sheets, restricting users from accessing sensitive information. This method will help you to hide sheets from specific users by only sharing the ones that you want them to access. Just like previous methods, we will work with the same dataset and try to hide the Monthly Budget tab from certain users. Follow the steps shown below:
Steps:
➤ Open the original spreadsheet.
➤ Select Employee Attendance and Product Data tabs.
Note:
You can hold the CTRL button while using mouse to select multiple tabs at once.
➤ Right click on the selected tabs and head to Copy to >> New Spreadsheet. You should now have a new, separate spreadsheet containing information about the tabs that you want to share.
Note:
Any changes made to the original spreadsheet will not affect the data of the copied spreadsheet.
➤ Next, click on the Share button and create a shareable link with all the necessary permissions.
Frequently Asked Questions
Why IMPORTRANGE Function in the Second Method Shows #REF! Error?
If you are getting a #REF! Error in IMPORTRANGE function, it typically means you have an incorrect URL, sheet name or range. Sometimes, case sensitivity and rogue spaces can also cause this issue.
Can I Permanently Hide a Sheet in Google Sheets?
No, Google Sheets does not have any built-in feature to permanently hide a sheet. But some effective workarounds have already been discussed in this article.
Which is the Best Method for Hiding Sheets?
Using the IMPORTRANGE function is the most effective method for hiding sheets in both corporate and everyday use. This method keeps certain sheets hidden while retaining the ability to automatically update data from the original source file.
Concluding Words
Restricting access to certain sheets for specific users is a standard practice in the corporate and professional environment. In this article, we talked about 3 effective methods of hiding sheets from certain users, including hiding sheets with view-only sharing, using the IMPORTRANGE function to hide sheets, and lastly, creating separate spreadsheets for different users. Choose the method that best aligns with your workflow and the level of confidentiality.
