Published on March 10, 2014
How-to Make a Sales Pipeline or Sales Funnel Excel Chart
How-to Make a Sales Pipeline Funnel Excel Chart Template • You probably have seen this type of Funnel Chart in Business Intelligence (BI) software packages and wondered how to make this type of chart in Excel. In this presentation, I will show you how to make a graphical view of a Sales Pipeline for use in an Executive Dashboard. 3/10/2014 How-to Make a Sales Pipeline Funnel Excel Chart Template 2
Problems with Funnel Charts • First, we should discuss issues with this type of chart. • Many of the data visualization experts in the industry dislike, sorry, I should have said HATE funnel charts. 3/10/2014 3How-to Make a Sales Pipeline Funnel Excel Chart Template
Why Data Visualization Experts Don’t Like a Funnel Graph? • Data visualization experts do not like a Funnel Graph or Funnel Chart because the chart may have the potential to mislead readers. • The area of each section may not represent the data proportionally to other sections. For instance, if your values for each section/category are the same, then the size of the top section is not proportionally equal to the bottom section even though they are the same value. 3/10/2014 4How-to Make a Sales Pipeline Funnel Excel Chart Template
Why Data Visualization Experts Don’t Like a Funnel Graph? • In this graphic, it can be deceiving to readers that the Sales in the Prospecting phase are much larger than those in the Negotiation phase, when in reality, they are exactly equal. The graphic is basing the height on the category value, not the width or volume of the section. • So be careful when using this as a representation of your data. 3/10/2014 5How-to Make a Sales Pipeline Funnel Excel Chart Template
• Many organizations and companies use the funnel graphic as a standard to convey a pipeline affect for the data. • That means that it is started at the top category and it moves to the bottom category, but not all of the entries at the top makes it to the bottom. • Even though this may be a bad graphic from all design and data visualization perspectives, you may be asked to create this type of graphic for your Executive Dashboard. • Your first step should be to talk with the powers that be about the issues with this type of chart. If your client or executives or managers still want this as part of the Company Dashboard, then give the people what they want. That is why you will see this type of chart in many well-known software packages like Salesforce.com. 3/10/2014 6How-to Make a Sales Pipeline Funnel Excel Chart Template
Now lets say you are the Dashboard Designer for your sales organization and this is your data in Excel: 3/10/2014 7How-to Make a Sales Pipeline Funnel Excel Chart Template
• Lets say that your sample data has categories from your sales organization on how the sales pipeline looks for the company. • Presently there are 652 sales opportunities in the Prospecting stage worth $33.6M. This is the start of the sales cycle for your company and each sales opportunity may be at a different level in the sales cycle. • Typically there are less and less at each progressive stage of the sales cycle because deals are not always a done deal. Some people in the prospecting stage may change their mind and not purchase anything. • Another potential sale that is in the Presentation stage may not get closed because the buyer chose another company. 3/10/2014 8How-to Make a Sales Pipeline Funnel Excel Chart Template
• However, not all sales fall out of the pipeline and your organization may be able to calculate the likely percentage that any given opportunity may close from each stage of the sales cycle. • This is why organizations like to see this data in a pipeline or funnel format because each stage fills the next stage in the cycle. And if you don’t have enough sales in each part of the pipeline, your company will have uneven cash flows as each sale will most likely have an average closing time from start to finish. 3/10/2014 9How-to Make a Sales Pipeline Funnel Excel Chart Template
I don’t see a Sales Funnel or Sales Pipeline Chart or Graph in Excel • Okay, that is enough about why organizations and people love Funnel Charts and the dangers about what they are showing you. • Lets get to the nuts and bolts of How-to make a Sales Funnel chart in Excel. • Using the data points above for our pipeline, lets build the chart. However, I don’t see a sales funnel chart or pipeline chart as an option in Excel. That is the good and bad about Excel. • Not everything is there and built for you, but with the standard tools, you can make a funnel or pipeline chart on your own. 3/10/2014 10How-to Make a Sales Pipeline Funnel Excel Chart Template
• So looking at the standard set of charts that are available in Excel, the closest representation of what we are looking for is a Stacked Pyramid chart. • More specifically, a 100% Stacked Pyramid chart since our data represents 100% of the sales pipeline data and we are not trying to compare the data from one pyramid to another. • You will find the 100% Stacked Pyramid chart under the Column Chart menu in the Insert Ribbon. This is what we are going to base or Sales Funnel or Sales Pipeline and here is how. 3/10/2014 11How-to Make a Sales Pipeline Funnel Excel Chart Template
3/10/2014 12How-to Make a Sales Pipeline Funnel Excel Chart Template
The Breakdown 1) Create a 100% Stacked Pyramid Chart 2) Flatten it to 2D 3) Flip it upside down 4) Label and Clean it up 3/10/2014 13How-to Make a Sales Pipeline Funnel Excel Chart Template
STEP-BY-STEP TUTORIAL ON HOW-TO MAKE A SALES FUNNEL OR SALES PIPELINE CHART OR GRAPH IN EXCEL 3/10/2014 14How-to Make a Sales Pipeline Funnel Excel Chart Template
1) Highlight the Data from A2:B6 (From “Prospecting” to Negotiation Value of “45”) and from the Insert Ribbon, choose the Column Menu from the Charts Group and then select 100% Stacked Pyramid. 3/10/2014 15How-to Make a Sales Pipeline Funnel Excel Chart Template
The resulting chart will look like this: 3/10/2014 16How-to Make a Sales Pipeline Funnel Excel Chart Template
2) In the chart, select the data series for Series one by clicking on the pyramids. Then from the “Design Ribbon”, select “Switch Row/Column” from the Data Group. 3/10/2014 17How-to Make a Sales Pipeline Funnel Excel Chart Template
The resulting chart will look like this: 3/10/2014 18 The funnel chart is starting to take shape. How-to Make a Sales Pipeline Funnel Excel Chart Template
3) Right click on the chart and select “3D Rotation…” from the menu or you can also see this as a choice from the Layout Ribbon in the Background Group. 3/10/2014 19How-to Make a Sales Pipeline Funnel Excel Chart Template
4) Change the Rotation Settings as follows: • X=0 3/10/2014 20How-to Make a Sales Pipeline Funnel Excel Chart Template
• Y=0 3/10/2014 21 Now we are getting really close to the Final Sales Funnel Chart in Excel. How-to Make a Sales Pipeline Funnel Excel Chart Template
5) Right click on the chart’s Vertical Axis and select “Format Axis…” from the menu or you can also see this as the Primary Vertical Axis choice from the Layout Ribbon in the Axis Group. • And choose “Values in reverse order” from the Axis Options Dialog Box. 3/10/2014 22How-to Make a Sales Pipeline Funnel Excel Chart Template
Looking like we want, now we just need to clean up the Chart and give it some Data Labels. 3/10/2014 23How-to Make a Sales Pipeline Funnel Excel Chart Template
6) Clean up: a) Remove the Vertical Axis – Click on the Vertical Axis and press your delete key. b) Remove the Legend – Click on the Legend and press your delete key. c) Remove the Horizontal Gridlines – Click on the horizontal Gridlines and press your delete key. d) Remove the Category Label – Click on the #1 at the top of the Sales Funnel and press your delete key. e) Add Data Labels – Click anywhere in the chart and change Data Labels to “Show” from the Layout Ribbon in the Labels Group. 3/10/2014 24How-to Make a Sales Pipeline Funnel Excel Chart Template
7) Admire your handy work: Your Sales Funnel for Excel should now be completed and should look like this: 3/10/2014 25How-to Make a Sales Pipeline Funnel Excel Chart Template
Sample File & Video Tutorial • You can watch a video demonstration and also get a FREE sample download template for the sales pipeline with instructions here: • http://www.exceldashboardtemplates.com/SalesFunnelChart 3/10/2014 26How-to Make a Sales Pipeline Funnel Excel Chart Template
Tutorial and demonstration on how to make a funnel or pipeline chart typically used in sales dashboards for executives
How To Create a Sales Pipeline Funnel Chart in Excel. August 18, ... but centering the bars makes the funnel process easier to understand and explain.
How To Create Sales Funnel Template in Excel. ... The topmost part of the sales pipeline chart is ... Menu or insert a text box on each stage to make a ...
See the Excel 2007 and Excel 2010 write up and video demonstration here: http://www.exceldashboardtemplates.co... Learn how to make a Sales ...
... learn how to create a sales funnel chart in Excel. Sales funnel chart is ... I am currently working on creating a sales pipeline ... and make its color ...
... to create funnel chart in Excel. Funnel is chart type which can be used for sales data presenting. Funnel chart can display stages of sales progress or ...
... but I am going to walk you through the process of building a funnel chart in Excel 2007 ... This is a 3D funnel chart, ... a sales pipeline or ...
Excel Charting Tip- How To Create A Sales Funnel Or Pipeline Chart. ... So let’s go ahead and create our own Sales Pipeline or Funnel Chart- In Excel.
A while ago, our reader Shay emailed me a Sales Funnel chart template. I had to create a sales funnel for my company and I looked all over the internet to ...