Google

Collaborative budgeting with protected Google spreadsheets

A shared Google spreadsheet with protected areas may simplify collaboration for many tasks.

The budgeting process can be a pain. If you're the only person creating the budget, it's easy: create a spreadsheet with a column for each month, and create a row for every income and expense item. Fill it out. Done.

More often, however, budgeting involves several people. The person creating the budget depends on other people to provide financial projections for a specific line item. Soon, budgeting turns into pleading: "Please give me your projections!" Too often, the person creating the budget ends up frantically cutting-and-pasting information from one spreadsheet into another at the last minute.

We could always share

A shared Google Spreadsheet may simplify the budgeting process. Go ahead and create your master spreadsheet with all of your budget line items and columns. Once you have that, there are at least three ways to collaborate on a budget using a Google Spreadsheet: share the entire document, share a sheet, or share specific cells. Here's a walk-through of increasingly restrictive levels of sharing.

1. Share the entire Spreadsheet

The easiest way to collaborate on a Google Spreadsheet is to share the entire spreadsheet, as you would share any other Google document. View-only permissions or comment-only permissions won't save you much time: you'd still end up re-typing information. Full edit permissions enable collaboration, but also present a problem: any of your collaborators may edit any portion of the spreadsheet. With a fully shared spreadsheet, there's nothing to prevent one department head from decreasing another department's budget!

2. Protect sheets

A more secure approach is to restrict collaborators editing rights to specific sheets. For example, you might protect your overall combined budget sheet, but create separate worksheets for different programs. If you were running an Art Museum, for example, you might have a worksheet for the "Development Department" to budget for contributed income, and a separate worksheet for the "Marketing Department" to track admissions income, etc. Neither department would be able to edit the other department's data, although they could view it.

Protect sheets to specific Google Sheet collaborators

To protect sheets, click on the Sheet name (found in the lower left corner of the screen) to bring up the menu, and then choose "Protect Sheet." You may allow access to all collaborators, specific collaborators, or restrict editing to - as the menu says - "only me." Your collaborators may edit any of the cells on the sheets for which they have editing privileges.

Choose your sheet collaborators

3. Restrict editing to Named Ranges

Google Sheet editing may be even more restricted: you can limit users to editing specific ranges of cells. You need to add your collaborators using the Share button in the upper right corner, then restrict access to each of your worksheets (as listed above). At that point, users can edit all the cells, as above.

To restrict editing within a worksheet, use named and protected ranges

To create a named range, choose the "Data" drop-down menu and select "Named and protected ranges." Select the cells you want in the range, then click the "+ Add a range" link in the upper right. Name the range (e.g., "Column Headers', or "Exhibit Income", etc.), then click the checkbox next to the "Protect" option, and then click "Done".

Name your range, then modify the permissions to protect the range

You'll see a display that lets you enable editing access for your sheet collaborators. Modify the permissions as needed, then click done.

Enable a collaborator to edit a named range

Your spreadsheet collaborators will see cells that appear with a patterned background. Cells that appear with the patterned background can't be edited; those are protected. Cells that have a white background may be edited. Each collaborator can edit only the cells to which you have provided access.

Protected cells appear with a patterned background to your collaborators

Check your work

As always, check your work the first time you share a spreadsheet with protected sheets and named and protected ranges. Make sure you haven't accidentally permitted your collaborators to edit formulas! And remember that shared spreadsheets may be viewed by collaborators, so don't use shared spreadsheets for highly sensitive information.

Google Spreadsheets with restricted sheets and protected ranges may be useful for other purposes, as well. Project managers might keep track of progress reported by team members on protected sheets. Editors could use restricted sheets to collaborate on topic idea lists and schedules for various writers. Shared spreadsheets - with protected sheets and ranges - can change your workflow. So stop cutting-and-pasting and start collaborating!

Editor's note: A reader sent us an email asking for some help on how to protect specific cells in a Google Docs spreadsheet and this is the result. If you have specific questions on how to do something in Google Apps, post in the discussion associated with this blog post.

Also read:

About

Andy Wolber helps people understand and leverage technology for social impact. He resides in Ann Arbor, MI with his wife, Liz, and daughter, Katie.

6 comments
mrugger
mrugger

I have a Google Doc spreadsheet (I am the owner of the Doc). I have named ranges in the doc. When I change the a range's permissions for users with DOC EDIT permission from CAN EDIT to CAN COMMENT (only alternate option allowed)  and save the range permission changes, they do not persist. The restricted users can still edit the range's cells. Not only that, when I check the permissions for the given range the changed users once again show CAN EDIT access. What am I doing wrong - HELPPPPPP MR. BILL?


Additional comment: In our version of GDOCs the DATA tab shows two range options: Named Ranges... and Protect Sheets and Ranges..., instead of the example in the response: Name and Protect Range...

John.365
John.365

Collaboration tools are many but spreadsheet is one of the best tools we have now, but it lacks certain capabilities like hiding certain data and sharing with multiple users at a time.And for me while budgeting its one of the most important criteria. There is firm which had a launch at the Demo conference in 2012 its http://collatebox.com/ they seem to be just an awesome tool for budgeting.
Andy, your thoughts on it

dogknees
dogknees

Is this real "collaboration"? ie Multiple simultaneous editors in the one file? Or is it just the old swap it back and forth process? We did some investigation of File Sharing sites and systems and while the say you can collaboration, it's not true simultaneous access to the one file with changes being merged intelligently.

Mark W. Kaelin
Mark W. Kaelin

Do you still cut-and-paste information from multiple spreadsheets together when budgeting? Have you used a shared Google Sheet with others to streamline your workflow? How did it work for you?

andy
andy

Up to 50 people can simultaneously edit a Google spreadsheet. So, yes, unlike many systems, this does provide collaboration on budgeting. Thanks for reading! And do check out Google Apps. I think you might find it worthwhile for collaboration. --Andy

Editor's Picks