How to Create a Lead Tracker in Google Sheets

Tracking your leads is the first step toward turning them into revenue, but you don't need complicated CRM software to get started. A well-organized Google Sheet can be a surprisingly powerful tool for managing your sales pipeline. This guide will walk you through exactly how to build a smart lead tracker from scratch, complete with data validation, visual cues, and a simple dashboard.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use Google Sheets for Lead Tracking?

Before jumping into a paid CRM, consider starting with Google Sheets. It's a completely free and flexible solution that offers a ton of benefits for small businesses, startups, and marketing teams.

• It’s free. The most obvious benefit is the cost. While powerful CRMs have their place, they often come with a monthly subscription fee per user. Google Sheets is 100% free with any Google account.
• Highly collaborative. You can easily share your tracker with your entire team. Sales reps can update lead statuses in real-time, managers can check on progress, and marketers can see which channels are driving quality leads, all from the same document.
• Accessible everywhere. As a cloud-based tool, Google Sheets is accessible from any device with an internet connection. You can update a lead's status from your computer at the office or check on a deal from your phone while on the go.
• Completely customizable. Unlike rigid software, you have total control over your spreadsheet. You can add any column you want, create custom formulas, and tailor the entire system to your specific sales process.

Step 1: Setting Up the Basic Structure of Your Tracker

First, let’s build the foundation of your lead tracker. This involves creating a new sheet and defining the essential columns that you'll use to record information for every lead.

Create and Name Your Sheet

Start by heading to sheets.google.com and creating a new, blank spreadsheet. Give it a clear name like "Company Lead Tracker 2024." It's also a good practice to name the first tab (at the bottom) Leads. This keeps things organized, especially if you add a dashboard tab later.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Establish Your Core Columns

Click on the cells in the first row (A1, B1, C1, and so on) and enter your headers. What you track will depend on your business, but here is a solid set of columns that work for most sales processes. Make the first row bold so your headers stand out.

• Date Acquired: The date the lead entered your pipeline. This helps you track pipeline velocity and a lead's age.
• Lead Name: The first and last name of your contact person.
• Company: The name of the company where your lead works.
• Email: The primary email for your contact.
• Phone Number: The primary phone number for your contact.
• Lead Source: Where did this lead come from? (e.g., Website, Google Ads, LinkedIn, Referral, Webinar). This is essential for measuring your marketing effectiveness.
• Lead Status: The current stage of the lead in your sales pipeline.
• Assigned To: The name of the sales team member responsible for this lead.
• Potential Value ($): The estimated deal size or contract value if this lead converts. This helps with sales forecasting.
• Next Action Date: The date for the next follow-up call, email, or meeting. This keeps you from letting leads go cold.
• Notes: Any relevant information about your interactions, the lead’s needs, or specific context.

Your blank sheet should now have a clean, organized first row ready for data entry.

Step 2: Add Smart Features to Supercharge Your Tracker

A simple list of leads is good, but a smart tracker makes your life easier and your data cleaner. Here’s how to use dropdown menus, conditional formatting, and formulas to take your sheet to the next level.

Use Dropdown Menus for Consistent Data

One of the biggest problems with spreadsheets is inconsistent data entry. One person might type "Won," another "Closed-Won," and a third "Closed/Won." This makes reporting a nightmare. Using dropdown menus (a feature called Data Validation) forces everyone to use the same terms.

Let’s set one up for the "Lead Status" column:

1. Select the entire "Lead Status" column by clicking the column letter (e.g., "G").
2. Go to the menu and click Data > Data validation.
3. A sidebar will appear. Under "Criteria," select Dropdown.
4. In the fields below, enter your sales stages one by one. For example: New, Contacted, Qualified, Proposal Sent, Closed - Won, Closed - Lost.
5. Click Done.

Now, when you click on any cell in that column, a dropdown arrow will appear, allowing you to select a status instead of manually typing it. Repeat this process for the "Lead Source" and "Assigned To" columns to ensure rock-solid data consistency.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Apply Conditional Formatting for Immediate Visual Cues

Conditional formatting changes a cell's color based on its content, making it easy to see the status of your pipeline at a glance. Let's make "Closed - Won" leads turn green and "Closed - Lost" leads turn red.

1. Select the entire sheet by clicking the empty square in the top-left corner (between row 1 and column A). This will apply the rule to full rows.
2. Go to the menu and click Format > Conditional formatting.
3. In the sidebar under "Format Rules," choose Format cells if... and select Custom formula is... from the dropdown.
4. In the value box, enter this formula: =$G1="Closed - Won" This formula looks at column G (your Lead Status column) and if a cell contains "Closed - Won," it will apply formatting to that entire row. The $ symbol is important - it locks the check to column G for every row.
5. Below, choose a formatting style, like changing the background color to a light green.
6. Click Done.
7. Click Add another rule and repeat the process for "Closed - Lost," using the formula: =$G1="Closed - Lost" and choosing a light red background.

You can also use this feature to highlight leads needing attention. For example, you could create a rule that turns the "Next Action Date" cell red if the date is in the past.

Step 3: Build a Simple Dashboard Tab

Now that your data is clean and organized, you can easily summarize it with a simple dashboard. This gives you a high-level view of your pipeline without needing to scroll through hundreds of rows.

First, create a new tab by clicking the "+" icon at the bottom left of your screen. Rename it Dashboard. On this new sheet, we'll create some summary tables and charts.

Summarize Your Key Metrics

Let's create a small table to see how many leads are in each stage. In your Dashboard tab, set up a few labels:

• In cell A1, type Lead Status. In B1, type Count.
• In the cells under A1, list out your exact lead statuses: New, Contacted, Qualified, etc.

Now, in cell B2 (next to "New"), use the COUNTIF formula to count the leads in that stage. The formula points back to your "Leads" tab:

=COUNTIF(Leads!G:G, "New")

Drag this formula down for each status, changing the criteria (e.g., "Contacted," "Qualified") for each one. You'll now have a dynamic summary of your entire pipeline.

You can use the same approach for other key metrics:

• Leads by Source: Use COUNTIF to see how many leads each marketing channel is generating.
• Total Pipeline Value: Use the SUMIF formula to calculate the total potential value of all open leads (e.g., any lead that is not "Closed - Won" or "Closed - Lost").
• Total Closed Revenue: Use SUMIF to add up the value of all leads where the status is "Closed - Won."

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualize Your Data with Charts

Numbers are great, but charts are better for quick insights.

1. Highlight the summary table you just created (e.g., the "Lead Status" and "Count" data).
2. In the menu, go to Insert > Chart.
3. Google Sheets will automatically suggest a chart type. A bar chart or pie chart works well for showing pipeline stages.
4. Customize the chart titles and colors using the chart editor on the right.
5. Click and drag the chart to position it neatly on your dashboard.

Repeat this process to create a pie chart for "Leads by Source." Just like that, you have a mini-BI dashboard that updates automatically as your team adds and updates leads.

Best Practices for Maintaining Your Lead Tracker

Your tracker is only as good as the data in it. To ensure it remains a valuable asset for your team, follow a few simple rules.

• Keep it clean: Don't try to track too many things. Stick to the essential columns and resist the urge to add dozens of fields that your team won't consistently fill out.
• Update it regularly: Encourage your team to update lead statuses as soon as they change. The best process is a real-time process. A weekly "pipeline review" meeting can also help enforce this habit.
• Protect your formulas: On the Dashboard tab, you might want to protect the cells containing formulas to prevent anyone from accidentally deleting them. Right-click the cells and choose View more cell actions > Protect range.
• Use it as a single source of truth: Make sure everyone on the team agrees that this tracker is the official record for all sales activities. This avoids confusion and competing versions of reality.

Final Thoughts

Building a lead tracker in Google Sheets is a fantastic, no-cost way to bring organization and data-driven insights to your sales process. In under an hour, you can create a customized, collaborative system that helps you visualize your pipeline, track performance, and ultimately close more deals.

Of course, as your company grows, you'll eventually generate data across many different platforms—ads, analytics, your CRM, your payment processor, and that Google Sheet. Soon you will realize you might want a tool to connect your tracker to your live marketing data for an easy 360 view of how deals get closed. That’s exactly why we built Graphed. We connect directly to your Google Sheets, as well as tools like Google Analytics, Facebook Ads, and Salesforce, so that your dashboards become automated reports using all of your business data together. But, for that all to happen, you need clean data - so this guide is the first step toward tracking (and understanding) all your company’s leads.