Sales Funnel Chart
Charts and Visualization

Dynamic Sales Funnel Chart with Slicers

In this article you will learn how to create dynamic Sales Funnel Chart with Slicers. This Chart can be used in your business dashboard or presentations.

Below are the data points which we have used to create this chart-

Data Point for Chart
Data Point for Chart

Below are the steps to create this beautiful Chart-

Create a pivot table using this data and move Identify, Validated, Qualified, Proposal and Won fields in Pivot table as given in below image.

  • Take the 3-support columns – Sale Stage, Count and Space as given in below image
  • For Sale Stage use formula “=TRIM(A4)
  • For Count use formula “=B4
  • For Space use formula “=(MAX($F$4:$F$8)-F4)/2
  • Fill down the formulas.
Pivot and Support Columns
Pivot and Support Columns

 

  • Select all 3 support columns and insert a 2D Stacked Bar Chart form Insert Tab
Insert Stacked Bar Chart
Insert Stacked Bar Chart
  • Right click on the chart and click on “Select Data
  • In the Select Data Source window, select the Space in Series and Click on Move up button.
Select Data Source Window
Select Data Source Window
  • Now Space series will be on left side.
Space series on left side
Space series on left side
  • Right click on Vertical axis and click on Format Axis.
 Format Axis option
Format Axis option
  • In the Format Axis window just tick the “Categories in the reverse order” check box
Format Axis window
Format Axis window
  • Now our sale stage will be in proper order on the chart.

  • Remove the Horizontal Axis, Gridlines and Legend from the chart (Just select and press Delete)
  • Right click on the chart and click on Format Data Series.
  • In the Format Data Series window change the Gap Width as 0%
 Format Data Series window
Format Data Series window

 

  • Go to the Insert >> Shapes>> Insert a Rectangle: Rounded corners
  • Change the radius of corners to make it maximum rounded
  • Right click on the rectangle and click on format shape.
  • Go to fill and select Gradient fill and select Stops and Color as given in below image.

 

Rectangle: Rounded corners Shape
Rectangle: Rounded corners Shape

 

  • Change the Chart Title.
  • Format Vertical Axis- Font color as black and make font bold.
  • Copy the Rectangle Shape and paste on “Count” series of the chart.
Sales Funnel Chart
Sales Funnel Chart
  • Now click anywhere on the pivot and go to Analyze >> Insert Slicer
Insert Slicer option
Insert Slicer option

 

  • In the Insert Slicer window, select Qtr, Month and Region.
Insert Slicer window
Insert Slicer window

 

  • Slicers will be inserted for Qtr, Month and Region.
Slicers
Slicers
  • Select the Chart and all Slicers and Cut the selection (Use Ctrl+X)
  • Insert a new worksheet and remove Gridlines from View tab.
  • Paste (Ctrl+V) the slicer and chart here.
  • Format and align the slicers with the chart as given in below image.
Sales Funnel Chart with Slicers
Sales Funnel Chart with Slicers

Now if you click on any slicers chart will be changed accordingly.

 

Click here to download Sales Funnel Chart.

 

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com