If you’re like most business owners, you probably use a lot of different software to manage your day-to-day operations. One of the most popular tools for tracking data is Microsoft Excel. In this article, we’ll show you how to create and customize a funnel chart in Excel. A funnel chart is a great way to visualize how customers move through your sales process. By plotting the number of leads generated, the number of sales made, and the average order size, you can see which stages of your sales process are most successful. To create a funnel chart in Excel, first open the file that contains your data. Next, click on the Insert tab and select Chart from the list of options. In the Chart dialog box that appears, select Funnel from the Type dropdown menu and click on OK. Next, specify which fields you want to include in your chart by selecting them from the Fields list box. (You can also include additional fields by clicking on Add Field.) Finally, configure your chart’s settings by clicking on OK twice. When you’re finished configuring your funnel chart, it will appear in your workbook as shown in Figure 1 below. ..
A funnel chart is great for illustrating the gradual decrease of data that moves from one stage to another. With your data in hand, we’ll show you how to easily insert and customize a funnel chart in Microsoft Excel.
As the name implies, a funnel chart has its largest section at the top. Each subsequent section is smaller than its predecessor. Seeing the largest gaps allows you to quickly identify stages in a process that can improve.
When to Use a Funnel Chart
Most commonly used to show phases in a sales process, you can use a funnel chart for other types of data as well. As examples, you may use one to display a flow of information, an order fulfillment procedure, or a business process flow.
For this how-to, we’ll stick with sales data. We have an email campaign for our new membership program. We’ll use a funnel chart to show the process from the email blast to prospective members to those from that pool who subscribed.
We’ll show numbers for each category, or stage, in the process for our funnel chart:
Emails sent Emails opened Link visited Signed up for trial Subscribed
Create a Funnel Chart in Excel
Open your spreadsheet in Excel and select the block of cells containing the data for the chart.
Head to the Insert tab and Charts section of the ribbon. Click the arrow next to the button labeled Insert Waterfall, Funnel, Stock, Surface, or Radar Chart and choose “Funnel.”
The funnel chart pops right into your spreadsheet. From it, you can review the data and as mentioned, see the largest gaps in your process.
For example, we see here the huge reduction from the number of emails sent to the number opened. So we know that we need to better entice potential members to actually open that email.
We can also see the small gap between those who signed up for the trial and then subscribed. This shows us that this particular stage in the process works pretty well.
Customize Your Funnel Chart
As with the other types of charts in Excel such as a waterfall or treemap, you can customize the funnel chart. This not only helps you include the most important elements in your chart but also gives its appearance a bit of a boost.
The best place to start when editing your chart is with its title. Click the default Chart Title text box to add a title of your own.
Next, you can add or remove chart elements, choose a different layout, pick a color scheme or style, and adjust your data selection. Select the chart and click the Chart Design tab that displays. You’ll see these options in the ribbon.
If you’d like to customize the line styles and colors, add a shadow or 3-D effect, or size the chart to exact measurements, double-click the chart. This opens the Format Chart Area sidebar where you can use the three tabs at the top to adjust these chart items.
With Excel on Windows, you have a couple of additional ways to edit your chart. Select the chart and you’ll see two buttons appear on the right. On top is Chart Elements and below is Chart Styles.
Chart Elements: Add, remove, or reposition elements on the chart like data labels and a legend.
Chart Styles: Use the Style and Color tabs to add some pizzazz to your chart’s appearance.
Once you finish customizing your chart, you can also move it or resize it to fit nicely on your spreadsheet. To move the chart, simply select and drag it to its new spot. To resize it, select it and drag inward or outward from an edge or corner.
If you have city, state, or other location data, take a look at how to create a geographical map chart in Excel.
