...

SAP - Advanced Excel Chart

Back to Course

Lesson Description


Lession - #489 Advanced Excel Charts – Funnel Chart


Channel chart is used to fantasize the progressive reduction of data as it passes from one phase to another. Data in each of these phases is represented as different portions of 100( the whole>
. Like the Pie chart, the Funnel chart doesn't use any axes moreover.

For illustration, in a deals channel, there will be stages as shown below.

Prospects-> good Prospects-> proffers-> Accommodations-> Final Deals.

generally, the values drop gradationally. numerous prospects are linked, but a part of them are validated and indeed lower qualify for proffers. A still lower number come for accommodations and in the end, there's only a handful of deals that are won. This will make the bars act a channel.

What's a Funnel Chart?
The Funnel chart shows a process that starts at the original state and ends with a final state, where it's conspicuous in what stages the fall out happens and by whatmagnitude.However, meaning quantified measures of just how numerous particulars are lost at each step of the deals or order fulfillment process, also the Funnel chart illustrates where the biggest backups are in the process, If the chart is also combined with exploration data.

Unlike a real channel, not everything that's poured in at the top flows through to the bottom. The name only refers to the shape of the chart, the purpose of which is elucidative.

Another variant of Funnel chart is where the data in each of these phases is represented as different portions of 100( the whole>
, to show at what rate the changes do along the Funnel.

Like the Pie chart, the Funnel chart doesn't use any axes moreover.

Advantages of Funnel Charts
Funnel chart can be used in colorful scripts, including the following –

• To allow directors to see how effective the deals platoon is in turning a deals lead into a unrestricted deal.

• A Tube chart can be used to display Web site visitor trends. It can display visitor page hits to the home page at the top, and the other areas, fore.g. the web site downloads or the people interested in buying the product will be proportionally lower.

• Order fulfillment funnel chart with the initiated orders on top and down to the bottom the orders delivered to satisfied guests. It shows how numerous there are still in the process and the percentage cancelled and returned.

• Another use of Funnel chart is to display sales by each salesperson.

• channel chart can also be used to estimate Reclamation process.

• channel chart can also be used to dissect the order fulfillment process.

Preparation of Data
Place the data values in a table.
Step 1 Insert a column in the table as shown below.
You'll get the following data. You'll use this table to produce the Funnel chart.
Creating a Funnel Chart
Following are the way to create the Funnel chart –
Step 1 Select the data and fit a Stacked Bar chart.
Step 2 Right click on the Shift Bars( blue color in the chart>
and change Fill color to No Fill.
Step 3 Design the chart as follows-

• Right click on the Vertical Axis.

• Select Format Axis from the dropdown list.

• Click AXIS OPTIONS in the Format Axis pane.

• Check the box – orders in reverse order.
Step 4 Fine tune the chart as follows-

• Deselect Legend in Chart Elements.

• Change the Chart Title.

• Right click on the orange Bars.

• Select Format Data Series.

• Change the Gap Width to 0 in the SERIES OPTIONS.
Step 5 Select Data Labels in Chart Elements.
Your Sales Pipeline Funnel chart is ready.

Formatting a Funnel Chart

Step 6 Make the chart more appealing as follows –

• Click on the Chart Elements.

• Deselect Horizontal Axis.

• Deselect Gridlines.

• Right click on the Bars.

• Select Format Data Series from the dropdown list.

• Click on Fill & Line under SERIES OPTIONS in the Format Data Series pane.

• Click on Gradient fill under the Fill option.

• Set Gradient stops.
Step 7 Click on Solid line under BORDER. Select Color as black.

Your formatted Funnel chart is ready.


Aesthetic Funnel Chart
Now that you understood the fundamentals of Funnel chart, you can do to produce an aesthetic Funnel chart that actually looks like a Funnel as follows –
Step 1 launch with the original table of data.
Step 2 Select the data and insert a 3- D Stacked Column chart.
Step 3 Design the chart as follows-

• Right click on the Columns.

• Select Format Data Series.

• Click on SERIES OPTIONS in the Format Data Series pane.

• Select Full Cone under Column shape.
Step 4 Fine tune the chart as follows-

• Click the Design tab on the Ribbon.

• Click Switch Row/ Column.

• Right click on the Vertical Axis and select Format Axis from the dropdown list.

• Click AXIS OPTIONS in the Format Axis pane.

• Check the box – Values in reverse order.
Step 5 Deselect all the chart Elements.
Step 6 Right click on the Bars and select Format Data Series from the dropdown list.
Step 7 Click on SERIES OPTIONS in the Format Data Series pane and type 50 for Gap Depth under SERIES OPTIONS.
Step 8 Format your chart with details as follows-

• Select Data Labels in Chart Elements.

• Place them and format them.

• You can choose Call Out option for the Data Label of the bottom part.

• Give a Chart Title.

Your Funnel chart is ready.