How to Create a Compliance Dashboard in Google Sheets with ChatGPT

Building a compliance dashboard sounds like a job for expensive software and a team of data experts, but it doesn't have to be. You can create a powerful, functional compliance dashboard for free using two tools you already know: Google Sheets and ChatGPT. This article walks you through how to structure your compliance data and use ChatGPT to generate the exact formulas and visuals you need, step-by-step.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use Google Sheets for a Compliance Dashboard?

Before diving into the how, let's touch on the why. Why choose a spreadsheet for something as important as compliance tracking? The answer lies in its simplicity and universal accessibility.

• No Cost: Google Sheets is free. You don't need to budget for specialized compliance software, which can be prohibitively expensive, especially for smaller teams.
• Accessibility and Collaboration: Your entire team already knows how to use spreadsheets. A Google Sheet is easily shareable, allowing stakeholders to view real-time data from anywhere without needing special access or training.
• Customization: Unlike off-the-shelf software, you have complete control. You can track the exact metrics that matter to your organization, structured precisely the way you want.
• Centralization: It brings all your disparate compliance activities - from training logs to incident reports - into one central, manageable location.

Step 1: Define Your Key Compliance Metrics

You can't build a dashboard without knowing what you want to measure. A great compliance dashboard gives you a high-level view of your program's health at a glance. Start by identifying the key performance indicators (KPIs) that are most critical to your organization. If you're unsure where to begin, here are some common examples:

• Employee Training Status: Tracks completion rates for mandatory training like security awareness, anti-harassment, or code of conduct. You’ll want to see completion percentage, overdue training, and department-level progress.
• Policy Acknowledgement: Monitors who has signed off on important company policies (e.g., IT Security Policy, Employee Handbook).
• Incident Management: Keeps tabs on reported security or compliance incidents, including the number of open incidents, their severity, and the average time to resolution.
• Audit & Risk Remediation: Tracks findings from internal or external audits and the progress of remediation efforts. You'll want to see how many findings are open, in progress, or closed.
• Vendor Risk Assessments: Monitors the compliance status of your third-party vendors, tracking when assessments are due or completed.

For this tutorial, we will focus on building a dashboard to track Employee Training Status, Incident Management, and Audit Remediation.

Step 2: Structure Your Data in Google Sheets

A clean data structure is the secret to a great dashboard. The biggest mistake people make is mixing their raw data with their dashboard visuals. The best practice is to separate them. Create one Google Sheet and use different tabs (sheets) for each dataset and for the dashboard itself.

Create a new Google Sheet and add the following four tabs:

• Dashboard
• TrainingData
• IncidentsData
• AuditsData

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Setting Up Your Data Tabs

Now, let's define the columns for each of your data tabs. This structure is what allows you to perform calculations and create charts later.

On your TrainingData tab, create these columns:

• Employee Name
• Department
• Training Module
• Assigned Date
• Due Date
• Completion Date
• Status (e.g., Completed, In Progress, Overdue)

On your IncidentsData tab, create these columns:

• Incident ID
• Date Reported
• Incident Type (e.g., Data Breach, Policy Violation)
• Severity (e.g., High, Medium, Low)
• Status (e.g., Open, Investigating, Closed)
• Resolution Date

On your AuditsData tab, create these columns:

• Audit Finding ID
• Finding Description
• Risk Level (e.g., Critical, High, Medium)
• Remediation Team
• Due Date
• Status (e.g., Open, In Progress, Closed)

Once you have this structure, go ahead and populate each tab with some sample data. Having at least 10-15 rows of realistic (but fake) data will make it much easier to build and test your dashboard in the next steps.

Step 3: Use ChatGPT as Your Formula Expert

This is where the magic happens. You don’t need to be a spreadsheet guru or spend hours on Google searching for the right formulas. You can simply describe what you want to achieve in plain English, and ChatGPT will provide the formula for you. Your job is simply to copy, paste, and adjust the cell references if needed.

Let's build out the "Dashboard" tab by asking ChatGPT for some formulas.

Metric 1: Overall Training Completion Rate

Your goal is to show the percentage of all assigned training that has been completed. Go to ChatGPT and use a prompt like this:

I have a Google Sheet with a tab named 'TrainingData'. Column G in this tab shows the training status, which can be "Completed", "In Progress", or "Overdue". On my 'Dashboard' tab, I want to calculate the percentage of all training that is marked as "Completed". What is the formula to do this?

ChatGPT will likely give you a formula using COUNTIF and COUNTA.

=COUNTIF(TrainingData!G:G, "Completed") / COUNTA(TrainingData!G:G)

Go to your 'Dashboard' tab, pick a cell (like B2), paste this formula in, and hit Enter. To make it look nice, select the cell and click Format > Number > Percent. You now have your first dashboard metric!

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Metric 2: A Count of Open, High-Severity Incidents

This is a crucial metric for any compliance team. You need to know, at a glance, how many critical fires you're currently fighting.

This requires checking multiple conditions, so let's ask ChatGPT again:

I have a Google Sheet tab called 'IncidentsData'. Column D lists the Severity ("High", "Medium", "Low") and Column E lists the Status ("Open", "Closed"). On my dashboard, can you give me a formula to count the number of incidents that are classified as both "High" severity AND have a status of "Open"?

ChatGPT will generate a COUNTIFS formula, which is perfect for checking multiple criteria.

=COUNTIFS(IncidentsData!D:D, "High", IncidentsData!E:E, "Open")

Paste this into a cell on your dashboard. You can create a small table for "Open Incidents" and use similar formulas to also count Medium and Low severity incidents.

Metric 3: A Pie Chart of Audit Finding Statuses

Visuals are essential for a good dashboard. Let's create a pie chart that shows the breakdown of audit findings by their status. This first requires creating a small summary table to power the chart.

Back to ChatGPT for the prompt:

My 'AuditsData' tab has a column F for 'Status' (values are "Open", "In Progress", "Closed"). On my 'Dashboard' tab, I need to create a small two-column summary table that lists each unique status and the count of how many times it appears. How can I do this with a single formula?

ChatGPT will likely suggest the powerful QUERY formula.

=QUERY(AuditsData!F:F, "SELECT F, COUNT(F) WHERE F IS NOT NULL GROUP BY F LABEL F 'Status', COUNT(F) 'Count'")

This formula is fantastic because it automatically finds the unique statuses and counts them for you. Paste it into your dashboard, and you'll see a neat little summary table instantly appear.

Now, to create the chart:

1. Highlight the summary table data that was just generated by the QUERY formula.
2. Go to the Google Sheets menu and click Insert > Chart.
3. In the Chart editor that appears on the right, select "Pie chart" from the Chart type dropdown.

You now have a dynamic pie chart that will automatically update every time you add or change data in your AuditsData tab.

Step 4: Design Your Dashboard for Readability

With formulas in place, the final step is to make your dashboard easy to read in 5 seconds or less. Your goal is clarity, not clutter.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Use "Scorecards" for Key Numbers

For your most important metrics - like "Overall Training Completion" or "Open High-Severity Incidents" - don't just leave them as small numbers. Place them in their own dedicated cells, give them clear labels, and make the font large and bold. This makes them jump off the page.

Add Color with Conditional Formatting

Conditional formatting helps draw attention to problem areas. Let's say you want to quickly see which training assignments are overdue in your TrainingData tab. ChatGPT can help with that, too.

Prompt for ChatGPT:

In my 'TrainingData' tab in Google Sheets, how do I apply conditional formatting to highlight an entire row in light red if the value in Column G for that row is "Overdue"?

ChatGPT will guide you through the menu:

1. Select all your data in the TrainingData tab.
2. Go to Format > Conditional formatting.
3. Under "Format rules," choose "Custom formula is" from the dropdown.
4. In the value box, enter this formula: =$G2="Overdue"
5. Set the formatting style (e.g., a light red background fill).

Now, any overdue training will instantly be highlighted, making it easy to spot.

Organize and Label Everything

Group related charts and metrics together under clear headings (e.g., "Training Program Health," "Live Incident Report"). Use cell merging and background colors to create visual separation between sections. The better it's organized, the more effective it will be.

Final Thoughts

By structuring your data thoughtfully in separate tabs and leveraging ChatGPT as your on-demand formula expert, you can build a robust and clear compliance dashboard in Google Sheets. This approach democratizes data analysis, allowing anyone to translate raw compliance data into actionable insights without needing to become a spreadsheet wizard.

While this method is powerful, the work of exporting data, pasting it into sheets, and refreshing reports can still consume a lot of time each week. At Graphed , we automate this entire process. We connect directly to your various data sources - like your HR systems, CRMs, and ad platforms - and allow you to build live, interactive dashboards by simply describing what you want in plain English. This eliminates manual data wrangling so you can get instantaneous, real-time answers and spend your time acting on insights, not just chasing them down.