Create and pivot a PivotTable

- Pivot tables are a powerful and flexible excel analysis tools. A pivot table lets rearrange, sort and filter a data set on the fly. So you can analysis it from serveral different perspectives with a minimum of effort. In this movie I will show you how to create a pivot table and rearrange it's data. My sample file is Project Analysis 0402. It's an excel work book that you can find in the chapter four folder of the exercise files collection. This work book contains a single work sheet and in it I have an excel table that has monthly data for a series of projects with budgeted and actual labor data. If I want to create a pivot table from this excel table I click any cell within the excel table and then go to the insert table of the ribbon and click the left most button which is pivot table. Doing so displays the create pivot table dialog box. My excel table is named table 1, so it's been correctly identified. I want to create my pivot table on a new work sheet so I'll leave that option selected, I don't need to select location on existing work sheet. The final check box does require a little bit of explanation. Starting with excel 2013, excel has come with the data model. The data model allows you to combine different tables of data based on common fields. For example you might have a table of customer information with a customer ID field, that's unique for each costumer and then have an order table that has the customer ID of the customer who placed the order and then you can combine those two tables together based on the common field. If your version of excel shows this check box as an option and you are a fairly advanced user then you should leave the add this data to the data model check box selected. There is an important caveat though and that is if you think you want to create a calculated field that is you don't have access to the original data and instead you want to perform a calculation within the pivot table by creating a new calculated field then you should uncheck this box. One of the limitations of adding data to the data model is that any table you add can no longer have calculated fields applied. So if you're unsure, I recommend that you clear this checkbox and if need be you can always create a new pivot table based on a work book with multiple tables. Right, with that out of the way let me go ahead and click okay to create the pivot table. Clicking okay creates a new work sheet and on it you see here we have the pivot table. Based on the state of your sample file it might named pivot table 1, pivot table 3 or something other than what you see here. At the right you will see the pivot table fields pane. And in there you have a list of fields and then also four field areas which I will demonstrate in a moment. If you don't see the pivot tables fields pane for example if you close it by clicking the close box, then make sure that any cell in the body of the pivot table is selected. Go to the analysis contextual tab in the ribbon, and click field list. That will bring it back. Now we can add fields to the pivot table and rather than explain what the different areas do I'll just demonstrate. Lets say that I want to have my project names as my row headers. So I'll drag the project field to the rows area. And there I see my four project names in alphabetical order. I want to break my data down by year. So I will scroll up and drag year to columns. And I want to see the budgeted amount so not want was actually spent but what we had budgeted, and drag budgeted to the values area. The data's unformatted but you can see how the pivot table works. Just by dragging field headers to the different areas I was able to define the layout. If I want to change the pivot tables layout I can do it by dragging field headers to and from the different areas. For example, lets say I want to see project and the year as my row headers. So the top level of organization will be project and then I'll see both years 2016 and 2017 underneath it. So I will drag year from columns to underneath project and there I see the data. As you can see from even this simple example, pivot tables are extremely powerful. They allow you to analysis your data multiple different ways and to find the view that works best to discover the information you need.