Sales Funnel Chart

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]

What is a Sales Funnel Chart?

Funnel charts are usually used to represent a process’s stages and their outputs. A Sales Funnel Chart does this with sales.

Like the name entails, the chart looks like a funnel made up of stacked bars, as shown below.

sales funnel chart

Other Funnel Charts

There are many other uses for Funnel Charts.

  • Number of employees at each job level (entry-level, experienced, manager, executive, etc.)
  • Recruitment process (applied, shortlisted, initial interview, etc.)
  • Order fulfillment (orders received, orders processed, orders approved, etc.)

Sales Funnel Chart Example

To make the process easier to understand, here’s a scenario:

Your goal is to sell a product that caters to a certain demographic, and the sales process has multiple stages. You can make a Sales Funnel Chart to depict the number of people that make it through each stage of the process, with each bar representing a stage, and the width of each bar representing the number of people that pass that stage.

Here’s an example of a sequence of stages:

  1. Research: Start off with about 5000 people to research.
  2. Qualification: About 3400 of these people fit the demographic you are looking for.
  3. Presentation: You send invites to these 3400 people to come to a sales presentation where you can pitch your product. 2700 people decide to come.
  4. Interest: Out of these 2700 people, 1500 stay to express interest in buying the product.
  5. Close: 800 people actually buy the product.

From here you have 6 main steps:

Step 1: Organize Data

To start off, create three columns.

The first column would consist of your stage names. For the sake of simplicity, let’s just stick with Stage 1, Stage 2, Stage 3, Stage 4, and Stage 5.

Label the second column Spacer (this will be used to center the bars). Keep the rest of this column blank for now.

Label the third column with your desired unit of output. In this example, I will use Stage Winners. Now list the number of units for each stage (in this case, the number of people that make it to each stage).

It should look a little something like this:

funnel - chart 1

In the Spacer column, we’ll be using Excel formulas!

Skip Stage 1, and start on Stage 2.

Each row’s equation will be slightly different, and is best shown through example.

In Stage 2’s Spacer box, type:

=(MAX($C$2:$C$6)-C3)/2

The =(MAX($C$2:$C$6) part stays consistent throughout the column because it selects the max number in the Stage Winner’s column.

Then you need to subtract the box coordinates of Stage Winner’s at whatever Stage you are on.

Divide the whole thing by 2 to center the bar.

Use the numbers I listed under Stage Winners first as a way to check if you are using the equations correctly. Your output should look like this:

funnel chart 2

Step 2: Create a Stacked Bar Chart

Now select the data including the labels for the vertical axis

selected data for vertical axis

Go to Charts tab à Bar à Stacked Bar Chart.

create a stacked bar chart1

create a stacked bar chart 2

Click that, and now you should have something that looks a little like this:

reverse funnel

Now you must be thinking, “Wait, isn’t this supposed to be upside down?”

No worries, we just have to reverse the order of the categories.

Step 3: Reverse Order of Categories

Right click the vertical axis (the one with the Stage Names) and select Format Axis.

reverse order of categories 1

There should be a checkbox that says Categories in reverse order”, so click that.

reverse order categories 2

Now your stages should be in the same order as how they’re listed on your chart.

reverse order categories 3

Do you see how spaced out everything is? We want to get rid of that.

Step 4: Change Gap Width to 0%

To do this, we want to get the gap width to 0% so that it looks more like a connected funnel than a couple bars floating above each other.

Just right click any of the bars and select “Format Data Series”.

change gap to 0

From there change the Gap Width to 0%.

change gap 0 1

Now your chart should look like this:

change gap 0 2

Now we want to make the spacer bar transparent so that only the data we want analyzed would appear.

Step 5: Set Spacer Bar Color to No Fill

Right click any of the bars in the Spacer series and select “Format Data Series”.

spacer-bar-no-fill

 

Change the Fill Color in the series options to No Fill.

space-bar-no-fill-2

Your chart should now look like this:

spacer-bar-no-fill-3

Step 6. Customize your Chart

Now here is the fun part!

You can customize your funnel chart so that it looks presentable for all your presentation needs.

To get to this chart:

sales funnel chart

I did the following:

  1. Changed the fill color from red to blue (Right Click Data Series à Format Data Series à Fill)
  2. Removed the shadowing (Right Click à Format Data Series à De-Select Shadow)
  3. Changed the outline to a different color from the fill (Right Click Data Series à Format Data Series)
  4. Removed the horizontal axis units (Right Click Horizontal Axis à Delete)
  5. Removed graph lines (Right Click Gridlines à Delete)
  6. Added data labels to the middle of the bars (Right Click Data Series à Add Data Labels)
  7. Added a chart title (Charts à Chart Layout à Chart Title à Title Above Chart)
  8. Removed the legend. (Right Click Legend à Delete)

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Leave a Comment