Indzara

Sales Pipeline Tracker 2024 Template – Track sales leads in Excel

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.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Sales Pipeline Tracker in Google Sheets.

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

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 Downloads

Requirements

Microsoft Excel 2013 or later

Microsoft Excel 2010 or later (if slicers are removed)

How to track Sales pipeline (CRM) in Excel?

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

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 %

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

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

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

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

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).

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.

For more functionality, please see our premium templates.

  • Automated & Interactive Dashboard with Sales Funnel KPIs
  • Actionable Insights on what type of deals are working
  • View Active Pipeline with projected sales by month and expected deal value
  • Easy to customize. Customize (up to 12) Sales funnel stages. Customize attributes & Dashboard
  • All In One File – 2000 deal limit (default) can be extended easily if needed
Sales Pipeline Manager Dashboard
Sales Pipeline Manager Dashboard

Available for Excel and Google Sheets.

80 Comments

  • Hello!
    I would like to know if I can purchase quality database of manufacturing start up companies.
    Will be expecting a reply from you soon.
    Thak you.

    Reply
  • I just downloaded this thank you, also would love to see how I could combine and or customize this with your Kanban template for my data.

    Reply
    • Thank you for showing interest in our template.

      You can write a lookup function to get some data from Sales Pipeline Tracker Template to Kanban Board Template. We take customization projects for a fee. You can write to us at the below link to get an estimation for combining Sales Pipeline Tracker and Kanban Board template:
      https://support.indzara.com/support/tickets/new

      Best wishes.

      Reply
  • hi, i am trying to create more collums to appear before a certain collumn. but when i do that changes, all the values on the top changes

    Reply
    • Thanks for reaching out to us. Can you please send a copy of the file to support@indzara.com? – We can look into this and get back to you with the cause and solution for this issue!

      Reply
  • I’m the sales manager and would love to track all my sales reps (3 at the moment) from one central dashboard. Is there an easy way to do this with this template? I don’t know if best practice would be to add a new column or duplicate the worksheet, but I’d like the dashboard to show metrics for all the sales team.

    Reply
    • Thanks for your interest in our template.

      As of now the details of the sales reps is not incorporated in the dashboard. We might add this feature in the future releases.

      Best wishes

      Reply
  • Hello ,

    I have been using this Sales pipeline tracker file it was amazing and easy for me to track all my sales activities but i having a problem that my deal is more than 4 ( lead , opportunity , Demo and Quote ) may i know how i can add 1 more deal ..? can you share me the formula will be great .

    TQ
    Jasen

    Reply
    • Thanks for using our template.

      This version has 4 stages only. You may rename them if needed. We might add more stages in the next release.

      Best wishes

      Reply
  • Hi I just downloaded sales pipeline tracker, it seems to good, just wanted to ask if it can be synced with iPad , I mean if I update on my iPad can it be synced with my MacBook.

    Reply
    • The file can be posted on a cloud drive (OneDrive) and can be accessed from different devices.
      Best wishes.

      Reply
  • Hello,
    Can you please email the latest “Sales Pipeline Tracker Excel Template”. Also have some questions, would be great if you can help:
    – How to create a new Status “Hold”
    – For revenue forecasting, how to show dashboard which displays the projected revenue for next 12 months based on the close dates.

    Thanks

    Reply
  • I have been using the Sales tracker which was a great platform to build upon. As you are aware, the sales process takes multiple contacts to get from one stage to the other. With the current tracker it is very tricky to have an accurate dashboard in this case. I asked my team to generate a new row every time they call their prospects and the purpose is to collect the lead feedback throughout the entire sales process. The salesperson can see the history of an older calls, which allow the salesperson to stay on top of the account and up to date, especially when the salesperson has several clients to follow. This also help another salesperson understand what was going on in the account incase of any account ownership change happens. The trickiest part was the count of the stage needs to be fed to the dashboard. The rule of thump was “the last occurrence of the account name and its Stage when the Status either empty cell or No Answer” (I’ve added No Answer and No Interest to the Status which was only WON and LOST). I also created Lead Source and Type of Business drop down menu which I track, all of that was based on the rule of thump mentioned earlier. Of course I faced some challenges with formulas, but I got a freelancer to help me out. I still would like to enhance it further. For instance, if the salesperson set a Follow up date in two days, then when the salesperson opens the tracker on the date of the follow up, then the entire row of that prospect should be copied to the bottom of the list with empty follow up date and Comments/Notes cell, to allow the salesperson to fill in those cells a long with any other cells to be updates.

    Reply
    • Thanks, Ammar for sharing your practices. We are not accepting any customized projects now.

      Best wishes

      Reply

Leave a Reply

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