PivotTables may be what put Excel on the map. They are so powerful but, once set up properly, can be so easy to use to analyze data. Since they are so very flexible and dynamic, we may take a few posts to get through only a portion of what can be done with PivotTables. Let's get started.
I have a large array of sales data that I need to analyze (I've hidden some rows to show the breadth of the data):
Next, I highlight the data, including the column headers, and then click on the PivotTable button in the Tables group on the Insert menu:
A pop-up will appear with the Table/Range already defined. The other choice that needs to be made is where you want the PivotTable located - on this worksheet or in another. If on this one, you need to select the cell location of the left uppermost corner of the PivotTable. I've chosen cell I1 of this same sheet and clicked OK:
Several things have happened:
- the beginning outline of the PivotTable appears in cell I1
- a new dynamic tab called PivotTable Tools appears in the Ribbon
- a PivotTable Field List appears showing all the column headings from your table along with boxes you can format that data into
In the upper right corner of the PivotTable Field List is a menu button that gives you options of how you want the Field List laid out. I've chosen the second option - "Field Section and Area Section Side-By-Side":
Now it's a matter of deciding how you want to structure and analyze your data. The four fields that your categories can be dragged into are:
- Report Filter: here I would put the column headings that you want to filter the data by. For example, I will choose the Region and New/Used categories.
- Row Labels: here I would put the headings for the rows. I am going to insert Salesman here.
- Column Labels: here is where I will put the headings for my columns. I've decided to put Model here.
- Values: in this field I will put the data that I want to display with each filter I apply, which will be the sales.
Note that you do not have to put every column heading into a field. At this point I still have the Date column heading not assigned. We will move that one into a field in a later post:
Note what happens with each move. The PivotTable starts to take shape. The Report Filter fields appear above the table and the row, column and value areas are being filled in.
Now, before you start your analysis, it might be time to format the PivotTable. As I noted above, by clicking anywhere on the PivotTable, PivotTable Tools with an Options and Design tabs appears in the Ribbon. Click on Design to choose how you want your PivotTable to look:
Clicking on the down arrow in the lower right corner will give you a wide variety of format options to choose from. I chose an orange colored table and checked the Banded Rows and Banded Columns. I also formatted my numbers with commas and no decimal places:
The Options tab provides a whole slew of variables that you can choose from. Note to the far right is a group called Show. This includes a button for the Field List if you want to bring it back to drag various columns into different fields:
Now it's time to play with the filters! As you can see, there are 4 filters that you can choose to modify:
- Column labels - this will allow you to choose any or all of the Models
- Row labels - this will allow you to choose any of the Salesmen
- Region & New/Used - here you can filter these as you wish
Clicking on any of the filter buttons will bring down a list of your options for that category:
As you filter out any choices, you will notice a funnel symbol indicating that there is a filter applied to that category, and the data in the values area will change dynamically. Below, I've filtered the Region to show just South and took one Salesman out of the list:
I'm sure you can see a wide range of uses for this in your work or at home. There's so much more to learn, but we'll cover some of that in future posts. Start to play with PivotTables and get comfortable with them, and we will go over more features soon.
Note - if you need to remove a PivotTable, click anywhere on the table, then click on the Clear button in the Actions Group of the Options tab:
Enjoy your pivoting! You may want to go back to a post I did about 4 weeks ago on Slicers, which is a nice adjunct to PivotTables!