Excel 2016 does not include a built-in Funnel chart type, but you can make a useful funnel-style visual with a stacked bar chart and a helper column that centers each stage. Arrange the stages in order, calculate the offsets, then hide the offset series so only the centered bars remain visible.
Does Excel 2016 have a native Funnel chart?
No. The standard Funnel chart option is available in newer Excel versions, but it is not part of Excel 2016’s built-in chart choices. If you have Excel 2016, you will not find Funnel under the Insert chart menu. The workaround below uses chart types that are available in Excel 2016 and does not require an add-in or macro.
Version labels can be confusing: someone who remembers using Excel 2016 might now be running a Microsoft 365 version of Excel, which receives newer features. Check File > Account to see your product information. If your current Excel has a Funnel option under Insert, you can use that native chart. If it does not, the stacked-bar method is a practical substitute.
A chart made in a newer Excel version may not behave or look the same when opened in Excel 2016. If you need to share a workbook with people using older versions, test it in the version they use or provide a static image as well as the editable data.
What a funnel chart shows
A funnel chart compares values across sequential stages, often to show where a process loses volume. Common examples include website visits moving to sign-ups and purchases, job applicants moving through interviews, or products moving through a sales pipeline. The bars become shorter as the values decline, making the size of each stage easy to compare at a glance.
A funnel chart is not automatically a conversion-rate analysis. It displays the values you supply; it does not explain why people left a process or calculate the rate between stages unless you add those calculations yourself. It also assumes that the stages belong in a meaningful sequence. Sorting labels alphabetically, for example, could make a sales funnel misleading even if the numbers are correct.
For a simple funnel, values generally decrease from the first stage to the last. If a later stage is larger, that may be valid, but it can break the familiar narrowing shape. Check whether the stages overlap, cover different time periods, or count the same people more than once before interpreting the chart.
Prepare the stage data
Put one stage on each row, in process order, and keep the labels and values together. For example, a basic sales process could use these figures:
- Website visits: 1,000
- Product views: 420
- Checkout starts: 150
- Purchases: 75
Use a small table with the stage labels in column A, a helper column titled Offset in column B, and the stage values in column C. Enter the values in C2:C5 and the labels in A2:A5; put headings in row 1. The offset is a spacer that moves each visible bar toward the center of the chart. It is not part of the business data and should not appear as a colored segment.
Keep the values numeric, not numbers stored as text. Avoid blank rows inside the table, and use clear labels that can fit beside the chart. If you are comparing different reporting periods, make sure every stage uses the same definition and date range.
Build the centered-bar workaround
With stage names in A2:A5, the helper column in B, and values in C2:C5, enter this in B2:
=(MAX($C$2:$C$5)-C2)/2
Copy the formula down through B5. It finds the largest value and calculates half the difference between that maximum and each stage value. For the example, the offsets are 0, 290, 425, and 462.5. A value of 420 plus its 290 offset reaches 710; the bar is centered around 500, just like the other bars. The largest value, 1,000, has no offset and spans the full chart width.
- Select the table from A1:C5, including the headings.
- Choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar.
- If the stages or series appear in the wrong order, choose Chart Design > Select Data. Set the category labels to A2:A5, and make sure Offset is listed before the stage values.
- Right-click the horizontal value axis and choose Format Axis. Set the minimum to 0 and the maximum to the largest stage value, 1,000 in this example. A fixed maximum keeps the chart scale predictable.
- Select the Offset series, open Format Data Series, and set its fill to No fill and its border to No line.
The visible value bars should now be horizontally centered and decrease in width by stage. If the first stage appears at the bottom, right-click the vertical category axis, choose Format Axis, and select Categories in reverse order. Excel may adjust where the horizontal axis crosses the categories, so check the result after changing the order.
Format labels and improve readability
Use data labels to make the exact figures visible without requiring readers to estimate bar lengths. Select the visible value series, then choose Chart Design > Add Chart Element > Data Labels and select a suitable position, such as Center. If the labels are hard to read inside short bars, try an outside position or use a data table next to the chart. The precise label positions available can depend on chart layout and available space.
Give the chart a descriptive title, such as Website visits to purchases, and use a consistent color for the stages. The goal is to make the order and the size differences clear, not to decorate each bar differently. If highlighting a final stage is useful, use one accent color sparingly and explain what it means.
- Remove the legend if it only lists Offset and Value; those are construction series, not meaningful categories for the reader.
- Keep the horizontal axis or data labels if readers need to judge the scale.
- Use whole-number formatting for counts and percentage formatting only for calculated rates.
- Check that stage labels are readable and that labels on short bars do not collide.
To show drop-off percentages, calculate them in separate worksheet cells rather than treating the funnel width as a rate. For example, the change from 1,000 visits to 420 product views is a decline of 58 percent, because 580 of the original 1,000 did not reach the next stage. State clearly whether a percentage is a conversion rate from the previous stage or from the first stage; those are different measures.
What the workaround can and cannot do
The stacked-bar construction is useful when you need an editable chart in Excel 2016 and the main purpose is to compare stage counts. It uses ordinary worksheet values, so you can update the counts and the helper formulas will recalculate. The chart also remains a standard Excel chart rather than a pasted screenshot.
It is not identical to a native Funnel chart. The visible sections are rectangular bars, not connected trapezoids, and Excel does not automatically interpret the stages as a process. You must maintain the stage order, calculate any conversion rates separately, and format the helper series correctly. If your audience expects a polished, continuously tapering funnel, consider a newer Excel version or a different visualization tool.
For many analyses, a regular bar chart may actually communicate the data better. It makes comparisons straightforward and avoids suggesting that every stage is a subset of the stage before it. A funnel is most suitable when that sequential relationship is real and important to the story.
Common problems and fixes
If bars are left-aligned instead of centered, check that Offset comes before the values in the stacked series and that the helper series has no fill. A visible offset segment means the formatting was applied to the wrong series or Excel reset its fill. Use Select Data to confirm the series order, then select the offset segment in the chart and format it again.
If the bars look nearly identical, inspect the horizontal axis maximum. A maximum much larger than the largest stage compresses all bars; a maximum smaller than the largest value can cut off the chart. Set the axis maximum to the largest stage value or to a deliberate fixed scale that is appropriate for comparisons.
If the stages run bottom to top, reverse the category order as described above. If your values are not declining, first check the data and definitions rather than forcing the chart into a funnel shape. Also verify that the helper formula was filled down with the intended range. In the example formula, the MAX range is absolute, while C2 changes to C3, C4, and C5 as you copy down.
If a label is missing, check whether the value series is selected when you add labels. The offset series is intentionally invisible, and labels added to it will not convey the stage counts. You can also widen the chart or move labels outside bars that are too short to contain text.
Alternatives if you need a native funnel
If your organization has access to a newer Excel release that includes Funnel charts, use Insert and look for the Funnel chart option in the chart choices. Menu placement can vary with the version and ribbon layout. A native chart can be quicker to create, but the data still needs meaningful sequential stages and consistent definitions.
Other options include building a simple horizontal bar chart, creating the graphic in a presentation or diagram tool, or using a business-intelligence application such as Power BI. Choose based on what the chart needs to communicate and whether the file must remain editable in Excel 2016. For a report that will not be edited, exporting a chart as an image can preserve its appearance, but retain the source data separately for future updates.
Frequently Asked Questions
Can I insert a funnel chart directly in Excel 2016?
No. Excel 2016 does not provide the built-in Funnel chart type. Use a stacked bar chart with an invisible offset series, or use a newer Excel version that offers a native Funnel chart.
Do I need an add-in to make a funnel chart in Excel 2016?
No. The centered stacked-bar workaround uses standard chart and worksheet features. An add-in may offer extra layouts, but it is not required for the basic visual.
Why is the offset formula needed?
A normal bar chart starts each bar at the same axis origin. The helper value adds an invisible spacer before each visible bar, positioning the bars so their centers line up and their widths reflect the stage values.
Can I use percentages instead of counts?
Yes, if the percentages are defined consistently. Format the cells as percentages and set the axis maximum appropriately, commonly 100 percent for percentages of the initial stage. Do not mix counts and percentages in the same series.
Will the chart update when I change the values?
Yes, provided the chart includes the cells you update and the helper formulas are filled down for every stage. If you add more rows later, expand the chart’s source range and update the formula range as needed.
Related
- Excel To-Do List Examples and Simple Templates
- Excel Text Number Format: How It Works and When to Use It
- Excel Text Filters: How to Find Matching Rows Easily
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Slicer Link to Different Pivot Tables: How It WorksYes. In Excel for Microsoft 365 on Windows, one slicer can filter multiple PivotTables when those PivotTables share…
- Excel Waterfall Chart Total Column: How to Add OneTo make a column a total in an Excel waterfall chart, select that individual column, right-click it, and…
- Excel Pivot Table Min and Max Values: How to Show ThemTo show a minimum or maximum in an Excel PivotTable, put a numeric or date field in the…
- How to Read Economics Graphs Clearly and ConfidentlyTo read an economics graph, identify what each axis measures, check the scale and legend, then interpret the…
- Pivot Power Sioux Falls, SD: What to KnowIf you are looking for Pivot Power in Sioux Falls, South Dakota, the name alone is not enough…
- Create an Org Chart in Excel from Your Employee DataYes. In Excel for Microsoft 365 on Windows, choose Insert > SmartArt > Hierarchy > Organization Chart, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.