Funnel charts in Excel are an excellent way to visualize a process or workflow that has stages with reducing quantities. Often used for sales pipelines and marketing campaigns, funnel charts can provide valuable insights to identify friction points and opportunities for improvement.
In this comprehensive guide, you‘ll learn what funnel charts are, why they are useful, and step-by-step instructions for building your own funnel chart in Excel, customizing it, and using it effectively for data analysis.
What is a Funnel Chart?
A funnel chart displays values in progressively decreasing amounts, similar to a funnel shape. The first stage has the largest quantity, and each subsequent stage shows smaller and smaller amounts, eventually leading to a final value.
Some common uses of funnel charts include:
- Sales pipelines – number of leads, prospects, quotes, negotiations, and deals closed
- Marketing campaigns – awareness, consideration, conversions
- Customer onboarding processes – trials, purchases, renewals
- Project management – pipeline coverage, completed tasks
The key benefit of a funnel chart is visualizing where large drop-offs are occurring in a workflow. This allows you to quickly identify problem areas and opportunities to improve. Funnels also provide an easy progress tracking mechanism towards end goals.
How to Create a Funnel Chart in Excel
Creating a basic funnel chart in modern versions of Excel (Office 365 and 2019+) is very simple:
- Select your dataset with headers in an Excel table
- Go to Insert > Charts > Funnel
- Pick the recommended funnel chart and customize as needed
For earlier versions of Excel, it takes a few more steps:
- Select your data and create a stacked column chart
- Change chart type to 3-D 100% Stacked Column
- Right click on a column, choose Format Data Series, pick Full Pyramid
- Right click on chart, select Switch Row/Column
Funnel Chart Example
Let‘s walk through an example funnel chart for a sales pipeline in Excel.
We have a simple dataset showing the number of sales prospects, qualified leads, proposals, negotiations, and closed sales.
To insert a funnel, select the data, go to Insert > Charts > Funnel, and pick the first recommended chart. Instant funnel chart complete!
Now let‘s customize it…
Customizing and Formatting Your Funnel Chart
The default Excel funnel chart works, but often needs formatting and customization for the best presentation of your data.
Change Funnel Shape and Size
We can control visual aspects like shape and color. For example, I‘ll change this to a bulging funnel shape and make the neck slightly longer.
Add Data Labels
An important customization is adding data labels directly on the funnel segments. This helps quickly convey exact values in each stage.
Right click, choose Add Data Labels, and make sure they display appropriately.
Modify Styles and Colors
Next, update styles under the Design tab or Format pane on the right side panel. Try different color variations that make sense for your data and contrast well.
I changed the color theme and added a gradient light green fill here. The final polished funnel conveys the key information cleanly and clearly.
There are many more ways to customize further – rotation, scale, animations, thresholds, and combining with other charts.
Use Cases for Funnel Charts in Excel
Now that you know how to create and format funnel charts, let‘s discuss some of the most effective use cases.
Sales Pipeline Tracking
One of the most popular funnel chart applications is visualizing your sales process. Typical stages include leads, prospects, proposals, negotiations to closed sales.
The funnel provides an instant view into conversion performance – where significant drop-offs may be happening between stages and impacting overall deals closed. Sales managers can then drill down into those weak spots and improve.
Funnels are especially helpful for very long complex sales cycles, like enterprise software. There may be a dozen intricate stages, and pipeline coverage projections depend heavily on conversion ratios.
Marketing Campaign Analysis
Funnel charts are extremely useful for visualizing marketing campaigns and identifying leaks reducing conversions.
Typical campaign stages are:
- Awareness – people exposed to campaign
- Interest – clicks, inquiries
- Consideration – demos, trials
- Conversion – purchases, signups
Performance marketers meticulously track every stage to optimize budgets and maximize conversions. Adding funnel charts makes this analysis more intuitive and actionable.
Customer Onboarding Processes
For SaaS businesses and other subscription services, effectively onboarding customers is crucial for expansion revenue and renewals. This onboarding involves its own small funnel:
- Trials started
- Successful trials
- Conversions from trial to paid
- Renewals
Funnel charts help quickly monitor if trial starts are consistent, usage and adoption is steady, conversion rates are above benchmarks, and renewals are strong. Any major drop-offs would warrant further investigation to limit churn.
Limitations of Funnel Charts
While funnel charts have many excellent applications, they also have some limitations to keep in mind:
- Not ideal for processes without clear reducing sequential steps – consider alternatives like bar charts or journey maps
- Cannot handle branching decision points and multiple paths – use swimlane flowcharts instead
- Limited data comparing features – add secondary metrics with combo charts
- Difficult to show very high volume enterprise-scale big data – funnel widths get distorted, supplemental tables advised
In these situations, consider supplemental charts that enrich insights like scorecards, scatter plots, combo charts, Pareto analysis, and dashboards.
The goal is picking the right visualization for your specific data, metrics, objectives, and audience. Funnel charts make an excellent addition to your Excel chart and analytics toolkit when applied judiciously.
In Summary
Here are some key takeaways from this guide on creating funnel charts in Excel:
- Funnel charts display data with progressively decreasing quantities through sequential stages
- They help spot major drop-offs and weak points in processes like sales, marketing, and onboarding workflows
- Modern Excel versions have a dedicated funnel chart option for quick setup
- For earlier versions, stacked columns formatted as a pyramid make a funnel chart
- Customizations like data labels, colors, shape, styles, and sizes optimize funnels
- Use funnel charts for appropriate use cases, and supplement with other visuals when more context is needed
Now that you‘re a funnel chart expert, try creating visually impressive funnels for your business data in Excel! Let me know in the comments if you have any other questions.
