How to Create an Insurance Dashboard in Excel

Building an insurance dashboard in Excel is a powerful way to turn rows of policy, claims, and sales data into clear, actionable insights for your agency or business. This guide will walk you through the entire process, from structuring your raw data to creating an interactive, professional-looking dashboard complete with filters that let you slice and dice your information with a few clicks.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

First, Plan Your Insurance Dashboard

Before you even open a spreadsheet, you need a clear plan. The most effective dashboards answer specific business questions. Start by asking what you need to track. Are you focused on sales performance, claims processing efficiency, or overall business health? A clear goal prevents you from building a dashboard cluttered with vanity metrics that don't drive decisions.

Once you have a goal, define the Key Performance Indicators (KPIs) you'll use to measure it. Here are some common examples for an insurance dashboard:

• Sales & Revenue KPIs: New business premium, renewal premium, policies in force, premium by policy type, quote-to-conversion rate, and top-performing agents.
• Claims KPIs: Number of open claims, average time to close a claim, total claim payout amount, claims frequency rate, and claim status distribution (open, pending, closed).
• Operational KPIs: Customer retention rate, cost per policy acquisition, and policies per agent.

With your KPIs chosen, grab a pen and paper (or a whiteboarding tool) and sketch a simple layout. Where will your main charts go? What about filters? A simple wireframe helps you organize your thoughts and build a more intuitive dashboard later on.

Step 1: Gather and Organize Your Insurance Data

Your dashboard is only as good as the data powering it. Your first task is to consolidate your data into a single, clean table. This data might come from your CRM, a claims management system, accounting software, or other spreadsheets. The key is to have all the necessary information in one place.

The best practice for managing data in Excel for a dashboard is to format it as a Table.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Why Use an Excel Table?

When you format your data as a Table (select your data, then go to Insert > Table or press Ctrl + T), Excel gives it superpowers. Tables automatically expand to include new rows, so you don't have to manually update your chart and Pivot Table ranges every time you add data. This makes refreshing your dashboard significantly easier.

Structure Your Source Data

Organize your data in a flat, tabular format where each row is a single record (like a policy) and each column is an attribute of that record. Here’s a sample structure for a master insurance data sheet:

• Policy ID: A unique identifier for each policy.
• Effective Date: The date the policy started.
• Agent: The name of the agent who sold the policy.
• CustomerID: Unique identifier for the customer.
• Policy Type: e.g., Auto, Home, Life, Commercial.
• Region: e.g., North, South, East, West.
• Premium: The premium amount for the policy.
• Status: e.g., Active, Expired, Canceled.
• Claim ID: Unique ID for any associated claim (if applicable).
• Claim Amount: Payout amount for the claim (if applicable).
• Claim Status: e.g., Open, Closed, Pending.

Take some time to clean your data. Check for typos, make sure spellings are consistent (e.g., "Auto Insurance" vs. "Auto"), and fill in any blank cells where possible. This cleanup work is tedious but crucial for accurate reporting.

Step 2: Build Your Analysis with Pivot Tables

Pivot Tables are the engine of an Excel dashboard. They do all the heavy lifting of summarizing and calculating your data, allowing you to create charts that update automatically. We will create a few different Pivot Tables - one for each chart or KPI we want to display. It's a good habit to keep all your Pivot Tables on a separate, hidden worksheet to keep your dashboard file tidy.

Create a new worksheet and name it something like "PivotTables" or "Backend". Now, let’s build some analyses.

Creating Your First Pivot Table

1. Click anywhere inside your source data Table.
2. Go to the Insert tab and click PivotTable.
3. Excel should automatically select your table. Choose "Existing Worksheet" and select a cell in your "PivotTables" sheet as the destination. Click OK.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Example Pivot Tables for an Insurance Dashboard

Here are a few essential Pivot Tables you might want to create. Repeat the process above for each one.

1. Total Premium by Policy Type

• Drag Policy Type into the Rows area.
• Drag Premium into the Values area. Excel will default to "Sum of Premium." Right-click the values and format them as Currency.

2. Policy Count by Agent

• Drag Agent into the Rows area.
• Drag Policy ID into the Values area. Excel might default to "Sum of Policy ID." Click on it, go to "Value Field Settings," and change it to Count. This counts how many policies each agent has.

3. Claim Status Overview

• Drag Claim Status into the Rows area.
• Drag Claim ID into the Values area and set it to Count to see how many claims fall into each status category.

4. Monthly Premium Trend

• Drag Effective Date into the Rows area. Excel will automatically group this by years, quarters, and months. You can expand or remove these as needed. If you just want months, keep only "Months" and "Years" in the box.
• Drag Premium into the Values area.

Continue creating a separate Pivot Table for every piece of data you want to visualize in your dashboard.

Step 3: Visualize Your Data with Charts

Now comes the fun part: turning those raw summaries into professional-looking charts. We'll build these as Pivot Charts, which are directly linked to your Pivot Tables. When the Pivot Table updates, the chart updates too.

1. Click on a Pivot Table you created (e.g., the "Total Premium by Policy Type" summary).
2. Go to the PivotTable Analyze tab and click on PivotChart.
3. Choose a suitable chart type. For this example, a Bar or Pie chart works well. Click OK.

Excel will create a chart on your "PivotTables" sheet. Repeat this for each of your summaries. Here are some chart suggestions for the examples above:

• Total Premium by Policy Type: A Doughnut Chart or a Horizontal Bar Chart is great for comparing categories.
• Policy Count by Agent: A Vertical Bar Chart works well for ranking performance.
• Claim Status Overview: A Pie Chart can quickly show the distribution of claim statuses.
• Monthly Premium Trend: A Line Chart is the best choice for showing trends over time.

Cleaning Up Your Charts

Default Excel charts can look a bit cluttered. Make them look more professional by:

• Removing Field Buttons: Right-click on one of the grey field buttons (like "Sum of Premium Total") and choose "Hide All Field Buttons on Chart."
• Adding Clear Titles: Give each chart a simple, descriptive title like "Premium by Policy Type."
• Deleting the Legend: If your chart is simple enough (like a bar chart where labels are on the axis), you can often delete the legend to save space.
• Adding Data Labels: Click the + icon next to the chart and check "Data Labels" to make the values easier to read.

Step 4: Design the Dashboard Layout

It's time to assemble everything. Create a new, blank worksheet and name it "Dashboard." This is where you will present your final visuals.

To move your charts onto the dashboard:

1. Go to your "PivotTables" sheet.
2. Select a chart, press Ctrl + X to cut it.
3. Navigate to your new "Dashboard" sheet and press Ctrl + V to paste it.

Repeat this process for all of your charts. Now, arrange them according to the sketch you made earlier. A common layout places high-level KPIs at the top, followed by more detailed trend and comparison charts below. Use Excel's alignment tools (Shape Format > Align) to snap your charts to a neat grid.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 5: Make Your Dashboard Interactive with Slicers

Slicers are user-friendly buttons that filter your data. They are what transform a static report into an interactive dashboard. We can add slicers for fields like Agent, Region, or Policy Type.

1. Click inside any of your charts on the dashboard.
2. Go to the PivotChart Analyze tab and click Insert Slicer.
3. A dialog box will appear with all your data fields. Check the boxes for the fields you want to filter by - for example, Agent, Region, and Policy Type. Click OK.

You’ll now see three slicer panels on your worksheet. When you click a button in one slicer (e.g., you click "John Smith" in the Agent slicer), the chart you originally selected will filter to show only John Smith's data. But what about the other charts?

Connecting Slicers to All Charts

To make the whole dashboard interactive, you need to connect each slicer to all your Pivot Tables.

1. Right-click on a slicer and select Report Connections.
2. In the dialog box, you'll see a list of all the Pivot Tables in your workbook. Check the box for every single Pivot Table you created.
3. Click OK.

Repeat this process for each of your slicers. Once done, clicking any filter button will instantly update every chart on your dashboard. You now have a fully interactive report that lets your team drill down into the specifics of your insurance data without ever having to touch a Pivot Table.

Final Thoughts

By following these steps, you can transform a raw data file into a dynamic and insightful insurance dashboard using only Excel. This method empowers you to track key metrics like policy sales, agent performance, and claim status with an interactive tool built from the data you already have.

While Excel is fantastic, the process of manually exporting data, cleaning spreadsheets, and refreshing Pivot Tables can become time-consuming, especially when you need weekly or daily updates. We built Graphed to eliminate that manual work entirely. Instead of wrestling with CSVs, you just connect your CRM or data sources once. From there, you can ask for charts and dashboards in plain English ("show me total premium by agent for last quarter as a bar chart"), and it automatically builds a live, auto-updating dashboard for you in seconds. It allows you to spend more time acting on insights and less time just trying to build the report.