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).