Sales Pipeline Tracker Template – Track sales leads in Excel – Free Download

Posted on
Sales Pipeline Tracker - Active Sales Pipeline - Metrics

Whether you are a small business owner looking for a tool to manage the data on your sales leads, or a sales person needing a simple CRM software, you have come to the right place. I am glad to present a simple Sales Pipeline Management tool to track and manage your sales leads.

Sales Pipeline Tracker - Excel Template - Reporting Metrics
Sales Pipeline Tracker – Excel Template – Reporting Metrics

 

In this article, I will cover the following topics.

Agenda

  • What is a Sales Pipeline?
  • Why do we need Sales Pipeline Management?
  • Benefits of Excel as a Sales pipeline management software
  • Features of the Free Sales Pipeline Tracker Excel template
  • How to use the template?
  • How to extend or customize it to your needs?

 

What is a Sales Pipeline?

In a typical sales process, a seller or salesman sells a product or service to a customer. This process may take multiple steps (or stages).

Let us consider a very simple example with 3 steps.

  1. A prospect sends an inquiry to the company with their needs
  2. The sales person in the company returns a quote with product and price
  3. The prospect purchases the product and becomes a customer.

 

Inquiry –> Quote –> Sale

Simple Sales Process - Inquiry - Quote - Sale
Simple Sales Process – Inquiry – Quote – Sale

 

In some scenarios, especially where the product or service’s price is very high, the process can be much longer and may go through many steps.

Companies also, sometimes, prefer the customers to go through a sequence of steps before converting. This is also called lead nurturing. This allows the sales person to identify the specific needs of prospect and find if there is a match between the needs and the products/service provided by the company. During the stages, the salesperson will be able to gauge the chances of a successful sale.

For example, the following stages could make your sales process.

Lead –> Opportunity –> Demo –> Quote –> Sale

A sample sales process with more stages - Lead - Opportuntiy - Demo - Quote - Sale
A sample sales process with more stages – Lead – Opportuntiy – Demo – Quote – Sale

 

A visual representation of such a sales process with the steps involved is usually called Sales Pipeline.

Sales pipeline is also referred to as Sales funnel, as the pipeline usually works like a funnel where prospects drop off along each stage before only a few (of those who began at first stage) will finally convert to a customer.

 

What is Sales Pipeline Management? Why do we need it?

Capturing information related to the sales pipeline and using them allows you to manage the pipeline better. The following are the key benefits.

Benefits of Sales Pipeline Management
Benefits of Sales Pipeline Management

 

  • At any point, know how many deals are in each sales stage and how much are the deal values. This allows prioritization of deals to work on, so that you can work on the deals that need immediate attention and those that are more likely to succeed.
  • Determine if there is a weak area in the sales funnel. For example, if more prospects drop off in the final closing stage , it tells you what you need to improve (may be the closing skills of a salesperson or incentives to quickly close the deal).
  • Identify if there is a bottleneck in the sales process. For example, if one of your steps requires a specialist to interact with the prospect and that step takes several days due to busy schedule of the specialist. This information could be useful to take action to either increase availability of that specialist or hire/train more specialists.
  • In a company with a lot of sales people, the sales pipeline metrics become invaluable in identifying best practices used by the most successful sales person. This knowledge could improve the performance of the entire sales team.
  • Know exactly how rich the current active pipeline is. Allows forecasting of future sales based on deals in the current pipeline, win % and the projected close dates of those deals. If the forecasts do not meet your sales target, you can take remedial steps earlier so that you improve chances of meeting sales targets in future.

 

Sales Pipeline Management Software (Tool)

There are several cloud based tools, of all sizes, available to manage sales pipelines in the market. They may also have features such as email notifications, reminders and mobile applications.

For simpler small to medium scale operations, to manage a salesperson’s leads, Microsoft Excel would be more than fine. The obvious benefits with Excel are

  1. Easy to learn and use
  2. Easy to extend by adding sheets/calculations
  3. Easy to modify design (fonts, colors, etc.)
  4. Easy to customize to unique business needs
  5. Less expensive than a monthly subscription based cloud software

 

I am glad to present a simple Sales Pipeline Excel Template that you can download for free.

 

Features of Sales Pipeline Excel Template

  • Track all your sales deals in an organized way in one sheet
  • View active sales pipeline broken down by stages
  • Calculates Expected Deal Values (based on Win %)
  • Calculates average time (days) taken to win deals
  • Identifies stages where deals are lost
  • Easy tool to filter deals by stage and status
  • Easy to customize

 

FREE DOWNLOAD

Sales Pipeline Tracker Excel Template

Sales Pipeline Tracker Excel Template (with Sample Data) for reference

 

REQUIREMENTS

Microsoft Excel 2013 or later

Microsoft Excel 2010 or later (if slicers are removed)

 

HOW TO USE THE SALES PIPELINE TRACKER TEMPLATE

 

Overview of Steps

  1. Review 4 sales stages. Rename if necessary.
  2. Entering a new deal in Deals table
    1. Enter basic deals data (Company Name, Contact Name, Deal Value, Created Date)
    2. Enter current Stage in which the deal is.
    3. Enter Win % as the chance of winning the deal.
  3. Updating a deal as you make progress
    1. Update Stage if the deal has moved forward to the next stage.
    2. Update Win % if it has changed over time.
    3. Enter Next Activity Date and Notes
  4. Closing a deal
    1. Enter Status (Won or Lost) if the deal is closed.
    2. Enter Close Date
  5. Monitoring performance regularly to gain insights
    1. View active pipeline to know if you are on target to meet your goal
    2. View deal conversion rate and average time to win a deal
    3. Identify stages where deals are lost and work on improving

 

Detailed Steps

Step 1: Review Stages in Sales Process

By default, the template comes with 5 stages (Lead, Opportunity, Demo, Quote and Sale) . The first 4 can be renamed easily by typing directly on the stage names.

4 Stages in the Sales Pipeline - Rename Stage names
4 Stages in the Sales Pipeline – Rename Stage names

 

Step 2. Entering a new Sales deal (or lead)

We can enter new deals by typing in the Deals table.

For more on data entry in Excel tables, please visit Excel Tables for Data Entry

Each row is a separate deal and it would have Company name, Contact person name, Deal Value and Created Date.

Enter sales leads data - Company name Contact name Deal value and Deal created date
Enter sales leads data – Company name Contact name Deal value and Deal created date

 

Deal Value can be estimated if it has not been determined yet. But please enter a value so that the pipeline can be evaluated.

 

We will then enter the current Stage of the deal.

Enter Current Stage of each deal from the drop down
Enter Current Stage of each deal from the drop down

 

The 4 stages we had defined earlier will be the values to choose from, in the drop down list.

 

Then, we enter a Win % for each deal. For deals that are not closed yet, we would enter our estimate of the chances of winning a deal. If we have a 50-50 chance of winning or losing a deal, we would enter 50%.

In some businesses, the Win % are defined to be constant for a specific stage. Example: 10% for all Leads, 30% for all Opportunities, 50% for all Demos, 80% for all Quotes.

 

This is a sample data set with Stage and Win % entered.

Sample Sales Deals data with Stage and Win %
Sample Sales Deals data with Stage and Win %

 

4 more Columns are provided to enter the Email address, Phone Number, Next Activity Date and Notes.

 

Step 3: Updating a Deal

As we continue to work on deals, new information may arrive. Based on that, we need to update the deals.

Update Win % and Stage columns for deals as needed, so that your active pipeline is always reflecting the reality.

After working on the deal, we would usually schedule the next activity for a future date. We can enter that next activity date in the Next Activity Date column and enter details in the Notes column.

Enter follow up details - Next Activity Date and Notes
Enter follow up details – Next Activity Date and Notes

 

Step 4: Closing a Deal

Deals can be closed if a decision has been taken by the customer to purchase. This is the scenario of a deal WON.

If prospect decides to not to purchase or if it has been a while since the prospect has been in touch, we can close the deal as LOST.

We can enter this information by entering the STATUS to be ‘WON’ or ‘LOST’.

Then, we should enter the CLOSE DATE. This date is used to calculate the Average time taken to Win a deal.

Sample Closed Deals data with Status Won or Lost and Close Date
Sample Closed Deals data with Status Won or Lost and Close Date

 

If the Close Date is left blank, you will see a red border indicating that the entry is missing.

Data Validation - Missing Close Date for Closed Deals - Shown by Red border
Data Validation – Missing Close Date for Closed Deals – Shown by Red border

 

This red border will also appear if Close Date is less than Create Date. This is to ensure that the data is accurately entered.

 

Tips:

Win % could be deleted for closed deals as we already know the result of the deal. They will be ignored for calculations.

For lost deals, Stage should reflect the last stage the deal was in. This will be used to identify where deals are being lost the most.

 

Step 5: Monitor Sales Pipeline performance

This step is one that should be done regularly. It is important to keep an eye on the pipeline metrics to improve performance.

The top section of the sheet shows several important metrics needed. Let’s discuss one by one.

Active Pipeline

Sales Pipeline Tracker - Active Sales Pipeline - Metrics
Sales Pipeline Tracker – Active Sales Pipeline – Metrics

 

First, the pipeline summary metrics.

Sales Pipeline Summary Metrics Deals, Deal Value and Expected Value
Sales Pipeline Summary Metrics Deals, Deal Value and Expected Value

 

  1. Open Deals: This represents the number of deals that are not closed yet. Any deal whose status is empty (neither WON nor LOST) will be considered as an open deal. Once a status is entered, it becomes a Closed Deal.
  2. Total Value: This is the total value of all open deals. The Deal Value we enter in the Deals table will be used as such. If we win all the open deals, this is the amount we will successfully sell.
  3. Expected Value: The reality is that we will win some deals and lose some deals. So, it is better to be realistic about our estimated sales. Expected value is the weighted sum of deal value by Win %.

 

Metrics by Stage

As the summary metrics describe the current scenario of the entire pipeline, it is also equally important to know the metrics on the deals by stage.

Sales Pipeline Metrics by Stage - Deals, Deal Value and Expected Value
Sales Pipeline Metrics by Stage – Deals, Deal Value and Expected Value

 

As shown in the image above, the same three metrics (Number of Open Deals, Total Deal Value and Expected Deal Value) are shown for each of the 4 stages.

 

Closed Deals Performance

The previous section showed the active pipeline. As important that is, it is also important to know how we have been performing in closing deals. It can tell a lot about the future.

The next section shows the performance of closed deals.

Performance Metrics of Closed Deals - Conversion Rate, Time To Win Deals
Performance Metrics of Closed Deals – Conversion Rate, Time To Win Deals

 

Deals Won and Lost

Closed Deals - Number of Deals Won and Lost - Deal Value
Closed Deals – Number of Deals Won and Lost – Deal Value

 

In the sample above, 3 deals were won for total of $161,000 and 7 deals were lost valued at a total of $74,000.

 

Conversion Rate & Average Time to win deals

Conversion Rate and Average Time to win Deals
Conversion Rate and Average Time to win Deals

 

3 deals were won out of 10 closed deals and hence Conversion Rate is shown as 30%.

The 3 won deals took on average 17 days to close. This is based on the Close Date and Created Date we entered.

 

Where are deals lost?

By knowing where the deals are being lost, we can work on those stages to improve our performance.

Deals Lost by Stage
Deals Lost by Stage

 

Of the 7 deals we lost, 29% were lost in the Lead stage, 14% in the Opportunity stage, 43% in Demo stage and 14% in Quote stage.

We have entered a small sample of deals here. As we enter more and more data, these % will become very telling in revealing our strengths and weaknesses. If Demo is the stage we fail most often, we may need to improve the demo event to convince our prospects about the usefulness of our products.

 

Tips on working with Deals Table

Filter deals by Slicers (compatible with Excel 2013 and later)

As we enter more and more rows in the Deals table, it will become important to have a way to easily find the deals to work on.  There are a couple of slicers (filters) provided at the top right.

Filter Deals table by Sales Stage and Deal Status
Filter Deals table by Sales Stage and Deal Status

 

We can use the filters to select deals by Status and Stage.

Filter Deals table by Stage and Status - Select Open Deals
Filter Deals table by Stage and Status – Select Open Deals

In the image above, I have selected (blank) for Status and Demo and Quote for Stage. This will now filter the table to show only deals that are open and in the Demo or Quote stages.

Tips:

To select multiple values in one slicer, press ctrl key

To clear all selections press the ‘Clear Filter’ button at top right of the slicer.

 

Filter deals on Table 

Since we are using an Excel table, we have the default features such as filtering and sorting.

For example, if we want to filter only deals of values > $5000.

Filtering Deals based on deal values
Filtering Deals based on deal values

 

The above (Greater Than) is just an example. You can use all other types of filtering available in Excel tables. You can use the filters in any of the fields (example: Company).

 

Another example is to sort by Next Activity Date. If you want to follow up with the deal contacts based on the next activity date in order, you can sort the table using that field.

Sort Deals by Activity Date
Sort Deals by Activity Date

 

Customizing the template

How to modify Currency?

The template by default uses US$ as currency. To change, just select the cells where you want to change the currency. Then, press Ctrl+1.

Change currency used in Sales pipeline tracker template
Change currency used in Sales pipeline tracker template

 

Then, change the currency symbol in the dialog box as shown above.

 

How to add a slicer? (Slicers on tables work only in Excel 2013 or later)

I have provided Stage and Status as two slicers. If you need to add any of the other columns as slicers, it is easy.

Select a cell inside the Deals table first.

Then, in the INSERT ribbon, choose Slicer.

From Insert Ribbon select Slicer
From Insert Ribbon select Slicer

 

Then, you will see a dialog box that will display all the fields in the Deals table.

Choose field to create slicers
Choose field to create slicers

 

Choose one or more fields for which you want the slicers, and press OK. Now, you should see new slicer(s) that you can use to filter.

 

How to add extra column?

You can create a column easily by typing the new column name in cell M11.

Adding New columns to Deals table
Adding New columns to Deals table

 

After that, if you need another one, enter a column name in cell N11.

 

Is the file password protected?

No, the file is free to download and fully open for editing.

The file uses formulas in just the DEALS sheet and there are no hidden sheets. As we are using an Excel table, Excel does not allow both sheet protection and Excel table’s auto-expand feature at the same time. As the table feature is critical to easy and quick data entry, sheet protection is not implemented.

The top section above the table, which has the metrics, have formulas embedded. Please be careful and do not edit the formulas unintentionally.

 


What do you think about this template?

I hope you find this template useful. This is the first version of the Sales Pipeline Tracker. It will be great if you can provide feedback on what features you like and what can be improved.

If you find this useful, please share with your friends.

 

Related Excel Templates

62 thoughts on “Sales Pipeline Tracker Template – Track sales leads in Excel – Free Download

  1. How to download the sales pipeline tracker? I dont see any link?

    1. Thanks for your interest in our template.

      Please download from the free download section of https://indzara.com/2017/01/sales-pipeline-tracker-excel-template/

      Best wishes

  2. Thank you very much for sharing good platform.

    1. Thanks for sharing positive feedback.

      Best wishes

  3. This excel template is just the best. Thank you very much for sharing it with us.
    I’m trying to add a slicer whereby i can see the deals based on my Sales Managers. However, it has brought me something to do existing connections. I have Microsoft Office Professional plus 2016. Can you assist me? Also i’d like to add computation of commission based on the deals they close. I’m i able to add that?

    1. Thanks for sharing your positive experience. MS Excel 2016 handles slicers through Pivot table.
      Please create a table and then you can access the slicers.

      Best wishes

  4. Marvelous Sales Pipeline Tracker, and tremendously helpful video guidance, thank you!!!

    1. Thanks Logan

  5. Hello Friend,

    Thank you for this wonderful format.
    I started using it today. I am a Sales Head and regulary analyse the progress week by week and month on month.
    So its always YTD, QTD, MTD, WTD
    How can i get an analysis for the above period?
    Your kind advice and suggestion will be a huge milestone for me

    1. Hello

      Thanks for your feedback.

      The current format does not support time-based reports.

      Best wishes

  6. i dnt receive any mail i tryed to download

    1. Hello

      Thanks for your interest. We have emailed the file to you.

      Best wishes

  7. Hello, I thank you for your effort.
    I want to add a new stage, can you help me please with that?

    1. Hello

      To add a stage, add a new one next to the four-stage names. Then update the named range L_ST.
      In the section (Where are deals lost?), we have to add a new row and use the new stage and follow the formula used for the other stages.
      To add a new status, edit the Data validation list and add a new status value.
      Then as we have used the formulas in (cells G1, H1, I1, and J1), we have to create two for the new status.

      Best wishes

  8. This template is really useful but I am having trouble updating Stages (renaming them).
    I looked at the Data Validation for the field and range says: =L_ST. I cannot find this range or the list of the stages. Help, pls.

    1. The range refers to cells C3 to F3.

      Please email the file if there are any further questions to contact@indzara.com

      Best wishes

  9. Excellent Template with snapshots. I have added a column for City/Region and would like to see the values (no. of open deals, values etc.) in tracker with respective to each city. Can you suggest what changes are required in the formulas.

    1. Thank you.
      If you just need to see metrics for one or more cities at a time, did you try adding a slicer for city?

      How to add a slicer? (Slicers on tables work only in Excel 2013 or later)
      Select a cell inside the Deals table first.
      Then, in the INSERT ribbon, choose Slicer.
      Then, you will see a dialog box that will display all the fields in the Deals table.
      Choose field to create slicers
      Choose one or more fields for which you want the slicers, and press OK. Now, you should see new slicer(s) that you can use to filter.

      Best wishes.

      1. Thanks for Reply.

        Have added the slicer, but after filtering it by a particular city, I would like to see the active pipeline for that particular city and similarly the number of leads, opportunities, demos & quotes for that city only.

        This will help to measure the city wise sales performance within one sheet.

        Hope my query is clear.

        BR.

        1. I understand now. Thanks for clarifying.

          We need to change formulas to have that feature.
          Please see the formula explained in this page https://exceljet.net/formula/count-visible-rows-only-with-criteria

          Or we have to use pivot tables and point our formulas to the pivot tables.

          Best wishes.

          1. Thanks, will try using the approach suggested.

            BR.

  10. Thanks team for sharing the template. Extremely useful. Cheers!

    1. Thanks for your feedback

Leave a Reply

Your email address will not be published. Required fields are marked *