How to Track Written Premium by Agent in Excel

Premium Written by Agent by Month in Excel

Step 1: Clarify the goal

This report shows written premium by agent from month to month so you can:

• See where growth is coming from
  
• Spot which agents consistently drive production
  
• Visualize the data with both a stacked column chart and a heat map
  

The structure is: rows for agents, columns for months, and each cell is the premium that agent wrote in that month.

Step 2: Build the agent list with UNIQUE

Start by creating a clean list of agents:

1. Go to the column that contains the agent names in your raw data.
   
2. Use the UNIQUE function on that column to pull each agent name once.
   
3. If you reference the whole column, Excel may return a zero at the bottom because of blank rows.
   
4. If you want a static list instead of a dynamic one, copy the UNIQUE output and use Ctrl + C, then Ctrl + Shift + V to paste values only.
   

This gives you a neat list of agents down the left side of your summary table.

Step 3: Add the month headers

Across the top row of your summary table:

1. Type the first month (for example, January) into the first column header.
   
2. Fill across through December.
   

Now your layout is:

• First column: Agent name
  
• Next 12 columns: January through December
  

Step 4: Use SUMIFS to calculate written premium by agent and month

You now need a SUMIFS formula to pull written premium into each cell of the matrix.

The logic in each cell is:

• Sum the Written Premium column
  
• Only for rows where the Month = the column header (for example, January)
  
• And where the Agent Name = the agent in that row
  

Key points:

• SUMIF handles only one condition.
  
• SUMIFS supports multiple criteria (month and agent at the same time), which is what you need here.
  

So each cell combines:

• Sum range: the Written Premium column
  
• Criteria range 1: the Month column
  
• Criteria 1: the header (January, February, etc.)
  
• Criteria range 2: the Agent Name column
  
• Criteria 2: the agent name in the leftmost column of that row
  

When you enter the formula correctly, the January cell for a given agent will return that agent’s total written premium for January (for example, 37,000).

Step 5: Fix relative and absolute references

If you just drag the formula across and down without locking anything, Excel will shift all the references and return zeros or wrong values.

You need a mix of relative and absolute references:

• Lock the entire sum range column (Written Premium) so it does not move left or right.
  
• Lock the Month criteria range so it always points to the Month column.
  
• Lock the Agent criteria range so it always points to the Agent Name column.
  
• For the month header reference, lock the row (so as you copy down it stays on the header row) but let the column move as you go from January to February, March, and so on.
  
• For the agent name reference, lock the column (so it stays in the Agent Name column) but allow the row to move as you go down the list of agents.
  

You can use F4 to toggle dollar signs and control which part is locked:

• Locking the column keeps it fixed left to right.
  
• Locking the row keeps it fixed up and down.
  

Once the references are set correctly, you can copy the formula across all months and down all agents, and the entire premium matrix will populate correctly.

Step 6: Create a stacked column chart

To visualize the written premium by agent and month:

1. Highlight the summary table with agents and monthly premium values.
   
2. Go to Insert and choose Recommended Charts, or go directly to the All Charts tab.
   
3. Select a Stacked Column chart.
   

This stacked column chart lets you see:

• The total written premium per month
  
• How each agent contributes to that monthly total
  

You can then:

• Rename the chart title to something like “Written Premium by Agent by Month”.
  
• Adjust colors and formatting to match your dashboard style.
  

Step 7: Add a heat map across all agents

Next, turn the matrix into a heat map using Conditional Formatting:

1. Select the entire premium matrix (all agents and months).
   
2. Go to Home > Conditional Formatting > Color Scales.
   
3. Pick a scale where high values are green and low values are red (or flip it if high is “bad” in your context).
   

This version answers questions like:

• Which agent/month combination has the highest written premium overall?
  
• Which month stands out as the best or worst when you look across the entire team?
  

For example, you might see that “Michael Win – May” is the single top-performing cell across all agents and months.

Step 8: Create per-agent heat maps

Sometimes you want to compare an agent against themselves, not against everyone else.

To do that:

1. Select a single agent’s row in the premium matrix.
   
2. Apply the same Conditional Formatting > Color Scales rule.
   

Now, for that agent:

• Their best month (relative to their own history) will be the brightest green.
  
• Their weakest month will be the deepest red.
  

To quickly apply the same format to all agents:

1. Select the formatted row.
   
2. Click the Format Painter on the Home tab and double-click it so the painter stays active.
   
3. Click each remaining agent row one by one to apply the same color scale.
   

Double-clicking Format Painter lets you reuse it multiple times until you press Esc or click it again.

Step 9: Optional enhancements

A few quick refinements you can consider:

• Add data labels to the stacked column chart if you want values visible, though with many agents it can get noisy.
  
• Try a line chart if you filter to one or a few agents at a time.
  
• Create a drop-down list of agents and link the chart to a small subset (one to three agents) for cleaner comparisons over time.
  

This combination of SUMIFS, stacked column charts, and heat map conditional formatting gives you a powerful, visual view of written premium by agent and month.