Quick Tutorial: Creating Funnel Charts in Excel

I recently had to create a funnel chart for a presentation. Surprisingly, Excel does not come equipped with a ready-made template for such charts — despite the popularity of these charts in marketing (remember the Purchase Funnel from Marketing 101?) So I decided to create my own template. This post will show you  how to create your own funnel chart using either of two methods (one manual and one automated).

Method 1: Manual

The idea is very simple: You can create a funnel chart by combining two horizontal bar charts. You create one horizontal bar chart (let’s call it Chart 1) that reflects 1/2 of all the values in your desired funnel chart. Then you create another horizontal bar chart (let’s call it Chart 2) that is an exact mirrored version of Chart 1 – i.e., it reflects 1/2 of all values in the funnel chart, but with a negative sign. You combine the two and you get your funnel chart. Below I’ve listed a more detailed step-by-step set of instructions:

1) Create two “dummy” variable columns in your data set. The first “dummy” variable halves your funnel values and makes them negative. The second “dummy” variable halves the funnel values but keeps the sign unchanged.

2) Select the two “dummy” variables as your Y-values, and the funnel categories as your X-values, and insert a “Bar Chart.”

3) Clean up the chart. Select the vertical lines that represent the percentage bands, and delete them. Then click on the X-axis (where you have the percentages listed: -60%, -40%, …, 40%, 60%) and delete it. Select the legend with “Dummy 1” and “Dummy 2” and delete it. You should get the following result:

4) Right-click on either the blue or the red bars (it really doesn’t matter) and select “Format Data Series.” Then in the “Series Options” menu, go to “Series Overlap” and change the value to 100%. While you are still in the “Format Data Series” window, go to “Fill” and choose the color of your choice to format the bars (I’ve elected to use light grey in my example). Then click on the other set of bars and select “Format Data Series,” then go to “Fill” and choose the same color you chose for the first set of bars (again, in my example, I’m using light grey). If you followed this correctly, you should get the following result:

5) Click on the vertical axis (just click on any of the funnel values – e.g., “Repeat Purchase”). Select “Format Axis” and then in the “Axis Options” menu, look for an option to have “categories in reverse order.” This will ensure your funnel is trending downward (i.e., it looks like an inverted pyramid). While you are in the same menu, also change the values for “Axis Labels” to “High,” and for “Major Tick Mark Type” to “None.”

6) Finally, you may want to make the bars a bit thicker, so they look more as part of the same funnel. To do that, right-click on either set of the bars and select “Format Data Series.” Then in the “Series Options” menu, change the “Gap Width” value to 30%. You should get the result below:

Et voilà! You have your funnel chart ready!

Method 2: Automated (Via an Excel Macro)

If you found Method 1 a bit too complicated or laborious, you can take advantage of a short script that I have coded for Excel. This can be pretty handy especially for those situations where you have to create multiple funnel charts.

Now, I am not a professional coder so please don’t poke fun at my programming skills. The script does not adhere to best practices in coding, but it works! And that is what matters to me in little situations like this one.

Three important things before I make the code available for you:

1) Don’t forget to enable macros when you open the attached Excel template. This is very important — if you fail to enable macros, the script will not work.

2) By way of instructions: Enter your categories for the funnel in Column A. Then enter values in Column B. Note: You can have as many or as few rows of funnel data as you want. Percentages will be automatically calculated in Column C. Please do not delete or modify the formulas in Columns C, D and E.

3) To execute the script, you can simply click on the “Create Graph” button that I have added for your convenience. Or, alternatively, you can run the script through Excel — go to the “Developer” tab on the ribbon, select “Macros,” select the “FunnelGrapher” macro and click on the “Run” button.

Click here to download the Excel template with the script. If you run the script with the sample values provided in the template, you will get the following result:

Disclaimer

A quick disclaimer: Many of you may point out that Microsoft Office 2007 and later versions already have a template for funnel charts. All you need to do is just go to Insert->SmartArt->Pyramid->Inverted Pyramid (see below).

That is true but the SmartArt inverted pyramid is just an image; it’s not really a chart. It does not reformat itself based on the values. The walls of the pyramid will be slanted at a constant angle, even if the rate of decline from one funnel step to another is variable. This makes the SmartArt pyramid a very rudimentary (I would even call it inferior) visual. Hence, the need for a real value-based funnel chart, such as the ones created through either of the two methods above.

This entry was posted in Tutorial and tagged , , , . Bookmark the permalink.

30 Responses to Quick Tutorial: Creating Funnel Charts in Excel

  1. Fayley says:

    I need a real inverted pyramid. The value in the widest section at the top will not always be the largest value, therefore the segment representing this should have less height. At the moment I create a stacked single column graph and overlay drawn white shapes to leave a funnel visible. It would be great to have a real graph automatically.

  2. Emil_M says:

    Hi Fayley, thanks for your comment. I am not sure I fully understand what type of inverted pyramid you’re trying to create. If the value in the widest section is not be the largest, then why will that section be the widest? Is the width in your graph dictated by another factor, which is different from the actual values? Can you upload an image of the funnel you create using the method you’ve described? That would make it easier for me to understand. Perhaps a few minor tweaks to my macro would be able to solve your issue, but to help you, I need to understand what you mean by a real inverted pyramid.

  3. Pingback: Analizar y Optimizar Embudos de conversión | Trucos Optimización

  4. Hi Emil, great post my funnel is up and running as per Method 1. Just curious how you got the percentage values to appear by the category name in Method 2 – unfortunately I’m working on a Mac with Excel 08 that doesn’t appear to support macros, soI can’t easily look at your script. Any direction would be really appreciated. Also would love to know what you think of my product, watch me on our website.

    • Emil_M says:

      Hi James, thanks for the kind words. Glad to hear you found my post helpful. Regarding macros not working in versions of MS Office for Mac — I discovered that myself the hard way some time ago when I tried to do real work on my Mac Mini.🙂

      I assume you’re asking how to do the formatting under the manual method (Method 1), correct? Right click on the chart and select the option to format label. Then select the radio button for percentages. It could be different under Mac, though. I don’t use a Mac any more so I can’t check and confirm that. Is this helpful or have I misunderstood your question?

      Regards,
      Emil

  5. zult1 says:

    I needed to represent some data in a funnel graph and found your method to be the quickest. Thanks for posting this and saving me time of struggling with Excel. The macro works like a charm.

    • Emil_M says:

      Hi, thanks for the kind words about my post. I’m glad the macro worked well and helped you save time and effort for your assignment. Regards!

  6. Mohit says:

    Many thanks for your tutorial. It was really helpful. Warm regards

  7. Useful info. Lucky me I discovered your site unintentionally, and I’m surprised why this coincidence did not came into being beforehand! I bookmarked it.

  8. Vincent says:

    Thanks very much. This helped me out of an otherwise tight spot…

  9. Pingback: Cómo hacer un embudo de conversión y saberlo usar | JD Community Manager

  10. Hosea says:

    I think this is among the most important info for me. And i am glad reading your
    article. But want to remark on some general things, The website style is ideal, the articles
    is really nice : D. Good job, cheers

  11. Impressive critique, I loved the technology in action 8th edition buzzwords answers part

  12. Preferabpy the flow program is desiigned at time the ceiling is designed.
    Theres a revolutionary method of roof ventilator systems famous in the industrial areas ‘ the
    turbo ventilator fitted to the roofs of the shed.
    As a result, it is necessary to use a connection standard that can handle this increased data transfer successfully.

  13. Lucy Myers says:

    Hi, brilliant suggestion and an exciting post, it will
    be interesting if this is still the state of affairs in a few years time

  14. Awfully fascinating short article

  15. Impressive Piece of writing

  16. I hardly ever comment on these items, but I thought this
    on deserved a thumb up

  17. Wonderful Read, I loved the electronics basic questions for interview section

  18. Extremely fascinating piece

  19. Awfully fascinating critique

  20. I seldom discuss these articles, but I thought this on deserved a well done

  21. Luke King says:

    Impressive Piece

  22. Lucy Diaz says:

    Hi, great suggestion and an interesting article, it’s
    going to be exciting if this is still the case in a few months time

  23. Well I searched for the article title and discovered this,
    great read

  24. Hey would you mind letting me know which hosting company you’re using?
    I’ve loaded your blog in 3 completely different
    browsers and I must say this blog loads a lot quicker then most.
    Can you recommend a good web hosting provider at a reasonable price?

    Thanks a lot, I appreciate it!

  25. Link exchange is nothing else however it is simply placing
    the other person’s weblog link on your page at suitable place and other person will also do similar in favor of you.

  26. It’s wonderful that you are getting thoughts from this
    piece of writing as well as from our argument made here.

  27. Marissa says:

    I have read several good stuff here. Definitely price bookmarking for revisiting.
    I wonder how a lot attempt you set to create this kind of great informative
    web site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s