How To Build A Prospect Tracking Spreadsheet
Whether you’re a seasoned sales leader, just starting a new role, or even taking on founder-led sales, a prospect tracking spreadsheet is a tool you can quickly build to improve the results of your sales efforts immediately.
I’ve been in sales for over 10 years. I've seen firsthand how a superstar sales representative with no tracking method is beaten by an average salesperson using a prospect tracking process.
This guide will walk you through how to get started with a tracking spreadsheet, build a small process around that sheet, and how to effectively use it to grow your business.
What Is A Prospect Tracking Spreadsheet?
A prospect tracking spreadsheet is a tool used primarily for sales, marketing, and business development people and teams to monitor and manage potential customers. Think of it as a mini customer relationship management (CRM) tool.
For companies just getting started, individuals who are selling their services or even large-ticket sellers who don’t have many prospects, a tracking spreadsheet can be a great tool that is essentially free and doesn’t have the steep learning curves of most CRMs.
Who Should Use a Tracking Spreadsheet?
It is most useful for small businesses that want to begin collecting sales data but don’t want the expense or hassle of setting up a complex CRM or lead management system.
Typically this spreadsheet is going to be built on either Excel or Google Sheets. Cloud project management software like ClickUp and Monday.com are also good choices for building prospecting spreadsheets.
Even if a team has a full-service CRM, tracking sheets are still helpful for appointment setters to track their sales metrics and hit their goals. Sales managers can ensure their frontline sales staff are completing enough meaningful activities per day or week without having to spend time micromanaging or reviewing calendars.
Above is an example of a highly effective call tracking spreadsheet used in a semi-automated outreach campaign for my appointment setters. Here, they track their new and progressing sales touch points daily, those can be: direct messages, texts, emails, and calls.
This tool allows the setter to keep organized and more importantly allows my sales manager to review our entire appointment-setting team’s progress in about 20 minutes each morning.
What Data To Collect
Key Columns and Information
Columns can include things like Lead ID, Company Name, Contact Name, Contact Email, Contact Phone, Industry, Lead Source, Date of Initial Contact, Last Contact Date, Status, Priority, and Notes. But, at the very least collect:
- Contact Information
- First Name
- Last Name
- Company Name
- Company details
- Phone Number
- Address
- LinkedIn profile
- Other social media profiles
- Interaction history
- Interest level
- Status of conversation/prospect
- Deal stage
- Next steps
- Interested
- Call
- Proposal
- DND
Improving Tracking Data Quality
Consistency is Key
When entering data into the spreadsheet for each lead ensure accuracy and completeness to maintain the utility of the tracking system. If you’re a sales manager, ensure your sales team is trained on consistent data standards so all data is easily aggregated for future review.
Use Features to Your Advantage
Utilize data validation features in your spreadsheet software to maintain data integrity (e.g., dropdown menus for Status and Priority).
Google Sheets now has fast-templated features utilizing the ‘@dropdown’ to quickly create conditional formatting for your spreadsheet.
Using these features (as much as possible) vs. plain text are a good idea. Plain text entries typically will have a lot of spelling and syntax errors that can make data aggregation more difficult.
Example:
- For Status, create a dropdown list with options like New, Contacted, Interested, Not Interested, Converted.
- For Priority, options could be High, Medium, Low.
How To Create a Prospect Tracking Spreadsheet
1. Define Your Objectives & KPIs
Start by clarifying what you want to achieve with this spreadsheet. Are you tracking lead generation, sales conversion rates, or customer follow-ups? Your objectives will determine the data you need to include.
2. Set Up the Spreadsheet Structure
Using the section above as your guide, create your table with columns that will help you track the necessary information about each prospect. Follow the Core Principles of Lead Management to help you build the foundation you’ll need to succeed.
3. Populate the Spreadsheet
Start filling in the spreadsheet with data on your current prospects. This can be done manually or by importing data from other systems if you're transitioning from another tracking method.
4. Set Up Conditional Formatting
Use conditional formatting to make it easier to visually assess the status and priority of leads. For example, set high-priority leads to have a red background and low-priority leads to have a green background or sort by "Last Contact Date" to see the oldest entries first.
A note about the “Last Contacted” column—learn from my mistakes.
This may seem like a tiny detail but if you’ve decided not to use a CRM yet I promise you this tiny detail will save you HOURS of frustration. The last contacted column is an automated way for you to see who your sales team has contacted recently. Here’s the deal; I guarantee you that your sales team will not fill it out after every call or message, they just won’t, so don’t try.
Instead I’ve included a helpful script for Google Sheets to automatically update the ‘last updated column’ if any of the other columns in the same row are changed. I have found this little piece of code to be extremely helpful as it eliminates the need for sales reps to remember to enter their last updated cell.
Step-by-step Guide for Installing the Script in Google Sheets
Open your Google Sheet.
- Go to Extensions → Apps Script.
- Delete any code in the script editor and replace it with the following script:
- Modify the watchColumns array to include the 1-based index numbers of the columns you want to monitor for changes.
- Change dateColumn to the column number where you want the date to appear when changes are made.
- Save the script with the save icon or File → Save, name your project.
- Close the Apps Script tab and return to your sheet.
Use this code block in the Apps Script tab to ensure it works. You’ll just have to update the columns and delete the annotated notes behind the //.
function onEdit(e) {
// Set the columns you want to watch for changes
const watchColumns = [2, 3]; // Example: Watch column B and C (which are 2, 3 in 1-based index)
const dateColumn = 4; // The column to set the date in (D in this case, which is 4)
const range = e.range;
const sheet = range.getSheet();
if (watchColumns.includes(range.getColumn())) {
// Set the date in the designated "dateColumn" when a watched column is edited
const row = range.getRow();
const dateCell = sheet.getRange(row, dateColumn);
dateCell.setValue(new Date());
}
}
5. Create Filters and Sorting Options
Enable filters for each column to help users easily sort through the data, like viewing all prospects that are at a particular stage in the sales process or those assigned to a specific sales rep.
What is a slicer?
A slicer is an interactive toolbar that allows you to sort through your spreadsheet based on specific values and data. You can use them to change visible information in your spreadsheet through the use of an easily accessible widget.
I like slicers a lot, they allow on-page manipulation of data.
6. Build a Training Module For Your Team
Training a new sales team on how to use a prospect tracking spreadsheet effectively can be structured into a series of educational modules. Here are suggested titles and a brief overview of each module to guide the training process:
Introduction to Prospect Tracking
- Understanding the Prospect Tracking Spreadsheet
- Data Entry Best Practices
- Managing and Updating Leads
- Using Filters and Sorting for Analysis
- Reporting and Insights
- Real-world Application and Role-playing
- Q&A and Feedback Session
7. Review & Analyze
One of the huge benefits of using Google Sheets is the easy sync with Google Lookerstudio. This tool will allow a sales leader to have more clear discussions about sales performance and use visualization to more easily and effectively track trends.
Who’s Involved?
Now that you’ve built your spreadsheet in either Excel or Sheets you’ll need people to run your sales process. Below are the typical people, roles, and functions as they pertain to the spreadsheet.
Sales Rep
In any business process involving sales, the sales rep acts as the connector between the company’s offerings and the customer’s needs. They play a vital role in not just executing sales strategies but also in shaping them based on direct market feedback. Their buy-in ensures they are motivated and proactive, characteristics essential for achieving the best sales results.
Sales reps are responsible for filling their portion of the spreadsheet out daily. The fewer and simpler the KPIs the better.
For a typical sales process I’d want my team to track:
- Calls per day
- Calls with qualified leads per day
- Total qualified pipeline
- Total revenue
- Qualified to Close Sale ratio
Those KPIs should be built into a main dashboard on the spreadsheet, totally open so all reps can see how each other are performing.
Sales Manager
One thing I can’t say enough about a sales manager is that they should almost never be the best salesperson. Think of Michael Scott in The Office. Great salesman; terrible boss.
Managing a sales team means being accountable to the entire team hitting their KPIs. That means constant monitoring of leading indicators, coaching struggling salespeople, and developing new strategies to improve closed sales.
Close adherence to the tracking spreadsheet is very important for the sales manager to keep the team on track to achieve sales goals. By ensuring the data input is correct and timely they can eliminate any risk of micro-management; or as I call it “The Death of Morale.”
When sales data indicates that a salesperson is doing their job above KPI, the sales manager should just leave them alone. Only when those KPIs start dropping should they intervene and work directly with the rep to improve their numbers.
Sales Leader
You’re most likely a small business owner, if that’s the case you’re probably also the sales leader of your company. In the case of founder-led sales teams it is essential to follow a couple of steps:
- Set the tone and consistency of your team with a separate, sales only meeting at least once a week. Don’t make sales meetings part of general meetings.
- Keep sales meetings concise and focused. Marcus Chan has a good book called 6-figure Sales Secrets where you can learn some really applicable tips on running an effective sales meeting.
- Delegate the easy wins. If you’re the founder your job is key accounts and developing those relationships over time. Your job is NOT to be the best salesperson. Pass off the best leads and customers to your sales team to build morale and cohesiveness.
If you are an owner, check out our article, “How To Keep Leads Organized (And Set Your Sales Team Up For Success) In 3 Steps” for some helpful tips at getting lead organization started with your team.
Tips and Mistakes To Avoid in Prospect Tracking
Tips
1. Integrate with Email Marketing System (EMS) ASAP
Email is one of the most powerful sales tools. A tool like ActiveCampaign or MailChimp should be utilized immediately. Using automation through Zapier you can easily pull relevant contact information to the EMS without having to do double entry.
2. Automate Where Possible
Utilize formulas and functions to automate calculations, such as the number of days since last contact, to save time and reduce manual errors.
3. Backup Constantly
Cloud based spreadsheets like Google Sheets are always backed up which is why I prefer sheets over Excel for my tracker templates.
Mistakes to Avoid
1. Overcomplication
Keep the spreadsheet simple and user-friendly. Avoid too many fields, complex formulas, and cluttered layouts.
2. Inconsistent Data Entry
Standardize data entry with clear guidelines and use dropdowns to minimize errors. Ensure all team members are trained on how to use the spreadsheet correctly.
3. Neglecting Updates and Maintenance
Regularly update and maintain the spreadsheet to keep it relevant and useful. Implement a backup strategy to prevent data loss.
More Articles
When Do I Need Lead Management Software?
Signs that your business has outgrown the spreadsheet model
- Increased Volume of Leads: Handling a large number of leads via spreadsheets can become cumbersome and prone to errors. If you find it difficult to track interactions or updates efficiently, it’s a sign to switch.
- Need for Real-Time Data Access: If multiple team members need to access and manage lead information simultaneously, spreadsheets can become limiting and may not support real-time updates effectively.
- Complex Sales Processes: As sales processes become more complex, involving multiple touchpoints and customized follow-ups, spreadsheets might fail to manage these effectively.
- Reporting and Analytics Limitations: When advanced analytics and reporting are required to make informed decisions and spreadsheets are too basic to provide these insights, a CRM system might be necessary.
How to transition from a spreadsheet to a CRM or lead management system
- Evaluate Your Needs: Understand what you need in a CRM by identifying pain points with your current spreadsheet system and specifying features that will address these.
- Choose the Right CRM: Select a CRM that fits your budget, scales with your growth, integrates with your existing tools, and is user-friendly.
- Plan the Data Migration: Prepare your data for migration by cleaning up existing data (removing duplicates, correcting errors) and deciding what data needs to be transferred.
- Train Your Team: Ensure that everyone who will use the CRM is trained on how to use it effectively. This might involve multiple training sessions or ongoing support.
Switching from a basic spreadsheet to a dedicated CRM or lead management system represents a significant step forward in managing customer relationships and enhancing sales effectiveness, especially as a business grows and its processes become more complex.
Need Help Making The Leap To A CRM?
We’ll match you with an advisor who can help you find the right CRM for your business—and your budget.
Prospect Tracking—The Foundation Of Sustainable Growth
A prospect tracking spreadsheet is not just about keeping tabs on who you’ve talked to; it’s about creating a foundation for sustainable growth. With functionalities like automated follow-up reminders, status updates, and prioritization of hot leads, this spreadsheet becomes a mini-CRM tailored specifically for the unique needs of startups.
As you input each lead’s details, watch your business intelligence grow. This allows you to refine your sales tactics on the fly, focusing on what truly works. Plus, it’s an excellent way for your entire team to stay aligned on sales goals, understand current standings, and push collectively towards targets.
Transitioning later from a spreadsheet to a full-fledged CRM becomes markedly smoother once you have these processes in place. It’s more than just a tool—it’s your early navigator in the vast ocean of entrepreneurship, steering you towards more systematic, data-driven sales successes. Begin with this simple step, and set the stage for advanced sales operations that will propel your business forward. Let the journey of growth and efficiency begin!
Sign Up For More RevOps Insights
Want to take your RevOps game to the next level? Subscribe to The RevOps Team newsletter for expert advice, software reviews, and other resources to help you deliver predictable growth at scale.