Choosing the right visual to represent your data is often a real headache. Last year, I was working on marketing conversion tracking with my team, and we realized that our classic table did not convey the idea of a "step transition" at all. That’s when I discovered the potential of funnel charts, and a little later, the flexibility of gauges to display key indicators. Result: a much clearer report that amazed everyone in the meeting. In this article, I will take you step by step to recreate these visuals in Excel, and even add a touch of interactivity.
Why use a funnel or gauge chart?
Each type of chart responds to a different communication logic. The funnel chart emphasizes a succession of steps where values decrease — ideal for a conversion funnel, for example. The gauge, meanwhile, illustrates a single key indicator relative to a threshold or range: think of satisfaction rate or customer service level.
Visually, these solutions often outperform a simple bar chart when it comes to telling a story or highlighting a critical threshold.
Prepare your data
Structure the values step by step
Start by listing each phase of your funnel or each level of your gauge in column A, then the actual or cumulative values in column B. If you are tracking quantities that filter through the funnel, make sure that B1 ≥ B2 ≥ B3… Otherwise, Excel will not understand the decreasing logic.
Calculate percentages for the gauge
For a gauge chart, you will need a “reached” block and a “remaining” block. For example, if the target is 100 and you are at 65, create two cells: 65 and 35. You can automate this calculation with a simple IF formula or, if you have multiple conditions, with a SUMIFS to aggregate your data on the fly.
Create a funnel chart in Excel
Depending on your version of Excel, you have two approaches: the native method (available since 2016) or a graphic trick based on stacked bars.
Native method (Excel 2016 and later)
- Select your data (labels and values).
- Open the Insert tab > Recommended Charts > choose Funnel.
- The chart is generated automatically respecting the decreasing order.
Then, you can adjust colors, add data labels, or even modify the funnel angle for a more dynamic effect.
Manual method (earlier versions)
If the “Funnel” option does not exist, you can simulate the effect with a stacked bar chart:
- In an additional column, calculate the difference between each step and the previous step (to create an invisible “cap”).
- Insert a stacked column chart with these two series: the invisible series at the bottom (formatted without color) and the “active value” series above.
- Reverse the axis order and adjust the bar spacing to 0% to achieve this funnel effect.
This technique takes a bit longer to set up, but it remains reliable in all versions of Excel.
Create a Gauge Chart in Excel
A gauge is actually a donut and a pie slice stacked: it combines a doughnut chart and a pie chart.
- Prepare three values: Achieved, Remaining, and Empty Zone (to complete 360°). For example: 65, 35, and 100.
- Select this data and insert a doughnut chart.
- Then add a pie chart overlaid in the same location, where the visible slice corresponds to the “Empty Zone”. Hide the other two segments.
- Set the starting point of the doughnut and the pie slice to 270° to get an opening downward or to the side.
Finally, adjust the colors: green/yellow/red if you want to use your gauge as a performance zone, for example.
Customization and Interactivity
Add Interactive Slicers
To filter your data with a single click, nothing beats slicers. After inserting a chart based on a table or a pivot table, go to the Insert tab > Slicers. You can then link these visual filters to your chart and switch from one category to another in real time, which gives a very appreciated “live dashboard” feel during presentations.
Use SUMIFS to Feed Your Visuals
When your funnels or gauges need to reflect multiple conditions (for example, a regional segment and a product type), the SUMIFS function becomes essential. It allows you to create a dynamic range that recalculates as soon as a slicer is activated. Result: your chart always stays up to date without manually adjusting the data ranges.
Use COUNTIFS for Precise Counts
If you want to display, for example, the number of transactions that have passed a certain stage, the COUNTIFS function will be helpful. By integrating it into your data source, you can automatically generate the series of values corresponding to each segment of your funnel.
Tips and Best Practices
- Keep the descending order in a funnel for intuitive reading.
- Limit the number of steps to 5 or 6 to avoid cluttering the view.
- Choose consistent colors for your gauge: green, yellow, red are a universal code.
- Add data labels to indicate actual values or percentages.
- Test filters via slicers to ensure your charts are responsive.
| Chart Type | Main Use |
|---|---|
| Funnel | Visualize a step-by-step process with decline |
| Gauge | Track an indicator against a target or range |
By combining these techniques — native chart or stacked bar hack, donut/pie tricks, and advanced functions like SUMIFS or COUNTIFS — you will have a complete toolkit to make your Excel reports both more elegant and more understandable. Don’t hesitate to experiment with colors, formats, and even mix these two types of charts on the same dashboard to find what resonates best with your audience.
By applying these methods, you will transform your raw data into meaningful visuals, and your audience will thank you for the clarity provided. So, let’s get straight to the point: open your Excel workbook and get started! 🎯