A Pivot table is a tool used in spreadsheets to make sense of large amounts of data. The table doesn’t change the spreadsheet or the data. It allows us to summarize the information so that it is easier for us to understand. Pivot tables can help answer questions with and about the data.
I have a spreadsheet that has responses from teachers. These responses came from a survey where we asked teachers questions about their experience and use of technology. I have a spreadsheet with eighteen columns of information and hundreds of responses from teachers.
The Pivot table tool is in the “Data” menu.
Click “Data” in the menu and select “Pivot table”.
A new sheet will be created in the workbook. The sheet will be named Pivot Table. Each new Pivot Table is given a number. The first Pivot Table doesn’t have a number but as more tables are created they are identified by a number. This name can be changed to reflect the information collected in the Pivot Table.
The Pivot Table has columns and rows. The Pivot Table is created with a header row and a header column. The header row and column are identified by the dark black line that separates it from the other columns and rows that will eventually display data.
A “Report Editor” panel opens on the right side of the Pivot Table sheet. This editor is used to select the data that will be displayed, organized, and totaled in the table. The Report Editor has four selectors that are used to import data from the spreadsheet onto the Pivot Table. These selectors include rows, columns, values, and filters.
Click the “Add field” link for Rows.
A list of the spreadsheet column headings will be displayed. This spreadsheet isn’t very complex so it only has column headings. Most spreadsheets are of this type. I’ll select the first field heading where teachers selected their grade level.
Headings in a spreadsheet can be referred to as fields. This is because fields are used in databases and databases are created with tables.
The field includes several options for displaying data on the table.
The Order option is used to sort the values.
The “Show totals” option will display the “Grand total” column or row for the values.
The headings are displayed in rows along the left edge of the table. Data from these headings are not included in the table. We need to select the values to be associated with the headings.
Click “Add field” for Values in the Report Editor.
I want to see how many teachers from each grade level responded to the survey. Clicking the heading for the grade level will bring in a count of each response.
The values returned are shown as zeros.
Looking at the values field we see that the values are summed. This means they are added to produce a total. Why aren’t the values totaled? The values aren’t totaled because they are not in number format. The responses are text responses. The Sum function will only total values that are represented by numbers.
The number of text responses can be counted if we use a different function. Click the “Summarize by” selector and choose the “COUNTA” function.
The totals for each grade level will now represent the number of responses collected. The COUNTA function counts any kind of information. As long as there is something in a cell it will be counted.
The grand total at the bottom of the table adds all the responses from all the grade levels.
Pivot tables can help answer questions with the data. We know how many teachers responded to the survey from each grade level. We want to know how many of those teachers have fast internet access at home.
Click “Add field” in the Report Editor for columns.
We’ll select the data that asks them about their home Internet access.
The data for home Internet access is listed in columns. The responses for each grade level and access type are places in the table.
The responses shown in our query are shown for all campuses in the district and for all grade levels. This information can be filtered so we can look at the responses for individual campuses and grade levels.
Click “Add field” in the filter section of the Report Editor.
We will select the Campus data in the filter.
The Campus filter is currently displaying all campuses. Click the disclosure arrow to see a list of campuses.
Click the clear link to de-select all the campuses.
Select one campus and click the OK button.
The chart will update to display data from that campus only.
We can add another field to apply another filter.
We will select the grade level this time.
Clear the filter and select one grade level. Click OK to display the results.
The table will collapse to show the filtered results.
Pivot tables are very useful for making sense of large data and for answering questions with the data. We can create several pivot tables from one sheet of data.