How to Manage Prospects or Leads in Excel

You don’t always need a dedicated CRM to stay organized. Whether you’re working in sales, networking, job hunting, or managing client outreach, Excel can provide a simple and effective way to track prospects, record conversations, and prioritize follow-up activities.

In this tutorial, you’ll learn how to build a lead tracking spreadsheet in Microsoft Excel using Data Validation, drop-down lists, filters, and date formatting. This practical system makes it easy to organize your contacts, monitor progress, and quickly find the information you need.

Why I Created This

Not everyone has access to a full-featured CRM, and for many people, one would be more complex than necessary. This spreadsheet demonstrates how a few simple Excel features can create an effective system for organizing contacts, tracking conversations, and managing follow-up tasks – all with tools many people already have.

---

What You’ll Learn

• Create a simple lead or prospect tracking spreadsheet.
• Use Data Validation to create drop-down lists.
• Track contact status and follow-up dates.
• Record conversation results and notes.
• Filter your spreadsheet to quickly find prospects that need attention.
• Build a system that can be customized for sales, recruiting, networking, or customer follow-up.

---

Common Uses

Although this example focuses on sales prospects, the same system can be adapted for many purposes, including:

• Sales lead tracking
• Job search networking
• Recruiting candidates
• Customer follow-up
• Vendor outreach
• Membership management
• Volunteer coordination
• Fundraising contacts
• Personal relationship management

---

Practice & Resources (Practice Workbook)

Follow along using the Excel workbook created for this tutorial. It includes a sample lead tracker with drop-down lists, filters, and suggested columns so you can explore the features before customizing the spreadsheet for your own workflow.

The workbook is available as a view-only Google Sheet. You can make your own copy online or download it as a Microsoft Excel workbook.

Click the button below to view the resources available for this tutorial.

---

Requirements & Compatibility

Requirements

This tutorial uses standard Excel features, including:

• Data Validation
• Filters
• Date formatting
• Basic cell formatting

Compatibility

These features are available in Microsoft 365, Excel 2021, Excel 2019, and many earlier versions of Microsoft Excel.

---

Video Tutorial

---

Breaking Down the Feature

Data Validation

Data Validation allows you to create a list of predefined options that users can select from a drop-down menu.

In this workbook, Data Validation is used to standardize information such as:

• Contacted (Yes/No)
• Contact Result (Interested, Left Voicemail, Follow Up, Not Interested, etc.)

Using drop-down lists helps ensure consistent data entry and makes filtering much more effective.

---

Step-by-Step Instructions

This guide walks through building a simple, organized prospect-tracking system in Excel, based on a walkthrough of the technique.

1. Set Up Your Prospect List

1. Start with your list of prospect names in a column.
2. Select column A, right-click, and choose Insert to add a new blank column to the left. This will hold your “Contacted?” status.

2. Add a “Contacted?” Dropdown (Data Validation)

Use this column to mark whether you’ve reached out to a prospect yet.

1. Highlight the cells next to each prospect’s name (the new column you inserted).
2. Go to the Data tab, click Data Validation, then Data Validation again.
3. Under Allow, select List.
4. In the Source field, type: Yes, No
5. Click OK.

Each cell in that column now has a dropdown letting you select Yes or No. You can press Delete to clear a selection without removing the dropdown.

3. Add a “Date of Contact” Column

1. Add a new column header called Date of Contact.
2. Select the column, right-click, choose Format Cells, and select Date.
3. Choose your preferred date format (e.g., four-digit year) and click OK.

This keeps your dates entered consistently.

4. Create a Standardized “Results” Dropdown

Rather than typing free-form outcomes (which get inconsistent), build a dropdown from a predefined list.

1. Add a new sheet/tab and name it Results.
2. In that tab, list your possible outcomes in a column (e.g., Interested, Not Interested, No Answer, Left Voicemail, Follow Up Later, Wrong Number, Do Not Contact). Label the top cell so you remember what the list is for.
3. Select all the result options (not the label).
4. Click the Name Box (top-left, where the cell reference normally shows) and type a name for this range, e.g., results. Press Enter.
   • Note: if you add new options later at the bottom of the list, they won’t automatically be included. Insert new rows in the middle of the list instead so they’re captured by the named range.
5. Go back to your Prospects sheet and add a Results column.
6. Highlight the cells under that column.
7. Go to Data > Data Validation > Data Validation.
8. Under Allow, choose List.
9. In Source, type: =results (using the exact name you gave the range).
10. Click OK.

Now each cell in the Results column has a dropdown matching the options from your Results tab.

5. Add a Notes Column

Add a final column called Notes for any free-text context — why a prospect is interested, callback preferences, objections, etc.

6. Turn On Filters

1. Click the corner of your table to select the entire sheet (or just your header row).
2. Go to the Data tab and click Filter.

This adds dropdown filter arrows to every column header.

7. Use Filters to Work Your List

Once you’ve spent time contacting prospects and filling in the sheet, use the filters to prioritize follow-ups:

• Filter Contacted? to No to see who you haven’t reached yet.
• Filter Results to No Answer or Left Voicemail to follow up with people you’ve already attempted to reach.
• Filter Date of Contact to a specific date to follow up with everyone you contacted that day.
• Combine filters as needed (e.g., Contacted = Yes AND Results = Left Voicemail).
• Click Clear on the Data tab to remove all filters and see the full list again.

As you follow up, update the Contacted?, Date of Contact, and Results columns so your list always reflects the current status of each prospect.

Summary of Columns

Column	Purpose
Contacted?	Yes/No dropdown tracking whether you’ve reached the prospect
Name	Prospect’s name
Phone / Email	Contact details
Date of Contact	Date formatted cell for when you reached out
Results	Dropdown of standardized outcomes (from the Results tab)
Notes	Free-text notes and context

Ways to Customize Your Lead Tracker

Every workflow is different. Consider adding columns such as:

• Company
• Phone Number
• Email Address
• Next Follow-Up Date
• Lead Source
• Priority
• Industry
• Estimated Value
• Last Contact Date

---

Additional Tips

Helpful Tip

Convert your data range into an Excel Table (Ctrl + T) before adding new records. Tables automatically expand, preserve formatting, and make filtering and sorting much easier as your list grows.

Version Compatibility

The features demonstrated in this tutorial are supported in both Microsoft 365 and older desktop versions of Excel, making this workbook easy to adapt regardless of which version you’re using.

---

---

Stay Updated

There are two easy ways to get notified when new tutorials are published:

Subscribe on YouTube for new video tutorials: @SimpleSoftwareTutorials

or enter your email below to receive new blog posts directly in your inbox: