{"id":15084,"date":"2026-05-29T12:22:00","date_gmt":"2026-05-29T11:22:00","guid":{"rendered":"https:\/\/sapphirebusinesstech.com\/en\/?p=15084"},"modified":"2026-10-05T08:02:32","modified_gmt":"2026-10-05T07:02:32","slug":"custom-excel-spreadsheet-sales","status":"publish","type":"post","link":"https:\/\/sapphirebusinesstech.com\/en\/excel\/custom-excel-spreadsheet-sales\/","title":{"rendered":"How to Build a Sales Pipeline Tracker in Excel: A Step-by-Step"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Key takeaways<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You can build a working sales pipeline tracker in Excel from scratch by first defining deal stages, such as Prospecting, Qualified Lead, Proposal Sent, Negotiation and Closed Won or Lost.<\/li>\n\n\n\n<li>Data Validation dropdowns on the Stage and Owner columns keep entries consistent, so variations like &#8220;Negotiating&#8221; versus &#8220;Negotiation&#8221; no longer break your filters.<\/li>\n\n\n\n<li>A <strong>Weighted Value<\/strong> column (=C2*E2\/100) multiplies deal value by probability, giving a more realistic revenue forecast that recalculates automatically as deals progress.<\/li>\n\n\n\n<li>Colour-code stages using three to four conditional formatting shades, then add a Dashboard tab with SUMIF totals per stage and a bar chart for weekly team reviews.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"434\" src=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel1-1024x434.png\" alt=\"Build custom Excel spreadsheet sales pipeline tracker - colorful dashboard showing deal stages and progress bars\" class=\"wp-image-15085\" srcset=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel1-1024x434.png 1024w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel1-300x127.png 300w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel1-768x326.png 768w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel1-1536x652.png 1536w, https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel1-2048x869.png 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>A sales pipeline tracker in Excel<\/strong> is a custom spreadsheet that records each deal&#8217;s stage, value, probability, expected close date and owner, keeping follow-ups visible and giving you a weighted forecast that recalculates automatically.<\/p>\n\n\n\n<p>A well-managed sales pipeline is the backbone of any growing business. Without a clear view of where every deal stands, it is easy to lose track of follow-ups, miss close dates, or misread your <a href=\"https:\/\/sapphirebusinesstech.com\/en\/excel\/building-a-excel-spreadsheet-for-sales-commission-forecast-pipeline\/\">revenue forecast<\/a>. The solution does not have to be expensive. In this tutorial, you will learn how to build a custom Excel spreadsheet that works as a fully functional sales pipeline tracker, even if you are starting from scratch and have no technical background.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Setting Up Your Pipeline Stages<\/h2>\n\n\n\n<p>Before building anything, you need to define the stages your deals move through. Common stages in B2B sales include Prospecting, Qualified Lead, Proposal Sent, Negotiation, and Closed Won or Lost. Furthermore, to <a href=\"https:\/\/blog.hubspot.com\/sales\/sales-pipeline\" target=\"_blank\" rel=\"noopener\">understand what a healthy sales pipeline looks like, see here<\/a>. It helps to map stages to your actual sales process, not a generic template.<\/p>\n\n\n\n<p>To create your stage list in Excel:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open a new workbook and name the first tab &#8220;Pipeline.&#8221;<\/li>\n\n\n\n<li>In row 1, add your column headers: Lead Name, Company, Deal Value, Stage, Probability (%), Expected Close Date, and Owner.<\/li>\n\n\n\n<li>On a second tab named &#8220;Lists,&#8221; type all your stage names in column A. This will power a dropdown later.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/sapphirebusinesstech.com\/en\/wp-content\/uploads\/2026\/05\/excel2.png\" alt=\"Build custom Excel spreadsheet with dropdown menus - data validation settings panel shown in Excel interface\n\" class=\"wp-image-15086\"\/><figcaption class=\"wp-element-caption\"><em>A data validation screen, commonly used on custom excel spreadsheets<\/em><\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Adding Dropdown Menus for Consistency<\/h2>\n\n\n\n<p>One of the most common challenges in shared spreadsheets is inconsistent data entry. For example, one team member types &#8220;Negotiating&#8221; while another writes &#8220;Negotiation&#8221; and suddenly your filters break. However, data validation solves this problem completely.<\/p>\n\n\n\n<p>learn below how to add a dropdown menu for the <strong>stage column<\/strong>:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Select all cells in the Stage column (below the header).<\/li>\n\n\n\n<li>Go to the Data tab and click &#8220;Data Validation.&#8221;<\/li>\n\n\n\n<li>Under &#8220;Allow,&#8221; choose &#8220;List.&#8221;<\/li>\n\n\n\n<li>In the Source field, reference the stage names you typed on your Lists tab separated by a comma (&#8220;,&#8221;).<\/li>\n\n\n\n<li>Click OK. Then every cell in that column now shows a dropdown with your approved stage names.<\/li>\n<\/ol>\n\n\n\n<p>In addition, repeat this process for the Owner column if your team has a fixed list of sales reps.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Calculating Weighted Pipeline Value<\/h2>\n\n\n\n<p>A pipeline tracker becomes truly powerful when it tells you not just the deal value, but the expected revenue. Therefore, multiplying deal value by probability gives you a weighted forecast that reflects reality more accurately.<\/p>\n\n\n\n<p>In a new column called &#8220;Weighted Value,&#8221; enter this formula in the first data row:<\/p>\n\n\n\n<p><em>=C2*E2\/100<\/em><\/p>\n\n\n\n<p>Where C2 is Deal Value and E2 is Probability (%). Consequently, as your team updates deal stages and probabilities, the weighted value recalculates automatically. This single column can transform how your leadership team reads the forecast.<\/p>\n\n\n\n<div class=\"sbt-cta-box\" style=\"margin:30px 0;padding:22px 26px;background:#eef4ff;border-radius:12px;text-align:center\"><p style=\"margin:0 0 16px;color:#1e293b;font-size:1rem;line-height:1.6\">Keeping deal values and probabilities consistent gets harder as more people edit the tracker. Our Excel specialists can build a pipeline tracker around your own sales stages.<\/p><p style=\"margin:0\"><a href=\"mailto:contact@sapphirebusinesstech.com?subject=Enquiry%3A%20How%20to%20Build%20a%20Sales%20Pipeline%20Tracker%20in%20Excel%3A%20A%20Step-by-Step&amp;body=Hi%21%20I%20read%20the%20article%20%22How%20to%20Build%20a%20Sales%20Pipeline%20Tracker%20in%20Excel%3A%20A%20Step-by-Step%22%20on%20the%20Sapphire%20website%20and%20would%20like%20to%20talk%20to%20a%20specialist.\" class=\"sbt-cta\" style=\"display:inline-block;background:#0a2351;color:#fff;border:none;border-radius:8px;padding:13px 26px;font-weight:700;font-size:1rem;cursor:pointer;text-decoration:none\">Talk to a specialist<\/a><\/p><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Applying Conditional Formatting by Stage<\/h2>\n\n\n\n<p>Visual cues make data easier to scan quickly. As a result, adding color-coding to your stage column allows anyone to read pipeline health at a glance without digging into filters.<\/p>\n\n\n\n<p>To apply <a href=\"https:\/\/sapphirebusinesstech.com\/en\/excel\/conditional-formatting-in-excel\/\">conditional formatting<\/a>:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Select the Stage column.<\/li>\n\n\n\n<li>Go to Home > Conditional Formatting > Highlight Cell Rules > Equal To.<\/li>\n\n\n\n<li>Set rules for each stage &#8211; for instance, &#8220;Closed Won&#8221; in green and &#8220;Prospecting&#8221; in blue.<\/li>\n\n\n\n<li>Repeat for each stage with a distinct color.<\/li>\n<\/ol>\n\n\n\n<p>On the other hand, avoid using too many colors. Three to four distinct shades are enough to keep the sheet readable.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Building a Summary Dashboard<\/h2>\n\n\n\n<p>Finally, a <a href=\"https:\/\/sapphirebusinesstech.com\/en\/excel\/build-an-excel-dashboard\/\">one-page summary view<\/a> makes your tracker genuinely useful for weekly team reviews. To do this, add a new tab named &#8220;Dashboard&#8221; and use SUMIF formulas to pull totals by stage:<\/p>\n\n\n\n<p><em>=SUMIF(Pipeline!D:D,&#8221;Proposal Sent&#8221;,Pipeline!C:C)<\/em><\/p>\n\n\n\n<p>This formula sums all deal values in the Pipeline tab where the Stage equals &#8220;Proposal Sent.&#8221; In summary, by replicating this formula for each stage, you create a live summary that updates every time your team edits the main tracker.<\/p>\n\n\n\n<p>Additionally, insert a simple bar chart from your summary table to give leadership a visual snapshot in seconds.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Step<\/th><th>What you set up<\/th><th>Why it matters<\/th><\/tr><\/thead><tbody><tr><td>1. Pipeline stages<\/td><td>Stage names on a Lists tab and column headers on a Pipeline tab<\/td><td>Maps the tracker to your actual sales process<\/td><\/tr><tr><td>2. Dropdown menus<\/td><td>Data Validation lists on the Stage and Owner columns<\/td><td>Stops inconsistent entries from breaking your filters<\/td><\/tr><tr><td>3. Weighted value<\/td><td>A Weighted Value column with =C2*E2\/100<\/td><td>A forecast that recalculates as deals progress<\/td><\/tr><tr><td>4. Conditional formatting<\/td><td>Three to four distinct colours by stage<\/td><td>Pipeline health can be read at a glance<\/td><\/tr><tr><td>5. Summary dashboard<\/td><td>SUMIF totals per stage and a bar chart on a Dashboard tab<\/td><td>A one-page view for weekly team reviews<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">When should your sales pipeline tracker be built for you?<\/h2>\n\n\n\n<p>You now have the foundation to build a custom Excel spreadsheet that tracks your entire sales pipeline. However, as your team grows and data becomes more complex, a manually maintained spreadsheet can reach its limits, especially when multiple users edit it simultaneously or when you need automation rules and advanced reporting.<\/p>\n\n\n\n<p>At <strong><a href=\"https:\/\/sapphirebusinesstech.com\/en\/about\/\" data-type=\"page\" data-id=\"396\">Sapphire Business Technology<\/a><\/strong>, our <a href=\"https:\/\/sapphirebusinesstech.com\/en\/services-excel\/\" data-type=\"page\" data-id=\"7343\">specialists in Excel solutions<\/a> have built multiple types of custom pipeline trackers, reporting dashboards, and fully automated spreadsheet systems for more than 500 satisfied clients the needs a custom Excel spreadsheet. We do not deliver generic templates, we build solutions that fit exactly how your sales process works and at the same time saving your team hours every week.<\/p>\n\n\n\n<p>Get in touch with Sapphire Business Technology and find out what a truly <a href=\"https:\/\/sapphirebusinesstech.com\/en\/services-excel-spreadsheets\/\" data-type=\"page\" data-id=\"14440\">custom Excel spreadsheet<\/a> solution looks like for your business.<\/p>\n\n\n\n<div class=\"sbt-cta-box\" style=\"margin:30px 0;padding:22px 26px;background:#eef4ff;border-radius:12px;text-align:center\"><p style=\"margin:0 0 16px;color:#1e293b;font-size:1rem;line-height:1.6\">Has your pipeline spreadsheet outgrown manual updates and shared editing? Tell us how your sales process works and we can design the tracker around it.<\/p><p style=\"margin:0\"><a href=\"mailto:contact@sapphirebusinesstech.com?subject=Enquiry%3A%20How%20to%20Build%20a%20Sales%20Pipeline%20Tracker%20in%20Excel%3A%20A%20Step-by-Step&amp;body=Hi%21%20I%20read%20the%20article%20%22How%20to%20Build%20a%20Sales%20Pipeline%20Tracker%20in%20Excel%3A%20A%20Step-by-Step%22%20on%20the%20Sapphire%20website%20and%20would%20like%20to%20talk%20to%20a%20specialist.\" class=\"sbt-cta\" style=\"display:inline-block;background:#0a2351;color:#fff;border:none;border-radius:8px;padding:13px 26px;font-weight:700;font-size:1rem;cursor:pointer;text-decoration:none\">Email our specialists<\/a><\/p><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently asked questions<\/h2>\n\n\n\n<details class=\"sbt-faq\" open style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">What is a sales pipeline tracker in Excel?<\/summary><div style=\"padding:2px 0 12px\">It is a spreadsheet that records every deal with its company, value, stage, probability, expected close date and owner. It gives you a clear view of where each deal stands, so you do not lose track of follow-ups or misread your revenue forecast.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">Which stages should a sales pipeline have?<\/summary><div style=\"padding:2px 0 12px\">Common B2B stages are Prospecting, Qualified Lead, Proposal Sent, Negotiation and Closed Won or Lost. Map the stages to your actual sales process rather than a generic template.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">How do I calculate weighted pipeline value in Excel?<\/summary><div style=\"padding:2px 0 12px\">Add a Weighted Value column that multiplies deal value by probability, for example =C2*E2\/100 where C2 is Deal Value and E2 is Probability (%). The result recalculates automatically as your team updates stages and probabilities.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">How do dropdown menus keep pipeline data consistent?<\/summary><div style=\"padding:2px 0 12px\">Data Validation lists stop variations such as &#8220;Negotiating&#8221; and &#8220;Negotiation&#8221; from breaking your filters. Apply them to the Stage column and, if your team has a fixed list of sales reps, to the Owner column.<\/div><\/details>\n\n\n\n<details class=\"sbt-faq\" style=\"border:1px solid #e2e8f0;border-radius:8px;margin:0 0 8px;padding:4px 16px\"><summary style=\"cursor:pointer;padding:10px 0;font-weight:600\">How do I build a summary dashboard for the pipeline?<\/summary><div style=\"padding:2px 0 12px\">Add a Dashboard tab and use SUMIF formulas to total deal values by stage, such as =SUMIF(Pipeline!D:D,&#8221;Proposal Sent&#8221;,Pipeline!C:C). Then insert a simple bar chart from the summary table for weekly team reviews.<\/div><\/details>\n\n\n\n<p><em>Content created by Sapphire Business Technology &#8211; specialists in Excel, Power BI and Microsoft 365 solutions with over 500 satisfied clients.<\/em><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Key takeaways A sales pipeline tracker in Excel is a custom spreadsheet that records each deal&#8217;s stage, value, probability, expected close date and owner, keeping follow-ups visible and giving you a weighted forecast that recalculates automatically. A well-managed sales pipeline is the backbone of any growing business. Without a clear view of where every deal [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":15085,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[95],"tags":[],"class_list":["post-15084","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel"],"_links":{"self":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts\/15084","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/comments?post=15084"}],"version-history":[{"count":4,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts\/15084\/revisions"}],"predecessor-version":[{"id":15631,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/posts\/15084\/revisions\/15631"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/media\/15085"}],"wp:attachment":[{"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/media?parent=15084"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/categories?post=15084"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sapphirebusinesstech.com\/en\/wp-json\/wp\/v2\/tags?post=15084"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}