Google docs are the best free alternative to Microsoft Office. Moreover, Google Docs provides the flexibility of being a web-based app; there is no need to install on your local PC. Google offers Google Sheets to replace Microsoft Excel, and there are more web-based applications from Google to replace Microsoft Word, PowerPoint, and Forms (Create Google Forms) similar to office tools.
By default, Google allows sharing Google Docs or Google Sheets to other users with a valid Gmail ID. You can limit their access to the document on a different level like view/comment/edit etc. Google SpreadSheet may have multiple sheets, and there is no easy solution to share these individual sheets with your team members.
We are going to discuss a solution to share only a particular sheet from a multiple sheets Google Spread Sheet.
Step 1: Protect Google SpreadSheet (Primary) to Limit Access to Specific People
This step is very important since we are going to use a link to share the primary document with another user. Google documents can view in public or viewable to anybody with a share link. You can change this access and limit only to certain members that you allow.
Open the required Google spreadsheet (we say primary sheet) and click on the Share button on the right top corner of the document. Now click on Advanced from the “Share with others” window. Now you will be on the ShareSettings window, click on “Change” link. Chose “Off – Specific people” from the next Link sharing window and click on Save button.
This step is critical since you are going to expose the share link with your team and you have to make sure they won’t be able to access your entire spreadsheet with this shared link.
Related: 12 Best Google Voice Features you Never Heard Before
Step 2: Get A Google Sheet Share Link
Once you complete the above step, now you will be back on the “Sharing settings” window and you can copy the link from the share window. Keep this link saved in notepad or somewhere for future use. If you miss this link, you can always come back to this screen and get the share link.
To get the Google Sheet share link from the main screen, click on Share > Advanced > Link to share. Alternatively, you can get the same link from File Menu > Share > Advanced > Link to share.
Related: How to Get Google Form Email with Form Content
Step 3: Create a New Google Spreadsheet (Secondary) to Share with Your Team Members
Now you need to create a new spreadsheet to share with the user. This is the sheet you are going to share with your team. Just go to Google Sheet application window and create a new spreadsheet with a valid name. There is no need to type any data inside of this sheet at this point.
Step 4: Import Google Individual Spreadsheet to A New Google Sheet
Well, in this step we are going to import the required individual Google sheet from the primary document to a new Google SpreadSheet (secondary) that you created in Step 3. We are going to use a function called “IMPORTRANGE” The syntax is:-
IMPORTRANGE (spreadsheet key, range_string)
We chose row A1 to type Spread Sheet heading and used row A2 to insert the function. The import function is like this;
=IMPORTRANGE(“sheet (primary) share link”,”share sheet name!share range start column: share range end column
In our example, we inserted the following URL and press Enter once you complete it. In this function, the URL is the same that we got in Step 2, Summary is the tab or sheet name from Primary Google SpreadSheet, and A & F are the cell ranges that you want to share. Don’t forget to insert the ‘!’ after sheet name and ‘:’ between row letters in the function.
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/xxxxx”,”Summary!A:F”)
Now you will see on A2 “#REF! and hover your cursor on top of it and click Allow Access. The secondary sheet will import the values now from the primary Google SpreadSheet specified tab/sheet.
This trick will import only the values from the primary sheet. You have to take care of the styling and renaming the secondary sheet as per your requirement.
Related: How to Search Images on Chrome with Google Lens
Step 5: Provide Your Members’ Limited Access to A New Google Sheet
You have to define the limited access to your team members. Click on Share button > Share with others window > Enter Gmail address of your team member > Change the access from “Can edit” to “Can comment”. This step protects new Google spreadsheet users from altering the share link that may expose more information than you intend to share.
Now you are done with the Google Spreadsheet tab sharing. Whenever you make any changes in the Primary Google Spreadsheet tab, the secondary sheet values will automatically update with primary table values.
You can use this workaround to share individual tabs of a Google Spreadsheet with separate members from your team.
Google lets you create, work and share these office documents with your teammates or office mates with a valid Gmail account. The beauty of this web application is more than one member can work on this document at a time; an ideal solution to draft your school project with your team.
Related: Best 12 G Suite Products Helped my Business to Perform Well.
This workaround is very useful for schools, a small-scale business where they want to share individual sheets with particular people. The solution is a bit tricky, but works well and easy to implement. Let’s take a two-sheet Google SpreadSheet as an example and will show you how to share only the sheet named “Summary” with one of the team members.
The downside of this solution is, you have to create separate secondary Google spreadsheets for each member on your team, but this will give you high security and your primary spreadsheet will be isolated and secure from other team members.
Also, you can’t reflect on the cell format on the Secondary Google Sheet. Whatever the color code or format of the cell you did on the Primary sheet is limited to that sheet. It won’t copy to the Secondary with this solution.
However, this workaround is a quick and free solution to manage multiple sheets separately with a master sheet. With this solution, you can share each tab of your primary sheet with the secondary Google Sheet.
This is excellent. Incredibly useful and very well explained. Follow up question: Is there a way to show them information on a tab within the same spreadsheet other than the primary tab?
Yes, You can share multiple tabs with this method, in each sharing code you have to add each tab name that required to share.
Thank you for this material. What if Sam copy the link from the ImportRange formula in “Sam Shared” document and just past it in browser?
Paul,
Even if Sam copy and paste the URL, will not break the accessibility to the file. This URL will prompt the user to provide the credentials to open the sheet, depends on the credential (admin or Sam), the user will redirect to there with the given access.
Still they cannot edit the new file. This is nothing different from just publish a single tab.
@William
You won’t be able to edit Secondary sheet, this workout is to reflect the changes from Primary Sheet to Secondary file. The edit access od secondary sheet/file should be blocked by default for security.
I don’t want to make any extra sheet. Is it possible to share a tab of same sheet which i am editing
Me too. There are 20+ tabs to share with individuals. Is there no way to just share the tab directly from the Primary sheet, then specify the editing restrictions as I list their email addresses in Share?
I really wish to see this feature on Google sheet, but so far, no luck.
Can you set up notifications on each sheet so that when new data is entered on the primary, your recipients are notified that changes have been made and they need to look at their sheet?
Hi Steve, need your suggestion here. I have 19 members in my team. I have devised a master sheet for Time motion study that each member needs to fill in. Now, I don’t want one person’s data to be visible to another, except me, for privacy reasons. You think that’s possible ? … I had thought that with your article, I can make 19 secondary replica sheets of my master sheet, and name these sheets as per the team members’s name, and that each person cud see or edit only their respective sheet, which apart from them, only i cud see …. but its not likely , is it ? There’s got to be a way ? …..
@Krishna,
In your case, the quick solution is to create a single sheet for each member with write permission and combine all together in a master sheet with multiple tabs. Since you are giving write permission to the members, this article workaround is not applicable for your requirement.
As one additional layer of security, you can hide the row that contains your formula. This prevents the team member from being able to see the unique share link.
To do so:
-Insert a blank row to the top of your primary document.
-On your secondary sheet (the one shared with your team member), include that blank row in the range_string.
-On the secondary sheet, hide that first row that contains your formula. If they only have viewing access, they won’t be able to un-hide the empty row containing the formula.
@Tyler,
Thank you for your wonderful suggestion. We’ll add this tip very soon.
Hi – this is an amazing thing! But what if only certain columns were required which are not in a range say A, C , E only?
Hi, The article is good.
How can I lock a sheet like you have did in the first screenshot. The second sheet is “Transaction” it has locked symbol. How to do that? Can you explain please.
Step 1: Tap on the share button, and add users to the next screen. You can limit the user access on this screen.
I keep getting a formula parse error, what could I be doing wrong?
Please check this part on the formula,
Summary!A:F”and make sure the symbol !Thanks so much for this article. I also keep having this issue, and it highlights the “//” in “https://docs…” in red and says “We cannot parse the operator in this context.” What does this mean and am I doing something wrong?
What is the difference between your formula and just copying and pasting the data on a new sheet and sharing that new sheet with members?
Copying and pasting will not update the sheet again if you make any changes to the master sheet.
Thanks…still helpful today.
But still there is no update from google about this..right?
This worked great and I learned so much from this article.
Thank you
great article, is there a way to create a separate spreadsheet that an associate can enter figures in that populate the master? Basically vice versa of what is done above.
Did you get turnaround for this ?
You can create the master file for associate and create second excel with dedicated tab to sync with the master file.
This is great, thank you! Is there a quick way to bring over color coding and formatting as well or just the values in the cells? Other than creating a template and making copies for the shared sheets…
1. On the original document right click the tab and click ‘copy to…’ and create a copy in your new googlesheet.
2. In the newly created sheet highlight all the data and delete
3. in the top right corner insert the importrange function.
Thank you for your reply. I tried that, and got the error “Array result was not expanded because it would overwrite data in B1.” And I don’t understand, as this is precisely what we want.
Oops, I just noticed I had skipped the delete the data part. I did it now and it worked. Thanks.
Yep. Jack’s solution still works great! Thanks jack!
Thank you! I had a client with whom I only wanted to share one particular sheet out the whole document and I honestly feel like a magician after figuring this out. Thanks!
Hi there….absolutely fantastic post and i do believe this is the PERFECT solution to my dilemma of making a version of a sheet shareable, while not having to manually update it. Thank you so much for sharing 🙂
One follow-up question, and apologize if this may be a more general question…
a few of the secondary sheets, i would like to have it so the user has access to sort it. (my document is useful sorted by day then hour OR by hour then days). Is there a way to give each user sort of one-way access so that they may do whatever they please with the data on their end only? (maybe a user has a way to sort the data that i have not thought of; and would like to allow them to manipulate/sort the data in their own way without their changes affecting my sheet {or other users})
Thanks again!
Go to Data /Filter Views/ Create a new Filter. Each user can use the same primary spreadsheet but filtered according to their requirements. Not sure if this is what you requested.
I have two questions:
1. should I have G suite account to use this trick or I can use it with general google account??
2. I need to share only a selection of a sheet, not entire the sheet. I select a few columns and rows, then right click and select link to this rang. however, the link shows entire sheet rather than the selected area, any suggestion pls?
thanks
1, You don’t need GSuite account, can use Google account.
2, Already explained in the article to limit the columns to display.
Hello there…I have a few different retail locations. I want to share a workbook with all of the locations, but only allow individual locations to see their own info on their own sheet.(these sheets will have sensitive sales info). Either by hiding the other tabs for that particular location or by making the sheet password protected when viewed and edited…In addtion, they must be able to edit their sheet as well. Is there any solution using Google Sheets?
The current solution won’t allow editing on the client sheet. If you want to allow editing only from client-side, you can reverse this solution and your master copy will be a mirror collection of all client sheet. Hope this help.
For those that are given access to the shared individual sheets, are they able to edit the sheets on their end? Or would only I be able to edit from the master sheet? I attempted this but am getting a lot of feedback that they are not able to edit on their end. Thanks for your help!
They can’t edit from their end.
Is there a way to allow them to edit? Or a different way of sharing that will give them editing access? Thanks!
Rebecca,
If we provide editing access, then there is no point for syncing from master files. However, you can provide access to create comments for each cell. Hope this help.
But there is a point…I want them to be able to edit their own individual sheets that I have shared with them, but not the entire workbook. Here is my situation: I am the admin for a nonprofit that has several chapters across the US. I need to have a master sheet that keeps track of all chapters info, however, each chapter needs access to their own sheet to edit as needed throughout the year. Is this possible?
What I understand that,
-You want to keep all chapter information in a master file.
-You want to give others to access to edit the chapters.
If that is the case, you can do in a reverse way. Keep master file as a synced version, and keep excel files for the chapter as a source file.
Hello, great manual, thank you. Any idea, how I can copy (synch) also pictures, that are inserted in the primary sheet? (I have some data in each sheet with ilustrative picture). Thank you.
I keep getting “Forumla parse error” and have verified my formula is correct. Can you help?
Hi, I saw that this was a slightly older post, and wanted to see if any changes have been made to sheets, so that a shared tab can be editable on both ends. For context, we have a sheet that has many tabs, and one of them needs to be shared, which has been done. This one tab though needs to be editable by both the administrator of the sheet, as well as those with which it was shared.
If there is not a direct way to do this, are there any workarounds that you’re away of to create this function? Thanks in advance for the help.
You can give edit access to any sheet for more than one user. Its is pretty simple and you can see the guide from Google here: Step 2: Choose who to share with & how they can use your file
Thank you for the reply. I do have sheet sharing on for some of the users, however a couple of users should only have access to one of the tabs within the entire sheet. Is there any way to give someone access to just one of the tabs vs the entire sheet? Thanks again for the help.
Hi, Ben! I think if you flip which version of the sheet is the editable master, you’ll be all set. So, set the one you share to be the live content that is editable and do the importrange into the multi-sheet document.
Thanks for the tip! Very useful, but is there a way of copying the formatting to the secondary sheet so that it syncs with the primary sheet? I have copied the formatting as described in an earlier comment however if adjustments are made to the primary sheet it throws out the secondary sheets formatting?
I want to be able to pull one sheet out of the worksheet for someone to view and sign and date. Is this possible?
Additional Tip! I prefer placing the ImportRange function on 3rd row. Then 2nd Row DUPLICATES the entire 3rd Row. Why? This allows me to HIDE thhe entire 3rd ROW to hide the link containing the importrange function. This is an added security so there’s no way they can access/see the main file’s link.
for ease of use for some of my non techy co-workers, i made the main file fully edit-able and sent the link for a very specific people. then the secondary file (public viewing) for all others to see.
when sharing there is no *advanced* option. I’m sure there is nothing