Why Google Sheets is essential for collaboration - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to demonstrate how Google Sheets helps your team collaborate effectively on project data.

📊 Data: You have a project task list with columns for Task, Assigned To, Status, and Due Date.

🎯 Deliverable: Create a shared Google Sheet that shows live updates from team members, highlights overdue tasks, and summarizes task status.

Sample Data

Task	Assigned To	Status	Due Date
Design Logo	Alice	In Progress	2024-06-10
Write Content	Bob	Not Started	2024-06-15
Develop Website	Charlie	In Progress	2024-06-20
Test Website	Dana	Not Started	2024-06-25
Launch Campaign	Eli	Not Started	2024-06-30

1

Step 1: Share the Google Sheet with your team members so everyone can edit the task list in real time.

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

Expected Result

Team members can open the sheet simultaneously and see live updates.

2

Step 2: Highlight overdue tasks automatically by checking if the Due Date is before today and the Status is not 'Completed'.

Apply conditional formatting with custom formula: =AND($D2<TODAY(), $C2<>"Completed")

Expected Result

Rows with overdue tasks are highlighted in red.

3

Step 3: Create a summary table that counts how many tasks are in each status (Not Started, In Progress, Completed).

Use COUNTIF formulas: =COUNTIF(C2:C6, "Not Started"), =COUNTIF(C2:C6, "In Progress"), =COUNTIF(C2:C6, "Completed")

Expected Result

Summary shows counts: Not Started=3, In Progress=2, Completed=0.

4

Step 4: Add a filter view so team members can filter tasks by Assigned To or Status without changing others' views.

Click Data > Filter Views > Create new filter view, then set filters on Assigned To and Status columns.

Expected Result

Each team member can filter tasks independently without affecting others.

5

Step 5: Use comments to discuss specific tasks directly in the sheet.

Right-click a cell and select 'Comment' to add notes or questions for team members.

Expected Result

Team members see comments and can reply, improving communication.

Final Result

Task List and Collaboration Summary

+----------------+-------------+-------------+------------+
| Task           | Assigned To | Status      | Due Date   |
+----------------+-------------+-------------+------------+
| Design Logo    | Alice       | In Progress | 2024-06-10 |
| Write Content  | Bob         | Not Started | 2024-06-15 |
| Develop Website| Charlie     | In Progress | 2024-06-20 |
| Test Website   | Dana        | Not Started | 2024-06-25 |
| Launch Campaign| Eli         | Not Started | 2024-06-30 |
+----------------+-------------+-------------+------------+

Summary of Task Status:
Not Started: 3
In Progress: 2
Completed: 0

Overdue tasks are highlighted in red.

Team members can filter and comment live.

✓Google Sheets allows multiple team members to work on the same data at the same time.

✓Conditional formatting helps quickly identify overdue tasks.

✓Summary counts give a clear view of project progress.

✓Filter views let each user customize their view without affecting others.

✓Comments enable easy communication directly on tasks.

Bonus Challenge

Create a progress bar for each task based on its status using a formula and conditional formatting.

Show Hint

Use a formula that assigns a percentage to each status (e.g., Not Started=0%, In Progress=50%, Completed=100%) and apply a color scale.