Sales performance - Microsoft Excel Tutorial

- [Instructor] Well, hello everyone. Today we are going to start with our dashboard challenge to take data from regions and product sales and create this fun little dashboard that we have right here. So I'm going to go ahead and hit start. That is to say, I'm going to hit the worksheet tab named Start. And here we have our raw data set. So the first thing I want to do to this is create a table on top of it. So we're going to create an Excel Table. An Excel table being a capital T table, not just a table of data. I can place my cursor anywhere in this continuous data region and I'll go to Insert and hit Table. Now, I can also hit Ctrl+T. So it's going to ask, where's the data for your table? Well, it's smart enough to figure that out. This checkbox, my table has headers. Indeed it does. So I'll go ahead and click OK. From here, first thing I like to do is rename it. So let's call this Regional Sales Data. Just make our life easier. And then one other thing I like to do, this is just me, is get rid of those banded rows. Ugh, I hate those. Alright, so the next step on our list of things to do would be to insert a PivotTable on top of this. So I'll go ahead and click on Insert, making sure that my cursor is anywhere inside that table region. I click Insert here, I go to PivotTable, and we can see that under table range, it has selected my data. And in this case, I want to start a new worksheet tab. Thus giving rise to the joke. When a PivotTable walks into the bar and the bartender says, Hey, should I start a new tab? Of course the PivotTable says yes. Okay, so once we've built our PivotTable, let's break this thing down. I'm going to take product here, I'll drop it into the rows. That's going to give me my uniqified product list. For sales I'll drop that into my values. That's going to give me the sum of sales with regard to those products. Now, what's missing here? Okay, well I can't slice on date here, and there's no way to figure it out by region. Plus it's a table and we want it to be in a dashboard. So what I'm going to do here is with my cursor, anywhere inside this PivotTable, we're going to add a PivotChart. So cursor inside here, take my mouse, do, do, do, do, do. Ah, it says PivotTable, analyze. That's the tab I want. So I'm going to take my cursor. Do, do, do, do do. Here we go. It says PivotChart. I'm going to click that. Do I want a clustered column chart? You bet. So I'll go ahead and click on that. Now that gets my chart in here. This says total. Total's kind of silly. Like do I really need multiple things that say total? So I don't need a legend 'cause there's only one thing on here. I'm just going to hit Delete on that. Okay, so now we've put this simple chart in here. The next thing we want to do is have the ability to slice on the data pieces that we don't see. For instance, how could we drill down by region? How could we drill down by date? Well, there's two different mechanisms to do this with PivotTables in Excel. One is called the slicer, the other is called the timeline. So with this PivotChart selected, or you could put your cursor in this data, you can click on PivotChart Analyze, and you could see here it says filter. So we have these two choices, insert slicer and insert timeline. I'm going to click Insert Slicer. In this case, we will insert the region, 'cause that's missing. I'll go ahead and hit OK, that's our region right there. You can actually just take this, make it a little smaller like that. And then next I'm going to click on my PivotChart again, I'm going to go to PivotChart Analyze, and from here we have an Insert Timeline. So I'll click that. A timeline is a special slicer that only works on date types. So I'll click Date here. It's automatically detected that I have a date type in my Excel file and I'll go ahead and click OK. And then I will take this and stretch it out to make it look good. Now you'll note that when you drop timelines in, it does have this extra white space here. Don't worry about that. What you could do to get rid of that is just start using the timeline over to the left. And you could see in our case it goes from January to December. So that is the last data point. So you can just take that and close it off right there. Final thing we want to do with our dashboard here is maybe just do a little bit of design work. I'm going to make everything kind of line up just a little bit, just so it looks a little bit nicer. Okay, so we have now completed our first challenge. Go ahead and take a look, make sure that that data works for you. And I will see you in our next challenge.