How to Create a Sales Tracker in Excel

Tracking your sales process in a spreadsheet is often the first step toward building a data-driven sales motion, but it can feel overwhelming. The good news is you don’t need an expensive CRM or complex software to get started. A well-organized Excel sales tracker can give you surprising clarity on your pipeline, deals, and team performance.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

This tutorial will guide you through creating a powerful and easy-to-use sales tracker from scratch, complete with automated calculations and visual cues. We'll build a foundational sales pipeline tracker and then create a simple dashboard to summarize the most important metrics.

Why You Need a Sales Tracker (Beyond Just Listing Deals)

Before jumping into the steps, it’s helpful to understand what a proper sales tracker accomplishes. It's more than just a list of potential customers. A functional tracker gives you the ability to:

• Centralize Your Information: Stop hunting through emails, notes, and CRMs. One central spreadsheet holds every deal's status, value, contact person, and next steps.
• Understand Pipeline Health: See at a glance where deals are getting stuck. Are you great at getting initial demos but poor at closing? A tracker will make that leakage obvious.
• Improve Forecasting Accuracy: When you know the value of deals in each stage of your pipeline, you can create much more reliable revenue forecasts instead of relying on gut feelings.
• Measure Performance: You can quickly see which sales reps are closing the most deals, who has the biggest pipeline, and what the average deal size is across the team.

A simple tracker turns scattered activity into structured data you can use to make smarter decisions.

Step 1: Define the Key Data Points to Track

The foundation of any good tracker is the data it contains. Starting too simple leaves you without insights, but starting too complex makes it a chore to maintain. Here are the essential columns to start with. You can always add more later.

• Deal ID: A unique identifier for each deal (e.g., 001, 002). This helps prevent confusion if you have multiple deals with the same company.
• Deal Name: A simple, descriptive name (e.g., "Q3 Website Redesign").
• Company Name: The name of the client or prospect.
• Contact Person: The name and email of your primary contact.
• Stage: The current stage of the deal in your sales process. This is crucial for pipeline management. Common stages include:
• Value: The potential monetary value of the deal if it closes.
• Expected Close Date: The date you anticipate the deal will be signed.
• Sales Rep: The name of the person responsible for the deal.
• Status: The overall status of the deal. Keep this clean and simple: Open, Won, or Lost.
• Last Contact Date: The last time you communicated with the prospect.
• Next Step: The next actionable task to move the deal forward.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Build the Sales Tracker Table in Excel

Now, let's turn that list of data points into a functional spreadsheet.

Set Up Your Columns

Open a new Excel workbook. In the first row, enter the column headers we just defined, one per cell starting from A1.

Format as a Table

This is the most important step for making your tracker dynamic and easy to use. Don't just bold the headers, use Excel’s “Format as Table” feature.

1. Click on any cell within your data range (e.g., A1).
2. Go to the Insert tab on the Ribbon and click Table.
3. Excel will automatically detect your data range. Make sure the "My table has headers" box is checked.
4. Click OK.

Your spreadsheet will now be formatted with colored rows and filter/sort buttons on each header. Formatting as a table gives you amazing benefits:

• Automatic Formatting: Any new row you add at the bottom is automatically included in the table's formatting.
• Easy Filtering and Sorting: You can instantly filter to see only deals belonging to one salesperson or sort by close date with a single click.
• Dynamic Formulas: When you build a dashboard (which we'll do next), formulas referencing the table will automatically update when you add new data.

Use Data Validation for Consistency

To avoid typos and keep your data clean, use drop-down menus for columns with repetitive options like "Stage" and "Status."

1. Create a new sheet in your workbook and name it Lists.
2. In this new sheet, list your sales stages in Column A (e.g., a cell for "Lead In," "Contact Made," etc.).
3. In Column B, list your deal statuses ("Open," "Won," "Lost").
4. Go back to your main tracker sheet and select the entire "Stage" column inside your table.
5. Go to the Data tab and click Data Validation.
6. In the "Allow" dropdown, choose List.
7. Click in the "Source" box, then navigate to your Lists sheet and select the range of cells containing your sales stages.
8. Click OK.

Now, when you click on a cell in the "Stage" column, a drop-down arrow will appear, letting you select from your predefined list. Repeat this process for the "Status" column.

Step 3: Add Visuals with Conditional Formatting

Bring your tracker to life by adding color-coded rules that highlight important information. A common use is to color-code the "Status" and "Stage" columns.

1. Select the entire "Status" column within your table.
2. Go to the Home tab, click Conditional Formatting > Highlight Cells Rules > Text that Contains...
3. In the text box, type "Won" and choose the Green Fill with Dark Green Text format. Click OK.
4. Repeat the process for "Lost," choosing the red format, and "Open," choosing a yellow or neutral format.

Now, with a quick scan, you can immediately identify the status of every deal. This makes your tracker much more readable, especially as it grows.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 4: Create a Simple Sales Dashboard

Manually scanning rows of data is tedious. Let’s build a small summary dashboard at the top of your sheet (or in a separate sheet) to give you high-level insights instantly.

First, give your table a memorable name. Click anywhere in your table, go to the Table Design tab, and type a name like "SalesPipeline" in the Table Name box on the far left. This makes your formulas easier to read.

In a clear area above your table, set up some labels for your key metrics:

• Total Pipeline Value
• Number of Open Deals
• Total Won Revenue
• Win Rate (%)
• Average Deal Size

Next to these labels, we'll use a few simple formulas to calculate the values:

Formula for Total Pipeline Value (Open Deals)

This formula sums the "Value" of all deals where the "Status" is "Open."

=SUMIF(SalesPipeline[Status], "Open", SalesPipeline[Value])

Formula for Number of Open Deals

This counts how many rows have the status "Open."

=COUNTIF(SalesPipeline[Status], "Open")

Formula for Total Won Revenue

Similar to the pipeline value, but this time we're summing up the value of deals marked as "Won."

=SUMIF(SalesPipeline[Status], "Won", SalesPipeline[Value])

Formula for Win Rate

Win rate is the number of won deals divided by the total number of closed deals (won + lost).

=COUNTIF(SalesPipeline[Status],"Won") / (COUNTIF(SalesPipeline[Status],"Won") + COUNTIF(SalesPipeline[Status],"Lost"))

Make sure to format this cell as a percentage!

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Formula for Average Deal Size (Won Deals)

This gives you the average revenue generated from each successfully closed deal.

=AVERAGEIF(SalesPipeline[Status], "Won", SalesPipeline[Value])

This simple dashboard now updates automatically every time you add or update a deal in your table below, giving you a real-time snapshot of your sales performance.

Best Practices for a Healthy Tracker

• Keep It Simple: Avoid the temptation to add dozens of columns. A tracker that's too complicated to fill out will be abandoned. Start lean and only add what you truly need.
• Be Consistent: Consistent data entry is essential. Using drop-downs helps, but also establish team-wide rules for naming deals and filling out notes.
• Update Regularly: A sales tracker is only useful if it's current. Set aside time at the beginning or end of each day to update your deals.

Final Thoughts

In just a few steps, you've transformed a blank spreadsheet into a dynamic sales tracker and summary dashboard. This tool is a huge step up from scattered notes and provides the data you need to manage your pipeline, forecast revenue, and coach your team effectively, all without spending a dime on new software.

While an Excel tracker is a powerful starting point, the process is still manual, especially when your sales data lives in other apps like Salesforce, HubSpot, or Shopify. Constantly exporting data to update your sheet can become its own full-time job. We built Graphed to connect directly to these live data sources, allowing you to ask questions in plain English - like "create a dashboard of my sales pipeline from Salesforce" - and get a real-time, interactive dashboard in seconds. This lets you skip the manual spreadsheet wrangling and focus purely on the insights.