How to Create an Insurance Dashboard in Google Sheets with ChatGPT

Building an insurance dashboard can feel complicated, but what if you could piece one together using tools you already know? By pairing the flexibility of Google Sheets with the power of ChatGPT, you can create a clear, functional dashboard to track your key metrics without needing a data science degree. This article will guide you through the entire process, from structuring your data to visualizing your most important KPIs.

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 an Insurance Dashboard?

Before jumping into complex business intelligence software, Google Sheets offers a practical and powerful starting point. It’s entirely free, cloud-based, and built for collaboration, making it easy to share insights with your team or stakeholders from anywhere.

Here are a few reasons why it’s a smart choice:

• Accessibility: Nearly everyone knows their way around a spreadsheet. There’s no steep learning curve, so you and your team can get started immediately.
• Cost-Effective: There are no subscription fees or licenses required. For small agencies, independent brokers, or teams on a tight budget, it’s the perfect solution.
• Flexibility: You can customize your dashboard to track the exact metrics that matter to your business, whether you’re focused on sales, claims, or customer retention.
• Integration: Google Sheets works seamlessly with other tools like Google Forms, which you can use as a simple way to log new policies or claims data directly into your sheet.

Starting with Google Sheets helps you define which metrics you truly need to monitor, making any future transition to a dedicated BI tool much smoother.

First, Define Your Insurance KPIs

A dashboard is only as useful as the metrics it tracks. Before you write a single formula, take a moment to identify the Key Performance Indicators (KPIs) that give you the clearest picture of your business health. The right KPIs depend on your role - an agent’s focus might be on policy sales, while an operations manager might care more about claim processing times.

To get you started, here are some common and valuable KPIs for an insurance business:

Sales and Growth Metrics:

• Policies Sold: The total number of new policies issued. You can break this down further by policy type (Auto, Home, Life), agent, or region.
• Gross Written Premium (GWP): The total revenue from premiums on all policies sold within a specific period, before deducting commissions or expenses.
• New Business vs. Renewal Premium: Differentiating revenue from new customers versus existing ones helps you understand growth and retention.
• Policy Renewal Rate: The percentage of policies that are renewed by clients at the end of their term. This is a critical indicator of customer satisfaction and loyalty.

Claims and Operations Metrics:

• Number of Claims Filed: The total volume of claims submitted. This helps with resource planning for your claims department.
• Average Claim Payout: The average cost per claim. Monitoring this can help you spot trends in claim severity.
• Claim Processing Time: The average time from when a claim is filed to when it's closed. A shorter cycle time often leads to happier customers.
• Loss Ratio: This compares the amount paid out in claims to the amount earned in premiums. It's a fundamental measure of underwriting profitability. Calculated as: (Total Claims Incurred / Total Premiums Earned).

Creating Your Dashboard: A Step-by-Step Guide

With your KPIs defined, you’re ready to start building. We’ll use ChatGPT to speed up a few key steps, like generating sample data and creating complex formulas.

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

Step 1: Structure and Generate Your Data

A great dashboard is built on clean, well-organized data. The best practice is to keep all your raw data on one sheet and build your dashboard on another. Create a new Google Sheet and name your first tab "Raw Data."

Your "Raw Data" tab should be structured like a simple database, with each row representing a policy and columns for different attributes. Here’s a good starting structure:

• Policy_ID
• Customer_Name
• Policy_Type (e.g., Auto, Home, Life, Health)
• Start_Date
• Premium_Amount
• Agent_Name
• Status (e.g., Active, Expired)
• Claim_ID (if applicable)
• Claim_Amount (if applicable)
• Claim_Status (e.g., Pending, Paid, Denied)

To populate your sheet without using real client data, you can ask ChatGPT to generate realistic sample data. This is perfect for building and testing your dashboard structure.

Try this prompt in ChatGPT:

“Act as a data analyst for an insurance agency. Generate 50 rows of sample data in CSV format for a policy and claims log. Include these columns: Policy_ID, Customer_Name, Policy_Type, Start_Date, Premium_Amount, Agent_Name, Status, Claim_ID, Claim_Amount, and Claim_Status. Make sure the policy types are 'Auto', 'Home', and 'Life', and the Claim_Status is 'Pending', 'Paid', or 'Denied'.”

You can then copy the CSV output directly from ChatGPT and paste it into your "Raw Data" tab.

Step 2: Create a Dedicated Dashboard Tab

Now, create a new tab and name it "Dashboard." This is where all your KPIs, charts, and visualizations will live. Keeping it separate from your raw data prevents accidental edits and keeps your work organized. Your "Raw Data" tab will act as the engine, while the "Dashboard" tab will be the "driver's seat."

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

Step 3: Calculate Your KPIs Using ChatGPT for an Assist

Switch over to your "Dashboard" tab. This is where you’ll lay out your key numbers. A clean way to do this is to create a "KPI Snapshot" section at the top of the page. Here's where ChatGPT becomes your formula-writing assistant, saving you from headaches and VLOOKUP tutorials.

Let's calculate a few core KPIs. For each, we’ll use a simple prompt to get the exact formula from ChatGPT.

Total Premium Revenue

• Give ChatGPT this prompt: "My Google Sheet has a tab named 'Raw Data'. Premium values are in Column E. Write a formula to get the sum of all premium values."
• ChatGPT will return:

Number of Active Policies

• Ask ChatGPT: "In my 'Raw Data' tab, Column G shows the policy status, which is either 'Active' or 'Expired'. Create a Google Sheets formula to count the number of 'Active' policies."
• It will give you:

Average Claim Amount

• Prompt: "Claim amounts are listed in Column I of my 'Raw Data' tab. Write a formula that calculates the average of all these numbers."
• You'll get a simple formula:

For more complex calculations like Loss Ratio, ChatGPT can also help. Just explain the logic:

Loss Ratio

• Prompt: "Let's calculate my agency's loss ratio. In 'Raw Data', my collected premiums are in Column E. The claims I've paid out are listed in Column I, but only if Column J for that row says 'Paid'. Write me the formula."
• ChatGPT should combine SUM and SUMIF:
• Just remember to format this cell as a percentage to make it easy to read!

Arrange these KPIs neatly at the top of your "Dashboard" tab so you can see your core business health at a glance.

Step 4: Visualize Your Data with Charts

Now that your KPIs are calculated, it's time to bring your data to life with charts. Visualizations make it much easier to spot trends and compare performance.

Let's create two common charts: a pie chart to show the policy breakdown and a bar chart for agent performance.

1. Policy Mix (Pie Chart)

A pie chart is perfect for showing composition, like what percentage of your business comes from Auto, Home, or Life insurance.

• First, you need a summary table. On a blank section of your dashboard, list your policy types ("Auto", "Home", "Life") in one column.
• In the adjacent column, use the COUNTIF formula you learned earlier to count each one. Your table should look something like this:

Auto | =COUNTIF('Raw Data'!C:C,"Auto") Home | =COUNTIF('Raw Data'!C:C,"Home") Life | =COUNTIF('Raw Data'!C:C,"Life")

• Highlight this summary table, then go to Insert > Chart. Google Sheets will likely recommend a pie chart automatically.

2. Premiums by Agent (Bar Chart)

A bar chart is great for comparing performance across different individuals or categories.

• First, create a summary table using the SUMIF function to total premiums for each agent.
• You'll need a unique list of your agent names. You can use the formula =UNIQUE('Raw Data'!F:F) to generate this automatically.
• Beside each agent’s name, use a SUMIF to total their premiums. If an agent's name is in cell G23, the formula would be:
• Highlight your new agent summary table, go to Insert > Chart, and select a column or bar chart to visualize the data.

Arrange these charts and your KPI scorecards on your dashboard to create a clean, logical layout. Use cell merging and background colors to separate different sections and make important numbers stand out.

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

Step 5: Make Your Dashboard Interactive with Filters

A static dashboard is good, but an interactive one is even better. By adding a simple dropdown filter, you can slice your data and dive deeper into specific segments, like seeing the performance of a single agent.

Let’s add a dropdown menu to filter the entire dashboard by agent.

1. Create the Dropdown: Select an empty cell at the top of your dashboard (e.g., cell B1). Go to Data > Data validation. For the "Criteria," choose "List from a range." In the box, select the column in your "Raw Data" tab containing agent names (e.g., 'Raw Data'!F:F). This creates a dropdown with all your agents.
2. Update Your Formulas: You now need to add a "condition" to your existing formulas. This requires upgrading from SUMIF and COUNTIF to their plural counterparts: SUMIFS and COUNTIFS, which allow for multiple criteria.

Let’s say your agent dropdown is in cell B1. Here’s how you’d update a formula for ‘Total Premiums’ for the selected agent:

• Old Formula: =SUM('Raw Data'!E:E)
• New Formula: =SUMIF('Raw Data'!F:F, B1, 'Raw Data'!E:E)

You can ask ChatGPT for help modifying these too. Just provide the context!

Prompt: “Revise my Google Sheets formula =COUNTIF('Raw Data'!G:G, 'Active'). I want it to ONLY count active policies for the agent selected in cell B1 of my 'Dashboard' tab. The agent names are in Column F of 'Raw Data'.”

ChatGPT will know to use COUNTIFS:

=COUNTIFS('Raw Data'!G:G, "Active", 'Raw Data'!F:F, B1)

Apply this logic to all your KPIs and chart summary tables. Now, when you select an agent from the dropdown, all the numbers and charts on your dashboard will update instantly.

Final Thoughts

Building an insurance dashboard in Google Sheets provides a clear view of your business using tools you already have. By neatly organizing your data, leveraging ChatGPT for complex formulas, and using charts to visualize trends, you've created a powerful report that's miles ahead of static spreadsheets and manual calculations.

While Google Sheets is an amazing starting point, you'll eventually notice the time it takes to maintain it. For instance, you still have to manually export and paste your data in weekly or monthly. As your business grows, we built Graphed to solve this. Instead of a manual process, we connect directly to your data sources and allow you to build live, auto-updating dashboards simply by describing what you want to see in plain English - no formulas needed. It's like having a data analyst on your team, ready to build reports and answer questions in seconds.