Graphed LogoGraphed
Login

How to Create an Insurance Dashboard in Excel with ChatGPT

Cody Schneider•

Building a dashboard in Excel to track your insurance metrics can feel like a daunting task, but it doesn't have to be. By pairing the power of Excel with the conversational intelligence of ChatGPT, you can create a clear, insightful dashboard to monitor your business performance without needing to be a formula wizard. This article will walk you through the process step-by-step, showing you how to use ChatGPT as your personal Excel assistant.

First Things First: Why an Insurance Dashboard in Excel?

An insurance dashboard gives you a visual snapshot of your most important Key Performance Indicators (KPIs) in one place. Instead of digging through endless spreadsheets, you get an at-a-glance view of your agency's or brokerage's health. Tracking metrics like policies sold, premiums generated, and claims processed helps you spot trends, identify top-performing agents, and make smarter, data-driven decisions.

Why Excel? It's a tool you likely already have and are familiar with. It's powerful enough to handle a significant amount of data and flexible enough to build a completely custom dashboard that fits your specific needs. The best part is that with ChatGPT, the barrier to entry is lower than ever. You don't need to memorize complex formulas, you just need to know how to ask for them.

Step 1: Get Your Insurance Data Organized

Before you can build a dashboard, you need clean, well-structured data. Your dashboard is only as good as the information it’s built on. For an insurance dashboard, you’ll typically be working with data related to policies, claims, and customers. The goal is to get all of this information into a single, flat table in an Excel sheet.

Your data might come from a CRM, a policy management system, or even multiple spreadsheets. The key is to consolidate it into a structured format. Create a new Excel sheet named "Data" and set it up with clear column headers like these:

  • Policy_ID: A unique identifier for each policy.

  • Customer_Name: The name of the policyholder.

  • Effective_Date: The date the policy starts.

  • Expiry_Date: The date the policy ends.

  • Policy_Type: (e.g., Auto, Home, Life, Health)

  • Premium_Amount: The total premium for the policy.

  • Agent_Name: The agent who sold the policy.

  • Region: The geographical area or state.

  • Claim_ID: Unique ID for any associated claim (leave blank if no claim).

  • Claim_Date: The date a claim was filed.

  • Claim_Amount: The total amount paid out for the claim.

  • Claim_Status: (e.g., Open, Closed, Denied)

A structured table is the foundation of your dashboard. Once your data is neatly organized in columns and rows, both Excel and ChatGPT will have a much easier time understanding and analyzing it.

Step 2: Use ChatGPT to Clean and Prepare Your Data

Raw data is rarely perfect. You’ll often have inconsistencies like extra spaces, different date formats, or typos. This is where ChatGPT becomes incredibly useful. You can describe the problem you're seeing in your data, and it will give you the exact Excel formulas to fix it.

In this example, let's assume your data is in a structured Excel Table named "InsuranceData". Naming your data range as a table (Ctrl + T in Excel) makes formulas much easier to read and manage.

Example: Standardizing Policy Types

Let's say in your "Policy_Type" column, you have entries like "Auto", "automobile", and "Auto Insurance". You want to standardize them all to just "Auto". You can ask ChatGPT:

"I have an Excel table named 'InsuranceData'. In the [Policy_Type] column, I have different variations like 'auto' and 'automobile'. Give me an Excel formula I can use in a new column to standardize them all to 'Auto'. I also have 'Home' and 'Life' policies to standardize."

ChatGPT might give you a nested IF or an IFS formula like this:

You can create a new column, paste this formula in, and drag it down. Once you're done, you can copy the new, clean column and "Paste as Values" over the original one.

Step 3: Calculate Your Core Insurance KPIs with ChatGPT's Help

Now for the fun part: calculating the metrics that will populate your dashboard. Create a new sheet in Excel and name it "Dashboard". This is where you'll display your KPIs. You can list the metric names in one column and use the next column for the formulas ChatGPT provides.

Here are some common insurance KPIs and the prompts you can use to get the formulas.

Total Premium Value

Prompt for ChatGPT:

"Give me an Excel formula to calculate the sum of the [Premium_Amount] column in my 'InsuranceData' table."

Formula from ChatGPT:

Total Policies Sold

Prompt for ChatGPT:

"I need an Excel formula to count the total number of policies. Each row in my 'InsuranceData' table is a unique policy."

Formula from ChatGPT:

Average Premium per Policy

Prompt for ChatGPT:

"Give me an Excel formula to calculate the average of the [Premium_Amount] column in my 'InsuranceData' table."

Formula from ChatGPT:

Total Claim Amount Paid

Prompt for ChatGPT:

"Give me a formula to calculate the sum of the [Claim_Amount] column in my 'InsuranceData' table for claims with a [Claim_Status] of 'Closed'."

Formula from ChatGPT:

Loss Ratio

The loss ratio (total claims paid out divided by total premiums collected) is a critical metric in insurance. It shows how much you're paying in claims for every dollar you bring in from premiums.

Prompt for ChatGPT:

"I want to calculate the loss ratio in Excel. I need to divide the total of the 'Claim_Amount' column by the total of the 'Premium_Amount' column in my 'InsuranceData' table. Give me the combined formula."

Formula from ChatGPT:

Tip: Be sure to format this cell as a percentage!

Step 4: Create Visualizations with PivotTables and Charts

KPIs give you numbers, but charts give you context. A great dashboard combines both. PivotTables are Excel’s most powerful tool for summarizing data, and ChatGPT can walk you through creating them and the charts based on them.

Creating a "Premium by Policy Type" Bar Chart

You want to see which policy types are generating the most premium income. You can ask ChatGPT for step-by-step instructions.

"Walk me through the steps to create a PivotTable and a bar chart in Excel. I want to show the total Premium_Amount for each Policy_Type from my 'InsuranceData' table."

ChatGPT will guide you:

  1. Select any cell inside your "InsuranceData" table.

  2. Go to the Insert tab and click PivotTable.

  3. In the PivotTable Fields pane, drag Policy_Type to the Rows area.

  4. Drag Premium_Amount to the Values area. Make sure it's set to "Sum of Premium_Amount".

  5. With the PivotTable selected, go to the PivotTable Analyze tab and click PivotChart.

  6. Choose a "Clustered Bar" chart and click OK.

Now you have a dynamic chart visually representing your premium income streams. You can cut and paste this chart (and its PivotTable helper, which can be placed on a separate "Calculations" sheet to keep the dashboard clean) onto your "Dashboard" sheet.

Creating "Policies Sold by Agent" Pie Chart

Similarly, you can visualize agent performance.

"How do I create a PivotTable in Excel that counts the number of policies sold by each agent? My table is 'InsuranceData' and I should count policies based on the 'Agent_Name' column."

ChatGPT's instructions would lead you to create a PivotTable with Agent_Name in Rows and a Count of Policy_ID in Values. From there, you can insert a Pie Chart to show each agent's contribution to the total policies sold.

Step 5: Assemble and Design Your Dashboard

This is where you bring it all together. Your "Dashboard" sheet is your canvas.

  1. Arrange your KPIs: Place the key metrics you calculated in Step 3 at the top. Use large, bold font for the numbers to make them stand out.

  2. Position your charts: Arrange the charts you created in Step 4 underneath the KPIs. A 2x2 grid is a common and clean layout.

  3. Add Slicers for Interactivity: Slicers are user-friendly filters that make your dashboard interactive. Select one of your PivotCharts, go to the PivotChart Analyze tab, and click Insert Slicer. You could add slicers for "Region" or "Policy_Type". The best part is you can right-click a slicer and in "Report Connections," connect it to all your PivotTables, so a single click filters your entire dashboard.

  4. Clean up the look: Hide the gridlines (View > Gridlines), give your dashboard a title, and use consistent colors to make it look professional.

Final Thoughts

Pairing Excel with ChatGPT streamlines the process of building an insightful insurance dashboard, transforming a potentially complex data project into a series of simple questions and answers. You can move from raw data to a fully interactive report that helps you understand your business's performance without getting bogged down in formula syntax and PivotTable settings.

While this method is powerful, it still involves manual steps like exporting CSVs and assembling the report by hand. For truly automated, real-time analytics, we built Graphed. Instead of wrestling with spreadsheet data, you can connect your data sources directly — like your CRM or other business software — and tell our AI what you want to see. Just ask, "Create a dashboard showing our total premium and loss ratio by policy type for this quarter," and it builds a live, interactive dashboard for you instantly, no formulas required.