How to Create a Sales Pipeline in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Are you a business analyst or running your own business farm? Then, you must hear the term sales pipeline. A contract travels through various phases of sales in a selling process before being closed successfully. A sales pipeline can help estimate upcoming sales by forecasting income and available funds in addition to optimizing sales activity. That’s why it’s so important in business. In this article, we’ll demonstrate 4 quick and convenient steps to create a sales pipeline in Excel. So, let’s go through the entire article to understand the topic properly.


How to Create Sales Pipeline in Excel: 4 Easy Steps

In order to create a sales pipeline, we need some basic data first. From these pieces of information, we can easily create this thing by just following some easy steps. In the following section, we’ll illuminate these steps. So, let’s see the procedure to create a sales pipeline step-by-step in Excel.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


Step 01: Prepare a Dataset

At the very beginning, we must construct or import our dataset, which includes the necessary data for creating a sales pipeline. Here, we are going to use a report of ABC Organization: Deals of 2021. This dataset contains the Qtr (a quarter of the year), Month names, Region, and different types of status in columns B to I respectively.

sales pipeline in excel

Note: This is a basic dataset with dummy data to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
You can notice that this dataset is large in the vertical direction. To accommodate the largest part of the table in the image, we used Freeze Panes in the worksheet.
Our first job is done. Now, let’s go to the second one.


Step 02: Create PivotTable

In this step, we’ll embed a PivotTable from the above-mentioned data table. Allow me to demonstrate the process below.

  • First, select any cell inside the dataset. In this case, we chose cell B4.
  • Then, navigate to the Insert tab.
  • After that, click on PivotTable on the Tables group of commands.

Create PivotTable

Immediately, the PivotTable from table or range dialog box appears before us.

Here, you can see the whole dataset is selected automatically. But double-check manually to avoid any unwanted mistakes.

  • Then, select New Worksheet in the Choose where you want the PivotTable to be placed section.
  • Later, click OK.

inserting pivot table in Excel

Instantly, you can see this PivotTable in the new worksheet.

  • Then, click on any cell inside the PivotTable area and you can find the PivotTable Fields task pane on the right side of the display.

pivot table fields task pane in Excel

  • Now, check the boxes of Identified, Validated, Qualified, Proposed, and Won. Excel will automatically place these fields into the Values area.

Working on pivot table task pane to create sales pipeline in excel

Here is the PivotTable present in our worksheet.

But we want to see the field name, like Identified, Validated. Not at all like the Sum of Identified. To do this, we should replace them.

  • So, tap CTRL + H on your keyboard to bring up the Find and Replace wizard.
  • In the Find what box, write down Sum of.
  • Also, keep the Replace with box blank.
  • Then, click on the Replace All button.

Find and Replace dialog box

Suddenly, you will get a MsgBox with the message All done. We made 5 replacements.

And the PivotTable looks like the one below.

Inserting pivot table to create sales pipeline in Excel


Step 03: Copy Values from PivotTable

Now, we have to copy these values from the PivotTable to somewhere else as we cannot insert a chart from this table. Let’s see the process in detail.

  • Firstly, create a new table in the B11:C16 range with two columns Stages and Count.
  • Secondly, go to cell B12 and enter the following formula.
=TRIM(B5)

Here, B5 represents the first text string in the PivotTable. The TRIM function removes the extra spaces from the text string in cell B5.

  • Thirdly, press ENTER.

Copy Values from PivotTable

  • At this time, bring the cursor to the right bottom corner of cell B12 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool. Consequently, drag this tool up to cell B16.

Instantly, Excel copies the formula to the lower cells and as a result, they get results inside them.

using fill handle to create sales pipeline in excel

  • Again, jump to cell C12 and insert the following formula.
=C5

This formula simply fetches the value from cell C5 to cell C12.

  • As always, press ENTER.

Copying Values from PivotTable to create sales pipeline in Excel


Step 04: Insert Funnel Chart

Here comes the most interesting part of the article. Now, we’ll insert a chart to complete our objective properly. So, without further delay, let’s dive in!

  • Primarily, select cells in the B11:C16 range.
  • Then, advance to the Insert tab.
  • After that, click on the Insert Waterfall, Funnel, Stock, Surface or Radar Chart drop-down icon on the Charts group.
  • Next, choose the Funnel chart from the drop-down list.

Insert Funnel Chart

Suddenly, it’s before our eyes.

  • Following this, give a suitable chart title and make the axis labels bold and a bit bigger.

Chart title and axis labels in Excel

  • At this moment, select the chart and press CTRL+X on your keyboard to cut it and paste it into a new worksheet named Pipeline using the CTRL+V shortcut.

Now, you must be thinking about how the color gets changed and the chart gets a shiny look. Don’t worry! It’s so simple.

  • Firstly, right-click anywhere on the bar of the chart.
  • Then, from the context menu, select the Format Data Series option.

using gradient fill in chart in Excel

Immediately, a Format Data Series task pane will be exhibited on the right side of the display.

  • Secondly, click on the Fill & Line icon.
  • Then, select Gradient fill under the Fill section.
  • Later, click on the Preset gradients drop-down icon. And you can choose whatever style you like from the available options.

Format Data Series task pane in Excel

Now, we’ll insert some slicers to get more control over the chart and to make it dynamic.

  • Right now, get back to the PivotTable worksheet.
  • Then, click anywhere inside the PivotTable.
  • After that, proceed to the PivotTable Analyze tab and click on the Filter group of commands.
  • Later, select the Insert Slicer command.

Insert slicers to create sales pipeline

Abruptly, the Insert Slicers dialog box will pop up.

  • Forthwith, check the boxes of Qtr, Month, and Region and click OK.

As a result, we can see three different slicers in the spreadsheet.

Slicers in excel spreadsheet

  • Then, select them all and cut them from this sheet. And, paste them into the Pipeline worksheet.

  • Next, observe the steps in real-time in the animated GIF below to feel the dynamism of this chart.

Inserting Funnel Chart to create sales pipeline in Excel


Practice Section

For your convenience, we have provided a Practice worksheet like the one below. Please do it yourself.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice.


Conclusion

Now, hopefully, you’re able to create a sales pipeline in Excel in a simple and concise manner. Thank you for reading this article. Please let us know in the comment section if you have any queries or suggestions. Keep learning new tricks in Excel.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo