Every, and I mean every, sales and marketing consultant or manager needs to have funnel charts somewhere in their bag of tools. Here are some tips on creating funnel charts that will make your audience pay attention in your next presentation.
This blog will show you five different ways to make funnel charts in Excel and two ways to link Excel funnel chart data into PowerPoint slides. Funnel charts are a favorite of marketing and sales for showing the volume and progress through different stages of the customer journey, from initial contacts, to qualified leads, to prospects and finally sales.
If you know someone that does reports for sales and marketing, please forward this to them. They will thank you.
The first Excel funnel chart fits nicely within crowded print reports. This in-cell funnel chart draws itself using the REPT function.
One of the most frequent ways of creating Excel charts uses Stacked Bar charts. This method can add data labels (in more current Excel version), has room for floating text, allows different segment colors, and can have many other modifications. It works with any version of Excel.
If you want an Excel funnel chart that is great for representing the customer’s journey, allows room for text, and can even show capture or loss rate, then use the Stacked Area chart and make a few modifications to enhance it. Good enough for Board presentations or reports!
At the end of this blog you will learn how to create dynamic links from Excel cells or charts into PowerPoint slides. That will save you a lot of time. Do it right and when you update your data your slide deck automatically updates.
Funnel charts show the change in volume during the customer’s journey from first touch to sale. Funnel charts are excellent for monitoring conversion and loss rates at each stage. They can help you identify when marketing isn’t attracting the right types of leads, when sales isn’t making contacts, and much more.
To create a useful and realistic funnel, marketing and sales need to identify critical points in the customer journey and then identify the metric definition for that touchpoint. For example, what exactly is a Marketing Qualified Lead? What exactly is a Sales Accepted Lead?
Some common measurement points are,
(A contact with unknown potential.)
(A contact that shows some engagement.)
3. Marketing Qualified Lead (MQL)
(Marketing thinks this contact has sales potential.)
4. Sales Qualified Lead (SQL)
(Sales accepts the MQL and will act on it.)
5. Sales Contact
(Sales made contact with the SQL.)
(Sales identifies this contact having a potential to close.)
Funnels can also be used to show capture and loss through customer journeys, like this online marketing and sales funnel,
1. Pay per Click Ad
2. Landing Page
3. Opt-in Form
4. Download eBook
5. Respond to Offer
6. Complete Demo
7. Convert Sale
In-cell funnel charts in Excel are great for putting a funnel in a crowded report. For example, a page might have large amount of columnar data from regional marketing and sales data. The in-cell funnel chart can fit right alongside the numeric data in adjacent cells.
My book, “Balanced Scorecards and Operational Dashboards with Microsoft Excel,” describes how to create in-cell charts using Sparklines to create in-cell charts, but Sparklines can’t be oriented to make a funnel. However, before Sparklines Excel users used the REPT function to draw in-cell bar charts using the vertical pipe (“|”) symbol. The REPT function repeats text as many times as specified. If you use the right character and font you can draw bar charts this way.
This REPT function results in 12 vertical pipe characters in the cell. The pipe keyboard character is co-located with the backslash at the right-side of an American keyboard.
With this technique it’s easy to use the number of contacts in the funnel as the number of character repeats and then center the resulting bar chart to create a funnel. However, you need to make two modifications to make it look right. Most marketing and sales numbers are in the hundreds or thousands so you need to scale your numbers down. And, the pipe symbol for most fonts displays as a vertical line with spaces on either side giving it a “breathy” bar chart appearance. Luckily both of these issues are easy to fix.
The following example shows a marketing and sales funnel with the numbers scaled. Marketing and sales numbers are divided by the scaling factor in cell E6 so the number in the REPT is smaller. To remove the spaces around the pipe characters, reformat the cell containing REPT with the Playbill font. (Playbill is a default Windows font.) Use center formatting to center align the pipe characters. To really spiff up your in-cell funnel chart you might want to add conditional formatting to highlight funnel stages that are above or below forecast.
This often used method of creating an Excel funnel chart creates a graphical funnel chart that is compatible with any version of Excel. It is also easy to give it custom modifications.
Prior to Excel 2013 and Excel 2016 the best way to create a funnel chart was to create a Stacked Bar chart with two sets of data. The leftmost bar is a spacer that pushes the rightmost bars into a funnel in the center. Filling the leftmost bars with no color makes them invisible when you finish the funnel chart. An additional couple of clicks closes the gaps between bars and turns the chart upside down so the funnel is the direction people expect. Excel 2013 and Excel 2016 have a quick way of creating this chart, but it is not backward compatible so they may want to use this method for compatibility.
The Stacked Bar chart needs two sets of data to create an Excel funnel chart. The data under Count, E21:E27 on the right, is the count of people at each stage in the funnel. The data under Invisible, D21:D27, is a calculated value that offsets the Count bars so they appear centered as a funnel. When the chart is first created the funnel bars appear inverted, but you will fix that later.
The formula in the topmost cell in the Invisible column is
Copy this formula down through D21:D27 so there is a spacer for each Count cell.
Use the following steps to turn the data into a finished, and customizable Excel funnel chart.
1. Enter the labels, formula, and data.
2. Insert a Stacked Bar chart. As the figure above shows the funnel will appear upside down and the spacer bars will be invisible. (Make sure you use a Stacked Bar chart.)
3. Clear the clutter. Delete the title, legend, and chart lines. Keep the Category (bottom) axis labels for now.
4. Make the funnel fit the full width of the chart area. Right-click the Category Axis, select Format Axis, select the Axis Options tab and Maximum to 1000 for this example. For your funnel set the maximum to the highest number in the funnel.
5. Delete the Category Axis.
6. Invert the funnel so the first stage is at the top. Right-click the Vertical Axis, select Format Axis, select Format Axis, select Axis Options tab and check the Categories in Reverse Order check box.
7. Close the gap between bars so the funnel is solid. Right-click a bar, select Format Data Series, and set Gap Width to 0.
8. Make the spacer bars invisible. Right-click the bars on the left, select Format Data Series, select the Fill tab and select No Fill.
9. Add legends and data so it appears in each stage of the funnel. Right-click any bar and select Add Data Labels and select the data labels, such as Value or Value from Cells so they appear in each bar. If you select Value from Cells you are given the opportunity to select cells containing labels you want to display.
There are a number of custom modifications you can make to your funnel when you create it as a Stacked Bar chart in Excel. For example, you can alert readers by making one stage a different color than others by clicking twice on an individual stage to select it alone and then right-click on it to format it with a different color or border than the others. With an individual bar selected you also can use Chart Tools Format on the ribbon, then in the Shape Styles group, select Shape Effects to create some really over-the-top charts.
To uniquely format each data label, use the same method of slowly double-clicking on the text and then right-clicking to format the font.
The data label feature is not backward compatible with Excel 2003 or earlier. But, you can manually create <linked data labels> in older versions of Excel.
You can create a smooth, elegant funnel chart in any version of Excel with this method, but the funnel displays sideways. This is my preferred method. It gives a good visual representation of the count in each stage, there is more room in the chart for explanations in floating text boxes, and it matches my personal view that a customer’s journey is like a journey across a landscape (horizontal). With this method you can also align it with a win/loss chart for even better representation. That method is later in this blog. If you are presenting to people who have their minds set on the “drop customers in the top and filter them”, then you will want to use the method with Excel’s Stacked Bar charts.
This method is very similar to creating a funnel chart with Stacked Bars, but in this case you use a Stacked Area chart. These basic steps and commands are almost the same as those of the Stacked Bar.
1. Create your table of data using the same layout as the Stacked Bar chart. In the figure this is in C36:E43.
2. Create the Invisible column of spacer data by entering the formula
In cell C37 and copying it down.
3. Select C36:E43 and insert a Stacked Area chart. The Stacked Area chart stacks areas on top each other. When you first create this chart you will see the lower area as a color, blue in the example. You will make this invisible later.
4. Using the same techniques as in the Stacked Bar chart, do the following to change this into a horizontal funnel chart.
5. Format the Data Labels by clicking on them and then right-clicking to change their font, shape, or effect.
Most funnel charts don’t highlight where to make improvements. With a slight addition to the horizontal Stacked Area chart we can take care of that issue. This chart uses the Stacked Area chart to show the marketing and sales funnel and then places a simple bar chart underneath it that can show win or loss (leakage) rates at each stage. Adding data labels to the bars makes it easy to see where the greatest win/loss occurs.
In this example, as in most cases I’ve come across, you can get the greatest return from your funnel by improving quality at the TOFU (Top of Funnel) and improving conversion on the BOFU (Bottom of Funnel). Some organizations just push to increase the volume of TOFU (Top of Funnel), but if you’re just capturing more unqualified visitors you are wasting resources throughout the funnel. Callout Think about your sales and marketing processes and decide whether you want to focus on increasing your volume at the TOFU stage or whether you want to increase the quality (and reduce loss) at the TOFU stage.
The loss rate chart is a simple bar chart based on F52:F59. You could just as easily have calculated and displayed the win rate. The column widths were narrowed and a data label added at the top. Excess chart clutter was deleted. This simple addition makes it very easy to see were you want to focus attention.
Even though this is a great looking chart (or two charts) that is a real decision aid, it is completely backward compatible with earlier versions of Excel.
Newer Excel versions, like Excel 2016 (with an Excel 365 subscription), Excel 365, and Excel for Mobile and Andriod have a menu option to immediately create a funnel chart. It gives you a result just like the Stacked Bar chart method, but you don’t need to create the Invisible spacer data and it is done with one menu selection.
In Excel 2016 or Excel 365, select the labels and data, then from the Insert ribbon, select Recommended Chart, Funnel chart.
To quickly apply formats, select the chart and apply commands from the Chart Tools ribbon with the Design and Format menus.
This funnel chart is not backward compatible with earlier versions of Excel. If you open it in earlier versions the chart does not appear.
I’ll keep this short. Don’t create funnel charts using Excel’s Stacked Pyramid charts. They distort your perception of the data. 3D Stacked Pyramids look fancy, but they distort the data even more.
<Pic Don’t create funnel charts using Excel’s Stacked Pyramid charts.
Stacked Pyramid charts make poor funnels to represent any type of process flow,
This method gives you the power to link Excel data into a PowerPoint slide. In this case, the example puts the Excel data into a funnel created with PowerPoint’s SmartArt. Although this has the disadvantage that the chart does not reflect the proportions of the data it makes your PowerPoint presentation dynamic, always showing the latest data.
(I know I just ranted about not using pyramid charts because they distort data. This is for those of you that must use pyramid charts. For another, and perhaps better method, check the next method.)
Before you create the dynamic link into PowerPoint you need to setup your Excel data. If you are linking to data in a report or worksheet with formatting you want to keep, it is a good idea to create a “staging area” that shows just the data you want linked to. You will see the reason why later.
1. Create an table with just labels and data. The example shows the data in C85:D91.
2. At the top of the third column, G85, enter the formula,
Copy this formula in G85:G91. This formula concatenates the label in column C with a comma formatted number from column D.
3. Make column D wide enough that it contains all of the concatenated labels. It’s important that you do this. The dynamic link in PowerPoint will only show what is inside the cell. If part of the concatenated label is outside the cell it will be cut off in PowerPoint.
Here is how to create the dynamic link from Excel into any PowerPoint slide.
1. Open the PowerPoint slide in which you want to show the linked data. You can insert a pyramid to act as a funnel by choosing the Insert ribbon and selecting SmartArt and Pyramid. The pyramid will want text for each segment. Just type a space as a holder. The actual text will be a dynamic link from each cell.
2. Switch to Excel. Copy the cell or cells you want to show in your PowerPoint slide. In this example, you should select one cell at a time as you will want to position each cell in a separate segment of the PowerPoint pyramid.
3. Switch to PowerPoint and select the slide you want to receive the dynamic link.
4. On the Home ribbon, in the Clipboard group, select the Paste tool to display additional paste options and select Paste Special.
5. Paste an embedded linked object from Excel into PowerPoint. In the Paste Special dialog box select Paste Link. In the As list, select Microsoft Excel Worksheet Object, then click Ok. The Excel content will probably appear small and unformatted.
6. Repeat this process for each cell you want to link and drag them into place over the pyramid.
7. Use the tips below to resize and format the linked content.
The Excel object, the cell you selected, will display exactly as it is in Excel with the same font, color, cell background, and borders.
You will probably see a light grey border around the linked cell. This is border is the gridlines around the cell in the worksheet. To remove those grey lines go to the worksheet and choose View, Show, and deselect the Gridlines checkbox.
Make the dynamic linked contents larger by right-clicking on it, choosing Format Object, and changing the Scale Height/Width. Keep the Lock Aspect Ratio box checked to make your size changes proportional.
If you want to change the font, color, or background of the linked content, change the cell in the worksheet. That’s why it is a good idea to put Excel content you are linking to in an isolated staging area so it can be formatted separately from your Excel worksheet formatting.
Updating PowerPoint slides with linked content is almost effortless. If the Excel worksheet and PowerPoint slide are both open, then changes to the linked Excel content immediately appear in the PowerPoint slide. If the Excel worksheet is closed, then right-click each object and select Update Link.
If you move linked slides and worksheets they can lose contact with each other. It’s a good idea to keep linked files in the same folder to make updating and tracking easier.
The previous method works Ok for linking individual cells into the PowerPoint funnel, but you have to link each cell and you have the same problem as with a pyramid chart in Excel. Pyramid charts give a poor representation of reality.
Here’s what you want to do instead, link the entire chart or charts from Excel into the PowerPoint slide. When the data in Excel changes, the Excel chart changes, and the linked PowerPoint slide in turn changes.
This slide shows the horizontal funnel chart with loss rate chart both in PowerPoint. They were copied as one by selecting all cells behind the two charts. Then, using the steps above they were pasted into the PowerPoint slide as a linked Excel object.
When you first paste this selection into the PowerPoint you will see that not all of the chart background is white. You can make the entire background white by formatting the worksheet cells behind the charts white or by formatting the background white of the object in PowerPoint.
Updating linked charts is the same as updating linked cells. Either open both files or right-click the linked object and select Update Link.