Hiding sheet tabs is not a new feature in Google Sheets; it has been available for some time. You can hide tabs from specific people in Google Sheets. However, it depends on what prompts you to do so.
The extent to which you prioritize hiding sheets can vary. For instance, when managing multiple sheet tabs, you might find it necessary to hide certain tabs from specific individuals in Google Sheets.
This could be driven by a desire to maintain data privacy. Additionally, you may prefer that others do not inadvertently interfere with the data on certain tabs.
Whatever the reason may be, there are solutions available. The methods for hiding tabs from specific people in Google Sheets can differ based on your specific goals.
Reasons for Hiding Sheet Tabs from Specific People in Google Sheets
Are you concerned with either of the following?
- Privacy of Data: If your concern is the privacy of your data in Google Sheets, hiding sheets won’t address your needs. It won’t work for you! There is a workaround, which I’ll explain later.
- Tampering of Data: If your intention to hide tabs from specific people in Google Sheets is due to concerns about tampering with data or formulas, then hiding the sheets is the solution. You need to make some basic settings in Sheets.
I will provide detailed explanations for both points below.
Hide Tabs from Specific People to Prevent Tampering with Data
In this scenario, you can hide sheets. The person with whom you’ve shared the file cannot unhide the sheet, ensuring the safety of your file.
However, it’s important to note that the person can still access the data in the hidden sheet by making a copy of the file. If this is acceptable to you, follow the steps below.
Protecting the Tab in Google Sheets:
- Right-click on any tab name and select “Protect the sheet.”
- In the sidebar panel, under “Sheets,” choose the tab to protect and hide by clicking the drop-down menu (refer to the first screenshot below).
- Click on “Set permissions.”
- Enable “Restrict who can edit this range” and choose either of the options below:
- “Only you” to disable all users with whom you share the file from unhiding the sheet.
- “Custom” and untick specific users to disallow them from unhiding the sheet (this is risky because if they unhide the sheet, others will be able to see it).
- Click on “Done” (see the second screenshot below).
Screenshot #1:
Screenshot #2:
Hiding the Sheet:
- Right-click on the tab name and choose “Hide Sheet.”
Now you can share this file, and the person with access cannot unhide the sheet from the View menu “Hidden Sheets.” However, they can still see that there is a hidden tab in the file from the View menu.
This method ensures that you’ve protected the tab from other users, preventing interference with the data or formulas within it.
Nevertheless, keep in mind that they can make a copy of the file to access the hidden sheet, and therefore, the data is not entirely private.
Hide Tabs from Specific People Due to Privacy Concerns
I am deeply concerned about the privacy of my data in certain tabs in Google Sheets. What’s the solution?
As far as I know, no option provides complete privacy for your hidden tabs. But don’t worry! There are two workarounds for you.
Using IMPORTRANGE:
To hide tabs from specific people in Google Sheets with complete protection, follow these steps:
- First, change the sharing settings of your file to “Restricted – Only people with access can open this link.” To do this, click on the “Share” button or go to File > Share > Share with others. Locate the mentioned setting under “General.”
- Create a new file by clicking File > New.
- In that file, import the content to share from your private file using the IMPORTRANGE function. In cell A1 in the new file, insert this formula:
=IMPORTRANGE("URL_HERE", "SHEET1!A1:Z1000")
- In the above IMPORTRANGE formula, replace
URL_HERE
with the URL of your private file. In the last part of the formula, replace"SHEET1!A1:Z1000"
with your sheet name and the data range that you want to share (not hide).
- In the above IMPORTRANGE formula, replace
- If you have two tabs in your private file to share, apply this formula in two sheet tabs accordingly.
- Protect cell A1 by navigating to it and clicking on Data > Protect sheets and ranges > +Add a sheet or range. Click “Set permissions” and choose “Only you.”
- Now you are set to share this new file, i.e., the file containing the imported data, with the people you wish to share.
This is the first workaround to hide tabs from specific people in Google Sheets. In fact, what we have done here is share all tabs except the one we want to keep private.
Using Publish:
The easiest way to hide sheets from specific people is to publish the document to the web. Here is how this works.
- Hide the sheets you want.
- Click on File > Share > Publish to the web.
- Under Link, make sure that you selected “Entire document” and “Webpage” as the publishing mode.
- Click Publish and share the link that you get upon publishing with the people you want. They won’t be able to see the data in the hidden tabs.
Resources
We have seen three examples of hiding tabs from specific people in Google Sheets. One approach addresses tampering with data, and the other addresses privacy. Here are a few related resources.
Post updated!
When I try to edit this imported sheet, the whole sheet goes blank, and the first cell A1 show #REF! – Error Message.
Kindly guide me on how to make it editable.
Hi, MD Jain,
That’s the expected result. To make it editable, copy the data, right-click, and apply the command Paste special > Values only.
Point number 1 assumes people can make a copy of the file to check hidden sheets
Does this refer to the function file and make a copy?
Because if that’s the case, there is an option when sharing to prevent users from making copies that I can un-check, and it does exactly that for both editors and viewers.
There is an option to download the file, but no hidden pages get downloaded.
Am I missing something?
Hi, Kirk,
That seems correct when you set “anyone on the internet with this link can edit,” not when adding specific people with edit access.
Does the person who DOES have access to the restricted tab have to unhide and re-hide it from the other user every time they use the file?
Hi, Angel Vail,
I think yes.
Would the solution described in this article prevent the users of the second spreadsheet to peek in the secret sheets by guessing a reference, e.g.
=IMPORTRANGE("URL","SHEET3!E2:G4")
?They already got the URL of the original spreadsheet but does it require certain rights to add new formulas and import other ranges?
Hi, Jh,
If you have turned off (green Share button > Get shareable link > OFF) the sharing settings in the ‘private’ file, with the URL they can’t access the file.
Best,
To do the first import, you have to allow access to the private sheet in the new spreadsheet.
Once you do that, folks will have access to modify the URL within that sheet and access your private data (per Jh approach).
Actually, anyone with edit privileges can UNHIDE the sheet. Here I quote Google:
“All spreadsheet editors can unhide and view these sheets.”
@Nick Boesch
I think what you’re looking for could be the use of Google Forms for data entry, then organising the data with functions based off of who it is submitting it.
Hi. I have multiple people that I would like to enter forecasts for a product. Each person needs to enter their data and not be able to see the other persons’s data. I would have a master sheet importing their forecasts and rolling the data up. However, I think you already said that hiding tabs is not a good option because they can copy the file and unhide the tabs. So how can they all enter data without seeing other people’s data, and have the data transfer to the master file that only I can see? Sort of like clicking “submit”.
I’ve the same query.
Hi,
As @Moygs has suggested, I guess you may want to use Google Forms.
All that You Want to Know About Setting up of Google Docs Forms.
Best,
I would suggest having each of your contributors working on their own sheet. Have them set up the sharing so you have access to theirs. Then your sheet can import from each of theirs (using the importrange() function). Then you will have access to all their data, without them being able to see each other.
I also need to know this.
If anyone knows the answer, plz let me know.