How to Create a Compliance Dashboard in Google Sheets

Tracking compliance can feel like trying to solve a puzzle with pieces scattered across different departments. A simple, well-organized dashboard brings all those pieces together, giving you a clear picture of your security and regulatory posture. This tutorial will guide you, step-by-step, through building a powerful compliance dashboard using nothing more than Google Sheets.

Why Use Google Sheets for a Compliance Dashboard?

Before building, it's good to know why Google Sheets is such a practical choice for this task. While specialized Governance, Risk, and Compliance (GRC) tools exist, they are often expensive and complex. Google Sheets offers a flexible and surprisingly powerful alternative.

• It's Free and Accessible: Every member of your team can access and collaborate on the dashboard in real-time from any device without needing a license.

• Highly Customizable: You're not locked into a vendor's pre-defined structure. You can tailor your dashboard perfectly to your specific framework, whether it's SOC 2, HIPAA, GDPR, or your own internal company policies.

• Simple to Understand: Most people have some familiarity with spreadsheets, which dramatically lowers the learning curve for both building and using the dashboard.

• Integration Ready: With add-ons and tools like Zapier, you can connect your sheet to other systems to automate data collection and reduce manual updates.

Planning Out Your Dashboard

A little planning goes a long way. Before you start creating formulas and charts, take a few minutes to define what you want to achieve. A clear plan will make the building process much smoother.

1. Define Your Compliance Framework and Controls

First, identify what you're tracking. This is the foundation of your dashboard. Are you preparing for a SOC 2 audit? Are you monitoring GDPR requirements? Or are you simply tracking adherence to internal IT security policies?

List out every "control" or requirement you need to monitor. A control is just a specific action or process you've put in place to meet a requirement. For example:

• Framework: Internal IT Security Policy

• Requirement: All employee computers must be encrypted.

• Control: "HR confirms endpoint encryption during employee onboarding" or "IT conducts quarterly encryption audits on all devices."

Create a simple list of every control you will be tracking. You can add more later, but starting with a solid list is crucial.

2. Identify Your Key Compliance Metrics

Next, decide what high-level numbers you want to see at a glance. These metrics will form the summary section of your dashboard. Good compliance metrics give you an instant health check.

Examples include:

• Overall Compliance Rate (%): What percentage of our controls are fully implemented and compliant?

• Status Breakdown: How many controls are Compliant, In Progress, or At Risk?

• Controls by Owner: Who is responsible for which controls, and how are their areas performing?

• Overdue Reviews: Which policies or controls are past their scheduled review date?

• Training Completion: What percentage of employees have completed their mandatory security awareness training?

3. Sketch the Dashboard Layout

Finally, grab a pen and paper (or open a simple diagram tool) and sketch a rough layout. Where will your summary metrics go? Where will the charts be? How will you display the detailed list of controls?

A typical layout includes:

• Top Section: Key performance indicators (KPIs) like Overall Compliance Rate and total number of controls.

• Middle Section: Visualizations like pie charts for status breakdown and bar charts for compliance by department.

• Bottom Section: A detailed table of all controls that can be referenced as needed.

This sketch isn't about design, it's about structure. It becomes your blueprint for the build.

Step-by-Step: Building Your Compliance Dashboard

With our plan in place, it's time to open a new Google Sheet and start building. We’ll organize our sheet into a few key tabs to keep things clean.

Step 1: Set Up Your Google Sheet Tabs

Organization is everything in spreadsheets. Create a few separate tabs to handle different functions. This prevents the dashboard from getting messy and makes it easier to manage.

• ‘Dashboard’ Tab: This will be the main view where all your visualizations and summaries live. It's the "front page" of your report.

• ‘Data Input’ Tab: This is the backend where you'll log the status of every single control. The dashboard will pull all its information from this tab.

• ‘Lists' Tab (Optional but Recommended): This tab will store predefined lists for dropdown menus (like statuses or department names). This ensures your data entry is consistent and clean.

On your 'Lists' tab, create small lists for things like 'Status' (e.g., Compliant, In Progress, At Risk, Not Applicable) and 'Owner/Department' (e.g., IT, HR, Engineering).

Step 2: Build and Populate the 'Data Input' Tab

Navigate to your ‘Data Input’ tab. This is where the raw data lives. Create columns for all the information you need to track about each control.

Essential columns include:

• Control ID: A unique identifier (e.g., "SEC-001").

• Control Description: A clear, concise explanation of the control.

• Framework: The standard it belongs to (e.g., "SOC 2 CC6.1").

• Status: The current state of the control.

• Owner: The person or department responsible.

• Last Reviewed Date: The last time this control was verified.

• Next Review Date: When the control is due for its next review.

• Link to Evidence: A hyperlink to a document, screenshot, or ticket in your project management system.

Use Data Validation for Clean Data

To avoid typos and keep your data uniform, use Data Validation to create dropdown menus for the 'Status' and 'Owner' columns.

1. Click on the top of the 'Status' column to select it entirely.

2. Go to Data > Data validation.

3. In the 'Criteria' field, select Dropdown (from a range).

4. Click the grid icon and select your list of statuses from your 'Lists' tab (e.g., Lists!A2:A5).

5. Click Done.

Now, when you click a cell in the 'Status' column, you’ll see a dropdown menu. Repeat this process for the 'Owner' column. This simple step saves you countless headaches down the line.

Step 3: Create Summary Metrics with Formulas

Now, let's head over to the ‘Dashboard’ tab and pull in some high-level numbers. We’ll use simple formulas to calculate our KPIs that refer back to the 'Data Input' tab.

For example, set up a small summary area on your dashboard:

Total Controls:

=COUNTA('Data Input'!A2:A)

Compliant Controls:

=COUNTIF('Data Input'!D2:D, "Compliant")

Compliance Rate:

Assuming "Total" is in B2 and "Compliant" is in B3: =B3/B2 Format as percentage via Format > Number > Percent.

Overdue Controls:

Counts controls that aren't compliant AND next review date is in the past: =COUNTIFS('Data Input'!G2:G, "<" & TODAY(), 'Data Input'!D2:D, "<>Compliant")

Step 4: Visualize Your Data with Charts

Numbers are good, but charts tell a story. Let's create a few visuals to make your compliance posture immediately understandable.

Create a Pie Chart for Overall Status

1. In an empty part of your Dashboard tab, list a unique value for each status ('Compliant', 'In Progress', 'At Risk') in one column.

2. In the adjacent column, use COUNTIF to count each status, like: =COUNTIF('Data Input'!D2:D, "A1") (assuming A1 contains "Compliant"). Drag down.

3. Select this small summary table.

4. Go to Insert > Chart. Google Sheets will likely suggest a Pie Chart. If not, select it from the Chart editor.

5. Customize titles and colors as desired.

Create a Bar Chart for Compliance by Owner

1. Use a Pivot Table:

   • Select your data range.

   • Go to Insert > Pivot table.

   • Choose 'New sheet'.

2. In the Pivot table editor:

   • Rows: add 'Owner'.

   • Columns: add 'Status'.

   • Values: add 'Status' (summarized by 'COUNTA').

3. Select the pivot table and go to Insert > Chart.

4. Select a Stacked Bar Chart to visualize counts per owner.

Step 5: Apply Conditional Formatting for Quick Insights

Conditional formatting turns your wall of text into a visually scannable report.

Highlight Overdue Review Dates

1. Select entire 'Next Review Date' column (e.g., Column G).

2. Go to Format > Conditional formatting.

3. Under 'Format cells if...', choose Date is before.

4. Set value to today.

5. Choose a red background fill.

6. Click Done.

Now, any date in the past will turn red, highlighting what needs attention.

You can do the same for the 'Status' column to create a Red/Amber/Green (RAG) status indicator: e.g., green for 'Compliant', yellow for 'In Progress', red for 'At Risk'.

Final Thoughts

Building a compliance dashboard in Google Sheets puts you in control, transforming scattered checklists into a dynamic and clear overview of your progress. By organizing your data, using simple formulas, and adding a few key charts, you can confidently report on your compliance posture and focus your efforts where they matter most.

As your company grows, manually updating spreadsheets from different platforms can become a drag. At Graphed, we made it our mission to automate that entire reporting process. You can connect sources like your IT service desk, cloud platforms, and security tools, and then create live, auto-updating dashboards simply by describing what you want to see. Instead of spending hours wrangling data, you can ask questions in plain English and have your dashboards built for you through Graphed in seconds.