A pivot table constructs a dynamic table from a data in a given range.
All columns must be labeled for a pivot table to work.
To create a pivot table select a cell where you want pivot table to appear. Select Insert tab and click on the PivotTable ribbon.
A window will appear. Select a range of data to be used.
Example:
We want to pivot on the following table:
After selecting a cell where we want the table to appear and clicking PivotTable ribbon the following window appears:
Select a range. Make sure first rows are labeled.
Select Add this Data to the Data Model. This will allow for additional functions to be available within the table. Older versions of MS Office will not have this option.
A window will appear displaying pivot table fields. Rows with headers will appear as clickable items.
Filter: Results within the table can be filtered for specific values in either axis.
Columns: Displays X axis
Rows: Displays Y axis
Values: Calculation for the values displayed.
Organize a pivot table to you needs. You can drag and drop items into appropriate buckets (Filter, Columns, Rows, Values). If we wanted to look up rep's performance by city we would organize the table in the following way:
Values calculation can be changed by a left click on the category within the values window:
Currently, we have a SUM function selected (adds all values together). If we highlighted COUNT function (counting instances of entries) and click OK, the results would show using COUNT function.
We get the following table using our results for SUM function:
Pivot table can be further broken down to itemize items. If we wanted to list Albert's sales in Chicago we would just have to double click on the value $810,000. The following table would appear in a separate tab:
Sorting within Pivot Table:
Sorting is available on the Pivot Table on both rows and columns:
Regular sorting options such as alphabetical sorting and value sorting will be available. In addition, Pivot Table allows you to sort by specific columns through More Sort Options...
The menu will give an option of manually drag and drop items. This is a particularly useful feature in bookkeeping, where certain items must be grouped together. There is an option to sort by specific values such as sum of sales.
In our example, a sum of sales will sort by total sales. However, what if we would want to sort an entire table by Corina's results and also display the rest of the information?
In the More Sort Options... we need to select descending (if we want largest to smallest) sum of sales and then we need to give a custom range where Corina's values reside by selecting More Options...
In the new window we need to select a range where Corina's values reside:
After selecting OK on all open windows we will have a Pivot Table that looks like this:
