This tutorial shows you how to create a sales funnel chart that is more artistically creative than the built-in funnel chart in Excel.
Examine the below sample data set.
We have a sorted list in descending order. The Funnel chart was introduced in Excel 2016.
We can create a funnel chart by selecting our data and then selecting Insert -> Recommended Charts.
We can see the Funnel chart at the bottom of the Recommended Charts list. The official home for the funnel chart is on the All Charts tab in the Funnel category.
This is how the Funnel chart looks both in Excel and PowerBI.
Prior to the introduction of funnel charts, one of the ways this could be created in previous versions of Excel was by creating a stacked bar chart and rendering the lower stack (red bars in image) invisible by setting their border and fill colors to “none”.
Although this is a fine chart, we would like to put our artistic spin on the chart. The steps below can also be used in a variety of charting scenarios to create more aesthetically pleasing charts.
Featured Course
Visually Effective Excel Dashboards
Creating the Initial Funnel Chart
Begin by selecting the data and then selecting Insert -> Recommended Charts and click the Funnel chart option.
Because the Funnel chart is a relatively new chart type (introduced in Excel 2016), it lacks many of the customization features present in charts that have been around for many years (for example you can’t add additional series and change the chart type of the added series as well as a few more restrictions).
The Funnel chart is basically finished; it needs just a few adjustments to make it easier to read. Changing the font colors of the data values and perhaps making their font larger would help improve readability. We may also wish to color each level in the funnel a different color.
Although this is an improvement, we want to change the shape of the bars to make it look more like an actual funnel.
Creating the Funnel
Begin by selecting Insert -> Illustrations (group) -> SmartArt.
Select the Pyramid category and then select the Inverted Pyramid from the list.
This yields the following:
Although we can add text to the SmartArt object, the object is not dynamic. To make this work with our data, add two more shapes to the hierarchy to match the number of categories in the data.
Select SmartArt Tools -> Design (tab) -> Create Graphic (group) -> Add Shape twice.
Now comes the twist;
We will convert the SmartArt graphic to a traditional shape object.
Right-click on the Inverted Pyramid and select Convert to Shapes.
With the converted shape object, select the top level in the pyramid and alter its fill color.
Repeat this process for each level, selecting a different color for each level.
Apply the Shape to the Funnel Chart
To transfer the pyramid shapes to our original Funnel chart, select the inverted pyramid shape and the click the top-most level in the pyramid.
Click Copy (or Ctrl-C) to copy the top-level shape to the Clipboard.
The typical strategy to apply the copied shape to the Funnel chart is to click on the Funnel chart, click on the top-most level and click Paste (Ctrl-V). Notice that this strategy does not produce the desired result. This is one of the differences of the “new” charts as opposed to the older ones.
The only thing we accomplished was to place the shape on top of the chart. The shape is on the Drawing layer of Excel, not in the chart.
The Proper Way to Paste
The technique that will work is to select the top-most level in the Funnel chart, right-click the level and select Format Data Point.
In the Format Data Point panel (right) click the Fill Options and expand the Fill category. Select Picture or Texture Fill and click Clipboard.
We have pasted the new shape to the top-most level. If the shape has a border, remove the border by selecting the new level and select Chart Tools -> Format (tab) -> Shape Styles (group) -> and set the Shape Outline to No Outline.
Now repeat the process for each additional level.
Final Touches
Set the Gap Width of the Funnel chart to 0%. This will reduce the whitespace between each level.
The whitespace will not be removed entirely, but that’s okay. We want a small amount of whitespace to aid in the visual separation of each level in the Funnel chart.
Test the dynamic nature of the Funnel chart by changing the values in the data (i.e. Opportunity from 76 to 90 and Client from 38 to 40. Observe how the chart has updated to reflect the new data.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
