A funnel chart is a useful visualization for showing progress through different stages of a process. It looks like a bar chart with the difference of centered bars which allow creating the funnel shape. In this guide, we’re going to show you how to create a funnel chart in Excel.
Funnel charts are popular in many areas, like sales and marketing, for displaying prospect numbers or monitoring conversion and loss rates in each stage of a sales pipeline at a specific period in time.
Funnel Chart Basics
A funnel chart has 3 main sections:
- Plot Area: This is the place where the graphic representation takes place. A Funnel chart visualizes stages wit horizontal bars. Bars values are usually distributed in a decreasing order to resemble a funnel.
- Chart Title: The title of the chart. Try to make it descriptive and short as much as possible.
- Category Axis: A vertical axis that can display stage names. You can place the axis either on the left or right.
- Legend: You can add a legend to your funnel chart. However, this is generally not necessary, because the chart can display only a single series of values.
Insert a Funnel Chart in Excel
Start with selecting your data in Excel. Include the data labels in your selection so they can be recognized automatically by Excel.
Activate the Insert tab in the Ribbon and click on the Waterfall Chart icon to see the chart types under category. Click the Funnel chart to create your chart.
Clicking the icon inserts the default version of the chart. Now, let’s look at some customization options.
Customize a Funnel Chart in Excel
You can almost edit any element on the chart. Excel provides a bunch ways to get started. Let’s take a look at them:
Double-clicking on any item pops up the side panel of Excel with options for the selected element. Please keep in mind that, once the side panel is open, you don’t need to double-click another item again - selecting them will be enough.
The side panel includes element specific options, as well as generic ones, like coloring and effects.
Right-Click (Context) Menu
Right-clicking an element also displays the context menu. You can modify basic styling properties like colors, or activate the side panel for more options. To display the side panel choose the options which starts with Format string. For example; Format Plot Area… in the following image.
Chart Shortcuts (Plus and Styles Buttons)
With Excel 2013 and newer, charts have shortcut buttons. You can add/remove elements, apply predefined styles and color sets and filter values in respective order with a few clicks.
Another useful feature is that you can see the effects of options on the fly, before applying them. For example; in the following image, the mouse is on Axis Titles item and we can see the labels on our chart.
Ribbon (Chart Tools)
Whenever you activate a special object, Excel adds a new tab(s) to the Ribbon. CYou can see these chart specific tabs under Design and Format. While the Design tab contains options to add elements, applying styles, modifying data, and modifying the chart itself; the Format tab provides more generic options which are common in most of other objects.
Briefly; the chart tabs in the Ribbon is the only place where you can find all options that are mentioned in the previous method.
Preset Layouts and Styles
Try preset layouts or styles to improve the look-and-feel of your chart. You can find styling options in the Design tab or from the brush icon of Chart Shortcuts. Here are some examples:
Applying a Quick Layout:
Update Chart Style:
Changing chart type
You can change the type of your chart at any time from the Change Chart Type dialog. Although, most chart types have multiple variations, Excel provides only a single type for Funnel charts.
To change the type of your chart click on the Change Chart Type item in the Right-Click (Context) Menu or Design tab.
In the Change Chart Type dialog, you can see the options for all chart types with the preview of your chart. Unfortunately, you don’t have any different options for your Funnel chart.
Excel assumes vertical labels to be the categories and horizontal labels data series by default. If your data is transposed, you can easily shift the labels with a single click. However, this is not the case for Funnel charts. Switch Row/Column button in Chart Design tab is disabled.
Move a chart to another worksheet
Charts are created in a worksheet where the selected data is placed in by default. If you need to move your chart into a new or existing sheet, you can use the Move Chart dialog. To open the Move Chart dialog you can click its icon in the Design tab or in the right-click menu for the chart itself. Please keep in mind you need to right-click on an empty place in chart area to see the option. You can’t see this if you right-click on an element.
In the Move Chart dialog, you have 2 options:
- New sheet: Select this option and type a name to create a new sheet under specific name which includes your chart.
- Object in: Select this option and select a name of an existing sheets in the dropdown input to move your chart to that sheet.