How to Create Funnel Charts in Excel

Excel now offers Funnel Charts. But before they did so, we had to create them using bar charts. This article shows how to turn bar charts into funnels.

3180

The word "funnels" is a widely used business term, but Funnel Charts aren't. Here's how and why to set up a Funnel Chart in Excel.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.

This chart illustrates the general idea:

An Excel funnel chart, created with a bar chart.

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 creating an Excel funnel chart with a bar chart.This 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 is to create stacked bar charts.To begin the chart, select the range B3:D7. And then 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.

First, 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.

Second, select the chart’s title and press the Delete key to delete it.

Hide the Blue Hidden Bars

The next stage of the funnel chart, which is to hide one of the bars in the bar chart.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 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 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 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 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, 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 complete funnel chart as it appears in your worksheet.To 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.