Why collaboration is Sheets' superpower in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a team lead managing a project with multiple members working remotely.

📋 Request: Your manager wants a live, shared project tracker that updates automatically as team members add their progress.

📊 Data: You have a list of tasks, assigned team members, deadlines, and status updates that each member will update in real-time.

🎯 Deliverable: Create a shared Google Sheet that allows all team members to update their task status simultaneously and shows a summary of completed tasks.

Sample Data

Task ID	Task Description	Assigned To	Deadline	Status
1	Design homepage	Alice	2024-07-10	In Progress
2	Write content	Bob	2024-07-12	Not Started
3	Develop backend	Charlie	2024-07-15	In Progress
4	Test features	Dana	2024-07-18	Not Started
5	Deploy to server	Eli	2024-07-20	Not Started
6	Create user guide	Fiona	2024-07-22	Not Started
7	Review design	Alice	2024-07-11	Completed
8	Update database	Charlie	2024-07-16	In Progress

1

Step 1: Share the Google Sheet with all team members and give them edit access.

Use the Share button in Google Sheets and add team members' email addresses with Editor permission.

Expected Result

All team members can open and edit the sheet simultaneously.

2

Step 2: Create a summary table to count how many tasks each member has completed.

=COUNTIFS(C2:C9, "Alice", E2:E9, "Completed")

Expected Result

Returns 1 for Alice, counting her completed tasks.

3

Step 3: Extend the summary to count completed tasks for all team members using a formula.

=UNIQUE(C2:C9) in one column and next to it use =COUNTIFS(C2:C9, G2, E2:E9, "Completed") where G2 is the member name.

Expected Result

Shows each team member's name with the count of their completed tasks.

4

Step 4: Add conditional formatting to the Status column to highlight 'Completed' tasks in green.

Select E2:E9, Format > Conditional formatting, Format cells if Text is exactly 'Completed', set fill color green.

Expected Result

Completed tasks cells turn green automatically.

5

Step 5: Explain how real-time collaboration works: when any team member updates a task status, everyone sees the change immediately.

No formula needed; this is a Google Sheets feature.

Expected Result

Team members can track project progress live without sending emails.

Final Result

Project Tracker Summary

Team Member | Completed Tasks
------------|----------------
Alice       | 1
Bob         | 0
Charlie     | 0
Dana        | 0
Eli         | 0
Fiona       | 0

Status column cells with 'Completed' are highlighted in green.

All team members can update their tasks live, and the summary updates automatically.

✓Alice has completed 1 task so far.

✓Most tasks are still in progress or not started.

✓Real-time updates help the team stay aligned without extra meetings.

Bonus Challenge

Create a chart that shows the number of tasks per status (Not Started, In Progress, Completed) updating live as statuses change.

Show Hint

Use a pivot table or COUNTIF formulas to count tasks by status, then insert a pie chart linked to that data.