OmniDocHow to make PPT funnel chart: steps of conversion analysis chart
Explain clearly the general method of making a funnel chart in PPT: use a stacked bar chart to center-align each stage, then mark the conversion rate, and provide hands-on steps and a checklist after completion. It is suitable for analysis and reporting on user conversions, sales leads, etc.
Key points of this article
- The core of the funnel chart is that the bars of each stage are arranged in the center. This can be achieved by using a stacked bar chart and adding a hidden auxiliary data. There is no need to manually shape the shapes.
- The algorithm of the auxiliary column is (top-level value − current value) ÷ 2, which determines the width of the left margin of each layer. When the value changes, it must be updated simultaneously.
- The conversion rate is calculated by dividing the next stage by the previous stage, plotted between two adjacent levels, and checked against the source data.
- It is recommended to control the number of funnel layers between 3 and 6. When there are too many layers, the stage names and numbers are easily crowded together, making it difficult for readers to see clearly.
If you want to explain the user's process from access to payment in one slide, the funnel chart is more intuitive than the ordinary bar chart: each layer represents a stage, and the narrowing between layers corresponds to the loss of people. Many people will try to use histograms or manually drag rectangles to put together, but the result is that either the layers are not aligned, or changing a number requires re-adjusting the entire shape.
This article is written for readers who need to do conversion analysis, sales funnel or enrollment, delivery and other stages of reporting. After reading, you can use the chart function of desktop office software to make a funnel chart with center alignment and updateable numbers, and know how to calculate and mark the conversion rate. The methods below do not rely on any specific software.
How to make a funnel chart? One sentence conclusion
Use a horizontally stacked bar chart, set the first series to no fill as a blank space on the left, and put real values in the second series to get a centered funnel layer by layer. The advantage is that when the numbers change, the graphics will change accordingly, so you don’t have to drag the shapes one by one.
| your situation | Recommended practices | reason |
|---|---|---|
| The stage is within 6 layers, and the data needs to be updated frequently. | Stacked bar chart plus hidden auxiliary series | Just change the value to update, and the layers will be automatically centered. |
| Only a one-time indication, no need to change the data | Arrange manually with trapezoidal or rectangular shapes | The operation is simple, but you need to realign it after changing the numbers. |
| Need to be shown layer by layer during explanation | Make the complete funnel first, and then split it into multiple pages according to layers. | Each page only displays one more layer to facilitate step-by-step explanation. |
| There are branches between stages (such as free and paid after registration) | Split into two funnel charts, or use a flow chart instead | A funnel chart can only express a single path |
Before you start: Confirm these points first
- The stage must be a process in which the same group of people decrease in sequence. Funnel charts assume that the later stages are subsets of the previous stages. If "visits" and "orders" come from different channels, the ratios are not comparable and should be done separately.
- The data is organized into two columns. One column is the stage name, and the other column is the number of people or amount. The names are arranged from top to bottom according to the process. Try to use integers for the values to facilitate verification.
- Unified statistical caliber. For example, whether "registration" refers to submitting a form or completing verification, it must be consistent in the same picture, otherwise the conversion rate will mislead readers.
- Control the number of funnel layers. Levels 3 to 6 are relatively clear. When levels exceed 6, labels tend to overlap and similar stages can be merged.
- Confirm whether bilingualism is required. If the same report is to be shown to English readers, it is recommended to finalize the Chinese format first, then translate it, and check the text of the figures and tables page by page.
Steps to use stacked bar chart to make funnel chart in PPT
- Prepare sample data and calculate auxiliary columns. Taking "Visits 1000, Registration 300, Payment 90" as an example, the top value is 1000. Auxiliary column = (1000 − current value) ÷ 2, giving 0, 350, 455. Place the auxiliary columns and original data in two adjacent columns of the table.
- Insert a stacked bar chart into the slide. Select stacked bar chart in the chart type. The data area contains three columns: stage name, auxiliary column, and value, with the auxiliary column in front and the value in the back.
- Let the stages be arranged from top to bottom. The bar chart is arranged from bottom to top by default. In the axis settings, adjust the category order to reverse order so that the first stage appears at the top.
- Set the auxiliary series to no padding. Select the color block corresponding to the auxiliary column, set the fill and border to none, and the white space on the left will be invisible, leaving only the real bar in the center.
- Adjust the bar width. Make the spacing or gap width smaller so that the layers are funnel-shaped rather than scattered thin strips. With fewer layers, the bars can be thicker.
- Set data labels and colors. Only add labels to real numerical series. Colors can be arranged from dark to light, or different brightnesses of the same color system can be used to avoid using too many colors.
- Mark conversion rate. Add a text box between two adjacent layers and write the result of "next stage ÷ previous stage", for example, 300 ÷ 1000, 90 ÷ 300. Conversion rates for the same graph retain the same number of decimal places.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether the bar is centered | Is the left and right white space on each layer symmetrical? | Check if the auxiliary column is (top value − current value) ÷ 2 |
| Hierarchical order | Is it consistent with the process from top to bottom? | Adjust the order of categories on the axis |
| Tag number | Compare the source data table layer by layer | After modifying the value, the auxiliary columns and labels are updated simultaneously. |
| Whether the auxiliary series is hidden | Auxiliary series should have no padding and no borders | Reset padding and borders |
| Conversion rate denominator | Whether the denominator is the previous stage | Recalculate and write down the formula clearly in the notes |
| Whether the text overflows | Whether the stage name is displayed in full | Shorten the name, reduce the number of layers, or enlarge the text box |
If the data is copied from a table, also make sure that the numbers are not treated as text, otherwise the calculated auxiliary column will be wrong.
📊 Core comparison
| practice | Suitable for the scene | Main limitations |
|---|---|---|
| Stacked Bar Chart Plus Auxiliary Series | Data changes frequently and needs to be accurately centered | More steps to set up for the first time |
| Manually arrange trapezoids or rectangles | One-time gesture, pursuit of appearance | After the number changes, each layer must be manually aligned. |
| Apply a ready-made funnel template | Want to quickly apply styles | The style is fixed and you need to adapt the data structure yourself. |
❓ Frequently Asked Questions (FAQ)
1. Why is the bar not centered, but tilted to one side?
Usually the auxiliary column is calculated incorrectly, or the auxiliary series is not set to no fill. Change the auxiliary value of each layer to (top value − current value) ÷ 2, and then make sure that the fill and border of the auxiliary series are turned off.
2. Why does the data label display the number of the auxiliary column?
Labels are added series by series. Only enable the label on the real numerical series and turn off the label on the auxiliary series.
3. Where should the conversion rate be marked and how many decimal places should it be kept?
Marked between two adjacent floors, indicating the proportion from the previous stage to the next stage. For the same picture, one or two decimal places should be retained to avoid misunderstandings by readers when making comparisons.
4. The data has been changed, does the funnel chart need to be redone?
unnecessary. As long as the values in the source data table are updated, and the auxiliary columns and conversion rate labels are recalculated simultaneously, the graph will change accordingly. This is also the main benefit of using stacked bar charts.
📝 Summary
The key to the PPT funnel chart is to use a stacked bar chart with a hidden auxiliary series to center each layer, and then use the ratio of two adjacent layers to mark the conversion rate. If the data is organized clearly, the calculations are correct, and each item is checked after completion, the chart will remain accurate when the data is updated.
