What is a Pivot Table?
A pivot table is a table that stores the summary of an underlying data set in a condensed form. A pivot table consists of rows, columns, page and data fields each of which can be moved around interactively and help the user isolate, expand, group and sum the data in real time.
The advantages of Pivot Tables are:
1. Pivot tables are normally used to show the condensed form of a large underlying set and therefore occupy much lesser screen space.
2. Since pivot tables summarize the data, they offer the reader the ability to get a grasp of the data for more quickly than a raw table..
Pivots tables in Excel enjoy the benefit of other complimentary features:
1. Excel offers many pre-defined functions that help the user extract data from pivot tables far more easily than would be possible with the raw table with multiple columns and rows
2. Pivot tables can be easily modified using VBA
3. The data processing power of pivot tables is further enhanced by the use for techniques like conditional formatting, grouping and filtering
How to create a Pivot Table in Excel?
Before we begin to use Pivot tables, let us first understand the various parts of a pivot table. A pivot table is based on a collection of data organized as columns (and rows). Each of these columns needs to have a label which acts as the identifier for the entire column. Each of these identifies can be moved around and placed in four “slots”. There are four of these slots in a pivot table:
1. Data or Value Fields – This field, located in the center of the pivot table, is used for aggregating data. Whichever field identifier is placed here is this slot will be aggregated. So for example if you place “Sales” in this field, the individual Sales figures will be clubbed and then presented to the user. The criteria for clubbing are usually specified in the remaining three slots – row, column and Page Fields.
2. Row Fields – When you place a field here, the information in the Data Field (point 1 above) will be aggregated using this field and then shown row wise, one row for each value of the row field. Let’s take the example of Sales figures over a 12 month period. Having placed “Sales” in the Data Field, we can drag and drop the “Month” field in the row slot. The outcome would be that the row field will have as many rows as there are months and against each month, the data field will the show the aggregate for the sales in that month across all categories and managers who made them. If we were to now drag and drop another field, say “Sales Manager” in the row slot, the number of row would increase and now start showing all the unique combinations of month followed by the Sales Manager. Against each of these rows, there would be the aggregate of Sales – but this time, it would be aggregated for a particular Sales Manager and for a particular month. So, as you just saw, the higher the number of fields that we introduce in the row slot, the higher the granularity that we will have for our data. This is an important feature of the pivot table – you can drill-down the each individual data point and yet with a few clicks aggregate thousands of individual data points into a few rows.
3. Column Fields – Similar to row fields but differ in their orientation. While inserting new row fields increases the number of rows that we have in the pivot table, inserting column fields add new columns to it. Any field that is placed in the row slot can be moved to the column slot simply by dragging and dropping.
4. Page or Report Fields – The page field (Page Field in Excel 2003 and Report Field in Excel 2007) differ from the row and the column fields in the sense that they tend to govern both (and therefore the entire pivot table). Placing a field in the page (or Report) slot will not have an immediate impact on the pivot table. However anytime we select a particular value from the page field drop-down list, the fields in the row and columns will automatically adjust and narrow down to show only those groupings that pertain to the selection in the page field. Bear in mind that the same can be achieved by placing them in the row or column fields and then choosing the appropriate value from the drop-down but that would tend to add additional number of rows or columns. So in a way page fields help avoid clutter and provide a better structure to the pivot table.
There are no hard and fast rules governing what fields should be placed in the row slot and what in the column slot. Often times, the decision is governed by how the user wants to view the information, what makes the information more “graspable”, the amount of space available and the whether a particular placements helps you do further analysis (Ex. Sorting). Having said that, you will find that typically, the date fields (days, months, years etc) are better off being placed in the column field as they help the reader get a sense of variation a bit better. Anything field that is of an immediate interest to the user can be placed in the row field. Finally the fields that may not change too often but help grouping the row and column fields may be placed in the page fields. So in our case, if we want to look at the performance of Sales Managers over time, we would place the Month field in the column slot, the Sales Manager field in the row slot and Divisional and Regional Manager fields in the page slot (and of course with Sales being the Data Field – right in the center). However as I said, the entire point of having a pivot table is to have the freedom to move things around.
To make a pivot table in Excel 2007 and later versions:
To create a pivot table in Excel 2007 and later versions, we first begin by selecting the range which we want to use as the pivot’s base data. Now on the “Insert” menu tab, select “Pivot Table”
In the Pivot table field list that appears, drag and drop the required fields to the various slots. As mentioned earlier, there are four available slots – row, column, report and value.
To make a pivot table in Excel 2003 and prior versions:
To create a pivot table in Excel 2003 and prior versions, we first begin by selecting the range which we want to use as the pivot’s base data. Once the base data range has been selected (see excel shortcuts to make the task easier), you can now use the “Data” -> “Pivot Table and Pivot Chart Report ” option form the menu. Follow the steps 1 to 3 by clicking the OK button. On step 3, click the “Layout” button. In the wizard window which opens up, drag and drop the fields in to respective slots as desired.
Working with Pivot Tables in Excel
Once we’ve placed our data fields in the proper placeholders, you can:
1. Move the fields of the pivot table
By their very nature, pivot tables are meant to slice and dice data – which means that you can drag, move and place the various fields at whatever place you like. This change in orientation allows the user to gather useful information with a few clicks which otherwise would have taken a few hundred formulas.
2. Drill down using the various drop-downs
Whenever you select a drop-down, the pivot table changes itself to reflect data pertaining to only that selection. In our case, if we were to select “Steve Williams” from the list of Divisional Managers in the page drop-down, the entire pivot table would shrink to reflect only those Sales Managers that work under him.
3. Turn on and turn off the totals and subtotals for various fields and the entire pivot table
Pivot tables by default will show the totals for most of the fields. Although it may be useful in a number of cases, in other it may help the reader make better sense of the data by simply turning off the totals. Since each row in the pivot table itself is a summary of the underlying data, under most circumstances, we would not lose out much by doing this step. To turn of totals for each field, select the field and simply right-click -> “Field Settings” and choose “None” under Subtotals option. You will have to repeat this step for each individual field. This can be used with both the row and the column fields. You can also turn off (or on) the subtotals for the entire table by right clicking on the table and selecting “Table Options” (in Excel 2003) or “PivotTable Options” (in Excel 2007) and then unchecking the two check boxes labelled Grand total for rows and columns.
4. Refresh the pivot table when the underlying data changes
Another interesting feature of a pivot table is its ability to update itself with fresh data. Say you created a pivot with some underlying raw data. Next month, fresh data points arrived and were pasted over the earlier data set. Do you need to create the whole pivot table again? No. All you need to do is to right click the pivot table and press “Refresh Data” and he pivot table will be refreshed with the new data set. This feature makes the pivot tables completely re-usable.
5. Pivot Chart using Pivot Table
Another useful feature of the pivot tables is the pivot chart. Think of pivot charts as pivot tales in picture! The shortest way to create a pivot chart is the select any cell in the pivot table area and press F11. Hey presto – we have our pivot chart ready. However since pivot tables will typically have a lot of data, a pivot chart based on such a table will tend to show a lot of data points – which in many cases limits their usefulness. However pivot charts, like pivot tables, offer the user the ability move the various fields around so that you are able to get a better grasp of the data. The fields in the pivot chart can be moved around in the same way as one would do in a pivot table.
6. Formatting a Pivot Table – Auto and Manual Format
Now that we are really getting to know the pivot tables a little better, let’s delve a bit deeper. Another interesting tool that can be used with a pivot table is to format the pivot table. Although a pivot table on their own do a good job of summarizing data, their usefulness can be further enhanced when proper borders and coloring are applied to them. The border and additional formatting help present provide a proper structure to the data in the pivot table and helps improve readability. But before we begin, right click on the pivot table and select “Table Options”. Uncheck “Auto Format Table”. If this options is left on, every time you refresh the table after adding new data or after move the fields around, and changes you made to the like adding colors, fills and special borders, will be lost. By turning this option off, we prevent the table from losing any of our customizations. So now if anyone comes to you and says, hey my pivot keeps on loosing its formatting after every refresh, you know what to tell them.
You can use the auto format feature in Excel 2003 and earlier versions by selecting the pivot table and clicking “Format” -> “Auto Format” in the menu bar and then selecting the format style. In Excel 2007, you can click on the “Design” tab and selecting the PivotTable Style that you need.
7. Pivot Table Display – Show items with no data
By default, the pivot table will not display items that do not have any data. So for example, if we had 12 months of data for all Sales Mangers but had the July data missing for a manager named ‘Cristobal’, when that particular manager is selected from the Sales Managers drop-down, the entire column for the month of July would be missing. In some cases, this could be undesirable and hence we may want to show the column even though it may not have any data in it. To show items with no data, select the field and simply right-click -> “Field Settings” and check “Show Items with no data” option.
8. Pivot Table Sorting – Ascending/Descending Criteria, Top X values
The pivot table can serve the very useful role as a tool for sorting lists.
Sorting using Pivot Table in Excel 2003 and ealier
In order to sort a row or a column field, simply right click it and choose the “Field Settings”. Under the “Advanced” tab, you can turn on the Ascending or Descending option. You can also specify which field to use as the criteria (the field itself or any of the aggregated fields). Once you’re done click ok. Your pivot table is now fully sorted and you can easily pick say, the top 10 or bottom N items from the list.
Sorting using Pivot Table in Excel 2007 and later
By default, each row and column field in Excel 2007 provides a drop down list which can be used to sort a pivot table. Additional sort options can be availed by clicking the “More sort options” option.
9. Preventing display of Errors – Pivot Table options and Conditional Formatting
If your underlying raw data is spic and span, you would not encounter any errors in how the pivot table displays errors. However there are cases (especially when you being to experiment with some of the advanced techniques), where the pivot table will being to show errors. To hide these errors, you can either correct the underlying data set (preferable) or hide them. If you decide to do the later – you have two options. The first one is the easier one – right click on the pivot and select “Table Options”. Turn on the “For error values show” message box and leave it blank and type in something more informative such as “value not found”. This will help you hide errors. The second approach it to use conditional formatting.
In the rule box, you can enter the rules as =IF(ISERROR(A1),1,0) where cell A1 is the cell you would like to format. In the color, choose white. By doing this what we ensure that whenever a cell contains an error value, it is displayed in white. If the background of the pivot table is also white, this would tend to hide the errors. Excel 2007 provides far greater control over these formatting rules than earlier versions of Excel. However keep in mind that conditional imposes its own overheads on an Excel spreadsheet and can slow down performance.
Excel 2007 and Later : Download a sample worksheet with a example of Pivot table in Excel 2007 and Later versions here or click on the button below:
Excel 2003 and Earlier : Download a sample worksheet with a example of Pivot table in Excel 2003 and Earlier versions here or click on the button below: