Demonstrate linear progression with an Excel Funnel chart

 

by Kara Hiltz

Application: Microsoft Excel 365/2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201808/funnel.zip

Excel continues to provide innovative ways to visually represent your data so that it becomes meaningful. Excel 365’s new Funnel chart type offers another option for charting data—this time to show a progression of data through a series of steps.

 

To create an effective Funnel chart in Excel 365, we’ll:

  • Discuss what types of data make good candidates for a Funnel chart.
  • Set up and sort our data.
  • Create a Funnel chart type and suggest some formatting changes.

One of Excel’s best features are its charts—a visual representation of your data that tells its story in seconds. As Excel evolves, Microsoft adds more chart types to the software, which may leave some Excel users overwhelmed. What chart is best for my data? How can I make my data meaningful to others?

The latest version of Excel, Excel 365/Excel 2016 includes the Funnel chart type, which you can use to show linear progression, as shown in Figure A. There are only certain kinds of data that work well with a Funnel chart, so we’re here to make sure you’re getting the most from this new addition to Excel.

Download: If you would like to follow along with our example, just download and extract the file funnelchart.xls from the URL given at the beginning of the article.

 

IME18801A

A:

A Funnel chart type helps you visually progress through a procedure, filtering out data as you go.

 

Identify data that works with a Funnel chart

A Funnel chart looks just as the name indicates—like a funnel. This chart type won’t work with any data set. Funnel charts are best for data that:

  • Contains 15 or fewer data points;
  • Is sorted in descending order from highest to lowest; and,
  • Progresses in a linear way.

In our example, we’re outlining the hiring process for a company as it progresses from submitting the application to interviewing with the CEO, as shown in Figure B.

Important:The standalone desktop version of Excel 2016 does not include the Funnel chart type. You must have an Office 365 subscription, which includes the desktop Excel 2016 software.

IME18801B

B:

If you have too many data points, a Funnel chart type isn’t the best choice; keep the number of data points under 15.

Other data that will work well with a Funnel chart

The Funnel chart type may seem pretty specialized and limited in its uses. With some creative thinking, however, you’ll be surprised at how many different kinds of data you can represent using a Funnel chart. Here are some ideas:

  • For online marketing, show how many people opened your email, clicked on a link, and then carried through to making a purchase.
  • For customer service, show how customers progress from having an issue to getting the issue solved, and the steps in between.
  • In a retail environment, visually show the popularity of certain products by putting their sales numbers into a Funnel chart design.

 

Apply a descending sort order

If your data is a good candidate for a Funnel chart, you should know that Excel will place the data points exactly in the order that they appear in your data table. So, if you don’t have the data points in descending order, your Funnel chart will not look like a funnel.

To sort your data table in descending order:

  1. Select any cell within your data table.
  2. Switch to the Home tab.
  3. In the Editing area, click on the Sort & Filter dropdown arrow.
  4. Select Sort Z To A from the dropdown [...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage