Preview
Pivot Table Structure
Determining Pivot Table Need
Generally, we would create a pivot table in any of the following situations:
- There is a large amount of transactional data that’s becoming more difficult to analyze and summarize in a meaningful way.
- You need to find relationships and groupings within your data.
- You need to find a list of unique values for one field in your data.
- You need to find data trends using various time periods.
- You anticipate frequent requests for changes to your data analysis.
- You need to create subtotals that frequently include new additions.
- You need to organize your data into a format that’s easy to chart.
- You need to create a dashboard and need a source for it
Data Table Used for Creating a Pivot Table
Pivot tables are created from regular Excel tables. If we used the FIGURE 1 table below for creating our pivot table, the resulting pivot table field list would be what we see in the graphic below in Figure 3. Notice that each field in the data table has its corresponding field listed in the fields list graphic below. Then we choose which fields will go into the pivot table by checking the box next to each field.
The above data table (FIGURE 1) illustrates a table that has its data structured properly for creating a pivot table. There are headings for each column. The dates are organized down the page instead of across the columns.
This is an example of a tabular layout. Tabular layouts are characteristic of data that you would find in the layouts of databases. These layouts are designed to store and maintain large amounts of data in a well-structured, scalable format.
Bad Data Table Candidate for a Pivot Table
The above table (FIGURE 2) illustrates a table that has its data structured improperly for a pivot table. Here are the following problems with it:
- The row headings consume more than one row; cells A1-A3, A5-A7, A9-A11 are merged cells.
- The row headings are angled.
- The problem with the above table is that there are no column headings.
- There is a problem with a blank column (column K). You cannot have any empty columns in the table if it is to be used as a pivot table.
- Another problem is that there is a blank row separating the headings. You cannot have any empty rows in the table if it is to be used as a pivot table.
- There is a problem with cell I7; it is blank. If only one cell is blank (or has text) in a column, Excel will perform a COUNT calculation on that column.
Pivot Table Structure
Whether you’re designing your own pivot table or selecting from the Recommended Pivot Tables optional choice, you will see the below graphic (FIGURE 3) appear on the far right of your Excel screen. The fields in the top portion of the Fields List will be based on what Excel finds in the data table used, which is based on the range you specify when you create the pivot table.
The FIGURE 3 graphic below shows the pivot table Fields List. To get the fields that are listed in the top section copied to one of the quadrant areas in the bottom section, one must check the box of the desired field or click on the field with a mouse and drag the field down to the quadrant the field is intended to go into. For example, if we wanted to place the Category field in the Columns quadrant, we would take our mouse and click on the Category field listed at the top section and drag it down to anywhere in the Columns quadrant and release the mouse button. As FIGURE 3 illustrates, the Category field is shown in the Columns quadrant.
A pivot table is comprised of four areas that we will call quadrants. The data you place in those quadrants defines both the usefulness and the appearance of the pivot table. Let’s look at each of these four quadrants, as shown in FIGURE 3 above.
Values Area Quadrant
- We can view the Values symbol as shown below in FIGURE 4 in 2 respects. First it should be remembered as one of the FIeld List quadrants. Secondly, you must also view it as a label placeholder for the fields in the VALUES quadrant.
- The VALUES quadrant is the area that calculates. As shown above, the VALUES area contains the Sales and Sales Qty fields. This area is required to have at least one field and one calculation on that field in it.
- The data fields you drop here are those you want to measure or calculate. The values area could possibly include Sum of Sales, Sum of Sales Qty, Sum of Price, or Sum of Cost. In our example, we have Sum of Sales and Sum of Sales Qty as our calculated values.
In the COLUMNS quadrant, is the VALUES quadrant labeling symbol shown on the right in FIGURE 4.
- In the Columns quadrant in FIGURE 3, the Values field is above the Category field, which means the calculated fields, Sum of Sales and Sum of Sales Qty, will have their labels positioned on the pivot table above the Categories Desktops, Laptops, and Tablets.
- The Values field does not represent a field from the data table like the others in the various quadrants. Think of this as a label placeholder, not a field. It represents where the labels for the calculated fields are positioned on the pivot table.
Rows Area Quadrant
The ROWS quadrant is comprised of the headings that go down the left side of the pivot table. Dropping a field into the rows area displays the unique values from that field down the rows of the left side of the pivot table. The rows area typically has at least one field, although it is possible to have no fields. An example of a no-fields situation would be where you would need to produce a one-line report. The example shown above is using the Market, Month, Year fields in this quadrant. The types of data fields you would routinely drop here include those you want to group and categorize; i.e. Products, Name, and Locations.
Columns Area Quadrant
The COLUMNS quadrant is comprised of the headings that stretch across the top of the columns in the pivot table. The columns area is composed of headings that stretch across the top of columns in the pivot table. The example shown above in FIGURE 3 is using the Category field in this quadrant.
Dropping fields into the columns area would display your items in column-oriented perspective. The columns area is ideal to show trending over time. The types of data fields you would drop here include those you want to trend or show side by side, i.e. Category, Month, Year
Filters Area Quadrant
The FILTERS quadrant is the gray area which is an optional set of one or more drop-downs at the top of the pivot table. Dropping fields into the filters area would enable you to isolate and filter the data items; i.e. Markets (Boston, New York); Months (Jan, Feb, Mar); or Year (2014, 2015).
Major Points to Good Data Table Source Design
- The first row of your data source is made up of field labels or headings that describe the information in each column.
- Each column in your data source represents a unique category of data.
- Each row in your data source represents individual items in each column.
- None of the column names in your data source double as data items that will be used as filters or query criteria (i.e. names of months, dates, years, names of locations, or names of employees).
- There are no blank columns in the data table.
- There are no blank rows in the data table.
- There are no blank cells in the data table.
END OF LESSON
Leave a Reply