Pharmacovigilance Case Tracking System in Google Sheets
Objective
Build a complete pharmacovigilance dashboard to track adverse drug reaction (ADR) cases, monitor status, and generate summary reports using Google Sheets tools like data validation, pivot tables, slicers, charts, and collaboration permissions.
Project Scenario
You are part of a Drug Safety Monitoring team at a pharmaceutical company. Your job is to record, validate, and track reported adverse events (AEs) from patients and healthcare professionals. These cases go through multiple stages — from initial reporting to regulatory submission.
This project simulates that workflow using a Google Sheet dashboard.
Google Sheet Structure (3 Sheets)
1. Sheet: AE Case Log (Input Sheet)
| Case ID | Patient Name | Drug Name | AE Reported | Severity | Reporter Type | Assigned Officer | Status | Date Reported | Date Closed | Comments |
|---|---|---|---|---|---|---|---|---|---|---|
| AE001 | Ritu Singh | Drug A | Headache | Mild | HCP | Radhika | Open | 2025-07-01 | - | |
| AE002 | Raj Malhotra | Drug B | Nausea | Moderate | Patient | Meera | In Review | 2025-07-02 | - | |
| AE003 | Komal Jain | Drug C | Rash | Severe | HCP | Ajay | Closed | 2025-07-03 | 2025-07-04 | Report Submitted |
🔧 Use formulas to calculate Turnaround Time (Date Closed - Date Reported)
2. Sheet: Summary Dashboard (Interactive Charts + KPIs)
-
KPIs:
-
Total Cases
-
Open / In Review / Closed count
-
Average turnaround time
-
-
Pivot tables:
-
Case count by Drug Name
-
Severity distribution
-
Officer workload (cases assigned per officer)
-
-
Charts:
-
Pie chart for Case Status
-
Bar chart for Severity per Drug
-
Line chart for Case Reporting Over Time
-
-
Slicers:
-
Filter by Assigned Officer, Severity, or Drug Name
-
3. Sheet: Officer Case Load (Pivot Table View)
Pivot table showing:
-
Number of cases assigned per officer
-
Average severity of cases
-
Total closed cases
Tasks to Perform
1. Data Validation
-
Severity: Drop-down – Mild, Moderate, Severe
-
Reporter Type: Drop-down – HCP, Patient, Caregiver
-
Status: Drop-down – Open, In Review, Closed
-
Assigned Officer: From a fixed list – Ajay, Meera, Radhika
2. Conditional Formatting
-
Highlight severe cases in red
-
Mark overdue cases (status ≠ Closed AND Date Reported > 7 days ago)
-
Highlight missing Assigned Officer fields in yellow
-
Grey out rows where Status is “Closed”
3. Pivot Table Creation
-
Count of cases per Drug Name
-
Count of cases by Severity
-
Average turnaround time per Officer
4. Chart Integration
Use pivot tables to create:
-
Pie Chart for Case Status
-
Bar Chart for Severity vs Drug
-
Line Chart for case reporting trends (by Date Reported)
5. Slicer Setup
Add slicers to filter:
-
Assigned Officer
-
Severity
-
Status
6. Sheet Protection & Permissions
-
Lock calculated fields (turnaround time)
-
Lock data validation columns
-
Share:
-
Editor access to Safety Officers
-
Viewer access to QA team
-
Commenter access to external auditors
-
KPI Examples (Dashboard Output)
| Metric | Value |
|---|---|
| Total Cases | 45 |
| Open Cases | 12 |
| Average Turnaround Time | 3.4 days |
| Most Reported Drug | Drug B |
| Highest Severity Case | Rash (Severe) – AE003 |
Bonus Tasks (Advanced)
-
Add a separate “Regulatory Log” tab for submission dates and authorities (USFDA, EMA, CDSCO)
-
Link Google Form for HCPs to submit AE reports directly
-
Use
COUNTIFSto track weekly case intake -
Create an alert formula:
=IF(TODAY() - [Date Reported] > 7, "Overdue", "")
Deliverables
-
Structured Google Sheet file with formulas and validations
-
A live, interactive dashboard
-
Comments and protections in place
-
Summary sheet for Officer-level insights
Learning Outcomes
Students will:
-
Simulate a drug safety officer’s role
-
Handle real-world AE data in Google Sheets
-
Create interactive, collaborative dashboards
-
Gain exposure to data governance and validation practices in pharma
