UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Pivot Tables    
Pivot Tables

Pivot tables are one of the most powerful tools in Excel. They readily group data into buckets and compute sums, averages, counts, percentages based on column totals or row totals and other aggregates as well.

Creating pivot tables is easy. Configuring them to do what you want takes a moderate amount of learning.

Contents

Source Data

To create a pivot table go to the tab that contains the data you wish to analyze. This data is called the source data. Below is a sample of the data used in this discussion. image:PT000.jpg

This data applies to a wireless repair operation:

  • Date is the date of the repair.
  • OEM is the manufacturer of the device.
  • T Code is the repair code assigned by the technician.
  • P Code is the complaint the customer had when returning the phone.
  • Qty is the number of that particular model with the T Code and P Code combination for the indicated date.
  • Type is the type of wireless device. There are 6 different types of device tracked.
  • Model is the model designation for the device. An OEM may have multiple models, but each model belongs to only one OEM.
  • T Group is the grouping of T Codes. For example all T Codes that have to do with power issues with the device belong to Group A.
  • P Group is similar to T Group except that it applies to P Codes.
  • Verified shows the number of models verified for the record combination.

Building the Pivot Table

Place the cursor anywhere in the body of the data and select INSERT -> Pivot Table from the ribbon.

image:PT001.jpg

This action pops up a dialog box. Excel takes its best guess as to what the boundaries of the data are. You can override this selection if you wish. In this case, the data is an Excel table. The advantage of using an Excel table as a data source is that the pivot table knows the boundaries of the table and you can even add columns after building the table and the pivot table will pick them up after a refresh.

image:PT002.jpg

There are two choices as to where to put the pivot table. The default is to place it on a new worksheet. This worksheet will be created on a tab immediately to the left of the source data.

You can also put the pivot table on an already existing sheet. Be cautious about putting a pivot table on an existing sheet. Pivot tables will expand to take over as much space as they need and might destroy other data. Also, pivot tables cannot overlap, so if you have multiple pivot tables on the same page, they might interfere with one another.

Laying out the pivot table is accomplished by drag and drop. There are three major parts to a pivot table:

  • The field list
  • The definition area that has 4 sub-areas
  • Filter
  • Columns
  • Rows
  • Values
  • The actual pivot table itself.

Every one of these areas is optional. For example you may have a pivot table that contains the Model in the Row Area and nothing else. This gives you a unique list of the model names.

image:PT003.jpg

In this example we are building a pivot table with OEM as the filter, Type as the Columns, Date as the Rows and Qty as the values.

The Filter section determines what records are even considered for display on the pivot table. It is sort of like a where clause in SQL. You can choose a single select which means that making one selection excludes all other selections, or you can select multi-select where you can choose multiple values.

You can have as many different fields in the Filters section as you wish. For example, you might want to restrict the analysis to OEM, T Group and P Group.

The Column section determines what gets shown across the top. In this case, there are 6 different types of wireless devices, so there are 6 different column headers.

The Row section determines what gets shown going down the rows. In this case, the dates that are contained in the source data.

The Values section contains the values aggregated by row and column. By default, if the field dragged into this section is numerical, it is the sum of the values. If the field is non-numeric, than it is the count of the values. The following other aggregates can also be shown:

  • Sum
  • Count
  • Average
  • Max
  • Min
  • Product
  • Count Numbers
  • StdDev
  • StdDevp
  • Var
  • Varp

You can also apply a filter to rows and columns. For example to filter for selected dates, click on a date and use the dropdown selector. To filter on a type click on a type and use the dropdown selector.

image:PT004.jpg

You can also have multiple fields in each section.

The following definition shows Models with their T Group and T Code.

image:PT005A.jpg

The following definition shows multiple fields used as columns.

image:PT006A.jpg

And finally an example with multiple fields in the Values Area

image:PT007A.jpg

Collapse / Expand

Here we have a spreadsheet with T Group and T Code

image:PT010.jpg

Clicking on the [-] icon collapses the group.

image:PT011.jpg

Clicking on the [+] icon will expand the group again.

You can also right click on the item and select Expand Entire Field or Collapse Entire Field. These actions will work across the entire spreadsheet.

image:PT012.jpg

Organizing Rows

By default rows “cascade.” That is, when you add a field to a row, it is indented from the previous row but in the same column.

You can make the display appear in separate columns by right clicking on the “main” item and selecting Field Settings. Select “None” for Subtotals.

image:PT013.jpg

On the layout and print tab, click Show item labels in tabular form.

image:PT014.jpg

This will give you a result that looks like:

image:PT015.jpg

For Excel Versions 2010 and later, clicking on Repeat item labels fills in the blanks.

image:PT016.jpg

Grouping

Grouping is a good way to organize dates into weeks, months, quarters, etc.

In the following pivot table we’d like to group the days by week starting with May 3rd (a Sunday).

Right click on the date and select Group.

image:PT017.jpg

Type in the start date (5/3/2015), select Days and type in 7 for the Number of days.

The spreadsheet is now grouped by week. Similar groupings can be done for months, quarters and months / years.

image:PT018.jpg

Formulas

Pivot tables have the ability to do formulas, but take this with a grain of salt. Not everything can be expressed as a pivot table formula, however doing some calculations or even breakouts with the source data may resolve the issue.

Select PivotTable Tools -> Analyze -> Fields, Items, & Sets -> Calculated Field

image:PT019.jpg

Fill in the name you want the metric to be known as in the Name Box.

image:PT020.jpg

In the formula block, type in the formula to compute. Double click on an item on the list and it will be placed in the formula box. In this case, we want to compute a value = Verified / Qty and call it PCT. It will calculate this value for each record.

Once you do this, it becomes a part of the pivot table.

image:PT021.jpg

This would be a good time to discuss formatting.

Formatting Data

Formatting cells in a pivot table is similar to how it’s done in regular excel. However instead of highlighting the range and applying the format, select a field and apply a format.

Right click on one of the percents. Select Value Field Settings and then click on the number format box. The familiar formatting options appear. Format the field to suit preferences.

image:PT022.jpg

While we are at it, let’s change those headers. If you type in Date, Qty and PCT, you’ll get a message that the name has already been taken. So type them in with a leading space.

Expressing Data in Other Ways

So far, we’ve looked at the data as sums or counts or maybe even as some other aggregate. We can also have the pivot table look at the data as a percent of column meaning add up all the items in a column and divide each element by the total.

Percent of row works the same way, except that each cell is divided by the total of the row it is on. Right click a value and select Value Field Settings. Select the Show Values As Tab.

image:PT023.jpg

Select % of Column Total.

This will change the display from the display on the left to the display on the right.

image:PT024.jpg

You can even display the same figure both ways. Put the quantity field twice in the values sub-area and format one of the quantities without calculations (normal) and the other as a percent of column total

image:PT025.jpg

As you can see from the dropdown lists there are other ways to organize the data. The options are: The options are:

  • No Calculation
  • % of Grand Total
  • % of Column Total
  • % of Row Total
  • % of …
  • % of Parent Row
  • % of Parent Column Total
  • % of Parent Total
  • Difference From
  • % Difference From
  • Running Total in
  • % Running Total in
  • Rank Smallest to Largest
  • Rank Largest to Smallest
  • Index

Final Comments

Sometimes you will have to play with the data to get the results you want. For example you might want to know the percentage of quantity by Type. This means that you need to divide Type 1 by the sum of types, Type 2 by the sum of types, etc.

The pivot table can’t handle these calculations, so you will have to break them out in the source data. Make separate helper columns with formulas like:

=IF([@Type]="Type 1",[@Qty],0)

and so on. Now you will have separate field headers to work with.

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 5,337 times.  This page was last modified 20:14, 29 June 2015 by dflak.   Disclaimers