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.

Editor's Picks