How-to Make a BETTER Excel Sales Pipeline or Sales Funnel Chart

Better Sales Pipeline Graph - Sales Funnel Chart in Excel

 

Learn how to make an accurate sales pipeline in Excel.  In previous posts, I have showed you how to make a sales funnel chart using an Excel pyramid chart.

Final Excel Sales Pipeline Chart

You can check out the previous Excel Sales Pipeline blog posts here:

How-to Make a Sales Pipeline Funnel Excel Chart Template

How-to Make a Cool Looking 3-D Sales Funnel or 3D Sales Pipeline Chart in Excel

Where Did My Excel 2013 Pyramid Charts Go? (Or How-to Make a Sales Pipeline Chart in Excel 2013)

 

However, there are many problems with distorting the true picture of the Sales Pipeline with that type of chart.  For instance, notice that all the phases of this Excel funnel chart makes it look like Negotiation is smaller than Prospecting, however, all phases have the same value so the funnel chart should be a column, not a upside down pyramid.

Sales Funnel Pipeline Chart

 

In the new Better Sales Funnel Excel Chart, notice how you can see discrete changes from one sales stage to the next.  Below you will see that Discovery Stage is very close but slightly larger than the Presentation Stage.  You would not have been able to easily see the difference in the previous chart.

image

Now let me show you how you can easily create this chart with the step-by-step and video tutorial below.  There is also a Free Template Download File below.

 

The Breakdown

1) Create the Sales Pipeline Categories and Data

2) Insert Column Between A & B and Add Filler Series Formula

3) Create Stacked Bar Chart

4) Flip the Vertical Axis

5) Change Horizontal Axis Bounds

6) Delete Horizontal Axis and Vertical Axis and Vertical Gridlines and Legend

7) Change Fill Options and Gap Width of Filler Series

8) Change Color of Data Points

9) Add Data Labels10) Change to Custom Number Format

 

Step-by-Step

1) Create the Sales Pipeline Categories and Data

Normally, your sales data would look like this (with categories and a value):

image

This is fine to start, but we will need to make a slight adjustment in the next step.

 

2) Insert Column Between A & B and Add Filler Series Formula

In order to get our Excel sales funnel chart to line up and look like a funnel or pipeline, we need to add another data series that will push the sales data to the right location in the chart.

To do this, we need to add a column for the new data series between our original data.  So right click on column B and click on Insert.  This will add a column between column A and B in the spreadsheet.

image

Then in Cell B2, enter this formula =IF(MAX($C$2:$C$6)=C2,0,(MAX($C$2:$C$6)-C2)/2)    and copy down to cells B2:B6. image

Here is a breakdown of the formula:

=IF(

this row contains the maximum value in our sales data MAX($C$2:$C$6)=C2  (if it is the maximum value, then we don’t need a filler bar since this will be our largest value in which to center all other values)

then enter in a zero in the current cell,0

else if it is not equal to the maximum sales value, then enter in this value which is equal to 1/2 of the maximum sales value minus the current row value,(MAX($C$2:$C$6)-C2)/2)  (This will make the fill value center the sales data bar on the maximum value)

 

3) Create Stacked Bar Chart

Now that we have our data setup for the Excel Sales Funnel Chart, let’s create the graph.  To do this, highlight the Range A1:C6, then go to your Insert Ribbon and Choose the a Stacked Bar Chart from the Chart group:image

Your chart should now look like this:image

 

4) Flip the Vertical Axis

If you look at the right (red) sales series in the Sales Pipeline Chart we just created above, it looks like an upside down pipeline.  So we need to flip the vertical axis so that Prospecting is on top and Negotiation is on bottom.

To do this, Right Click on the Vertical Category Axis and Select “Format Axis” from the context menuimage

Then from the Series Options, Check the Box for “Categories in Reverse Order” in the Axis Options Dialog Boximage

Your chart should now look like this:image

5) Change Horizontal Axis Bounds

You may have noticed that there is more white space than we need on the right side of the chart.  Excel always adds in more space than is needed for the axis that contains values.  So we need to set the horizontal axis maximum bound to the maximum value and the minimum bound to zero.

To do this, Right Click on the Horizontal Axis Select “Format Axis” from the context menu:image

Change the Maximum bound equal to your maximum value, in our case 33631725 and then Change the Minimum bound to zero:image

Your chart will then look like this:image

6) Delete Horizontal Axis and Vertical Axis and Vertical Gridlines and Legend

We can now delete all the things that aren’t needed.  To do this, Click on the chart, then click on the following and Press the Delete Key:

a) Horizontal Axis

b) Vertical Axis

c) Legend

d) Vertical Gridlines

e) Optional – Delete the Title or add text to the chart title at your discretion.  I changed mine to Sales Pipeline.

Your chart should now look like this: image

 

7) Change Fill Options and Gap Width of Filler Series

This step will bring the chart into shape.  We need to hide the filler series and push the bars together.

To do this, Right Click on the “Filler Formula” series (left most blue series) and choose “Format Data Series” from the context Menu:image

Then change the “Gap Width” to 0% from the Series Options:imageThen choose the Fill Options Menu then choose “No Fill” radio button:image

Your chart should now look like this:image

8) Change Color of Data Points

As you can see from the picture above, the pipeline is all one color.  So I suggest changing the colors of each data point.

To do this, Select any Data Point in the Chart by clicking on a single bar 2 times (twice). You will then see that only one data point is selected instead of the entire series.

Your selection might look like this:image

Then you can Right click on that single data point and choose the “Format Data Point…” menu option:image

Then goto the Fill options and choose Solid Fill and pick a color.  Then also choose a Border of Solid Line and a color of Black.image

DO NOT CLOSE THE DIALOG BOX.  Your chart should look like this:image

As I said above, you don’t have to close the dialog box and you can repeat this step for each data point by double clicking on the data point until only the data point is selected.  Then

Repeat these fill and border steps for all the sales stages in the chart with a different color.

Your chart should now look like this:image

9) Add Data Labels

We are getting very close.  Since we deleted the stages from the vertical axis, we should add them back as data labels and also include the sales value.

To do this, Select the chart in the white space, then from the Design Ribbon, choose Data Labels from the Add Chart Element button and then choose the More Data Label Options… menu option:image

Your chart will now look like this.  You should select the Filler Series Data Labels on the left and press your delete key:image

Then right click on the data labels in the Sales series and Right Click on any of the labels and choose “Format Data Labels” from the context menu.

From the Dialog Box under the Label Options group, select label contains as “Category Name” and “Value” with “New Line” as the separator and “Center” as the Label Position to add these to the labels on the chart. DO NOT CLOSE THE DIALOG BOX as we will use it in the next step.

image

Your chart should now look like this:image

10) Change to Custom Number Format

This is an optional step, but I don’t like so much detail in such a large number.  My brain just can’t get grasp it quickly.  So I like to format large numbers with a $#M (for millions) or $#K (for thousands).  So my final step, is to add a custom format by selecting the data labels and going to the number format options and entering this custom formula.

If you want to learn more about this technique, check out this post:

How-to Format Chart Axis for Thousands or Millions

 

To do this, choose Custom as the Number Category and put this formula    [>999999]$#,,”M”;[>999]$#,”K”;$#    (note, if you are having problems copying and pasting this formula into Excel, you may have to delete and replace the “ ” quotation marks as the browser may not be truly copying a quotation mark) into the Format Code box and and then press the Add button.image

Your final chart will then look like this:image

 

 

Video Demonstration

 

Free Excel Template File Download

Better-Excel-Sales-Funnel-Sales-Pipeline-Chart.xlsx

 

Even though this chart it doesn’t look as pretty as the 3-D pyramid sales pipeline Excel chart, it is more accurate to the human eye and to the data.  Let me know what you think of the Better Excel Sales Funnel Chart in the comments below.

 

Steve=True