For business users of Microsoft Excel Free guides and templates

Reporting Strategies

How to Create Funnel Charts in Excel

"Funnels" are widely used in business, but Funnel Charts aren't. Here's how to set up a Funnel Chart in Excel.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

An Excel funnel chart(Download the workbook.)

Although “funnels” are a common term in selling, the concept also can apply to other business activities.

The general idea is that an organization begins with a large number of possibilities and then, through several stages, winnows them down to a small number of successful outcomes.

In sales for example, the large number could be all possible prospects and the small number could be those who buy. Similarly, in recruiting, the large number could be all qualified recruits and the small number could be those who are actually hired. In both cases, we want the small number to as large a percentage as possible of the large number.

Funnels also can apply to challenges or problems. For example, the large number could be the total number of emails received in a day, and the small number could be the unanswered emails that remain after a certain number of days. In this instance, of course, we want this number to be as small as possible.

The chart above illustrates the general idea.

If the chart represents a sales funnel, we would want Stage 4 to be a large percentage. But if the chart represents an email funnel, we would want Stage 4 to be a small percentage.

This figure combines several charting techniques that are easy to set up but are seldom used in Excel. So let’s create this chart…

Set Up the Funnel Data

The data for the Excel funnel chartThis figure shows the data for the chart. The columns with a green fill in their title cells are columns referenced by the chart…where “green equals graph.”

Notice that the rows of data are in reverse order, with Stage 1 at the bottom of the table and Stage 4 at the top. This is the arrangement that works best when you create the chart.

Enter the labels and the date shown for rows 2 and 3. And then assign range names. To do so…

  • Select the range B2:C2. Press Ctrl+Shift+F3 to launch the Create Names dialog. Make sure that only Left column is checked. Then choose OK.
  • Select the range B3:E7. Press Ctrl+Shift+F3 again. Make sure that only Top row is checked. Then choose OK.

Now enter the formulas for these three cells:

D4:  =E4/MAX(Values)

This formula calculates the current value’s percentage of the largest value in the Values column.

C4:  =(1-D4)/2

This formula returns half the distance between 100% (the value 1 in the formula) and the percentage returned by cell D4.

B4:  ="Stage "&ROWS(B4:B$7)&" "&CHAR(13)&TEXT(D4,"0%")

This formula returns “Stage ”, followed by the Stage number and the formatted data value for the current bar. Specifically, the formula in the cell shown returns the label “Stage 4”, followed by a space, followed by a carriage return character generated by the CHAR(13) section, followed by a formatted version of the value in cell D4. (Your worksheet won’t use the carriage-return character, but your chart will…as the following figure illustrates.)

After you enter these three formulas, copy them downward as needed. Then format the table as shown.

Set Up the Chart

The first stage of the funnel chart.To begin the chart, select the range B3:D7. And then…

…In Excel 2007 and 2010, choose Insert, Charts, Bar, Stacked Bar.

…In Excel 2013, choose Insert, Charts, Bar, 2D Bar, Stacked Bar.

After you do so, you should have a chart that looks something like this figure.

You can see that the red bars somewhat resemble a funnel. So to complete the chart, you need to adjust it in several ways…

Delete the Legend and Title

You won’t need the legend, which shows the colors for Hidden and Pct. So delete it. To do so easily, select the legend and press Delete.

Also, Excel 2013 adds the title Chart Title. Select the title and press the Delete key to delete it.

Hide the Blue Hidden Bars

We use the blue bars as a foundation to support the red bars. But because we don’t want that foundation to be visible, we must hide the blue bars. To do so, click one of them. And then…

The next stage of the funnel chart…In Excel 2007 or 2010, press Ctrl+1 to launch the Format Data Series dialog. In the Fill tab, choose No fill. In the Border Color tab, choose No line. Then choose OK.

…In Excel 2013, press Ctrl+1 to launch the Format Data Series side panel. In the Fill & Line tab, choose Fill, No fill. And in the Border tab, choose No line.

Your chart now should look something like this figure.

Format the Horizontal Axis

Select the horizontal axis and press Ctrl+B to assign a bold font. Then…

…In Excel 2007 and 2010, press Ctrl+1 to launch the Format Axis dialog. In the Axis Options tab, specify a Minimum fixed amount of 0 (zero), and a Maximum fixed amount of 1 (100%). In the Number tab, specify Percentage with 0 decimal places; then click Add and then Close.

…In Excel 2013, press Ctrl+1 (if necessary) to launch the Format Axis side panel. In the Axis Options tab, in the Bounds section, specify a minimum value of zero and a maximum value of 1. In the Number section, specify a Category of Percentage with zero decimal places. Then choose Add.

Format the Vertical Axis

Select the vertical axis and press Ctrl+B to assign a bold font. Then…

…In Excel 2007 and 2010, press Ctrl+1 to launch the Format Axis dialog. In the Line Color tab, choose No line and then choose Close.

…In Excel 2013, press Ctrl+1 (if necessary) to launch the Format Axis side panel. In the Fill & Line tab, in the Line section, choose No line.

Format the Plot Area

Select the Plot Area. To do so, click in the lower-right area of the Plot Area. Or else, click on the edge of the chart object to select the entire chart and then press the Up arrow on your keyboard once to select the Plot Area. Then…

…In Excel 2007 and 2010, press Ctrl+1 to launch the Format Plot Area dialog. In the Border Color tab, choose Solid line, which displays the Color paint bucket control. Click on the control icon and choose a medium-gray color. Then choose Close.

…In Excel 2013, press Ctrl+1 (if necessary) to launch the Format Plot Area side panel. In the Border section, choose Solid line. In the Color paint-bucket control, choose a medium-gray color.

Format the Gridlines

Select any gridline. Press Ctrl+1 to launch your Format Gridlines dialog or side panel. Specify a solid line and then set the color to the same medium-gray color as your border.

Format Your Chart’s Bars

Click on one of the red bars in your chart to select it. And then…

…In Excel 2007 and 2010, press Ctrl+1 to launch the Format Data Series dialog. In the Series Options tab, set the Gap Width to 25%. In the Fill tab, specify Solid Fill. Assign any color you want. Then choose Close.

…In Excel 2013, press Ctrl+1 (if necessary) to launch the Format Data Series side panel. In the Series Options tab, set the Gap Width to 25%. In the Fill tab, specify Solid Fill. Assign any color you want. Then choose Close.

Format the Chart Area

Click on the edge of the chart object to select the chart. Press Ctrl+1 to launch the Format Chart Area dialog or side panel. In the Fill tab, choose No fill. In the Border Color tab or Border section, choose No line. Then close your dialog or dismiss your side panel.

Complete Your Chart

The final funnel chartTo complete your chart, select the range H3:M14. Press Ctrl+7 to assign a border to that range. Then position your chart within that border as shown here.

Enter and format your chart’s title in cell H2.

To display the report date, enter this formula for the cell shown:

M2:  =TEXT(ReportDate,"mmmm, yyyy")

(If your computer is set up for a language other than English, you’ll probably need to use different characters to represent month and year in the TEXT formula.)

Finally, if you need to change the number of Stages that your chart displays, add rows to your table of data, or remove them, then adjust your formulas as required.

Take Your Next Steps

You can take the next steps with this in two ways. First, you can download the completed workbook here. By doing so, can start to experiment with funnel charts immediately.

Second, if you want to add funnel and other professional charts to your reports and analyses, I can help you in two ways. To learn more, see Excel Coaching and Consulting.



Tags: #funnel, #chart, #sales, #excel 2013, #excel 2007, #excel 2010
Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.