Excel dashboards are one of the most practical ways to turn raw, messy data into a story that decision-makers can actually use. In this tutorial, I’ll walk you through exactly how I built a complete Insurance Claims Dashboard from a raw CSV file — structured across three clean sheets: Data, Pivot Tables, and Dashboard. Whether you’re a business analyst, an insurance professional, or simply someone learning Excel, this step-by-step process will help you build dashboards that look sharp and function flawlessly.

Why Structure Matters: The Three-Sheet Approach

Before diving into the technical steps, it’s worth understanding why I split the workbook into three sheets instead of cramming everything onto one. A dashboard that mixes raw data, calculations, and visuals on a single sheet quickly becomes cluttered and fragile — one accidental edit to a data cell can break your entire report.

By separating concerns — Data for the source of truth, Pivot Tables for calculations, and Dashboard for presentation — you get a workbook that’s easy to audit, easy to update, and easy to hand off to someone else. This is a best practice I follow in every business dashboard I build, and it’s the backbone of this tutorial.

Get the dataset used in the project here: https://github.com/Kent-Ward/insurance-claims-risk-dashboard/blob/master/data/raw/Insurance_Claims_Data.csv

Step 1: Understand and Clean Your Raw Data

Every good dashboard starts with good data. I began with an Insurance_Claims_Data.csv file containing 500 records across ten columns: Policy ID, Customer ID, Claim Type, Region, Claim Status, Fraudulent Claim, Accident Type, Policy Type, Processing Days, and Claim Amount.

Before building anything, I inspected the dataset to understand its shape — checking row counts, spotting unique values in each categorical column (like Region: East, West, North, South, Central), and reviewing the numeric ranges for Processing Days and Claim Amount. This diagnostic step is critical because it tells you what kind of summaries and visuals will actually be meaningful later. Skipping this step is the most common reason dashboards end up with broken charts or misleading numbers.

Get dataset here:

Step 2: Build the Data Sheet

The first sheet, Data, houses the complete, untouched dataset. Here’s how I approached it:

1. Import the CSV directly into Excel, keeping every row and column intact.
2. Format the header row with a bold white font on a navy fill — this immediately signals “this row is different” and improves readability.
3. Apply number formatting — Claim Amount as currency ($#,##0) and Processing Days as a plain integer — so the numbers are instantly readable without extra mental math.
4. Convert the range into an official Excel Table (Insert → Table). This isn’t just cosmetic. Tables in Excel auto-expand when new rows are added, support structured references, and make your downstream formulas far more robust.
5. Freeze the header row so that as users scroll through 500 rows, they never lose track of which column is which.

This sheet becomes the single source of truth. Every formula in the rest of the workbook points back here — never the other way around.

Step 3: Design the Pivot Tables Sheet

This is where the real analytical heavy lifting happens. Rather than relying purely on Excel’s native PivotTable feature (which can be fragile when files move between systems), I built formula-driven summary tables using SUMIFS, COUNTIFS, and AVERAGEIF. This approach has a major advantage: the summaries recalculate live the moment you update the Data sheet, with zero manual refreshing.

Here’s the breakdown of the six summary tables I created:

1. Claims by Status — Using COUNTIF and SUMIF, I calculated the count, total claim amount, average claim amount, and percentage share for each status (Approved, Rejected, Under Review). This immediately shows the approval-to-rejection balance.

2. Claim Count by Region and Claim Type — A cross-tabulation using COUNTIFS with two conditions (Region and Claim Type), giving a matrix view of where different claim types are concentrated geographically.

3. Fraudulent Claims by Region — Combining COUNTIFS and SUMIFS, this table isolates fraud rate and fraud-related claim amounts by region, which is often the single most valuable table for a risk or compliance team.

4. Claims by Policy Type — Summarizing count, total amount, and average processing days per policy tier (Gold, Platinum, Premium, Standard) using AVERAGEIF.

5. Claims by Accident Type — A simple but powerful count and total-amount breakdown across accident categories like Collision, Flood, Fire, and Burglary.

6. Processing Time Distribution — I bucketed Processing Days into four ranges (0–15, 16–30, 31–45, 46–60) using COUNTIFS with “greater than or equal to” and “less than or equal to” conditions, revealing how efficiently claims move through the pipeline.

For each table, I applied consistent formatting: bold navy headers, alternating row shading for readability, currency formatting on monetary columns, and percentage formatting where relevant. A small but important detail — every table includes a Total row, so nothing needs to be manually cross-checked against the raw data.

Step 4: Design the Dashboard Sheet

With the calculations complete, the final step is presentation — the sheet that leadership will actually look at. I approached this with the mindset of “what does someone need to know in the first five seconds?”

KPI Cards at the top. I created four colour-coded cards summarizing:

• Total Claims (500)
• Total Claim Amount (~$12.7M)
• Average Processing Days (~32.7 days)
• Fraud Rate (~15.8%)

Each card uses a large, bold number with a short label above it, and every value is a live formula pulling from the Data sheet — not a hardcoded number. This means if the underlying data changes, the entire dashboard updates automatically.

Six supporting charts, organized into three neatly labelled rows:

1. A pie chart showing the split between Approved, Rejected, and Under Review claims.
2. A column chart showing fraud rate by region — instantly flagging high-risk geographies.
3. A horizontal bar chart ranking claim amount by accident type.
4. A stacked column chart breaking down claim counts by region and claim type together.
5. A column chart showing the distribution of processing times across the four buckets.
6. A line chart tracking average processing days by policy type, useful for spotting service-level gaps between tiers.

Each chart pulls directly from the Pivot Tables sheet using cell references, not pasted values — so, again, everything stays dynamic.

Step 5: Polish, Format, and Verify

A dashboard is only as trustworthy as its accuracy. Before calling it done, I always run a verification pass:

• Recalculate every formula to confirm there are zero errors — a dashboard with a stray #DIV/0! or #VALUE! destroys credibility instantly.
• Cross-check totals — for example, confirming the sum of claims across all statuses equals 500, and the sum of the Region table matches the Data sheet’s row count.
• Apply a consistent visual language — one font (Arial), one primary colour palette (navy and blue, with red reserved for risk indicators like fraud), and consistent spacing between sections.
• Add a footer note citing the data source and explaining the workbook’s structure, so anyone opening the file for the first time understands how it’s organized without needing an explanation.

Common Mistakes to Avoid

Having built dozens of these workbooks, I’ve noticed the same handful of mistakes trip people up again and again. It’s worth calling them out explicitly:

Hardcoding numbers instead of using formulas. It’s tempting to just type “500” into a KPI card because you already know the count. Don’t. The moment someone adds ten more rows to the Data sheet, that hardcoded number becomes silently wrong, and nobody notices until it’s too late. Every number on the Dashboard sheet in this workbook is a live formula referencing either the Data sheet or the Pivot Tables sheet.

Skipping the Total row. Summary tables without a total row are surprisingly hard to trust. A reader has no quick way to confirm the numbers add up to the whole dataset. Adding a bolded, shaded total row at the bottom of each table costs almost nothing and buys a lot of credibility.

Overloading a single chart with too many categories. I initially considered cramming all five claim types and all five regions into one chart. It became unreadable. Splitting the analysis into a matrix table (Pivot Table 2) and letting the stacked chart summarize it visually turned out to be a far cleaner solution.

Ignoring colour discipline. Random colours across charts make a dashboard feel unplanned. I stuck to a consistent palette — navy and blue for neutral metrics, red only for risk-related figures like fraud rate — so that colour itself carries meaning rather than just decoration.

Forgetting to test formulas after copying them across cells. A single miscopied SUMIFS range can silently pull the wrong data into an otherwise perfectly formatted table. I always spot-check two or three cells against a manual calculation before trusting the rest of the table.

Extending This Dashboard Further

Once the core three-sheet structure is in place, there are several natural ways to extend it depending on what your stakeholders need:

• Add slicers if you convert the summary tables into native Excel PivotTables, allowing users to filter the entire dashboard by Region, Claim Type, or Policy Type with a single click.
• Add a trend sheet if your data includes a date column, so you can track claim volume, fraud rate, or processing time over months or quarters.
• Add conditional formatting to the Data sheet — for instance, highlighting claims with unusually high processing days or unusually large claim amounts, so outliers are visible at a glance without needing a separate report.
• Build a “What-If” section where a user can adjust an assumption (like an expected fraud rate reduction) and see the KPI cards update accordingly — a lightweight way to support planning conversations.

None of these are required for a solid first version, but they’re worth keeping in your back pocket as the dashboard matures and stakeholders start asking “can it also show me…?” questions.

Final Thoughts

What makes this dashboard genuinely useful isn’t just the charts — it’s the underlying discipline of separating raw data, calculations, and presentation into distinct layers, and making sure every number on the dashboard traces back to a live formula rather than a static, one-time calculation. That’s the difference between a dashboard that looks good today and one that keeps working reliably as the data grows.

If you’re building your own version, start small: get your raw data clean and structured first, build your summary formulas second, and only then move to the visuals. Skipping straight to charts before your data foundation is solid is the fastest way to end up rebuilding everything from scratch.

I hope this walkthrough gives you a practical, repeatable framework for your next Excel dashboard project.