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)
Use this workbook for the chapter.
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.
Then, we repeat the steps for the UNIQUE function to label the rows.
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.
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.
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.
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.
A Pivot table can function similarly to the previous cross-tabulation and display calculations with a few short steps.
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.
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)
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)
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)
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/basic_pivot_tables.