How to Create a Sales Pipeline Tracker
You can create a sales pipeline tracker in Excel using a prebuilt template. Simply fill in information about your in-progress deals, and create custom drop-down menus to reflect the stages of your sales pipeline. Then use the template to track projected close dates and next steps.
Below, you’ll find a step-by-step guide to building a sales pipeline tracker in Excel.
- Download and open the
sales pipeline template for Excel
.
- Fill in contact information for quarter 1 leads.
- In the Finance section, fill in data in the Size of Deal and Probability of Deal columns. The Weighted Forecast column will automatically populate based on your inputs.
- Create drop-down menus for the Action section by typing the list of words you want to include as options so that users know the next follow-up action to take. You can create the list of drop-down menu items anywhere on the sheet, such as beneath your tables, to the right of them, or in another sheet.
- Click the cell where you want to insert the drop-down menu.
- Click Data at the top of the page and then click Data Validation at the top right.
- A pop-up window will open. Click the green arrows next to Any value and select List.
- A new box labeled Source will pop up. Click the calendar icon in the right-hand corner of the box.
- Highlight the list you wrote for your drop-down menu. Hit Enter, and then click Ok.
- Click the drop-down arrow to view your list and make your selection.
- To add the drop-down menu to each cell in the column, click the arrow icon on the cell that contains your drop-down menu, and then click the green box on the bottom right of the cell. Drag it down to the bottom of the column.
- Now, you can make your selection from the drop-down menu for each cell in the Deal Status column.
Find more free sales pipeline templates for your business.
Sales Pipeline Tracker Elements
Most sales pipelines follow the same general structure: prospecting, lead generation, lead qualification, initial sales call and demo, offer, negotiations, contract, and ongoing support.
Here is more information about each of these key elements:
Prospecting and Lead Generation: Find prospective clients through cold outreach or through contact information you’ve gained from engaging company events, product demonstrations, or newsletters.
Lead Qualification: Call interested leads and determine whether they have the budget, timeline, and decision-making capacity to be a good fit for using your product.
Product Demo, Free Trial, or Sales Call: Offer the potential client either a product demonstration meeting, a free trial, or a sales call where your team will walk the client through how to use the product and its features. Talk through any questions about the product.
Offer: Send an initial offer and pricing for your product. Talk through any issues that the customer is having when considering purchasing.
Negotiation: Negotiate any of the concerns that the customer has and work with pricing or any personalized changes needed to create the sale.
Contract: Send the finalized contract to the customer to secure the commitment and have them sign the contract.
After-Sale Care: Follow up with the client at regular intervals to make sure the product is working well for them. Remain available to solve any issues or questions that arise while using the product.
Dean Karrel is a career development coach, sales trainer, and LinkedIn Learning
Instructor. He suggests tracking these four KPIs in your sales pipeline:
- Average Number of Leads: This includes overall leads, qualified prospects, verified opportunities, and existing accounts. According to Karrel, this KPI is “straightforward and easy to track. It’s a barometer of the health of your pipeline and is easy to flag where numbers may be lagging.”
- Sales Cycle Length: This metric shows the average amount of time between securing a lead to closing a sale. “Some salespeople track [this metric] from when you have a real opportunity to the day business is closed,” says Karrel. “Your objective is to have a firm grasp of typical timelines so that the pipeline is adequately filled, but also to reduce the number of days it takes from start to finish.”
- Win Rate: A win rate shows the percentage of leads that result in actual deals. “Look at the revenue estimates in the opportunity stage and the actual revenue achieved.” says Karrel. “The close rate is the number of deals closed versus the number of opportunities in the pipeline. Both are similar but clearly show how well you’re doing when compared to your coworkers.”
- Pipeline Opportunity Volume and Weighting: The sales manager and the finance team will use these metrics for forecasting purposes. Karrel explains: “This is where giving an accurate projection of potential revenue, along with the expectation of it closing by a specific date realistically or optimistically, is essential.”
When to Review and Analyze Sales Pipeline Data
Review and analyze the data in your sales pipeline tracker weekly to see if any new leads have come in, keep an eye on response times, and quickly identify bottlenecks. It is also crucial to hold monthly and quarterly pipeline check-ins to identify larger overall trends.
Saso says he reviews data weekly: “This includes understanding which accounts are being worked and why. The frequency really depends on your sales motion and the nature of your business. Some businesses require fast pivots and short cycles, while others have longer, more strategic deal flows. The key is to align your analysis cadence with the pace of your market so that you can adjust your approach without overreacting or dragging your feet.”
During your review, you’ll look at a variety of metrics, including length of average sales cycle, deal close rate, and deal fallthrough rate.
Best Practices for Tracking Your Sales Pipeline
Creating a sales pipeline tracker involves identifying and nurturing qualified leads, implementing sales-pipeline tracking software, and continually reviewing your process to identify bottlenecks and optimize. Having a tracker will help you streamline the sales process.
When creating your sales pipeline, consider the following tips:
- Focus on the Lead Procurement and Qualification: The most important part of this process is finding the right customers for your product. Teams can achieve this through targeted social media campaigns, SEO optimization content marketing, and lead scoring.
- Be Consistent and Professional With Leads: Once you identify the right customer, be sure to promptly follow up with sales demos, offer paperwork, and after-sale support.
- Find the Right Sales-Pipeline Tracking Software: Do your research and implement the right sales-pipeline tracking software for your needs. Look for a tool that will organize and visualize your data, automate tasks, and prompt follow-up communications or move a client to the next stage. Learn more about how to pick the right sales pipeline software with our guide.
Identify Bottlenecks: Your sales-pipeline tracking software will provide insight into issues within your process. Look to see where clients are falling off on the pipeline (i.e., not moving to the next stage) and problem solve to improve upon your tactics.
Dan Saso is a Community Growth Manager at Embrace. He says, “a healthy pipeline includes a balanced mix of attributes: strong inbound and outbound opportunity creation, a solid conversion rate from meetings to opportunities and from opportunities to closed deals, and low customer churn. These indicators increase the likelihood that your pipeline contains high-quality, winnable deals. On a tactical level, this means rigorously qualifying good-fit opportunities while also being proactive in disqualifying poor-fit deals early. The strength of a pipeline isn’t just in how much is in it, but in how real it all is.”
Learn more about the basics of sales pipeline management.
How to Build a Sales Pipeline in Smartsheet
You can also create a sales pipeline for your business in Smartsheet to help track leads and aggregate data for sales analysis and optimization. Below, you’ll find a step-by-step how-to using a pre-built Smartsheet template. The sales pipeline template set includes a dashboard, a tracking sheet, and an opportunities report.
- Open the sales pipeline tracking and reporting template in Smartsheet.
- On the left-hand menu, click Sales Opportunity Form.
- A form will open on your screen. Fill it out with the customer company name, contact email address, sales state, forecast amount, probability, expected close date, sales rep email, and next steps. Click Submit.
- Navigate to your sales pipeline sheet. The information you enter into the form will auto-populate in your sheet.
- To calculate the weighted forecast, click the empty cell and then click Formulas in the toolbar.
- In the pop-up window, use the formula =[Forecast Amount] * Probability and then enter the row after each input. In this example, for row 11, the formula is =[Forecast Amount]11 * Probability11. Click Apply.
- Your sheet will auto-populate with the result of the weighted forecast formula.
- On the left-hand menu, click Current Opportunities Report to see all unfinished deals that need to be moved through the sales pipeline.
- On the left-hand menu, click Dashboard - Sales Pipeline Overview to see the full summary of your pipeline information, including the total amount won from closed deals, the number of opportunities covered by each sales representative, and a graph of how many deals are in which stage of the sales pipeline.
Improve Sales Pipeline Tracking and Drive Results With Real-Time Work Management in Smartsheet
Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.
The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.
When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.