Build an Insurance Leads Tracker

Introduction

30 Second Summary

Following up with insurance leads means juggling Facebook messages, phone calls, and mental notes about who to contact next. One forgotten follow-up can mean a lost client.

In this project, you will build a leads tracker in Google Sheets that organizes every prospect from first contact to conversion. The tracker flags overdue follow-ups automatically so you never have to rely on memory.

What You'll Build

You open your tracker between client visits and instantly see who needs follow-up today, color-coded by urgency with overdue leads jumping out in bold red.

By the end of this project, you'll have:

• A color-coded leads tracker where you add a new lead through dropdown menus and watch the entire row light up based on their status.
• Automatic overdue alerts that flag any lead whose follow-up date has passed, so you never miss a callback.
• One-click filters that switch between your prospect list and active leads without copying data between sheets.
• Secret Mission: A conversion rate calculator that reveals which lead source produces the most paying clients.

Do I need any spreadsheet experience?

All you need is a Google account (Gmail). This project covers every formula from scratch.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your insurance sales workflow.

Set Up Your Spreadsheet and Add Sample Leads

Right now, your leads are scattered across Facebook conversations, phone contacts, and memory. When a prospect messages you on Messenger asking about insurance, there is no single place to log their details and track what happens next.

This step creates your home base. You will build a structured spreadsheet with clear columns for every piece of lead information you need, then fill it with sample data so you can see the tracker take shape.

In this step, get ready to:

• Create a new Google Sheet and name it.
• Add 10 column headers for tracking lead information.
• Enter 5 sample leads with a mix of statuses and sources.

Create a new Google Sheet

Google Sheets is free with any Google account and runs entirely in your browser. No downloads needed.

• Open your browser and go to sheets.google.com.
• Click the Blank spreadsheet option (the large + icon) to create a new sheet.

A new spreadsheet opens with the default title Untitled spreadsheet in the top-left corner.

• Click the title Untitled spreadsheet in the top-left corner of the page.
• Type Insurance Leads Tracker and press Enter.

The spreadsheet title now reads Insurance Leads Tracker at the top of the page.

Still seeing Untitled spreadsheet?

Make sure you clicked the title text itself, not the area around it. A text field should appear for you to type in. After typing the new name, press Enter or click anywhere else on the page to confirm.

Help me rename my Google Sheet

Set up your column headers

Column headers define what information you track for each lead. Your tracker needs columns for contact details, lead categorization, and follow-up scheduling.

• Click cell A1.
• Type each header below, pressing Tab after each one to move to the next cell:

You should see 10 headers spanning from column A to column J across row 1.

Why these 10 columns?

The columns are grouped by purpose. Columns A through C hold contact details (name, phone, Facebook). Columns D through G categorize each lead (where they came from, what product they want, how warm they are, and how you reached them).

Columns H and I track timing (when you last spoke and when to follow up). Column J is for free-form notes. This layout keeps your most-referenced columns visible without scrolling.

• Click the row number 1 on the far left to select the entire header row.
• Press Cmd+B (macOS) or Ctrl+B (Windows) to bold all headers.

All 10 headers should now appear in bold. Next, you will freeze this row so it stays visible when your lead list grows.

• Click View in the menu bar.
• Click Freeze.
• Select 1 row.

What does freezing do?

Freezing pins row 1 at the top of your screen. When your lead list grows to 20, 50, or 100 rows and you scroll down, the headers stay visible so you always know which column is which.

• Scroll down several rows to test that the freeze is working.

Your bold headers should stay locked at the top of the sheet, even as you scroll past rows 10, 20, and beyond. A thin gray line appears below row 1 to show it is frozen.

Headers disappearing when you scroll?

Go back to View then Freeze and confirm 1 row is selected. If it says No rows, click 1 row again.

Help me freeze my header row in Google Sheets

Enter 5 sample leads

Sample data lets you see the tracker in action before using it with real leads. You will enter 5 leads with a variety of statuses, sources, and dates.

Dates in Google Sheets

Google Sheets recognizes most date formats. Type dates in whatever format feels natural, like 8/11/2026 or Aug 11, 2026. For the sample data below, you will enter dates relative to today's date.

• Click cell A2 and enter the following data for your first sample lead, pressing Tab after each value to move to the next cell:

Your first lead should now fill row 2 from column A through column J.

• Enter 4 more leads in rows 3 through 6, following the same pattern. Use the data below for each row:

• Row 3:
• Row 4:
• Row 5:
• Row 6:

Why are some Follow-Up Dates in the past?

Rows 4 and 5 intentionally have Follow-Up Dates that have already passed. In a later step, you will build a formula that automatically flags these as overdue. Having past dates in your sample data means you will be able to see that feature in action right away.

Before you read on, take a close look at your Status column (column F). Do all five status values look perfectly consistent?

You typed carefully from a reference, so they probably match. But imagine typing statuses from memory during a busy day. You might type hot in one row and Hot in another, or accidentally type Warm with a trailing space. Google Sheets treats each of these as a different value, which breaks filtering and counting later.

✔️ Awesome, I've got everything!

Great. Confirm you have saved your spreadsheet. Google Sheets auto-saves, but you can double check by looking for the text "All changes saved in Drive" at the top of the page.

ⓧ I'd like to double check my spreadsheet

Scroll up and confirm each of these is in place:

• The spreadsheet title reads Insurance Leads Tracker.
• Row 1 has 10 bold headers from Name (A1) through Notes (J1).
• Row 1 is frozen (a thin gray line appears below it when you scroll down).
• Rows 2 through 6 contain 5 sample leads with a mix of statuses (Hot, Prospect, Warm, Cold, Contacted) and sources (Facebook, Referral, Event).
• At least 2 leads have a Follow-Up Date that is already in the past.

Your spreadsheet has structure and data, but that inconsistency problem with manually typed statuses is real. Next up, you will add dropdown menus that enforce clean, consistent data every time you log a lead.

Add Dropdown Menus for Clean Data Entry

Your spreadsheet has 10 columns and 5 sample leads ready to go. Now it's time to lock down how data gets entered.

Think about the Status values you typed in the last step. You might have "hot" in one row and "Hot" in another.

These small differences will break your filters and formulas later. Dropdown menus fix this by restricting each cell to a predefined list of options, keeping every lead's data consistent.

In this step, get ready to:

• Create a Status dropdown with colored chips for visual tracking.
• Create a Source dropdown for lead origins.
• Create a Contact Method dropdown for communication channels.

Create a Status dropdown with colored chips

The Status column tracks where each lead sits in your sales pipeline. Adding a dropdown with colored chips makes it easy to pick the right status and spot it at a glance.

• Select cells F2 through F100 in the Status column. Starting at F2 skips the header, and ending at F100 leaves room for future leads.
• Click Data in the menu bar.
• Click Data validation.
• Click Add rule.

What is data validation?

Data validation controls what values can be entered in a cell. When you create a dropdown rule, the cell only accepts values from your predefined list.

This prevents typos like "hot" vs "Hot" from sneaking in and breaking your formulas later.

• Under Criteria, select Dropdown.
• Enter each of the following options. Click the color circle to the left of each item to assign its color:
• Click Done.

The dropdown rule is now applied to cells F2 through F100.

• Return to your sample data in rows 2-6.
• Click any Status cell (for example, F2). A dropdown should appear with all 7 options as colored chips.
• Select the correct status for each lead in rows 2-6 using the dropdown. This replaces your manually typed values with clean, dropdown-selected ones.

Each Status cell now shows a small colored chip next to the text. That color comes from the dropdown option you assigned.

Don't see the dropdown when clicking a cell?

• Make sure you selected the range starting at F2, not F1. The header cell should not have a dropdown.
• Verify you clicked Done to save the rule. If you closed the sidebar without clicking Done, the rule was not applied.
• Check that the cell is within the range F2:F100. Cells outside this range will not have the dropdown.

Help me troubleshoot my data validation dropdown in Google Sheets.

Create a Source dropdown

The Source column tracks where each lead came from. A dropdown here ensures you use the same labels every time, which matters when you count leads by source later.

• Select cells D2 through D100 in the Source column.
• Click Data in the menu bar.
• Click Data validation.
• Click Add rule.
• Under Criteria, select Dropdown and enter these 4 options:
• Click Done.
• Return to your sample data and click each Source cell in rows 2-6 to re-select a value from the new dropdown.

Click any Source cell to confirm the dropdown appears with all 4 options.

Create a Contact Method dropdown

This column records how you communicate with each lead. Keeping it consistent matters when you want to filter by communication channel later.

• Select cells G2 through G100 in the Contact Method column.
• Click Data in the menu bar.
• Click Data validation.
• Click Add rule.
• Under Criteria, select Dropdown and enter these 4 options:
• Click Done.
• Return to your sample data and click each Contact Method cell in rows 2-6 to re-select a value from the new dropdown.

Click any Contact Method cell to confirm the dropdown appears with all 4 options.

Before you move on, look at all three columns with dropdowns. What visual difference do you notice between the Status column and the Source or Contact Method columns?

The Status column shows a small colored chip inside each cell. The Source and Contact Method cells just show plain text. Those colored chips help, but they only color one small cell per row. With dozens of leads, you would still need to scan each tiny chip individually to find your Hot leads.

Why only the Status column has colored chips

Google Sheets dropdown options can have per-option chip colors, but this only affects the cell itself. To color an entire row based on a lead's status, you need a different tool called conditional formatting. That is exactly what the next step covers.

Your data entry is now clean and consistent across three key columns. Next up, you'll color-code entire rows so your hottest leads jump out the moment you open the sheet.

Color-Code Entire Rows by Lead Status

Your spreadsheet now has dropdown menus that keep data clean and consistent. The Status column even shows colored chips for each option.

Those chips only color one tiny cell per row. When you have 10 or more leads, you still have to squint at small squares to spot your Hot leads. In this step, you will color-code entire rows so urgency is visible from across the room.

In this step, get ready to:

• Set up conditional formatting rules that color entire rows based on lead status.
• Test the rules by changing a lead's status and watching the row change color instantly.

Add a conditional formatting rule for Hot leads

Right now, your Status dropdown chips add a small splash of color to column F. Conditional formatting takes this further by changing the appearance of entire rows based on a cell's value.

What is conditional formatting?

Conditional formatting is a spreadsheet feature that automatically changes how cells look based on rules you define. You will create a rule that says "if the Status column equals Hot, color the entire row light red."

The formatting updates instantly whenever you change a cell's value. No manual recoloring needed.

• Select the range A2:J100 by clicking cell A2, then holding Shift and clicking cell J100. This covers all your data columns with room for future leads.
• Click Format in the menu bar.
• Click Conditional formatting.
• A sidebar opens on the right. Under Format cells if, click the dropdown and select Custom formula is.
• In the formula field, type =$F2="Hot".
• Under Formatting style, click the fill color icon (the paint bucket) and select a light red.
• Click Done.

Why does the formula use =$F2?

The dollar sign before F locks the formula to column F (your Status column) no matter which cell in the row is being checked. Without the dollar sign, the formula would shift columns as it moves across the row.

The row number 2 stays relative so the formula checks each row independently. Row 3 checks $F3, row 4 checks $F4, and so on.

Look at your sheet. Any row where the Status is Hot should now have a light red background stretching across all columns.

Row not turning red?

• Make sure the Status cell for that row says exactly Hot (selected from the dropdown, not manually typed). Re-select the value from the dropdown to be sure.
• Double-check that your formula reads =$F2="Hot" with a dollar sign before the F. A missing dollar sign causes the formula to check the wrong column.
• Confirm the range is set to A2:J100 in the conditional formatting sidebar. If only column F is selected, only that cell will change color.
• Still stuck? Why isn't my conditional formatting rule coloring the full row?

Add rules for the remaining statuses

Five statuses still need their own color rules. The process for each is identical to what you just did for Hot.

Same process for each rule

For every rule below, the steps are the same:

• Click Add another rule in the conditional formatting sidebar.
• Select Custom formula is under Format cells if.
• Enter the formula, set the background color, and click Done.

• Click Format in the menu bar.
• Click Conditional formatting to reopen the sidebar.
• Add a rule with the formula =$F2="Warm" and set the background color to light orange.
• Add another rule with the formula =$F2="Cold" and set the background color to light blue.

Check your sheet. Rows with a Warm status should now show a light orange background. Cold rows should show light blue.

• Return to the conditional formatting sidebar and add a rule with the formula =$F2="Converted" and set the background color to light green.
• Add another rule with the formula =$F2="Lost" and set the background color to light gray.
• Add one final rule with the formula =$F2="Contacted" and set the background color to pale yellow.

What about Prospect rows?

Prospect rows stay white with no formatting rule. This is intentional. Keeping them visually neutral makes the active statuses (Hot, Warm, Cold) pop by contrast.

Check your sheet again. You should now see the Converted, Lost, and Contacted rows each showing their assigned background color.

Time to test everything. Before you make any changes, look at one of your sample leads and predict what color the row will turn if you change its status to Hot.

• Click a Status cell in your sample data (any row from 2 to 6).
• Select Hot from the dropdown.

The entire row turns light red instantly. Now change that same lead's status to Warm. The row switches to light orange. Each status has its own distinct color across the full row.

Colors not changing when you switch statuses?

• Open Format > Conditional formatting and verify all six rules are listed in the sidebar. Each one should show its formula and background color.
• Make sure every rule applies to the range A2:J100. If a rule only covers column F, it will only color that one cell.
• Check that each formula starts with =$F2 (dollar sign before the F, no dollar sign before the 2). A missing or misplaced dollar sign breaks the row-level coloring.
• Still stuck? My conditional formatting rules are not applying to entire rows when I change the Status dropdown. Help me fix this.

Your sheet is now visually scannable at a glance. Hot leads jump out in red, cold leads fade to blue, and converted leads glow green. Next up, you will add formulas that automatically flag overdue follow-ups so no lead slips through the cracks.

Track Follow-Ups with Formulas

Your spreadsheet now lights up with color the moment you set a lead's status. Hot leads glow red, Cold leads turn blue, and you can scan the whole sheet at a glance.

But color only tells you temperature, not timing. A lead marked Hot three weeks ago with no follow-up is slipping through the cracks right now. In this step, you'll add formulas that calculate how long it's been since your last contact and automatically flag overdue follow-ups.

In this step, get ready to:

• Add a formula that calculates the number of days since your last contact with each lead.
• Create an automatic "OVERDUE" flag for leads whose follow-up date has passed.
• Format overdue flags in bold red and extend your row colors to cover the new columns.

Add a Days Since Contact formula

Knowing a lead's status is helpful, but knowing when you last reached out is just as important. A simple formula can calculate the number of days since your last contact with each lead.

• Click on cell K1.
• Type Days Since Contact and press Enter.
• Bold the header by clicking on K1 and pressing Cmd+B (macOS) or Ctrl+B (Windows).
• Click on cell K2 and enter the following formula:

=IF(H2="", "", TODAY()-H2)

What does this formula do?

• IF(H2="", "", ...) checks if the Last Contacted cell is empty. If it is, the cell stays blank instead of showing an error.
• TODAY()-H2 subtracts the Last Contacted date from today's date. The result is the number of days since your last contact.

• Press Enter to confirm the formula.
• Click on cell K2 again and copy it by pressing Cmd+C (macOS) or Ctrl+C (Windows).
• Select the range K3 through K100 by clicking on K3, scrolling down, and holding Shift while clicking K100.
• Paste the formula by pressing Cmd+V (macOS) or Ctrl+V (Windows).

Check column K in your sample data rows. Every row that has a date in the Last Contacted column (H) now shows a number. Rows without a Last Contacted date show nothing.

Seeing 0 or a negative number?

• A 0 means the Last Contacted date is today. That's correct.
• A negative number means the Last Contacted date is in the future. Double-check that your sample dates in column H are in the past.
• If you see a date like 01/01/1900 instead of a number, the cell may be formatted as a date. Right-click the cell, select Format cells, and choose Number.

Help me fix my Days Since Contact formula.

Add an Overdue flag formula

The Days Since Contact column tells you how long it's been, but it doesn't tell you whether you've missed a planned follow-up. The next formula checks each lead's Follow-Up Date against today and flags the ones you need to act on.

• Click on cell L1.
• Type Overdue? and press Enter.
• Bold the header by clicking on L1 and pressing Cmd+B (macOS) or Ctrl+B (Windows).
• Click on cell L2 and enter the following formula:

=IF(AND(I2<>"", I2<TODAY(), F2<>"Converted", F2<>"Lost"), "OVERDUE", "")

What does this formula do?

This formula checks four conditions at once using AND(). All four must be true for the flag to appear.

• I2<>"" confirms a Follow-Up Date exists in that row.
• I2<TODAY() checks whether that date has already passed.
• F2<>"Converted" and F2<>"Lost" skip leads that are already closed. There's no point flagging a converted client as overdue.

If all four conditions are true, the cell shows "OVERDUE". Otherwise, it stays blank.

• Press Enter to confirm the formula.
• Click on cell L2 again and copy it by pressing Cmd+C (macOS) or Ctrl+C (Windows).
• Select the range L3 through L100 by clicking L3, scrolling down, and holding Shift while clicking L100.
• Paste by pressing Cmd+V (macOS) or Ctrl+V (Windows).

Check column L in your sample data. Any lead with a Follow-Up Date in the past and a status that isn't Converted or Lost now shows "OVERDUE". Leads with future dates or no Follow-Up Date show nothing.

Not seeing OVERDUE where you expect it?

• Make sure the Follow-Up Date in column I is a real date, not plain text. If the cell doesn't respond to date formatting, re-enter the date by typing it directly (e.g. 08/01/2026).
• Check that the Follow-Up Date is in the past. A date set to tomorrow or later won't trigger the flag.
• Check the Status column. Leads with a status of Converted or Lost are intentionally excluded by the formula.

Help me troubleshoot my Overdue formula.

Make overdue flags visually obvious

The "OVERDUE" text works, but it blends in with the rest of the data. Bold red formatting makes it impossible to miss.

• Select the range L2 through L100.
• Click Format in the menu bar, then click Conditional formatting.
• Under "Format cells if," select Text contains.
• Type OVERDUE in the value field.
• Under Formatting style, click the Bold button and change the text color to red.
• Click Done.

Check column L. Any cell showing "OVERDUE" now appears in bold red text. Much harder to ignore.

Why conditional formatting instead of manual formatting?

Conditional formatting applies formatting automatically based on rules. When a lead's Follow-Up Date passes and the formula outputs "OVERDUE", the bold red style kicks in on its own. You never have to manually color a cell.

Now you need to update your existing row-color rules. Right now, those rules only cover columns A through J. Your two new columns (K and L) are outside that range, so the row colors stop short.

• Click Format in the menu bar, then click Conditional formatting.
• In the sidebar, you'll see your existing rules (Hot, Warm, Cold, Converted, Lost, Contacted). Each one applies to the range A2:J100.
• Click the first rule to edit it.
• Change the range from A2:J100 to A2:L100.
• Click Done.
• Repeat for each remaining rule (Warm, Cold, Converted, Lost, Contacted), changing each range from A2:J100 to A2:L100.

Six rules to update

You have six row-color rules (Hot, Warm, Cold, Converted, Lost, Contacted). Click each one, change J100 to L100 in the range field, and click Done. It takes about 30 seconds once you get into a rhythm.

Before you check the result, think about what you expect to see. If you have a Hot lead whose Follow-Up Date is in the past, what should that row look like now?

• Find one of your sample leads that has a Follow-Up Date in the past and an active status (not Converted or Lost). If none do, change one lead's Follow-Up Date to a past date.

The entire row should be colored by its status from column A all the way through column L. The "OVERDUE" text in column L should appear in bold red.

Row color stops at column J?

• You likely missed updating one of the six rules. Open Format then Conditional formatting and check that every row-color rule shows A2:L100 as its range.
• If the OVERDUE text is not bold red, make sure the "Text contains" rule you created applies to the range L2:L100 and that the value is OVERDUE (all caps).

Help me fix my conditional formatting rules.

Your spreadsheet now catches overdue leads automatically. Every time you open the sheet, the formulas recalculate and flag anyone you need to follow up with today. Next up, you'll add filters to switch between your prospect list and active leads in one click, then build a summary dashboard.

Filter Your Views and Build a Summary Dashboard

Your spreadsheet now catches overdue follow-ups the moment they happen. That solves one problem, but there is another one lurking: prospects and active leads are mixed together in one long list.

In this step, you will add filters that let you switch between a Prospect view and an Active Leads view in one click. Then you will build a summary dashboard that counts your leads by status and source so you can see the big picture without scrolling.

In this step, get ready to:

• Enable filters on all columns to instantly switch between Prospect view and Active Leads view.
• Build a summary dashboard with formulas that count your leads by status, source, and overdue flags.

Enable filters on your data

Filters let you hide rows that do not match a condition and show only the ones you care about. The filtered rows are not deleted. They are just hidden until you remove the filter.

• Click any cell inside your data, for example cell A1.
• Click Data in the menu bar.
• Click Create a filter.

Small filter icons appear in every column header. Each icon is a dropdown that lets you choose which values to show or hide in that column.

What is a filter?

A filter temporarily hides rows that do not match your selected values. Your data stays intact. Filters only change which rows are visible, not which rows exist.

This means you can switch between a "prospects only" view and an "active leads only" view without copying data between sheets.

Time to test your filter. Try switching to a Prospect-only view.

• Click the filter icon on the Status column header (F1).
• Uncheck all statuses except Prospect.
• Click OK.

Only your Prospect rows appear. Every other status is hidden. This is your Prospect View.

• Click the filter icon on the Status column again.
• Uncheck Prospect, then check Hot, Warm, and Cold.
• Click OK.

Now only your active leads (Hot, Warm, Cold) are visible. This is your Active Leads View.

• Click the filter icon on the Status column one more time.
• Check Select all to bring back all rows.
• Click OK.

All five sample rows are back. Filters work on any column, not just Status.

• Try the filter icon on the Source column (D1) and uncheck everything except Facebook to see only Facebook-sourced leads.
• Reset the filter by clicking the Source filter icon again and checking Select all, then clicking OK.

Don't see filter icons in your headers?

Make sure you clicked a cell inside your data (like A1) before clicking Data > Create a filter. If you clicked an empty cell far away from your data, Google Sheets may not detect your data range.

If you already have a filter active and want to start fresh, click Data > Remove filter, then try again.

Still stuck? Help me troubleshoot why my Google Sheets filter icons are not showing up.

Build a summary dashboard

Filters are great for drilling into specific rows. But sometimes you want the big picture at a glance: how many Hot leads do you have right now? How many leads came from Facebook?

You will build a summary dashboard to the right of your data in columns N and O. Placing it here keeps it always visible, because filters hide rows, not columns.

• Before you start, make sure all your filters are cleared so every row is visible. Click the Status filter icon and check Select all if it is not already selected.
• Click cell N1 and type DASHBOARD.
• Bold it by pressing Ctrl+B (Windows) or Cmd+B (macOS).
• Click cell N3 and type Status Summary. Bold it.

Now you will enter the first status label and its counting formula. This formula uses a function called COUNTIF that counts how many cells in a range match a specific value.

• Click cell N4 and type Hot Leads.
• Click cell O4 and enter this formula:

=COUNTIF(F:F, "Hot")

What does COUNTIF do?

COUNTIF scans an entire column and counts every cell that matches the value you specify. =COUNTIF(F:F, "Hot") looks at every cell in column F (your Status column) and counts how many say "Hot".

Every formula you add in the dashboard follows this same pattern. Only the column letter and the value in quotes change.

Check cell O4. It should show a number that matches how many of your sample leads have a "Hot" status.

Now add the remaining six status labels and formulas following the same pattern.

• In cell N5, type Warm Leads. In cell O5, enter:

=COUNTIF(F:F, "Warm")

• In cell N6, type Cold Leads. In cell O6, enter:

=COUNTIF(F:F, "Cold")

• In cell N7, type Prospects. In cell O7, enter:

=COUNTIF(F:F, "Prospect")

• In cell N8, type Contacted. In cell O8, enter:

=COUNTIF(F:F, "Contacted")

• In cell N9, type Converted. In cell O9, enter:

=COUNTIF(F:F, "Converted")

• In cell N10, type Lost. In cell O10, enter:

=COUNTIF(F:F, "Lost")

Look at the numbers in cells O4 through O10. Each count should match the number of sample leads you gave that status. If you have two Hot leads in your data, O4 should show 2.

Seeing 0 for a status you know you have?

The value inside the quotes must match your dropdown option exactly. "Hot" is not the same as "hot" or "HOT". Double-check that the capitalization in your formula matches the capitalization in your dropdown.

Also make sure the formula points to column F (your Status column), not a different column.

Still stuck? Help me figure out why my COUNTIF formula shows 0 when I know I have matching data in my Google Sheet.

Status Summary is done. Now add a Source Summary section below it to see where your leads are coming from.

• Click cell N12 and type Source Summary. Bold it.
• In cell N13, type Facebook. In cell O13, enter:

=COUNTIF(D:D, "Facebook")

Why column D this time?

The Status formulas counted column F because that is where your Status dropdown lives. The Source formulas count column D because that is your Source column. The function is the same. Only the column letter changes to match the data you want to count.

• In cell N14, type Referral. In cell O14, enter:

=COUNTIF(D:D, "Referral")

• In cell N15, type Event. In cell O15, enter:

=COUNTIF(D:D, "Event")

• In cell N16, type Other. In cell O16, enter:

=COUNTIF(D:D, "Other")

Check cells O13 through O16. The counts should match the number of leads you assigned to each source in your sample data.

One more formula to go. You will add an overdue count so the dashboard surfaces how many leads need immediate attention.

• Click cell N18 and type Overdue Leads.
• Click cell O18 and enter this formula:

=COUNTIF(L:L, "OVERDUE")

What does this formula count?

This COUNTIF scans column L (your Overdue? column) and counts every cell that contains the text OVERDUE. This gives you a single number showing how many leads need follow-up right now.

Verify that the overdue count in O18 matches the number of OVERDUE flags you see in column L of your data.

✔️ Awesome, I've got everything!

Great. Your dashboard is complete. Make sure all your changes are saved (Google Sheets auto-saves, but you can confirm by checking for the "All changes saved in Drive" message in the menu bar).

ⓧ I'd like to double check the full dashboard

Here is every label and formula that should be in your dashboard area. Scroll through columns N and O and confirm each cell matches.

• N1: DASHBOARD (bold).
• N3: Status Summary (bold).
• N4: Hot Leads | O4: =COUNTIF(F:F, "Hot").
• N5: Warm Leads | O5: =COUNTIF(F:F, "Warm").
• N6: Cold Leads | O6: =COUNTIF(F:F, "Cold").
• N7: Prospects | O7: =COUNTIF(F:F, "Prospect").
• N8: Contacted | O8: =COUNTIF(F:F, "Contacted").
• N9: Converted | O9: =COUNTIF(F:F, "Converted").
• N10: Lost | O10: =COUNTIF(F:F, "Lost").
• N12: Source Summary (bold).
• N13: Facebook | O13: =COUNTIF(D:D, "Facebook").
• N14: Referral | O14: =COUNTIF(D:D, "Referral").
• N15: Event | O15: =COUNTIF(D:D, "Event").
• N16: Other | O16: =COUNTIF(D:D, "Other").
• N18: Overdue Leads | O18: =COUNTIF(L:L, "OVERDUE").

Time for a final check that ties everything together. Before you test, predict: if you filter the Status column to show only Hot leads, how many visible data rows should you see? Does that match the number in your dashboard next to Hot Leads?

• Click the filter icon on the Status column header.
• Uncheck everything except Prospect and click OK.

Only your Prospect rows appear, and the dashboard in columns N-O stays visible with accurate counts. The number of visible rows matches the Prospects count in your dashboard.

• Clear the filter by clicking the Status filter icon, checking Select all, and clicking OK. All rows return.

Secret mission

Calculate Your Conversion Rate by Lead Source

Your dashboard counts how many leads come from Facebook, referrals, and events. But which source actually turns into paying clients? Add conversion rate formulas to find out where your best leads really come from.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your spreadsheet or delete it entirely. This project runs entirely in Google Sheets, so there are no ongoing costs or running processes to worry about.

Resources you used:

• Insurance Leads Tracker spreadsheet in Google Drive.

Keep everything running

No action needed. Choose this if you plan to keep using or updating your leads tracker.

• Your spreadsheet stays in your Google Drive and takes up minimal storage.
• You can access it anytime from sheets.google.com or the Google Sheets app on your phone.
• Start adding your real leads whenever you are ready.

Pause - I'll come back to this later

Since Google Sheets runs in your browser with no background processes, there is nothing to pause. Your spreadsheet stays in Google Drive and is always ready when you return.

• Close the browser tab when you are done. Your work is saved automatically.
• To pick up later, go to sheets.google.com and open Insurance Leads Tracker from your recent files.

Delete - I don't want to use this again

Remove the spreadsheet from your Google Drive completely.

• In the Insurance Leads Tracker spreadsheet, click File in the menu bar.
• Click Move to trash.
• The spreadsheet moves to your Google Drive trash and is permanently deleted after 30 days.

Nice Work!

Nice Work!

Nice work! You just turned a blank Google Sheets spreadsheet into a fully functional insurance leads tracker, complete with automated color-coding, overdue alerts, and a live dashboard.

You've learned how to:

• Build a structured leads tracker with dropdown menus that enforce clean, consistent data across Status, Source, and Contact Method columns.
• Set up conditional formatting that color-codes entire rows by lead status, and write formulas that automatically flag overdue follow-ups so no lead slips through the cracks.
• Use filters to instantly switch between Prospect and Active Leads views, and build a summary dashboard with COUNTIF formulas that show lead counts by status and source at a glance.
• Secret Mission: Added conversion rate formulas using COUNTIFS and IFERROR that reveal which lead source actually produces the most paying clients.

Ready to quiz yourself?