Home>Blogs>Charts and Visualization>Dynamic Sales Funnel Chart with Slicers
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

Steps to create Dynamic Sales Funnel Chart with Slicers

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.
Dynamic Sales Funnel Chart with Slicers
Dynamic Sales Funnel Chart with Slicers

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

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step video tutorial:

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com