How to Analyze Average Written Premium by Agent and Lead Source in Excel

How to Calculate the Average of Written Premium by Agent and Lead Source in Excel

This tutorial walks through how to calculate the average written premium by both agent and lead source using PivotTables, then adds a heat map, chart, and interactive slicers for easy filtering. By the end, you’ll have a dynamic report that shows where your premiums are being generated and how different sources perform.

1. Create a Pivot Table

1. Click anywhere inside your dataset.
   
2. Go to Insert → PivotTable.
   
3. Choose Existing Worksheet, select your desired location (for example, cell A1), and click OK.
   
4. You’ll now see an empty PivotTable placeholder ready to configure.
   

2. Add Fields to Rows, Columns, and Values

• Drag Agent Name to the Rows area.
  
• Drag Lead Source to the Columns area.
  
• Drag Written Premium to the Values area.
  

By default, Excel uses Sum of Written Premium. Change this to Average:

• Click the dropdown in Values → Value Field Settings → Select Average → OK.
  

Now you have the average written premium by each agent and lead source.

3. Clean Up the Pivot Table

• Remove grand totals by right-clicking the total cell and choosing Remove Grand Total.
  
• Sort agents alphabetically: click the gear icon → Sort A to Z.
  
• Format the numbers as currency with Ctrl + Shift + 4.
  

4. Create a Heat Map

1. Highlight the values in your PivotTable.
   
2. Go to Home → Conditional Formatting → Color Scales.
   
3. Choose a green color scale, where darker shades represent higher averages.
   

This gives a quick visual cue of which agents and sources bring in stronger written premiums.

5. Create a Pivot Chart

1. Click anywhere in your PivotTable.
   
2. Go to Insert → PivotChart.
   
3. Choose a Stacked Column Chart.
   
4. Adjust chart design as needed (for example, a black theme or color-coded bars).
   

If the chart displays unnecessary field buttons, remove them:

• Go to PivotChart Analyze → Field Buttons and deselect options such as Axis, Legend, and Value buttons for a cleaner look.
  

6. Add Interactive Slicers

1. Click anywhere in your PivotTable.
   
2. Go to Insert → Slicer.
   
3. Select fields like Product Line and Customer Type.
   
4. Arrange the slicers side by side, and set Columns = 2 under the Slicer tab to make them compact.
   

You can now click to filter data instantly by product or customer type.

To hide unused slicer options:

• Right-click the slicer → Slicer Settings → Hide Items with No Data.
  
• You can also remove the header by unchecking Display Header.
  

7. Format and Arrange Everything

• Resize and reposition slicers to fit above or beside your chart.
  
• Adjust chart title (e.g., Average Written Premium by Agent and Lead Source).
  
• Make sure slicers, heat map, and chart are aligned neatly for easy interpretation.
  

8. Analyze Your Results

Now you can interactively filter by:

• Agent (e.g., Ava Thompson, Jason Rivera, etc.)
  
• Lead Source (e.g., Facebook Ads, Referrals, Email Campaigns)
  
• Product Line or Customer Type
  

This setup makes it simple to identify which agents and lead sources consistently produce higher written premiums and where to focus your sales or marketing efforts.

Result

You now have a fully dynamic dashboard that includes a PivotTable, heat map, PivotChart, and slicers, all tied together to visualize the average written premium by agent and lead source. This structure lets you see exactly which agents, products, and marketing channels are driving premium performance in real time.