In today's Excel video,
I'm gonna show you how you can create
a sales funnel chart in Excel
that looks like this one.
Some time ago there was a comment
from one of the viewers of this channel
who said they would like to see my take
on the sales funnel chart.
So here it is.
Now, this is some sample
data set that we have.
It's sorted, so first we have Prospect,
then we have Contact,
Initial Visit, Opportunity and Client.
Now, in Excel 2016,
you already have an
inbuilt sales funnel chart.
All we have to do is click somewhere
in our data set.
We're gonna go to Insert
and click on this icon
here to see more charts.
We can actually already see it
in recommended charts
but we can also see it
if you click on all charts
and at the bottom we see
the funnel chart, right.
So in 2016,
that's how it looks.
That's how it also looks in Power BI.
And in the older versions
where this wasn't available,
the general solution or
the most common solution
was to create a bar chart, right.
And this part,
so the part in the bottom that's white,
that was set to invisible.
But in this tutorial I'm gonna show you
how you can turn this
Excel 2016 funnel chart
to something that looks like this.
But you can apply the same concept
to the bar chart version as well.
(upbeat music)
So that's our sample data set.
First thing I'm gonna do,
make sure the data's sorted.
In this case it is.
I'm gonna go to Insert.
I'm just gonna click on recommended charts
because it's gonna come up.
I'm gonna click on this and click on OK.
Now, the funnel chart is one
of these newer chart
types that was added here.
And the newer ones,
they don't have the flexibility
that we have in the older ones.
And we're gonna come across one
of the restrictions in here as well.
We're gonna see that in a bit.
Now, this funnel chart
is pretty much ready, right.
So we don't really need to do anything
except maybe change the colors
and make this more readable.
If I wanted to have different colors
for each of these,
I just have to double
click on one of the series
and then just change the color.
So I'm gonna go to the Home tab
and let's say change
that color to a yellow.
And then I can click, click again
on the second one,
just so that you see that one highlighted
and the other one's not
and then you can change the color
of the other one as well.
But now what we're gonna do is
to not just change the color,
but also make it look like a funnel.
So I'm gonna go to the Insert tab.
From there I'm gonna insert a SmartArt.
So click on SmartArt.
Let's go down to Pyramid
and I'm gonna click on this one
and click on OK.
Now, I have the ability to add text here
and so on but all of this is static.
Right, it's not dynamic.
So what I'm gonna do is first to add in
the number of shapes I have in my chart.
Okay, so that's my number of categories.
In this case there are five.
So I'm gonna click on Add Shape.
I need to click it again
so I have two more shapes added here.
And now here comes the deal.
I'm actually gonna convert this
to a normal shape.
I don't want it as a SmartArt.
So I'm gonna right mouse
click on this shape
and say convert to shapes.
Now they're just normal shapes.
So what I'm gonna do
is to change the color
of each one to the color that I want
to ultimately have in my chart.
So with this one let's go with yellow.
The other one, orange.
And for the last one, a green.
Okay, so these are the colors
that I ultimately wanna have here.
So now this is what I have to do,
I'm gonna click, click.
So basically select the first one,
press Control C.
Now, here's the difference
between the new charts and the core,
the older Excel charts,
is that I could click
and click on a series
and do Control V.
But look what happens if I do this here.
I just put a shape on top of this, right.
This shape is not even inside the chart.
So what I need to do is not click,
click and do Control V (paste),
but instead go to the Fill options here,
select a picture.
Now I'm gonna select Clipboard.
Okay so because it was
copied to my clipboard
and now I'm gonna click
away and I have my chart
with this blue border.
So let's take away the
border from all of this.
I'm just gonna click on this once,
go to Format under Shape Outline,
click on No Outline.
I have now the look for the first one.
I just have to repeat
that for the other ones.
So Control C, click, click.
Go to Fill options,
click on Picture,
click on Clipboard.
And then the same thing
for the other ones.
Okay so that gives me a funnel chart
that really looks like a funnel.
I can decrease the Gap Width here.
I'm actually gonna take it to zero.
That's not gonna cover the entire gap
and I don't want that either
because I do have a white
line in here as well.
So I do have a white border in there.
Let's just make sure it's dynamic.
So that's my 76, is for Opportunity.
Let's change that to 90.
That updates.
Let's change this to 40
and that updates as well.
Okay so this is how you can tweak
the funnel chart to make
it look like a funnel.
I hope you liked this video.
If you did,
don't forget to give it a thumbs up.
If you enjoy these type of videos,
you wanna become more advanced in Excel,
if you haven't subscribed
to this channel, consider subscribing.
(upbeat music)
