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.