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-
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.
- Select all 3 support columns and insert a 2D Stacked Bar Chart form Insert Tab
- 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.
- Now Space series will be on left side.
- Right click on Vertical axis and click on Format Axis.
- In the Format Axis window just tick the “Categories in the reverse order” check box
- 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%
- 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.
- 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.
- Now click anywhere on the pivot and go to Analyze >> Insert Slicer
- In the Insert Slicer window, select Qtr, Month and Region.
- Slicers will be inserted for Qtr, Month and Region.
- 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.
Now if you click on any slicers chart will be changed accordingly.
Click here to download Sales Funnel Chart.