Excel Skills for Insurance Jobs: Claims, Premiums and Reports

The Excel skills for insurance jobs come down to a short list: counting and totalling claims, looking up rates and policy details, and calculating premiums and ratios without rounding errors. Claims handlers, underwriting assistants, broker account staff and junior analysts all use the same core formulas. This guide shows each one on small insurance-style examples, so you know what to practise before an application or an Excel test.

What insurance teams use Excel for#

Insurance companies run on core systems for policies and claims, but much of the checking and reporting happens in Excel. Typical tasks by role:

The formulas below cover most of the first four rows. Actuarial roles build on them with much more statistics and modelling.

Count and total claims with COUNTIFS and SUMIFS#

Claims reports answer "how many?" and "how much?" by type and status, which is exactly what COUNTIFS and SUMIFS do. Here is a small claims list:

Every condition in COUNTIFS must be true for a row to count. The COUNTIFS function guide covers the syntax, and the COUNTIFS and SUMIFS lesson lets you practise these exact questions. Auditors use the same formulas to test whole populations, as the guide to Excel skills for auditors shows.

Look up policies and rates with XLOOKUP#

XLOOKUP finds a value in one column and returns the matching value from another. Insurance work uses it two ways.

Exact match, for policy details. If a policy list has policy numbers in column A and the insured name in column C:

=XLOOKUP(G2,A2:A500,C2:C500,"Not found")

This returns the name for the policy number in G2, or "Not found" if it isn't there.

Approximate match, for rate bands. Rating tables often list the lower limit of each band. Say a motor rating factor depends on driver age:

For a 31-year-old driver in G5:

=XLOOKUP(G5,J2:J5,K2:K5,,-1)

The -1 tells XLOOKUP to use an exact match or, if there isn't one, the next smaller value. 31 falls in the band starting at 25, so the result is 1.25. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, =VLOOKUP(G5,J2:K5,2,TRUE) does the same job if the ages are sorted from smallest to largest. The XLOOKUP function guide and the XLOOKUP lesson cover both kinds of match.

Round premiums correctly with ROUND#

Premiums, refunds and commissions are money, so the stored value should have exactly two decimals. Formatting a cell to show 2 decimals only hides the extra digits, and totals can end up a cent out.

Use the ROUND function in the formula itself:

=ROUND(1150*100/365,2)

This returns 315.07 instead of 315.068493… Use ROUNDUP or ROUNDDOWN when a company rule says premiums always round one way. The rounding lesson practises all three.

Earned premium, days open and loss ratios#

These three calculations appear in many insurance reports, and each is a short formula.

Earned premium is the part of the premium that relates to time already passed. With a simple straight-line method, a 1,200 annual premium that has run 73 days of a 365-day year has earned:

=ROUND(1200*73/365,2)

That is 240, leaving 960 unearned. Real policies may use other earning patterns, so follow your company's method.

Days open for a claim is today's date minus the date it was reported: =TODAY()-E2. To count only working days, use =NETWORKDAYS(E2,TODAY()).

Loss ratio compares claims with premium. If claims are in B2 and earned premium in C2:

=B2/C2

With claims of 64,000 and earned premium of 100,000, the result is 0.64, which shows as 64% when you format it as a percentage. Teams define the exact figures differently, for example paid or incurred claims, so check which ones your report uses.

Other skills that help in insurance roles#

Beyond formulas, these everyday skills save time:

• Sorting and filtering large claims or policy lists, and removing duplicates.
• PivotTables to summarise claims by month, type or region in seconds.
• Data cleaning with TRIM and VALUE when exports bring extra spaces or numbers stored as text.
• Checking totals against the system, a habit shared with accounts payable work and bookkeeping.

For the wider finance side of insurance, the guide to Excel skills for finance jobs covers SUMIFS by period, percent change, budget variance and reconciliation checks.

How to show your Excel skills when you apply#

Name specific tasks on your CV rather than writing "good Excel". For example: "built a weekly open-claims report with COUNTIFS and SUMIFS". The guide to Excel skills on a resume has more phrasing ideas.

Many employers check Excel with a short assessment. To see where you stand, take the Excel level test. Then try a timed basic Excel test or, for analyst roles, the intermediate Excel test.

Practise it#

The fastest way to get job-ready is to type these formulas yourself. Practise COUNTIFS and SUMIFS in a live spreadsheet: you build claims-style counts and totals and the sheet checks each one instantly. Then move on to rate lookups in the XLOOKUP lesson and premium amounts in the rounding lesson.