Advanced Excel - Funnel Chart

Advanced Excel – Funnel Chart

Funnel chart is used to visualise the progressive discount of knowledge because it passes from one part to a different. Information in every of those phases is represented as totally different parts of 100% (the entire). Just like the Pie chart, the Funnel chart doesn’t use any axes both.

For instance, in a gross sales pipeline, there will probably be levels as proven beneath.

Prospects → Certified Prospects → Proposals → Negotiations → Remaining Gross sales.

Usually, the values lower steadily. Many prospects are recognized, however part of them are validated and even lesser qualify for Proposals. A nonetheless lesser quantity come for negotiations and ultimately, there’s solely a handful of offers which might be received. This may make the bars resemble a funnel.

What’s a Funnel Chart?

The Funnel chart reveals a course of that begins on the preliminary state and ends with a last state, the place it’s noticeable in what levels the autumn out occurs and by what magnitude. If the chart can be mixed with analysis information, which means quantified measurements of simply what number of objects are misplaced at every step of the gross sales or order achievement course of, then the Funnel chart illustrates the place the most important bottlenecks are within the course of.

Not like an actual funnel, not every thing that’s poured in on the high flows by to the underside. The title solely refers back to the form of the chart, the aim of which is illustrative.

Funnel Chart

One other variant of Funnel chart is the place the info in every of those phases is represented as totally different parts of 100% (the entire), to point out at what charge the modifications happen alongside the Funnel.

Just like the Pie chart, the Funnel chart doesn’t use any axes both.

Benefits of Funnel Charts

Funnel chart can be utilized in varied eventualities, together with the next −

  • To permit executives to see how efficient the gross sales crew is in turning a gross sales lead right into a closed deal.
  • A Funnel chart can be utilized to show Site customer traits. It could show customer web page hits to the house web page on the high, and the opposite areas, for e.g. the site downloads or the folks keen on shopping for the product will probably be proportionally smaller.
  • Order achievement funnel chart with the initiated orders on high and all the way down to the underside the orders delivered to happy prospects. It reveals what number of there are nonetheless within the course of and the proportion cancelled and returned.
  • One other use of Funnel chart is to show gross sales by every salesperson.
  • Funnel chart may also be used to judge Recruitment course of.
  • Funnel chart may also be used to investigate the order achievement course of.

Preparation of Information

Place the info values in a desk.

Preparing Funnel Chart Data

Step 1 − Insert a column within the desk as proven beneath.

Insert Column in Table

You’re going to get the next information. You’ll use this desk to create the Funnel chart.

Create Funnel Chart

Making a Funnel Chart

Following are the steps to create the Funnel chart −

Step 1 − Choose the info and insert a Stacked Bar chart.

Steps to Create Funnel Chart

Step 2 − Proper click on on the Shift Bars (blue coloration within the above chart) and alter Fill coloration to No Fill.

Change Fill Color

The chart appears as proven beneath.

Funnel Chart Looks

Step 3 − Design the chart as follows.

  • Proper click on on the Vertical Axis.
  • Choose Format Axis from the dropdown listing.
  • Click on AXIS OPTIONS within the Format Axis pane.
  • Test the field – Classes in reverse order.

Design Funnel Chart

Step 4 − High quality tune the chart as follows.

  • Deselect Legend in Chart Components.
  • Change the Chart Title.
  • Proper click on on the orange Bars.
  • Choose Format Information Collection.
  • Change the Hole Width to 0% within the SERIES OPTIONS.

Fine Tune Funnel Chart

Step 5 − Choose Information Labels in Chart Components.

Select Data Labels

Your Gross sales Pipeline Funnel chart is prepared.

Formatting a Funnel Chart

Step 6 − Make the chart extra interesting as follows

  • Click on on the Chart Components.
  • Deselect Horizontal Axis.
  • Deselect Gridlines.
  • Proper click on on the Bars.
  • Choose Format Information Collection from the dropdown listing.
  • Click on on Fill & Line below SERIES OPTIONS within the Format Information Collection pane.
  • Click on on Gradient fill below the Fill choice.
  • Set Gradient stops.

Make Funnel Chart

Step 7 − Click on on Stable line below BORDER. Choose Colour as black.

Select Black Color

Your formatted Funnel chart is prepared.

Formatted Funnel Chart

Aesthetic Funnel Chart

Now that you simply understood the basics of Funnel chart, you possibly can proceed to create an aesthetic Funnel chart that truly appears like a Funnel as follows −

Step 1 − Begin with the unique desk of knowledge.

Original Table

Step 2 − Choose the info and insert a 3-D Stacked Column chart.

Insert 3D Stacked Column

Step 3 − Design the chart as follows.

  • Proper click on on the Columns.
  • Choose Format Information Collection.
  • Click on on SERIES OPTIONS within the Format Information Collection pane.
  • Choose Full Cone below Column form.

Select Full Cone

Step 4 − High quality tune the chart as follows.

  • Click on the Design tab on the Ribbon.
  • Click on Change Row / Column.
  • Proper click on on the Vertical Axis and choose Format Axis from the dropdown listing.
  • Click on AXIS OPTIONS within the Format Axis pane.
  • Test the field – Values in reverse order.

Fine Tune the Funnel Chart

Step 5 − Deselect all of the Chart Components

Deselect Chart Elements

Step 6 − Proper click on on the Bars and choose Format Information Collection from the dropdown listing.

Step 7 − Click on on SERIES OPTIONS within the Format Information Collection pane and kind 50% for Hole Depth below SERIES OPTIONS.

Click Series Option

Step 8 − Format your chart with particulars as follows.

  • Choose Information Labels in Chart Components.
  • Place them and format them.
  • You’ll be able to select Name Out choice for the Information Label of the underside half.
  • Give a Chart Title.

Format Your Chart

Your Funnel chart is prepared.