You just spent 40 minutes hunting for a lead’s phone number. It was buried in a text thread, a sticky note, and a half-finished spreadsheet with three different versions. Meanwhile, a hot buyer went cold because nobody followed up on time.
This happens to solo agents and small brokerages every week. Manual tracking leads to missed follow-ups, duplicate entries, and no clear picture of what’s actually in your pipeline. The fix doesn’t require expensive software. A simple Excel CRM dashboard with a few formulas can show you every lead, every follow-up, and your expected commission at a glance.
In this tutorial, you’ll build one from scratch. It takes about 30 minutes, and you don’t need any coding experience.
The Scenario and Dummy Data
Let’s say you’re Dana Whitfield, a solo agent with a small real estate practice in Austin, Texas. You’re working with about a dozen active leads at different stages, and you keep losing track of who needs a call this week.
Here’s the sample data we’ll use throughout the tutorial:
| Lead Name | Lead Source | Stage | Est. Home Price | Last Contact | Next Follow-Up |
|---|---|---|---|---|---|
| Marcus Reyes | Zillow | Showing | $485,000 | 09/22/2026 | 09/29/2026 |
| Priya Nair | Referral | Offer Made | $620,000 | 09/25/2026 | 10/02/2026 |
| Tom & Julie Baker | Open House | New Lead | $375,000 | 09/28/2026 | 10/05/2026 |
| Angela Brooks | Under Contract | $540,000 | 09/20/2026 | 10/08/2026 | |
| Devon Carter | Zillow | Showing | $410,000 | 09/18/2026 | 09/26/2026 |
| Sam Okafor | Referral | Closed | $455,000 | 09/10/2026 | 10/15/2026 |
| Lisa Tran | Open House | New Lead | $350,000 | 09/29/2026 | 10/06/2026 |
| Greg Holloway | Offer Made | $590,000 | 09/24/2026 | 10/01/2026 |
For the commission math, we’ll assume a 3% commission rate. Type that rate into cell K2 and label J2 as “Commission Rate.”
Step 1: Set Up Your Lead Tracker as an Excel Table
Open a blank workbook and rename Sheet1 to Leads. Type the column headers from the table above into row 1, starting in cell A1. Then enter the sample data below them.
Now turn the range into a proper Excel Table. Click any cell in your data, then press Ctrl + T (or Cmd + T on a Mac). Check the box for My table has headers and click OK.
[INSERT SCREENSHOT: The Create Table dialog box in Excel with the data range $A$1:$F$9 selected and the “My table has headers” box checked]
Why a Table Instead of a Plain Range?
A Table grows automatically. When you add a new lead in row 10, every formula, chart, and dropdown that points to the Table updates on its own.
With a plain range, you’d have to edit formula ranges by hand every time you add a lead. That’s exactly the kind of small chore that causes errors later. Click Table Design and rename the table to tblLeads in the Table Name box so your formulas are easy to read.
Step 2: Add a Stage Dropdown with Data Validation
Typos ruin dashboards. If one row says “Showing” and another says “showing ” with a trailing space, your counts will be wrong. A dropdown list prevents that.
First, create a small list of stages on a new sheet. Name the sheet Lists and type these in cells A1:A5: New Lead, Showing, Offer Made, Under Contract, Closed.
Go back to the Leads sheet and select the entire Stage column data (cells C2:C9). Click Data > Data Validation. Under Allow, choose List. In the Source box, click the small arrow and select Lists!$A$1:$A$5, then click OK.
[INSERT SCREENSHOT: The Data Validation dialog box with “List” selected under Allow and the Source field showing =Lists!$A$1:$A$5]
How It Works
Data Validation tells Excel to accept only the values on your approved list. Think of it like a bouncer at the door who only lets in names on the guest list. Anyone typing something different gets a warning, so your stage names stay perfectly consistent.
Step 3: Add a Follow-Up Status Column with a Formula
Now for the feature that saves the most time: a column that tells you at a glance which leads need attention. In cell G1, type Follow-Up Status. Then in cell G2, enter this formula:
=IF([@[Stage]]="Closed","Done",IF([@[Next Follow-Up]]<TODAY(),"Overdue",IF([@[Next Follow-Up]]=TODAY(),"Due Today","Upcoming")))
Press Enter. Excel fills the formula down the whole column automatically because you’re working inside a Table.
[INSERT SCREENSHOT: The Leads sheet showing the Follow-Up Status column filled in with Overdue, Due Today, Upcoming, and Done values]
Breaking Down the Logic
This formula asks a series of yes-or-no questions, top to bottom, and stops at the first “yes.”
- First question: Is the deal closed? If so, show “Done.” There’s no reason to follow up on a closed sale.
- Second question: Is the follow-up date earlier than today? If so, show “Overdue.”
- Third question: Is the follow-up date exactly today? If so, show “Due Today.”
- Everything else: Show “Upcoming.”
TODAY() is a built-in function that returns the current date. Because it refreshes every time you open the file, your statuses stay accurate without any manual updates.
Add Color Alerts
Select the Follow-Up Status column. Click Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Type Overdue and choose Light Red Fill with Dark Red Text. Repeat the process for Due Today using Yellow Fill with Dark Yellow Text.
Step 4: Calculate Expected Commission
Add a new column in H1 called Est. Commission. In H2, enter:
=[@[Est. Home Price]]*$K$2
Format the column as currency by selecting it and clicking Home > Number Format > Currency. Marcus Reyes’s $485,000 home should now show an estimated commission of $14,550.
The $K$2 part is called an absolute reference. The dollar signs lock the formula onto that one cell, so if you change your commission rate from 3% to 2.5%, every row updates at once.
Step 5: Build the Dashboard Sheet
Insert a new sheet and name it Dashboard. This is the page you’ll actually look at each morning. We’ll build four summary numbers.
In the Dashboard sheet, type these labels in column A and formulas in column B:
| Label | Formula |
|---|---|
| Total Active Leads | =COUNTIFS(tblLeads[Stage],"<>Closed") |
| Overdue Follow-Ups | =COUNTIFS(tblLeads[Follow-Up Status],"Overdue") |
| Pipeline Value | =SUMIFS(tblLeads[Est. Home Price],tblLeads[Stage],"<>Closed") |
| Expected Commission | =SUMIFS(tblLeads[Est. Commission],tblLeads[Stage],"<>Closed") |
[INSERT SCREENSHOT: The Dashboard sheet showing four summary cells with labels and calculated values, formatted as large bold numbers]
Why These Formulas Work
COUNTIFS counts rows that meet a condition. Here, it counts every lead whose stage is not “Closed.” The "<>Closed" piece is Excel’s way of saying “not equal to Closed.”
SUMIFS works the same way but adds up dollar amounts instead of counting rows. It looks at your price column and totals only the rows that pass your condition. Using column names like tblLeads[Stage] instead of cell ranges keeps everything readable and updates itself as your list grows.
Add a Pipeline Chart
Click any empty cell on the Dashboard. Click Insert > PivotChart, choose From Table/Range, and select tblLeads. Drag Stage to the Axis area and Est. Home Price to the Values area. You’ll get a bar chart showing how much money sits in each stage.
Pro-Tip and Troubleshooting
Fix: Formulas Show #NAME? After Renaming the Table
If your dashboard formulas suddenly display #NAME?, the table name likely changed or has a typo. Click inside your data, open Table Design, and check the Table Name box. It needs to read tblLeads exactly.
Fix: Follow-Up Dates Won’t Calculate
If Overdue never appears, your dates may be stored as text. Select the date columns and click Home > Number Format > Short Date. If they still look left-aligned, click Data > Text to Columns > Finish to force Excel to recognize them as real dates.
Pro-Tip: Filter to Today’s Call List
Every morning, click the dropdown arrow on the Follow-Up Status header and uncheck everything except Overdue and Due Today. That’s your call list, ready in two clicks. You can also sort by Est. Home Price to prioritize the biggest deals first.
Conclusion
You now have a working CRM that flags overdue follow-ups, tracks your pipeline value, and estimates your commission automatically. It costs nothing beyond the Excel you already own.
Start by entering your real leads this week, even if you only add five. Open the Dashboard each morning before your first call, and you’ll spot problems before they cost you a deal.