Sales funnel - Microsoft Excel Tutorial

- [Instructor] Welcome back to another Excel Dashboard Challenge. We got an awesome challenge for you this week. This is for all my marketers out there. If you're a marketer out there, raise your hand. Even if you're not, you're going to love this challenge because it's going to show you things in Excel that you probably did not know. So this week, we're going to talk about how to build a funnel chart, and we're going to do this in one of two ways. All right, we're going to use the funnel chart and then I'm also going to show you a neat font trick that will allow you to create funnel charts really quickly. Now, if this is your first time looking at a funnel chart, they're really useful to understand a customer's journey through a lead to a sale. For instance, first, we would generate a lead. So perhaps we have 500 people coming in, then we qualify them. Maybe we give them a test and say, "Oh, this product is good for you." Then, we send them a proposal, and then we negotiate that proposal, and then we close the deal. So we know that a certain amount of people aren't going to make it to the end of the funnel, but if we understand deterministically who moves through our funnel and at what conversion rates, we understand that if we increase the lead generation, that's going to actually close more deals. And a funnel chart is an amazing way to visualize that journey. So go ahead and click on that Start tab, just so we can get started over here. Like I said, there's two ways to do this. At some point, in the past 10 years, I can't quite remember when Excel added a funnel chart to the mix, and this was around the time that they started doing Power BI. And you're going to see that because the chart's a little bit different than its other charts. So let's go ahead and add that funnel chart in. I'm going to highlight cells A2 all the way through B6. I'll go to Insert here. And under this waterfall-looking chart here, I'll click on the Down Arrow and you can see the funnel is right there. Mr. Funnel, so I'll click on that. Here's our funnel chart, and you can see it's built it for us. Now, I said that this is slightly different than other charts because you can actually click on this chart and highlight a specific item. What can you do with that highlighting? Nothing. It's like they built out the feature, but they didn't add more onto it. But that does make it different than most of the other charts. So I'm just going to call this Customer Journey over here, like that. Okay, so that's one way to make a funnel chart. Let me show you the other way, the way we used to do it before Microsoft created this type of funnel chart. So what I'm going to do is I'm going to start in cell N2, and I'm going to highlight A2 all the way down through A6. And that's going to pop out the different stages of our funnel. And then I'm going to go to cell P2 over here and I'll do an equals, and I'm going to bring in this data right next to it. And it's important that I skip a column. So notice that I skipped a column. Take this and drag it down. We're going to make column O a little bit bigger. Now, in cell O2, we're going to actually use a little font trick to create our funnel chart, so I'll type an REPT like that. And then in quotes, I'm going to put in the pipe symbol. The pipe symbol is part of the Backslash key above my Enter key on my American-style keyboard. Now, on a European-style keyboard and a few other types of keyboards, you might find it somewhere else. So make sure you take a moment to see where it is on your keyboard. So we're going to use that pipe symbol here. And how many times do we want to repeat it? I'll select this 500 over here, cell P2, I'll close this off, and then I will double-click this the way down. Okay, I know what you're saying. "That does not look like a funnel chart to me." Well, fair enough. One of the problems is we don't want to repeat this 500 times, that's way too many times. But I did want to show you how this works. So we're going to start back in cell O2. And under where it says P2, we're going to divide it by 10. So we're going to scale it down, double-click this here. We see it's now scaled down a little bit. I know what you're thinking. "This does not look like a funnel chart yet, and these pipes do not look good." So what we want to do is actually change the font, and that's going to make them look a lot better. So from on the Home tab, we're going to go to the Font dropdown, and here I'm going to type in Playbill. So we're going to use the Playbill font. And then you're saying, "Okay, well that's cool, but it doesn't look like a funnel." Well, check this out, okay? I'm going to go ahead and click on Center Alignment 'cause it's a font. Now that looks like a funnel. Now, I did divide this by 10, and I'm looking at that, I'm thinking, "I want these to be a little wider," so I'll divide it by five, make that a little wider. Just make sure that you divide all of them by that number. So you can do that. And then we can actually close this in a little bit. So I'll go to View, remove the gridlines, and then what I like to do is make this left aligned so that the numbers align to the funnel like that. Okay, so before I end this, I want to show you that there's actually two fonts that this works best for. The first is Playbill, the other one is Stencil. And we'll just make that a little bit bigger to deal with the fact that Stencil makes it a little wider. Well, that's two ways to create an awesome funnel chart for your dashboards. I can't wait to show you what's next in my next Excel Dashboard Challenge. I'll see you then.