Calculate Claim Frequency by Product Line in Excel

How to Calculate Claim Frequency in Excel

This tutorial shows how to calculate Claim Frequency by product line and then convert it into a Claim Frequency Percentage. This KPI helps you understand your loss exposure and spot possible carrier friction when claim frequency is unusually high.

1. Create a Unique List of Product Lines

• In your dataset, locate the Product Line column (e.g., Column H).
  
• Use the UNIQUE() function to remove duplicates.
  
• If a zero appears at the top, it’s because you selected the entire column instead of just the data range.
  
• To make the list dynamic for future data, add a period (.) after the range in the formula.
  
• Copy the results and paste values using Ctrl + Shift + V to remove formulas.
  
• Optionally, sort alphabetically for cleaner organization.
  

2. Count the Number of Claims per Product Line

• Identify the column that tracks whether a claim exists (e.g., “Has Claim” in Column C).
  
• Use the COUNTIFS() function to count how many “Yes” responses occur for each product line.
  

=COUNTIFS(HasClaimRange, “Yes”, ProductLineRange, ProductLine)

• This gives you the total number of claims by product line.
  
• Drag the formula down to apply it to all product lines.
  
  

3. Count the Total Number of Policies per Product Line

• Use COUNTIFS() (or COUNTIF) to count how many policies exist in total for each product line.
  

=COUNTIFS(ProductLineRange, ProductLine)

• This captures the total policy count, regardless of whether a claim was filed.
  
• Drag the formula down to populate the full list.
  

4. Calculate the Claim Frequency Percentage

• Divide the number of claims by the total number of policies.
  

=Claims ÷ Policies

• Press Ctrl + Shift + 5 to format the result as a percentage.
  
• Center the results using Alt + H + A + C.
  
• This shows the percentage of policies that resulted in a claim.
  

5. Build a Combo Chart to Visualize the Results

• Highlight the columns for Product Line, Number of Claims, and Claim Frequency (%).
  
• Go to Insert → Recommended Charts → Combo Chart.
  
• Assign the Number of Claims to the primary axis and Claim Frequency % to the secondary axis.
  
• The result will show:
  
  • Bars representing the number of claims
    
  • A line representing the claim frequency percentage
    
• Adjust the line color for contrast (e.g., black or dark gray).
  
• Add a descriptive title like “Claim Frequency by Product Line”.
  

6. Sort and Analyze the Data

• Sort the product lines alphabetically or by frequency percentage to identify high-risk products quickly.
  
• The chart now makes it easy to see relationships like:
  
  • A product with many policies but few claims (low frequency, low risk)
    
  • A product with few policies but high claim frequency (potential carrier friction)
    

7. Optional Enhancements

• Add data labels to the chart for clear reference.
  
• Apply conditional formatting to highlight products with above-average claim frequency.
  
• Combine this metric with Loss Ratio or Average Claim Cost to see a more complete risk picture.
  

Result

You now have an Excel model that calculates Claim Frequency and Claim Frequency % by product line, complete with a combo chart visualization. This analysis helps reveal which products are driving claim volume, enabling smarter underwriting and carrier management decisions.