Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We're going to take a high level look at creating Pivot Tables, so the information we're going to be working with in the next few movies are going to show you how to easily do a Pivot Table. We're not going to get in depth in all the details behind this, but we're going to give you a taste for what a Pivot Table will actually let you work with, and hopefully spur some interest for you to explore a little bit more on your own. The data that we're going to be working with is our inventory data, and it's included in Pivot Table A's worksheet in your Exercise files, if you'd like to follow along.
Right now I'm looking at my the Data tab. In order to create a Pivot Table, I simply select the table itself, go to the Insert tab, and pick Pivot Table. I've then select the Pivot Table option, confirming what data range I'm looking at. And you can tell that you've selected all of your data because it's highlighting it up here in your worksheet. And then you determine where you want that Pivot Table to go, so I want it to go on a brand new worksheet, which is what the default is, I click OK.
And you'll see that the information is now pulled into its own worksheet. With the Pivot Table field list on the right, and the area where the Pivot Table is going to be created here on the left. Before we go on, let's go down to the bottom and select the Sheet4 name, right-clicking on it and selecting the Rename option, and calling this Pivot Table, so if we happen to use this again in our exercises, we know that this particular tab contains our Pivot table information.
We then want to be able to go over to our Pivot Table field list, and I start identifying some of the pieces of information we want a show on here. First thing we want to select is Item Type. So if we select that, you'll see that you have all the different Item Types shown here along your rows. If I select Item Category, it will also include them on my rows. Now this makes it a little bit difficult to understand the information that I'm looking at here.
This is where you get into the real value of working with a Pivot Table, because all you need to do is to select one of your row labels, in this case Item Category, click and drag it, and pop it into the Column Labels box. So what this section of the pivot table field list allows you to do, is to mix and match where the information is stored. And what you're doing is you're pivoting your rows and columns so that you get the information in a way that's going to make sense for you to understand and analyze.
Hence, Pivot Tabling. Next what you want to identify, is now that you've got the information aligned correctly, What are you going to track? The current Costs, the Inventory levels, or the Final Costs of the different pieces of Inventory that you've got to look at. Well let's just take a look very quickly at the current cost. If I select Current Cost, it then goes and presents for me, the current costs that are sitting in the different cells in my original data file, and it presents it here.
You'll notice as I mouse over them, that you get this little dialog box that pops up, it says Sum of Current Costs and the value is No value. A Pivot Table will automatically default to want to add items up, so in this case, because I'm just pulling an individual value from my data source, it's not going to give me the sum of anything because there's nothing add up. But if I did deselect Current Cost and wanted to add Inventory, I could then see that the Inventory is the addition of all the different pieces.
So it's showing me and it's helping me identify very, very quickly the inventory levels of my particular store. I also have a Grand Total column, of the column and row summarizing all of the inventory levels for all the different ways that I'd like to look at it. So you can see, with three minutes of instruction and a few clicks, you've been able to pull the information from your data source into a Pivot Table and quickly analyze what you need to know.
Let's look further at some of the options that we can find at Pivot Tables.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90643 Viewers
80 Video lessons · 137957 Viewers
59 Video lessons · 56720 Viewers
52 Video lessons · 70329 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.