Yet Another Sales Funnel Chart in Excel

Posted on March 5th, 2012 in Charts and Graphs - 41 comments

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 see how to do what I needed to do. I couldn’t find anything. I tried your funnel chart as well but because my numbers are all over the place I couldn’t get it to work for me.

I get so much information from you that I decided to share this chart with you. I used the shapes drawings to create the funnel graphic and the camera tool to place the values in each shape. I used formulas in the original data so that I will not have to recalculate every month.

I took the liberty of making little changes to Shay‘s funnel chart template to make it even better. Here is how the funnel looks like.

Sales Funnel Chart in Excel

How to use this Sales Funnel Chart?

Using this funnel chart is child’s play when you compare with creating actual funnel of $ 1.371 Bn.

Just download the sales funnel chart template. Go to Data sheet and plug in the numbers. And your funnel is ready.

How is this Funnel Chart setup?

There are 2 components to this funnel.

The blank funnel:

This is nothing but a drawing of funnel made with several cylinders. You can create such a funnel by using drawing tools in Insert ribbon.

Blank Funnel Chart - Excel

The Numbers:

All the numbers (and text) you see in the chart is made using a bunch of text boxes. Each text box refers to one value in Data sheet. Once all the text boxes are added, we just adjust their formatting and our funnel chart is ready.

The numbers & formulas for Sales Funnel

Download this Sales Funnel Chart

Click here to download the Excel workbook. The file contains 4 different funnel formats. Just plug in your funnel numbers to go.

Thanks to Shay

Many thanks to Shay for creating this and sharing with us all. If you liked this funnel chart, please say thanks to Shay.

Do you use Funnel Charts?

Back when I was working with an IT services company, sales funnel is a key part of our monthly scorecard. We would use the funnel get a view of our sales process, how it has fared compared to last month, year and whether we are in a good shape to reach our annual goals.

What about you? Do you use funnel charts? Share your experience with us using comments.

More on Sales Analysis & Presentation using Excel

Sales Analysis using Excel – 78 Alternatives

Budget vs Actual Charts – 14 Options

Sales Dashboards in Excel

Sales Funnel Charts

Excel School

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

41 Responses to “Yet Another Sales Funnel Chart in Excel”

  1. Robert says:

    I am never using funnel charts. From my point of view they are a very ineffective way of visualizing data. I guess I do not have to go into the details, Jon Peltier already said everything about it:

    http://peltiertech.com/WordPress/bad-graphics-funnel-chart/

    http://peltiertech.com/WordPress/sales-funnels/

    Just one idea regarding the formulas in column E of your data worksheet: you can get rid of all the nested IFs by using another format string in the TEXT function:

    =TEXT(B5,”$#,##0.00″)&” (“&TEXT(C5,”0%”)&”)”

  2. Clarity says:

    Nice chart Chandoo but I don’t like the sound of a “Fist interview!

  3. hanif says:

    thanks to Shay

  4. Jon Peltier says:

    I don’t believe you’ve posted such an awful chart. The funnel metaphor isn’t all that great anyway, and most attempts to graph it like a set of trapezoids are failures.
     
    This “funnel” doesn’t even look like a funnel, and nothing about the stacked up disks indicates values visually. You would be better with a simple table, since any attempts to interpret the chart graphically are doomed.
     
    If the graphic elements do not encode the data in any way, then don’t graph the data.

    • Chandoo says:

      Hi Jon.

      I disagree. When I was working as a business analyst we used to make this exact chart every month. Not just that, the cxo meeting would stop at this slide and discuss at length about the process.

      I agree that this chart is a poor choice but if it is what your boss wants, as the case with shay, we have no choice but to make it.

      • Jon Peltier says:

        I’m sure you had to stop at this slide and discuss it at great length, because it makes absolutely no sense. A table would be easier to describe, and all those little disks are nothing but chart junk.
         
        Can you say in all honesty that this chart is worth the ink it costs to print? Not just a poor choice, but worse than useless?
         
        If your boss insists on this kind of chart, ask where the numbers go, and how the graphical elements reflect the corresponding numerical values. I’m sure that response will also be useless.

        • Shay says:

          Thanks for your feedback and I completely understand what you are saying. The chart here is for the sales process. The thought process behind it is the top is the beginning of the sales process and the bottom is the closing or the sale. The “funnel” represents the steps that we go through for sales and not the dollar value. It is just an image to show where money is in each step of the sales process not the dollar value. Thank you for taking the time to review and comment.

          • Shay says:

            Also, this is a replica of a chart that they are familiar with and I just had to replicate it in a dashboard. So actually no, I didn’t have to stop and explain. Thanks again for your comments.

  5. Greg says:

    @Clarity. I agree. I don’t want any part of a “Fist interview”

    And I agree with Jon on the chart. Reminds me of when my boss fell in love with the speedometer chart and I had to plaster 8 of them on a single page. What a PITA that was.

  6. Anis Dadani says:

    Hi,

    Good attempt however i agree with Jon.

    On the other issue can you please help me in putting small numbers in cell as superscript with other numbers

    regards
    Anis

  7. Bob Gannon says:

    Not only does the chart provide no insight, the numbers make no sense. There are probabilities where there are $0 values and a low probability where you already have a commitment. The amounts in the chart add to $580 million, not to $1.371 billion. What happened to the other $791 million? This also seems a case where the preciseness ($1,371,291,502.10) may far exceed the accuracy. Further, if you only got one $5 thousand dollar commitment out of a $580 million (or $1.4 billion) opportunity you have a much bigger problem than vizualization – the data (not the chart) suggest you spend your time selling not drawing.

    • Shay says:

      Thanks for the comment Bob. Let me start off by saying that I am a noob here and the data and labels are random. I didn’t consider that people would scrutinize the data. I changed the information for confidentiality purposes and I changed it quickly because I am at work and should be working. Just giving back a little because I have taken so much from everyone here. I did not take the time to see if the numbers would add up correctly after changing them. The chart is really for the visual effect and it is a cool trick that can be applied in many scenarios. However, if I post anything in the future, I will be sure to make sure the numbers add up properly. Thanks again.

      • Jon Peltier says:

        “I didn’t consider that people would scrutinize the data.”

        It’s a chart. Its purpose is assumed to be to represent the data. Visual effects that have no relation to data really ought to have no place in any reporting or decision-making process.

        • Shay says:

          Unfortunately, everyone does not agree with you and a chart can represent whatever the person designing the chart wants it to represent. By definition a chart is a sheet of information in the form of a table, graph, or diagram. This would fall under diagram. Again, moving forward I will make sure the numbers add up.

          • Jon Peltier says:

            “a chart can represent whatever the person designing the chart wants it to represent”

            Interesting interpretation, if a chart is actually a communication medium.

  8. Ron says:

    Of course that’s assuming you had a choice of not making the funnel chart and the powers that be in the organization are willing to listen to alternatives. If those who sign the all-mighty paycheck require you to put something like this together, however wrong it may be, its good to know how to do it. Thanks Shay for contributing!

  9. Russ says:

    I agree with Ron. While I haven’t played around with the funnel chart yet, I always appreciate when someone shares something that they have found useful. For those that don’t like the chart, no need to use it. For others, like me, I will download the chart and play around with it to see if there is something that I can incorporate into my work.

    Thanks, Shay, for sharing something you’ve found useful. And, in the future, please don’t hesitate to share anything else you find helpful or useful.

  10. roger says:

    I agree with the ‘bean counters’ in the audience. If I presented a chart tallying $1.37B that was mathematically $580M I’d fold my green eye-shades and pick up a shovel at the tool room door. NOT
    PROFESSIONAL! Too bad the graphic couldn’t incorporate a size to cost ratio. If it is projecting a linear event report understandable but then the $0.00 amount shouldn’t be a percentage either.
    Gad, it is easy to chew on the meat someone else killed and served, but we are about training aren’t we?
    CPI is Continuous Process Improvement and we all learn more delightfully from listening to someone else getting roasted.
    Lighten up, but continue to expect High Standards. And Inspect what you Expect to improve your Aspect

  11. Hui... says:

    I personally think the chart is poor as zero should never be shown as anything but zero.

    However often bosses go away on courses/conferences and come back with a “I want one of those” moments

    If the boss wants it, that is what he should get !
    However you also have a responsibility to demonstrate alternatives that may assist him (the boss) getting the story across in a better manner.

    One issue I need to note here is that Chandoo.org isn’t about telling you how to do your job.
    It is about demonstrating techniques that may be applicable in some cases and often can be extrapolated or used in other spaces.
    In this case the use of Text Boxes to assign values / text to various objects has a huge application in lots of presentation/dashboard techniques in Excel.

    I think the effort of MVP’s throwing words back and forth at each other would better be spent requesting MS albeit nicely to add dynamic objects to Excel where the size of the object can be linked to a cell without reverting to VBA!

  12. Jon Peltier says:

    1. A boss shouldn’t get anything he wants, if it can be demonstrated that what he wants is counterproductive.
     
    2. There are a lot of techniques that could be shared with everyone, but should not be shared if they are counterproductive. Using textboxes to display linked values is one very good technique. Putting textboxes into the context of a silly graphic like this one is a bad technique.

    3. Hmm, dynamic objects in excel that are sized to values in a cell. . . This might be useful, but I predict it would be grossly misused and abused. Markers in a bubble chart are sized to cells in the worksheet, and they’re not always done right.

  13. If you were writing a book that your boss dictated, and he said, “Him ain’t going there” would you edit it or leave his words as they were? Of course, you’d edit it. Why don’t we show the same respect for numbers as we do for words.

    • Jon Peltier says:

      Naomi -
       
      Thanks. I couldn’t come up with a quick example for when it’s “appropriate” to correct the boss, but yours is perfect.

    • Shay says:

      Thanks for the comment. That’s funny that you would say that. I had that happen once and I made the grammatical change. My boss at the time argued me down about it and changed it to what she wanted before having it sent off. Sometimes they want what they want.

  14. Dave says:

    Lighten up guys, its only a bit of fun, dont be so serious. Mountains out of molehills come to mind :)

  15. Erk says:

    Hei! Chandoo,

    I find your site really useful, it helps me produce good results at work. I like all these different charts you throw out. Ok Jon ” I hate everything unless it’s my idea” Peltier doesn’t like the things you put up BUT you have many, many more readers than Jon. Please keep throwing us these ideas, because one day they might just come in useful!
    Thanks for a great site, don’t go changing!

    • This blog is for training and learning. As several of you have mentioned, hearing strong opposing opinions is instructive. It encourages us to think for ourselves. However, name calling is totally inappropriate, especially when it is so undeserved. I cannot think of anyone who accepts constructive criticism as readily as Jon does, often complimenting those who suggest modifications to his work. Here is a tweet that Jon wrote about http://onforb.es/xjEEZg: “I never would have thought of using the Likert chart to show the breakdown by age. Well, I will now. A great insight.” I certainly wouldn’t call anyone who wrote that “I hate everything unless it’s my idea.”

  16. John Hackwood says:

    Agree Dave I reckon it looks like a funnel and wasn’t bothered about the actual detail used (I never even looked at it to be honest) as I just saw it as nice example of how Excel can be creatively used to tell a story and the actual usage wasn’t the point I was quite surpised it aroused such negative feedback. Sure it is definitely better suited to situations where a dataset is being filtered down to an area of focus. But Shah thanks for being so kind in taking your own time to submit this example from your job.

  17. Chandoo says:

    @all… While I appreciate such diverse comments and strong views, keep in mind that the funnel in this chart is just a diagram. It has no relation to the data other than acting as a placeholder for numbers. So it is not even a chart. Think of it as a background image for the numbers.

    Remember the motivation for this: In Shay’s words,

    “but because my numbers are all over the place I couldn’t get it to work for me..”

    I shared this file with you because I like the idea of using a static image and text boxes to show & format numbers so that it looks a little more exciting than the data table in DATA sheet.

    And also, I would make the same chart if my boss asked me to. Especially if she has 22 years experience, strong results & excellent support behind her. (Which was the case in my experience).

    One more note: I wish you would focus less on the numbers and more on technique as these are just Random numbers.

  18. Shay says:

    Thanks for all of your comments. Believe it or not I did read the information Mr. Peltier wrote about the topic however this is what was needed and is actually a replica from a quite popular crm system. Just a little creativity was needed to pull this off. The data that I used here is not actual data. I changed the numbers and the titles for confidentiality purposes. So if the data doesn’t at add up, it is not real data. You’re welcome to all who said thanks and thanks for the constructive criticism from those that provided it. Thank you all for the information that has been contributed. I have learned a lot from this site and am happy to be able to contribute a little piece of my imagination.

  19. Fayley says:

    The “Sales Funnel” is the latest rage with Managers at Senior and Executive Level. As analysts we have to present the data in the way that the users understand it … and reporting is such a fashion thing … this year its funnels and dashboards, last year it was balanced scorecards! As a way of indicating that you start with a large quantity and whittle it down to the won business its a reasonable visualisation. I actually do a single stacked column graph with a white overlay of shapes to create a funnel look – they love it (go figure).

  20. XLCalibre says:

    Having worked with unreasonable bosses in the past, I can understand the difficulty when they simply won’t listen to reason.

    I recently had a very similar problem – organisation hierarchy was being presented in a standard pyramid / triangle shape without any link to the actual data. The director wouldn’t consider changing the shape. So I made a graph that represented the data but kept to the shape they liked: http://xlcalibre.com/hr-dashboard-organisation-heirarchy-pyramid-chart/

    It could easily be used for a Sales Funnel instead.

  21. Nishan Ibrahim says:

    Awesooooooooooomeeeeee work….. like it……….

  22. Kathryn says:

    From a sales standpoint, I’ve been trying to find something like this forever. You have saved me a lot of time. Awesome chart Chandoo and thank you so much for sharing it.
    I would also like to create something like this to show the process from prospect to customer. If you have anything else you have found effective for this or other ideas, I’d be interested to see too.
    Thanks Chandoo.

  23. ABISH says:

    Hi Chandoo, any chance you can send me a funnel with 13 stages? I was not successful in making additional stages and copying the formulas. Pls advise.

    Thanks

    Abish

  24. Ashish says:

    i download the excel template which is a zip file and it has all xml files and not xls files. can you please upload the xls file.

Leave a Reply