Pivot Tables are a quick and easy way to manipulate, consolidate, and summarize data. There is a lot of information that can be derived from pivot tables. In this article, we will go over how to create and manipulate a pivot table, as well as show some common operations performed with pivot tables with examples. We will use the following data set of used cars that a dealership received in June 2020 and have subsequently sold.



There's a lot of information here, but that is what pivot tables are designed to simplify. The first thing we will do is create the pivot table. Go to the 'Insert' tab and click the very left selection labeled 'PivotTable'.



A window will pop up to ask for some information. First it will ask you for your table or range that you want to form into a Pivot Table. If your information is formatted like a table, you can simply type in the name of the table here. Otherwise select the range that the information is in. The above table selection will be A1:J16, but the table's name is "DealershipData".


The next thing it will ask for is where you want the Pivot Table to be placed. If you select "New Worksheet," then it will create a new tab in the workbook that will contain only the Pivot Table. If you select "Existing Worksheet," you will need to specify a cell that you want the Pivot Table to be placed in the current sheet. For this example, we will select Existing Worksheet and place it in cell B18. Click "OK" to create your Pivot Table.


You will now see the Pivot Table starting in the cell you selected as well as a new menu on the right called PivotTable Fields.


             


We are going to use this new Fields menu to manipulate our data into the PivotTable.


The fields are the columns of your original table and are stored, waiting for you to determine where they are to go. You can put them in one of the four areas below: Filters, Columns, Rows, and Values. You can start creating your Pivot Table by dragging the fields into the different areas.

  • Rows – Here we will choose the field/s which we would like to base our Pivot Table rows upon.
  • Columns – Here we will choose the field/s which we would like to base our Pivot Table columns upon.
  • Filters – Here we will choose the field/s by which we would like to filter our data in the Pivot Table.
    • i.e.- we would choose “Year” to filter by a specific year
  • Values – Here we will choose the field we want Excel to calculate and our desired calculation.


Let's start with something simple, the sum of the final price of the cars based on make. Drag "Make" into the Rows area and "Final Price" into the Values area. In the Values area, it will be defaulted to the sum of the values.



The Pivot Table will automatically change based on the fields you have inputted.



We can change the way these values are calculated. We do this by clicking the "Sum of Final Price" in the values and selecting the Value Field Settings. This will open a menu with a list of ways to summarize the value field. Let's pick Average. Again, this will automatically change the PivotTable based on how you set up your fields.



Now let's compare the average final price to the average tag price. This lets us know if the dealership would be more or less profitable than they expected. Drag the Tag Price down to the Values area, then go into the settings and change the summarization option to Average. Our new PivotTable will appear as such.


       


Notice how the Columns area is no longer empty. Since we have added a second column of values to the Pivot Table, it now must specify what the columns are made up of. This will change automatically.


Now let's try something different. Delete the current fields from your Pivot Table so we can start fresh. We can segment the rows and columns by specifying different fields in the rows and columns area.


The dealership wants to know how much revenue they earned on used cars each month, but also want it to be broken up by car make. To figure this out, place the fields as such:

  • Rows
    • Make
  • Columns
    • Sale Date
    • Months*
  • Values
    • Sum of Final Price

*Months was not previously a field we had, but since we are using information that was formatted as a date, it will add appropriate additional fields to better summarize the data. Since all of these dates exist within 2020, it will add a months field to summarize by month, and have a selection in the Pivot Table to expand each month to see the sales by date within that month.


         


We can also do segmentation of more than one field. We do this by putting multiple fields in the rows section. 


Let's do a count of how many manuals and automatics were sold each month. From a blank Pivot Table, input the following fields:

  • Rows
    • Months
    • Gear
  • Values
    • Count of Final Price

Your Pivot Table should look like this after inputting this data:


Click the plus sign next to each month to expand the section to see manual and automatic counts each month.



Now suppose we want to see the number of sales each month, but we want to be able to filter by the number of previous owners. We will now utilize the Filter Field Area. Input the following data:

  • Rows
    • Months
  • Values
    • Count of Final Price
  • Filters
    • Previous Owners

Your Pivot Table should now look like this, adding extra rows to the top to indicate your desired filters.



If we click the filter arrow at the top, it will let us select what values we want to filter to. Let's filter for one previous owner. Our Pivot Table will update to this:



This concludes the basics of Pivot Tables. Download the practice file attached and play around with manipulations. Ask yourself some questions on how you want to summarize the data and see if you can figure out how to do it with the Pivot Table.