As a data analyst and Excel geek, I love funnel charts. These insightful visualizations can instantly highlight bottlenecks, trends and opportunities in your sales, marketing or other business processes.
In this comprehensive guide, I‘ll teach you everything you need to know to create powerful, customized funnel charts in Excel.
What is a Funnel Chart and Why is it Valuable?
A funnel chart displays values across different stages of a process. As the name suggests, it is shaped like a funnel, with the first stage having the largest number of prospects or customers. As they move through the subsequent stages, fewer remain, creating a narrowing funnel shape.
Here‘s an example funnel chart tracking a typical sales pipeline:
Funnel charts are extremely helpful for visualizing conversion rates through a multi-step process. For instance, you can easily see how many prospects make it to each successive stage, from initial contact all the way to closed sales.
This gives you insights into questions like:
- Where is the biggest dropoff occurring in our funnel?
- Which stages have lower than expected conversion rates?
- How do our conversion rates compare period over period?
According to a survey by FunnelCake, 79% of businesses use funnel reports and dashboards to closely monitor their sales pipelines. Funnel charts are one of the most popular visualizations for this.
Beyond sales, marketers use funnel charts to analyze traffic source through to customer acquisition. Support managers visualize tickets from open status to resolution. Ecommerce managers can view orders from add to cart through purchase.
"Funnel charts are invaluable for seeing the big picture health of our pipeline. We instantly know when prospects are falling out at a certain stage and can take actions to improve it." – Alan Klement, CEO, When I Work.
So in summary, the key benefits of funnel charts are:
- Track conversion rates through multi-step processes
- Identify major drop-offs or blockages
- Compare funnel performance period over period
- Visualize volumes and flow for stakeholders
- Gain insight into improving your business processes
Step-by-Step Instructions for Building a Funnel Chart
Now that you know what funnel charts are and why they‘re useful, let‘s go through exactly how to build one in Excel.
The good news is it‘s straightforward to create a funnel chart once your data is structured properly. I‘ll cover the steps for both Excel 2013 and later versions.
Step 1: Set Up Your Funnel Data
Begin by organizing your data with funnel stage names in the first column and number of prospects/customers in the second column:
| Funnel Stage | Number |
|---|---|
| Contacts | 25,000 |
| Leads | 5,000 |
| Opportunities | 2,500 |
| Customers | 1,250 |
You can add more columns to include things like stage targets, conversion rates, etc. But at minimum you need the stage names and number for each.
Step 2: Insert a Stacked Column Chart
With your table selected, go to the Insert tab and click the Column Chart icon:
Excel 2013:
Choose the 3-D 100% Stacked Column option:
Excel 2016+:
Pick the 100% Stacked Column:
This generates a stacked column chart reflecting your funnel data.
Step 3: Switch the Chart to Pyramid Type
Right click on a column and choose Format Data Series:
In the pane that appears, change the Chart Type to Pyramid:
This transforms the stacked columns into a pyramid shape.
Step 4: Flip Orientation to Funnel
To point the pyramid downwards:
- Select the chart
- Go to Design > Switch Row/Column:
Flipping the orientation gives us the funnel shape we want.
Step 5: Format the Funnel Chart
Right click the chart again and choose 3D Rotation. Set X Rotation to 0 and Y Rotation to 30:
This angles the funnel for a more natural look:
Next let‘s show the values on each section by adding data labels:
The complete formatted funnel chart:
And that‘s it! By following these steps you can quickly generate a funnel chart from any data showing stages and values.
Customizing and Formatting Your Funnels
The beauty of Excel funnel charts is you can customize them in endless ways to meet your specific needs. Here are some formatting tips and tricks:
Modify funnel colors – Use Shape Fill to set custom color for each section. This can make stages stand out.
Add data labels – Display values or percentages on each funnel stage. Percentages highlight conversion rate.
Change styles – Apply built-in color and style variations under Chart Styles.
Use 3D effects – The 3D Rotation options add depth. You can also try 3D funnel charts.
Resize chart – Drag the edge handles to perfectly fit the funnel into reports, dashboards or presentations.
Align periods – Place two funnel charts side by side to easily compare periods.
Add target line – Show sales or conversion rate goal with a target line across the funnel.
Surround with metrics – Inserts KPIs, sparklines or other context around the funnel.
Annotate chart – Use callouts and text boxes to highlight insights directly on the funnel.
Examples of formatted funnel charts (Source: Smartsheet)
The possibilities are endless for customizing funnel charts to meet your specific needs.
Funnel Chart Tips and Best Practices
Here are some pro tips to make your funnel charts as insightful as possible:
-
Place funnel stages in chronological order from left to right. This shows the customer‘s journey.
-
Use consistent stage names across periods. This makes trends easier to analyze.
-
Show percentages instead of absolute values for easier comparison.
-
Visualize targets and actuals to assess performance.
-
Keep the chart simple and clutter free. Only essential data.
-
Align the charts when comparing periods for quick visual analysis.
-
Sort funnel charts by segment to analyze differences. For example, by marketing campaign source or sales rep.
-
Use reference lines to indicate goals, past performance, or other reference points. This adds context for analysis.
-
Include funnel charts in presentation dashboards to quickly convey volumes and flows. The visual really resonates with executives according to my experience.
-
Don‘t forget to cite your data source! Add a note indicating the date range, system source, etc. for complete transparency.
Properly designed funnel charts make it effortless for your audience to grasp volumes, flows and trends in the data. Follow these tips and you‘ll have funnels that maximize analytical insight.
Conclusion and Next Steps
Now you have all the tools you need to create informative, professional funnel charts in Excel. The key takeaways:
-
Funnel charts visualize conversion rates through multi-step processes. This highlights bottlenecks.
-
They are easy to build in Excel once your data is structured properly. Follow the step-by-step instructions provided.
-
Customize funnel colors, labels, orientations, styles and formatting to meet your needs.
-
Use best practices like consistent stages, percentages and targets to maximize analytical value.
Your next step is to identify a business process and start charting your funnel! Analyze trends over time, uncover issues causing leaks and make data-driven decisions to optimize performance.
I hope this guide equips you to build funnel charts that provide true business insight. Let me know if you have any other questions as you start analyzing your first funnels!
