Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Compliance Dashboard in Excel with ChatGPT

Cody Schneider••8 min read

Building a compliance dashboard in Excel lets you instantly see who is up-to-date with required training and policies, and more importantly, who isn't. But starting with a blank spreadsheet can be intimidating. This guide shows you how to use ChatGPT as your co-pilot to generate the exact formulas, code, and steps needed to build a professional compliance dashboard from scratch.

GraphedGraphed

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.

Watch Graphed demo video

What is a Compliance Dashboard?

A compliance dashboard is a visual report that tracks and displays key metrics related to an organization's adherence to laws, regulations, and internal policies. Instead of sifting through massive spreadsheets, you get an at-a-glance view of performance, making it easy to spot potential risks and address issues before they become serious problems.

Common examples of compliance tracking include:

  • HR Compliance: Tracking mandatory employee training completion (e.g., security awareness, anti-harassment) or policy acknowledgments.
  • IT Compliance: Monitoring system patch levels, access reviews, or data security controls against standards like SOC 2 or ISO 27001.
  • Safety Compliance: Ensuring employees have completed required safety certifications or that equipment inspections are up to date.

While dedicated software exists, many organizations lean on Excel. It's affordable, flexible, and already installed on nearly every work computer. Its main drawback has always been the manual effort required to build and maintain reports - a problem we can now largely solve with AI.

Step 1: Plan Your Dashboard and Gather Your Data

Before you write a single formula, you need a clear plan. A great dashboard starts with well-structured data. Forgetting this step is like trying to build a house without a blueprint.

Define Your Key Metrics

What questions does your dashboard need to answer? Start with the most critical information you need to see. For an employee training compliance dashboard, you might want to track:

  • Overall Completion Rate: What percentage of all required training is complete?
  • Completion Rate by Department: Which departments are leading and which are lagging?
  • Individual Status: A detailed list of employees who are not compliant.
  • Training Course Performance: Which specific courses have the lowest completion rates?

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.

Structure Your Source Data

ChatGPT can't work its magic on messy data. Your source data should be in a simple, tabular format - think of a straightforward list. Each row should represent a single record (like one employee's assignment for one course), and each column should represent a piece of information about that record.

Create a worksheet in Excel and name it "ComplianceData". Set it up with the following headers:

  • EmployeeID
  • EmployeeName
  • Department
  • CourseName
  • Status (e.g., "Completed" or "Incomplete")
  • CompletionDate

Pro Tip: Use an official Excel Table for your data (select your data and press Ctrl + T). Tables automatically expand as you add new data, which keeps your formulas and charts up to date without manual adjustments.

Step 2: Use ChatGPT to Create Summary Metrics

Now, let's create a new worksheet named "Dashboard" where our final report will live. On this sheet, we'll build a small summary table. This is where ChatGPT's ability to act as an Excel expert really shines.

Calculating Overall Completion Rate

Let's start by calculating the total percentage of completed training. Open ChatGPT and use a specific, contextual prompt.

Example Prompt for ChatGPT:

I have an Excel sheet named "ComplianceData". In this sheet, I have a column named "Status" (Column E) which contains either "Completed" or "Incomplete". Can you give me an Excel formula to calculate the overall completion percentage?

ChatGPT's likely output:

=COUNTIF(ComplianceData!E:E, "Completed") / COUNTA(ComplianceData!E:E)

On your "Dashboard" sheet, create a label like "Overall Completion %" in one cell, then paste this formula into the cell next to it. Be sure to format the cell as a percentage.

ChatGPT Prompt:

Thanks, now on that same "ComplianceData" sheet, there's a "Department" column. How can I create a summary list that shows the training completion rate for each individual department?

You now have the core metrics for your dashboard! Without having to dig for a formula online or decipher a series of IF/THEN statements!

GraphedGraphed

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.

Watch Graphed demo video

Step 3: Tell ChatGPT to Generate Charts with VBA

Visuals turn raw numbers into actionable insights. While you can create charts manually, you can also ask ChatGPT to write a small script (a VBA macro) to do it for you instantly. This is perfect for standardized reports you need to create regularly.

Let's say your department summary table is in cells A4:B9 on your "Dashboard" sheet. Now, provide that context to ChatGPT.

Example Prompt for ChatGPT:

I have a summary table on my "Dashboard" sheet in Excel. The department names are in cells A5:A9 and their completion rates are in cells B5:B9. Write me Excel VBA code to create a clean bar chart from this data, with a title that says "Compliance by Department".

ChatGPT should provide a code block like this:

Sub CreateComplianceChart()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Dashboard")

    Dim cht As Chart
    Set cht = ws.Shapes.AddChart2(216, xlBarClustered).Chart

    '--- Set Data Source and Chart Properties ---
    With cht
        .SetSourceData Source:=ws.Range("A5:B9")
        .HasTitle = True
        .ChartTitle.Text = "Compliance by Department"
        .HasLegend = False

        '--- Position the Chart ---
        .Parent.Top = ws.Range("D2").Top
        .Parent.Left = ws.Range("D2").Left
    End With
End Sub

How to Use the VBA Code

  1. In Excel, press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
  2. Go to Insert > Module. A blank code window will appear.
  3. Copy the code from ChatGPT and paste it into the module.
  4. Close the VBA editor by clicking the 'X'.
  5. Back in Excel, press Alt + F8 to open the Macro dialog, select CreateComplianceChart, and click Run.

Your bar chart will appear instantly on your dashboard sheet, perfectly formatted and ready to go.

Step 4: Use AI to Create Dynamic Visuals and Lists

A great dashboard makes problems obvious. Conditional formatting and filterable lists are excellent ways to highlight areas that need attention.

Highlighting Non-Compliant Employees

A list of non-compliant employees is one of the most actionable parts of a compliance dashboard. Let's use dynamic array formulas, which are perfect for a task like this.

Example Prompt for ChatGPT:

Using my "ComplianceData" sheet, can you give me a single, modern Excel formula that will spill a list of all Employee Names (Column B) and their corresponding Course Names (Column D) where the Status (Column E) is "Incomplete"?

ChatGPT's likely output (using the FILTER function):

=FILTER(CHOOSECOLS(ComplianceData, 2, 4), ComplianceData[Status]="Incomplete", "All training is complete")

Paste this formula into a cell on your "Dashboard" sheet. It will dynamically "spill" an updated list of all non-compliant employees. If you update the status for an employee to "Completed" on the data sheet, they will automatically disappear from this list.

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.

Adding Conditional Formatting

Conditional formatting makes your summary table easier to read by adding color based on values.

Example Prompt for ChatGPT:

How do I apply conditional formatting to my department completion rates (cells B5:B9)? I want values below 75% to have a red fill, values between 75% and 90% to have a yellow fill, and values over 90% to have a green fill.

ChatGPT won't write code for this but will give you precise, step-by-step instructions:

  1. Select cells B5:B9.
  2. Go to the Home tab > Conditional Formatting > New Rule...
  3. Select "Format only cells that contain".
  4. For the red rule, set the conditions to "Cell Value", "less than", and enter 0.75. Click format and choose a red fill.
  5. Repeat the process for the yellow and green rules based on the instructions.

Your dashboard now instantly draws the eye to underperforming departments.

Tips for Better ChatGPT Prompts

The quality of your output depends entirely on the quality of your input. Here’s how to get the best results from ChatGPT when working with Excel:

  • Be Specific and Verbose: Always mention sheet names, column headers, and cell ranges. Instead of saying "my sales data," say "my sheet named 'SalesData' with columns for 'Date', 'Product', and 'Revenue'."
  • State Your Goal Clearly: Explain what you are trying to achieve, not just what you want to do. For example, "I'm trying to find our top 3 most popular products" is better than "I need a formula."
  • Iterate and Refine: Your first prompt isn't always your last. If the first formula doesn't work, tell ChatGPT what went wrong. For example, "That formula produced a #VALUE! error. My date column sometimes contains blank cells. Can you adjust the formula to ignore blanks?"

Final Thoughts

This process - planning data, querying ChatGPT for formulas and code, and assembling the components - transforms a daunting task into a manageable workflow. You can build a powerful, professional compliance dashboard in Excel without being a formula wizard or VBA expert. For the first time, your ability to ask good questions is more important than your technical Excel knowledge.

While using ChatGPT dramatically cuts down on the manual build time, keeping your Excel dashboard updated with fresh data can still be a chore. That's why we built Graphed. It eliminates the need to download CSVs or copy-paste data by connecting directly to your tools. You can create real-time, shareable dashboards simply by describing what you want to see, helping you move from manual reporting in Excel to fully automated, live intelligence.

Related Articles