CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Basic Pivot Tables

Before we look at Pivot Tables in this chapter, we'll work on creating cross tabulation using function formulas. Cross tabulation summarizes data for analysis. For this chapter, we'll be using data from a survey performed on 1,500 people who bought fishing supplies from stores in three locations. (See Figure 25.1)

BUS115_image_25.1.png
Figure 25.1

Practice Spreadsheet

Use this workbook for the chapter.

Cross Tabulation

If we want to know the number of customers by location that prefers a specific fishing method, a cross-tabulation will present the data for analysis. We'll set up the cross-tabulation by using the UNIQUE function in combination with TRANSPOSE to list the type of fishing method across the columns.

  1. Select an empty cell and type =TRANSPOSE followed by a left parenthesis ( ( ).
  2. Type UNIQUE followed by another left parenthesis ( ( ).
  3. Select the set of data for the Type of Fishing Preferred column.
  4. Type a right parenthesis ( ) ) to close the UNIQUE function.
  5. Type another right parenthesis ( ) ) to close the TRANSPOSE function.
  6. Press Enter to complete the formula (=TRANPOSE(UNIQUE(E2:E1501)) correlating with Figure 25.1).

Then, we repeat the steps for the UNIQUE function to label the rows.

  1. Select the cell down and to the left of the previously completed cell.
  2. Type =UNIQUE followed by a left parenthesis ( ( ).
  3. Select the set of data for the Store column.
  4. Type a right parenthesis ( ) ) to close the function.
  5. Press Enter to complete the formula (=UNIQUE(D2:D1501) correlating with Figure 25.1).

Lastly, we need to populate the cross-tabulation cells with data from the survey information correlating to the column and row headers. The COUNTIFS function will add the data based on criteria that we will specify in the formula.

  1. Select the first cell in the cross-tabulation.
  2. Type =COUNTIFS followed by a left parenthesis ( ( ).
  3. Select the set of data for the Type of Fishing Preferred column and anchor the references with the F4 key, then type a comma ( , ).
  4. Select the cross tabulation's column header and anchor the reference's row only, then type a comma ( , ).
  5. Select the set of data for the Store column and anchor the references, then type a comma ( , ).
  6. Select the cross tabulation's row header and anchor the reference's column only.
  7. Type a right parenthesis ( ) ) to close the function.
  8. Press Enter to complete the formula (shown as =COUNTIFS($E$2:$E$1501,H$3,$D$2:$D$1501,$G4) in Figure 25.2).
  9. Copy the formula to the cross tabulation's remaining cells.
BUS115_image_25.2.png
Figure 25.2
 

This function's formula can be used for cross-tabulation of other criteria from the survey data, such as gender and age. Additionally, we can add the totals for each row and column by using the SUM function to total the row or column's cells.

Insert a Pivot Table

There are two methods to adding a Pivot table. The first method is to select the data set and name it before inserting the table. The second method skips the range naming and Excel will attempt to guess the desired data range to be used when opening a Create PivotTable menu.

  1. Select the data set.
  2. Type a new name for the range in the top-left box in the formula bar. For this example, the range will be renamed "data." (See Figure 25.3)
    BUS115_image_25.3.png
    Figure 25.3
     
  3. Go to the Insert tab.
  4. Select PivotTable to open a Create PivotTable menu.
  5. Select the range manually in the Table/Range field or type the equals sign ( = ) and the range name. (See Figure 25.4)
    BUS115_image_25.4.png
    Figure 25.4
     
  6. Select to place the Pivot table in a New Worksheet or Existing Worksheet.
  7. Press OK.

Upon creating a Pivot table, a side menu will open with a list of the data range's columns and fields for building the table. The data items can be placed into the Filters, Columns, Rows, and Values fields by dragging the desired item from the list.

BUS115_image_25.5.png
Figure 25.5
 

Data Analyses and Filters

A Pivot table can function similarly to the previous cross-tabulation and display calculations with a few short steps.

  1. Select and drag Store from the list on the left to the Rows box in the PivotTable Fields menu.
  2. Select and drag Type of fishing Preferred to the Columns box.
  3. Select and drag Store to the Values box to complete the table. (See Figure 25.6)
BUS115_image_25.6.png
Figure 25.6
 

The data can be removed or rearranged to display other analyses. In another example, the table could show the average age for male and female customers based on the preferred fishing method. (See Figure 25.7)

Note: Unlike the cross-tabulation using formulas, a Pivot table will not automatically update values if the base data is changed. However, the table can be manually updated by selecting the table, going to the PivotTable Analyze tab, pressing the Refresh button, and selecting Refresh All.

  1. Select the Pivot table and go to the PivotTable Analyze tab.
  2. Press the Clear button and select Clear All.
  3. Select and drag Gender to the Rows box in the PivotTable Fields menu.
  4. Select and drag Type of Fishing Preferred to the Columns box.
  5. Select and drag Age to the Values box.
  6. Press the down arrow for the Age value and select Value Field Settings….
  7. Change the Summarize Values By to Average.
  8. Press OK.
  9. Adjust the formatting to Number and reduce decimal places to show one digit.
BUS115_image_25.7.png
Figure 25.7
 

Multiple items can be placed in a single box to categorically list values based on the desired criteria. For example, the table can display the average age of customers that prefer a particular type of fishing method, and also the average for females and males under that fishing method. Additionally, the table may show the standard deviation of age for each. (See Figure 25.8

BUS115_image_25.8.png
Figure 25.8
 

  1. Insert Type of Fishing Preferred into the Rows box.
  2. Insert Gender into the Rows box.
    1. Note: The order may also be rearranged to list gender, then the type of fishing.
  3. Insert Age into the Values box and change the Value Field Settings… to show Average.
  4. Insert Age into the Values box again and change the Value Field Settings… to show StdDev (standard deviation).

Filters can be assigned to select the details to be shown on the Pivot table. Simply drag the list item into the Filters box for the table to display the filter option and select the data to be filtered. (See Figure 25.9)

BUS115_image_25.9.png
Figure 25.9
 

Pivot tables can have an unlimited number of hierarchy levels. For example, if we want to see statistics by gender, and then fishing method and store location, then each category can be added to the Rows box and arranged in the desired order. The Pivot table includes a button next to each level for collapsing and expanding the data under the specific level. (See Figure 25.10)

BUS115_image_25.10.png
Figure 25.10
 

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!