How to Create a Funnel Chart in Excel with AI
Trying to figure out where potential customers are dropping off is a classic challenge for any business. You know people are visiting your website, but how many sign up? And of those who sign up, how many become paying customers? The answers are hiding in your data. This article will show you exactly how to visualize that customer journey by creating a funnel chart in Excel, covering both the classic manual methods and the newer, faster AI-powered approaches.
What Exactly is a Funnel Chart?
A funnel chart is a type of chart that shows how values shrink through sequential stages of a process. It's shaped like an inverted pyramid or funnel, with the widest bar at the top representing the total starting audience and each subsequent bar getting narrower as the audience shrinks at each stage.
Marketers and sales teams love them because they perfectly illustrate the conversion process. Think about these common examples:
Sales Pipeline: Leads > Qualified Leads > Proposals Sent > Deals Won
Marketing Funnel: Website Visitors > Leads Captured > Marketing Qualified Leads (MQLs) > Sales Qualified Leads (SQLs)
E-commerce Checkout: Viewed Product > Added to Cart > Started Checkout > Purchase Complete
Each step shows a drop-off. The funnel chart makes it incredibly easy to spot the biggest leaks in your process so you know where to focus your improvement efforts.
Step 1: Get Your Data Ready
Before you can make any chart in Excel, you need to structure your data correctly. This is the single most important step. For a funnel chart, you need a simple two-column table:
Column 1: Stage. The name of each step in your funnel (e.g., "Awareness," "Consideration," "Conversion").
Column 2: Value. The number of people or items at that stage. This should be a whole number.
It's important to list the stages in chronological order, from the widest part of the funnel (the start) to the narrowest (the end). Your data should look something like this:
Stage , UsersWebsite Visitors , 65,000Product Views , 12,500Added to Cart , 2,800Reached Checkout , 1,100Purchase , 950
With your data organized like this, you're ready to build the chart.
Method 1: Create a Funnel Chart Manually (Excel 2019 and newer)
If you have a modern version of Excel (included in Office 2019 or a Microsoft 365 subscription), creating a funnel chart is incredibly straightforward because it's a built-in chart type.
Select Your Data: Click and drag to highlight the cells containing your data, including the headers ("Stage" and "Users").
Insert the Chart: Go to the Insert tab on the Ribbon. In the Charts section, click on the "Waterfall, Funnel, Stock, Surface, or Radar Chart" icon. It looks like a set of falling blue bars.
Choose Funnel: From the dropdown menu, select the Funnel chart option. Excel will immediately create the chart for you.
That's it! From here, you can customize it just like any other Excel chart. Double-click on the chart title to change it, or right-click on the bars to change their colors or add data labels to make it easier to read the exact numbers for each stage.
The Workaround: Building a Funnel Chart in Older Excel Versions
What if you don't see a "Funnel" option in the chart menu? If you're using an older version of Excel (like Excel 2016 or earlier), you'll have to get a little clever by creating a "fake" funnel chart using a stacked bar chart. It takes a few extra steps, but the result is just as professional.
Step A: Create a "Spacer" Column
The trick is to create invisible bars on either side of our actual data bars, which will perfectly center our data and create the funnel effect. To do this, you need to add a new "Spacer" column to your data table.
In a new column next to your values, you'll add a formula that calculates how much empty space is needed. The formula is:
=(MAX($B$2:$B$6)-B2)/2
Here's the breakdown:
MAX($B$2:$B$6)finds the largest value in your data set (the topmost bar of your funnel). The dollar signs lock the range so it doesn’t shift as you copy the formula down.-B2subtracts the value of the current stage./2divides the result by two, so the empty space is distributed evenly on both sides.
Drag this formula down for every row in your data table. Your table should now look like this:
Stage , Users , SpacerVisitors , 65,000 , 0Product Views , 12,500 , 26,250Added to Cart, 2,800 , 31,100Checkout , 1,100 , 31,950Purchase , 950 , 32,025
Step B: Create a Stacked Bar Chart
Highlight just the Stages, the Spacers, and the Values. Be sure to select the "Spacer" column data before you select the "Values" data. You can do this by holding the Ctrl key (or Cmd on Mac) while selecting the ranges.
Go to the Insert tab and choose the 2-D Stacked Bar Chart.
Right-click your chart and select Select Data. In the dialog box, click the Switch Row/Column button.
You should now see bars centered on your chart. The final part is just cleaning things up.
Step C: Format the Chart to Look Like a Funnel
Make the Spacers Invisible: Click on one of the bars representing the "Spacer" data. The whole series will be selected. Right-click it, select Format Data Series, and set the Fill to "No fill."
Reverse the Order: The chart will be upside down. To fix it, click on the vertical axis labels (e.g., "Visitors," "Purchase," etc.). Right-click and choose Format Axis. In the Axis Options pane that appears, check the box for Categories in reverse order.
Close the Gap: While you're in the Format Series options, find the Gap Width slider and set it to 0% or a very low number like 1-5%. This will make the bars touch and form the funnel shape.
Clean Up: Delete the legend and add a proper chart title. Feel free to add data labels and customize the colors to match your brand.
It's more work, but this workaround gives you a perfect funnel chart that works in any version of Excel.
Method 2: Let AI Create Your Funnel Chart
If you'd rather not manually click through menus, Excel's AI features can speed things up dramatically, turning you from a chart builder into a data analyst.
Using Excel's "Analyze Data" feature
On the Home tab of the Ribbon, you'll find a button called Analyze Data (it used to be called "Ideas"). This feature automatically scans your data for patterns and suggests relevant visualizations.
Simply click on any cell within your data table.
Click the Analyze Data button on the Home tab.
An "Analyze Data" pane will pop up on the right, filled with suggested PivotTables and charts. If your data is structured properly for a funnel, it will often suggest a funnel chart as a top option.
Find the funnel chart preview and click the + Insert Chart button. Excel will add the complete, formatted chart to your sheet instantly.
It's a fast and easy way to let Excel do the heavy lifting for you.
Using Microsoft Copilot for Excel
If you have a Microsoft 365 Copilot subscription, the process gets even simpler. Copilot adds a Large Language Model (like the one that powers ChatGPT) directly into Excel, allowing you to create charts and analyses just by writing a prompt in plain English.
Make sure your data is formatted as an Excel Table. (Select your data and press Ctrl+T).
Click the Copilot button on the Ribbon.
In the chat pane that opens, you can simply type what you want to create. For example: "Create a funnel chart from this table showing sales process conversion."
Copilot will analyze your request, read your data table, and generate the funnel chart for you without you having to click a single menu item. You can then ask follow-up questions to customize it, like "Change the chart title to '2024 Customer Purchase Funnel'" or "Make the bars blue." This conversational approach entirely removes the learning curve and lets you focus on the insights, not the mechanics.
Final Thoughts
Visualizing your conversion process with a funnel chart in Excel is a powerful way to quickly spot where you're losing customers. Whether you use the simple built-in chart, the classic stacked-bar workaround, or modern AI features like Analyze Data and Copilot, you can create a clear and compelling visualization in just a few minutes.
While Excel is great for these tasks, the setup often requires you to manually pull data and constantly update it by copy-pasting new numbers from your other platforms. That's where we wanted to streamline the process. With an analytics tool like Graphed target="_blank" rel="noopener"), you connect directly to your data sources - like Google Analytics, Shopify, or Salesforce - just once. Then, you can ask for a funnel chart using simple language, and we'll build a live dashboard that updates automatically, saving you from any manual data wrangling for good.