Funnel charts are often used to convey how numbers vary through a process. They are quite common in marketing presentations as they can illustrate how, for example, many people are aware of a product, a smaller number like the product, a still smaller number regularly buy the product, etc. But rather than display values in a standard column chart like this:
It might be more visually effective to display it in a funnel chart:
Creating the funnel chart is simple – just select the range that holds the data and press the Funnel button on the Excel ribbon toolbar.
The single-click funnel chart is not “live”, however, because an additional “invisible” data series is added to create the offset. This means that it won’t update with changing data. However, to achieve this, you can use the “=QC.FUNNEL(…)” formula.
Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.
The formula takes a single parameter, the same data as the single-click version, and returns an array of the same size but with one extra column which contains the invisible series. Select a cell in the formula region and press the Funnel button on the ribbon toolbar and the chart will be created linked to the new data. Alternatively, if you right-click on a cell in the formula array then the top option on the menu should be “Create Funnel Chart”.
There is also the option of adding another data series to the chart, which will produce a “Butterfly chart” (sometimes called a “Tornado chart” if the items are ordered from largest to smallest).
This works both with the single-click version and the QC.FUNNEL formula (which will return four columns instead of the usual three).
The Funnel Chart Settings dialog box can be accessed through the Funnel drop-down on the Excel ribbon toolbar.
- Horizontal / Vertical – sets the orientation of the chart (indicating whether the direction of the funnel is from left-to-right or top-to-bottom respectively).
- Keep Gap Between Layers – You can choose to have a gap between each segment of the funnel or to make them a contiguous block as shown below.