How to Create a Sales Funnel Chart in Excel to Visualize Your Sales Process

Using Excel to create a sales funnel chart provides a powerful visual representation of the sales process from initial contact to closed deal. Mapping out your sales stages in a funnel chart makes it easy to analyze conversion rates, identify leaks in the funnel, and optimize your sales strategy.

In this step-by-step guide, we’ll walk through how to build a sales funnel chart in Excel using data on leads, prospects, and customers. Follow along to create your own interactive funnel chart to gain insights into your sales pipeline.

Why Use a Sales Funnel Chart?

Before we dive into the mechanics of building a sales funnel chart let’s look at why it’s such a valuable asset

• Visualize the sales process – See each sales stage mapped out from initial contact to closed sale. Easily understand the customer journey.

• Calculate conversion rates – Add conversion percentages between each funnel stage to quantify progress. Uncover where drop-off happens.

• Identify weaknesses – A skinny funnel stage indicates an issue to address, Are leads not converting to prospects? Is close rate low?

• Track progress over time – Compare monthly or quarterly funnel charts to see the impact of changes in sales initiatives.

• Forecast revenue – Factor avg deal size into the funnel chart to estimate total sales revenue in the pipeline.

A sales funnel chart brings the sales pipeline to life visually so you can analyze performance and fine-tune your sales strategy for better results. Now let’s look at how to make one in Excel.

Step 1: Set Up the Sales Funnel Chart Structure in Excel

The first step is structuring the visual funnel shape in Excel by adding the funnel chart type:

1. Click an empty cell in your Excel worksheet to add the chart.

2. In the Excel ribbon, go to the Insert tab.

3. Click the Funnel Chart icon under Recommended Charts.

4. An inverted funnel chart will appear on the worksheet.

Now we need to add the sales stages as labels for each funnel stage. Here are some common stages to use:

• Leads
• Marketing Qualified Leads
• Sales Qualified Leads
• Prospects
• Opportunities
• Customers

But you can customize the stages for your specific sales process.

Step 2: Add Sales Stage Labels to the Funnel Chart

To add the stage names:

1. Right click on the horizontal axis of the funnel chart.

2. In the pop-up menu, select Select Data.

3. In the Legend Entries box, add the names of your sales stages.

4. Click OK to apply the labels.

Now you should see the sales stages populate along the funnel chart horizontally. Next we’ll look at adding data.

Step 3: Input Total Number Data for Each Sales Stage

Now it’s time to quantify each stage of the funnel with real data totals:

1. In an Excel table or data set, input the total number of leads, prospects, or customers you have at each respective funnel stage.

2. Highlight the data set to create a dynamic data range for the chart.

3. Click back on the funnel chart and right click.

4. Select Select Data again.

5. Configure the funnel chart’s data series to link to your data range.

6. The numbers will then populate within each funnel stage automatically based on your data.

7. Update the data any time to refresh the funnel chart.

With the volume data added, you can clearly see the total number of contacts as they progress through the sales pipeline.

Step 4: Add Conversion Percentage Metrics

The next step involves calculating the percentage conversion rate between stages to quantify effectiveness:

1. In an adjacent Excel table or data set, input formulas to calculate the percentage conversion from one stage to the next.

2. For example, take Prospects/Leads to get the percentage of leads that become prospects.

3. Add that percentage conversion data next to each funnel stage.

Now you can analyze the percentage of contacts progressing to the next level in the sales cycle. Low conversion percentages pinpoint potential issues to address.

Step 5: Incorporate Revenue Data

You can take the funnel chart one step further by factoring in potential revenue:

1. After each funnel stage name, add the average deal value or revenue amount per customer for that stage.

2. Input the # of customers again next to it.

3. Excel will automatically calculate the Total Revenue for each stage.

With deals sizes included, your funnel chart now displays not just volume of contacts, but also revenue potential in your sales pipeline. This makes forecasting easier.

Step 6: Customize the Look and Feel

To turn your standard Excel funnel chart into an inviting, branded visual:

• Change colors: Click each section to edit colors to match branding.

• Add graphics and text: Insert logos, icons, and explanatory text.

• Modify layout: Resize, position, and wrap text around the funnel shape as needed.

With a few simple tweaks, your funnel chart can blend seamlessly into sales reports and presentations.

7 Tips for Building Insightful Funnel Charts

Creating funnel charts in Excel is fairly straightforward, but the key is turning them into truly powerful sales analytics tools. Here are some top tips:

• Break down stages further: Add sub-stages like marketing lead nurturing phases for greater precision.

• Use consistent stages: Keep stages and naming conventions consistent across funnel charts to accurately track performance over time.

• Update frequently: To make data as timely as possible, refresh funnel metrics at least monthly if not weekly.

• Compare timeframes: Use separate funnel charts for different time periods to spot trends.

• Create for segments: Build funnel charts for specific products, services, markets, buyer types, etc to uncover differences.

• Add projections: Use historical conversion rates to project future pipeline growth by stage.

• Link to actions: Review funnel data regularly and tie insights directly to sales strategy improvements.

Creating Interactive Funnel Charts with Dashboards (Optional)

The examples so far covered building funnel charts directly in Excel worksheets. But you can also create interactive funnel dashboards using Excel combined with a data visualization platform like Microsoft Power BI.

With Power BI, funnel charts link dynamically with live data and can be filtered on dimensions like date, product, geography, and more. This allows in-depth analysis by segment. You can even set up automatic refresh of the dashboards to always have the latest funnel metrics at your fingertips.

Learn more about constructing funnel analysis dashboards in Power BI here:

[Insert link to dashboard funnel content]

This adds the flexibility to instantly view funnel charts by any category, update live data, share easily with any device, and customize visuals. Interactive dashboards take funnel reporting to the next level for truly timely, actionable pipeline insights.

Get Started with Funnel Charts to Boost Sales Strategy

Creating sales funnel charts in Excel provides immense visibility into pipeline performance. Visualizing contacts progressing through stages makes it easy to calculate conversion rates, uncover weak points, and estimate revenue.

The steps covered in this guide equip you to build insightful funnel charts to improve sales strategies and processes. Start tracking funnel metrics today to:

• Monitor conversion rates over time
• Pinpoint where contacts are falling out
• Model revenue projections
• Optimize marketing and sales tactics

Accurately visualizing your sales pipeline with funnel charts accelerates growth by aligning sales efforts to your most lucrative opportunities. With an analytical, metrics-driven approach, your sales team is empowered to convert more leads into delighted customers.