Graphed LogoGraphed
Login

How to Create an Insurance Dashboard in Excel with AI

Cody Schneider•

Building a dashboard in Excel to track your insurance agency’s performance is a must, but the manual process of creating pivot tables and charts can feel like a chore. You can spend an entire morning wrestling with spreadsheets just to get a clear picture of your key metrics. This article will show you two ways to build an effective insurance dashboard: the classic, manual method in Excel and the faster, more intuitive approach using AI.

Why You Need an Insurance Dashboard

An insurance dashboard brings all your critical data into a single, visual snapshot. Instead of digging through endless reports or your Agency Management System (AMS), you get an eagle-eye view of your business's health. It helps agents, brokers, and agency owners make smarter, data-driven decisions on the fly.

Here are some of the key performance indicators (KPIs) a good dashboard can track:

  • Policy Sales: Track new policies written by type (auto, home, life), by agent, or by month.

  • Premium Written: Monitor the total premium generated over specific periods.

  • Claims Analysis: See the number of claims, their status (open, closed, pending), and the average payout per policy type.

  • Client Demographics: Understand who your customers are by visualizing their location, age, or other segments.

  • Renewal Rate: Keep a close eye on your client retention by tracking what percentage of policies are renewed.

  • Lead-to-Close Ratio: Measure the effectiveness of your sales process from new lead to bound policy.

With this information centralized, you can quickly spot trends, identify top-performing agents, find opportunities for growth, and address issues before they become major problems.

Step 1: Gathering and Structuring Your Data

Whether you’re building your dashboard manually or with AI, you need clean, well-organized data. A dashboard is only as good as the data feeding it. Your information likely lives in several places, such as:

  • Your Customer Relationship Management (CRM) system (e.g., Salesforce, HubSpot).

  • Your Agency Management System (AMS).

  • Spreadsheets used for tracking claims or commissions.

  • Exported reports from accounting software.

To get started, export the relevant data into a single CSV or Excel file. Your goal is to create one master spreadsheet with clear, consistent columns. A typical structure for an insurance data set might look like this:

Policy ID, Client Name, Agent Name, Policy Type, Premium, Start Date, End Date, Claim Status, Claim Amount, Client State

Before you start building, do a quick data cleanup:

  • Standardize Entries: Make sure states are uniformly abbreviated ("CA" not "Calif." or "California"). Ensure policy types are consistent ("Auto" not "Automobile").

  • Remove Duplicates: Check for any duplicate rows that could skew your numbers.

  • Fix Formatting Issues: Dates should be in a consistent format (e.g., MM/DD/YYYY), and currency values should be formatted as numbers, not text.

  • Fill in the Blanks: Look for any empty cells and fill them in where possible. A claim with no amount should have a '0', not be blank.

This prep work is the most important step. Taking 15 minutes to clean your data will save you hours of headaches later.

The Traditional Way: Building a Dashboard Manually in Excel

The classic Excel dashboard relies on PivotTables to summarize data and charts to visualize it. It’s powerful and still widely used. Here’s how to do it.

Step 1: Set Up Your Workbook

Create a new Excel workbook with three tabs:

  • Raw Data: Paste your clean data here. This tab is your single source of truth.

  • Analysis: This tab will house your PivotTables. Keeping them separate from the dashboard makes things less cluttered.

  • Dashboard: This is where you’ll arrange all your charts and slicers for the final visual report.

Step 2: Create PivotTables for Key Metrics

PivotTables do the heavy lifting of summarizing your data. From your "Raw Data" tab, click anywhere in your data, then go to Insert > PivotTable. Create each PivotTable on your "Analysis" sheet.

Here are a few essential PivotTables for an insurance dashboard:

  • Total Premium by Policy Type:

    • Rows: Policy Type

    • Values: Sum of Premium

  • Policy Count by Agent:

    • Rows: Agent Name

    • Values: Count of Policy ID

  • Claims Status Breakdown:

    • Rows: Claim Status

    • Values: Count of Policy ID

Create a separate PivotTable for each metric you want to display on your dashboard.

Step 3: Build Charts from Your PivotTables

Now it’s time to visualize your summaries. For each PivotTable on your "Analysis" sheet, click inside it and go to PivotTable Analyze > PivotChart.

  • For "Total Premium by Policy Type," a Bar Chart is a great choice.

  • For "Policy Count by Agent," a Column Chart works well.

  • For "Claims Status Breakdown," a Pie or Donut Chart clearly shows proportions.

After creating each chart, cut it (Ctrl+X or Cmd+X) from the "Analysis" sheet and paste it (Ctrl+V or Cmd+V) onto your "Dashboard" sheet. Arrange them in a logical way that’s easy to read.

Step 4: Design and Add Interactivity with Slicers

A static dashboard is good, but an interactive one is even better. Slicers are user-friendly buttons that filter your data. Click on any of your charts, go to PivotChart Analyze > Insert Slicer, and select fields like "Agent Name," "Policy Type," or "Client State."

To make one slicer control all your charts, right-click the slicer and select Report Connections. In the pop-up window, check the boxes for all the PivotTables in your workbook. Now, when you click an agent’s name, all the charts will update to show only their data.

Finish up by giving your dashboard a title and hiding the gridlines (View > uncheck Gridlines) for a clean, professional look.

The Faster Way: Building a Dashboard with AI

The manual method works, but it's rigid and time-consuming. Every time you have a new question, you have to create a new PivotTable and chart. Generative AI tools like ChatGPT's Advanced Data Analysis or Microsoft Copilot drastically speed this up. Instead of clicking and building, you simply tell the AI what you want to see.

Step 1: Upload Your Cleaned Data File

Start by uploading your clean CSV or Excel file directly into the AI tool of your choice. A simple "I’ve uploaded a file with our agency's insurance data. Analyze it for me," is all you need to get started.

Step 2: Ask for Your Visualizations in Plain English

Now for the fun part. Instead of building PivotTables, just ask for what you need. The AI will write the code and generate the charts for you.

Try these prompts:

  • "Create a bar chart showing the total premium for each policy type."

  • "Make a pie chart that visualizes the distribution of claim statuses."

  • "Show me a column chart of the number of policies sold by each agent."

  • "Generate a line chart that tracks the sum of premium by month, using the 'Start Date' column."

The AI will produce the visuals right in the chat interface. You can then download them as image files to assemble your own dashboard in PowerPoint or another tool.

Step 3: Drill Down with Follow-Up Questions

The real power of AI is its conversational nature. A chart often leads to more questions, and now you can ask them immediately.

  • After seeing the agent performance chart, you might ask: "Which agent has the highest premium for Auto policies?"

  • Looking at the claims chart, you might follow up with: "What is the average claim amount for policies in California vs. Nevada?"

  • If you see a spike in premiums, ask: "In which month did we see the highest premium total, and which policy type contributed the most?"

This changes reporting from a static, backwards-looking task into a dynamic, "just-in-time" analysis. You can explore your data and chase down insights without building a single PivotTable.

Best Practices for a Great Dashboard

Whether you build it manually or with AI, a functional dashboard follows a few core principles:

  • Focus on What Matters: Don’t cram it with every metric imaginable. Highlight 5-7 key KPIs that drive your business.

  • Know Your Audience: A dashboard for a sales team might focus on new policies and commissions, while one for an agency owner needs to show profitability and renewal rates.

  • Keep It Simple: Use clear chart titles, label your axes, and avoid clutter. The goal is for someone to understand the key takeaways in under 30 seconds.

  • Ensure Data Accuracy: Remember the phrase "garbage in, garbage out." Create a process for regularly cleaning and uploading fresh data to keep your dashboard relevant and trustworthy.

Final Thoughts

Creating an insurance dashboard in Excel gives you valuable insight into a complex business. The manual approach offers control and is a great skill to have, but leveraging AI simplifies the process, making sophisticated data analysis accessible to anyone, regardless of their Excel skills.

While uploading files and chatting with an AI is a huge leap forward, we built Graphed to automate the entire process. Instead of exporting CSVs from your CRM or AMS, you connect those sources directly to us one time. Your dashboards then update in real-time without you ever having to manually refresh them. Using simple prompts, you can build interactive dashboards that are always live, giving you instant answers so you can focus on building relationships and writing policies, not wrestling with spreadsheets.