Build a Combined Ratio Dashboard with Filters in Excel

How to Calculate Combined Ratio and Add Filters by Region, Carrier, and Time

In this tutorial, you’ll learn how to calculate the Combined Ratio in Excel and create a dynamic Pivot Table dashboard that lets you filter results by location, carrier, and month. This metric helps you understand profitability across your book of business and quickly identify which policies or regions need attention.

1. Understand What the Combined Ratio Is

The Combined Ratio represents total expenses and losses as a percentage of written premium.
It’s calculated as:
Combined Ratio = Expense Ratio + Loss Ratio

• Loss Ratio = (Claims Paid + Loss Adjustment Expenses) ÷ Written Premium
• Expense Ratio = Operating Expenses ÷ Written Premium
• A combined ratio over 100% means your costs exceed premium income, while under 100% indicates profitability.
  

2. Insert a Pivot Table

• Click anywhere inside your dataset (no need to highlight the entire range).
  
• Go to Insert → Pivot Table.
  
• Choose Existing Worksheet and specify the location for your Pivot Table (for example, cell A1).
  
• Verify that the data range is correctly outlined by the “marching ants” border.
  

3. Add Fields to the Pivot Table

• Drag Product Line to the Rows area.
  
• Drag Combined Ratio to the Values area.
  
• By default, Excel will sum the combined ratio values but we need the average instead.
  
• Click the field dropdown → Value Field Settings → Average.
  Now your Pivot Table shows the average combined ratio by product line.
  

4. Format and Clean Up the Table

• Remove the “Average of” prefix from the field title to keep it clean.
  
• Center align all columns.
  
• You now have a readable table showing average combined ratios by product.
  

5. Create a Pivot Chart

• Click inside the Pivot Table.
  
• Go to Insert → Pivot Chart (not Recommended Charts).
  
• Choose a Column Chart for visual clarity.
  
• Title the chart “Combined Ratio”.
  
• Adjust colors and design for visibility.
  

6. Remove Field Buttons for a Cleaner Look

• Click on the chart.
  
• Go to PivotChart Analyze → Field Buttons.
  
• Deselect all options (Legend, Axis, and Value).
  The chart now looks clean and professional, without cluttered filter buttons.
  

7. Add Slicers for Region and Carrier

• Click inside your Pivot Table.
  
• Go to Insert → Slicer.
  
• Choose Region and Carrier as slicer fields.
  
• Arrange slicers above your chart, resizing as needed.
  
• To improve layout:
  
  • Increase the number of columns in each slicer to make them horizontal.
    
  • Right-click → Slicer Settings → uncheck Display Header to remove titles and save space.
    

Now you can click any region or carrier to instantly filter your Combined Ratio chart.

8. Add a Timeline for Monthly or Yearly Filtering

• Click inside the Pivot Table.
  
• Go to Insert → Timeline.
  
• Choose Policy Effective Date as your timeline field.
  
• Resize and position it below the slicers for intuitive navigation.
  
• If your dataset spans multiple years, you can switch between monthly or yearly analysis views directly in the timeline.
  

9. Test Your Filters

• Use slicers to toggle between carriers (e.g., Allstate, Liberty Mutual) or regions (e.g., Central Texas).
  
• Adjust the timeline to view performance for specific months or years.
  
• Watch your Pivot Chart update instantly with each filter combination.
  

10. Final Adjustments

• Add consistent styling for slicers and chart elements.
  
• Align slicers and timeline for a clean dashboard appearance.
  
• Optional: Save this setup as a template for other KPI analyses.
  

Result

You now have a dynamic Combined Ratio dashboard in Excel that updates automatically by region, carrier, and time period. This allows you to pinpoint high-cost areas, measure underwriting efficiency, and make informed business decisions in seconds.