Pivot tables in Excel allow you to easily summarize data and group information by category. The benefits of pivot tables is you can also double click on any number to see the individual items that make up the totals. The downside is pivot tables are not always very flexible to work with.
In this introductory post I will go over the following:
- How to create a pivot table
- How to setup the pivot table’s rows and columns
- Grouping dates into months, years, and quarters
- Filtering data in a pivot table
- Changing the formatting of fields
- Changing pivot table values to averages
- Show values as a % of a column or row
- Adding more fields and changing views to tabular
- Viewing contents that make up a cell
If you want to follow along with my example you can download my sample file here
How to Create a Pivot Table
This is a simple step, the only requirement is your columns should have headers as you can see from my sample data table below. Select any cell on your data table and click on the Insert tab and click on the Pivot Table button.
That will pop up the next screen:
Excel automatically determined my table range because I had selected a cell on my data. If I hadn’t, then Excel would not be able to pull this information. As long as you make sure you click the insert pivot table button when you have your data selected then usually Excel will get the range correct. Of course if the information is incorrect you can change the range at this screen.
You can select whether you want the pivot table in a new worksheet or the existing one. In most cases you’ll want a new worksheet. In this screen I usually just click OK as the defaults usually work fine.
Setting Up the Pivot Table
When the pivot table is generated you will see the following:
This pivot table is blank and not terribly useful right now. On the right-hand side you will see this:
This is where you will select where you want your data to show. Suppose I want to see a summary of total sales by customer and by month.
First I will find the Total Dollars field from the above list and drag it into the Values section of the pivot table.
Next, I will move the Customer field into the Rows section. Whether I put it into rows or columns will not matter, but normally you want the field with the most amount of items to go into the rows section, because if you have a lot of data in the columns section you will have to do a lot of scrolling to see all of the table. In this example I don’t have that many customers so it does not matter.
Lastly, I will move the Date field into the Columns section.
Your pivot table should look something like this:
If the dates don’t quite look like this then that is fine. In this version of Excel it automatically grouped my dates.
Grouping Dates by Month, Year, Quarter, etc…
If you want to group your dates or want to change the grouping, select one of the dates on your pivot table and under the PivotTable Tools section, under the Options (or Analyze, depending on which version of Excel you have) tab, click on Group Field button.
After clicking that button you will see the following options:
Excel has automatically divided my data into months, quarters, and year. I am going to un-select quarters since I only want to see months and years. Once I make that change I click on OK and it will update my pivot table:
I know have a summary that shows total dollars split by customers and by month. But I’ve decided I do not want to see 2010. I can filter the data even though I’ve included it in my pivot table.
Filtering Data in a Pivot Table
To filter the data, I will click on the Column Labels button since I put the dates into columns. I will see a drop down option to select the field I want to filter (assuming I have more than one field).
I select the Years field and then in the list of checkboxes below I can uncheck the ones for 2010 and
After updating my selections I click on OK. Now anything prior to 2011 has been removed from my pivot table.
Changing the Formatting of Fields
My pivot table looks okay so far except that I don’t like the number formatting that is used. You could change the formatting by selecting the columns and making your changes but the problem with this is it is a temporary solution. If you refresh your pivot table at any point the formatting will go back to what it was. In order to change the formatting for a field, simply right-click on one of the data points and click on Value Field Settings
Which will bring up this pop up:
From here I click on the Number Format button. That gets me back to the Format Cells pop up:
Here I’m going to change my format to Accounting so that it has commas and dollar signs. However, you can change to whatever format you prefer. I now hit OK and get back to my pivot table which now looks like this:
Changing Pivot Table Values to Look at Averages
Currently I see the total dollar value of my sales, but what if I wanted to see the average instead? Then I can right-click on any of the dollar amounts and again go back and select Value Field Settings.
Here I can change from Sum to Average. If I click on OK now my table will show averages now:
You will notice at the top now instead of Sum of Total Dollars it now says Average of Total Dollars. This is a quick way to check what data you are in fact looking at.
Show Values as a % of a Column or Row
I’ll go through another scenario now, assuming that I don’t care about averages but instead want to see the sales as a percentage of the total month. Again I go back to Value Field Settings and change the calculation back to Sum and then click over on the tab on the right called Show Values As
In the drop down selection I select % of Column Total. The reason I select this is because I want a % of the total for the month. Since my dates (and as a result, months) are in the columns, I select columns here. After clicking OK I now see the following pivot table:
If I wanted to see what % of the customer’s sales occurred per month then I could use the % of Row Total rather than column. I am going to revert back to showing as values for the next example. To do this you can just go back to Value Field Settings and under the Show Values As tab, select No Calculation.
Adding More Fields and Changing Views to Tabular
I am going to now add another field to the rows, specifically, I am going to drag the Salesperson field over to the row columns. If you want the pivot table to first be sorted by Customer and then Sales Rep, the Sales Rep field should be dragged under the Customer field. If you want the pivot table to first sort by Sales Rep and then by Customer, make sure the Sales Rep field is dragged above the Customer field.
In my example I am going to put the Sales Rep field underneath the Customer field since I think that is a more logical hierarchy in this example. I’ve added another field, but now I get to a common pivot table problem, my table looks like this:
If I want to use the pivot table in a formula or copy it as values somewhere, then this format is not very helpful since it doesn’t have all the information on one line. I want the customer to show on every line. To do this, I go under the PivotTable Tools section again and under Options/Analyze I click on Report Layout and select Show in Tabular Form
My pivot table now looks like this:
I’ve now gotten my column back for the Customer and Sales Rep fields, but the Customer field is still showing blanks for many of the items.
Again I will go back to the Report Layout options and this time select Repeat All Item Labels
Now my pivot table looks like this:
This is an easier format to follow since it is has all the relevant data on one line and it is easier to read. If I don’t want to see all the customers and the sales rep detail, I can collapse the field by pressing on the – button next to the customer’s name at the top. This will change it to a + and collapse the field. If you want to do this for all customers, right click on any customer and select Expand/Collapse and select Collapse Entire Field.
This will now give me a tidier pivot table:
Viewing Contents That Make Up a Cell
Lastly, I will go over how to see the contents of a cell. As mentioned earlier, one of the benefits of a pivot table are that you can simply double click on a cell to see what makes up its contents.
For instance, if I clicked on the cell in the first row for $30,625, it will open a new sheet with the following in it:
What this tells me is that cell was made up only one of this one entry. If there were multiple entries for that customer, rep, month, and year, then you would see a list of all of those items. In this case it was only made up of one transaction.