We will continue working with the AlumniDonors dataset. In this exercise, we will use the sorting tools in Google Sheets to put the school and major with the highest donations at the top of the pivot table.
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
- To begin copying the practice workbook in a new browser window,
Right-Click Copy practice workbook, Click Open link in new window
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
- To copy the workbook file, in the browser window,
Click 
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
We're going to use this dataset about university donors to create a pivot table that tells us the school and major with the highest donations.
- To move to the AlumniDonors worksheet, if necessary, at the bottom of the workbook,
Click the AlumniDonors tab
- To begin creating the pivot table, on the menu bar,
Click Insert, Click Pivot table
- To accept the range and placement and to place the pivot table in a new worksheet, in the Create pivot table dialog box,
Click
- To name the pivot table worksheet,
Double-Click the new worksheet tab, type: DonationsBySchool Enter key
With the new worksheet created and named, we're ready to add fields to the pivot table. We'll add the School, Major, and Donation Amount fields.
- To add the School field to the Rows area,
Press & Drag the School field to the Rows area
- To add the Major field to the Rows area,
Press & Drag the Major field to the Rows area, underneath the School field
- To add the Donation Amount field to the Values area,
Press & Drag the Donation Amount field to the Values area
This pivot table gives us a lot of information. We can see how much the donations were for each school and each major within the school. Before we sort this pivot table to see the largest amounts, let's change the name of the Sum of Donation Amount column and widen the columns.
- To change the name of the SUM of Donation Amount heading,
Click in cell C1, type: Donations Enter key
- To select the columns, in the column header,
Press & Drag column A to column C
- To widen the columns, in the column headers,
Double-Click
Each column is now as wide as it needs to be to fit the contents.
With the formatting complete, let's sort the pivot table.
Sorting the pivot table by multiple criteria
Currently, the pivot table is in alphabetical order by the names of the schools. We want the school with the most donations to be at the top. Within each school, we want the major with the most donations at the top. To accomplish this, we will sort the donations on two levels. First, we will sort by school. Then we will sort by major within each school.
In Google Sheets, the sorting options are in the pivot table editor.
The School field is currently in ascending order and sorted by School. The total is shown at the bottom of the pivot table and the row labels are not repeated.
We can use these options to sort the pivot table in descending order by Donation.
- To change the sort order to Descending, in the School field in the pivot table editor,
Click
, Click Descending
The pivot table adjusts. The school field is now in descending order with the School of Social Work at the top.
Because we want the school with the most donations at the top of the list, we must sort the pivot table in descending order by donations.
- To sort the pivot table by Donations, in the School field in the pivot table editor,
Click
, Click Donations
The pivot table adjusts and the School of Business with donations of $11,363 is at the top.
Collapsing and expanding pivot table fields
It may be difficult to get an accurate idea of the total donations for each school. Let's collapse the School field to show just the totals.
- To collapse the field, in the pivot table,
Right-Click cell A2, Click Collapse all in School
The pivot table now shows just the schools and donation amounts. The majors are still part of the pivot table. They just aren't visible.
We can see that the School of Business had the most donations. But what if we want to see the donations each major within the School of Business contributed? We can expand just that entry in the pivot table.
- To expand the School of Business entry,
Click
The entry expands and we can now see all of the majors within that school.
If we want to see all of the majors, it isn't necessary to individually expand the entries. There is a tool that will expand the entire School field.
- To expand the field, in the pivot table,
Right-Click cell A11, Click Expand all in School
Currently, the majors within each school are listed in alphabetical order. Because this pivot table is focused on the donation amounts, it would be helpful if the majors were sorted by donation amount.
Let's add a second-level sort to the pivot table.
Adding a second-level sort to the pivot table
We will adjust the Major field to be sorted in descending order by Donations. When sorting a pivot table, the sort will stay within the higher field. Therefore, the sort of the Major field will stay within each school.
- To sort the Major field in order from largest to smallest, in the Major field,
Click
, Click Descending - To sort the Major field by Donations, in the Major field,
Click
, Click Donations
The pivot table is now sorted by School and Major with the donation amount being the criteria for the sort. The school with the most donations, The School of Business, is at the top. Within The School of Business, the Entrepreneurship major had the most donations.
Viewing hidden source data
When working with pivot tables, it may be helpful, or even necessary, to see the original source data hidden behind the pivot table.
We can see that within the School of Music, Ballet alumni donated $1,655. What if we want to know more about these alumni? Who are they? When did they graduate? How much did each person donate?
Google Sheets makes it easy to get this information.
- To see the source data,
Double-Click cell C40
A new worksheet opens. It contains the source data for the six Ballet alumni who made donations.
Before we move on, let's name this worksheet and save the workbook.
- To rename the worksheet,
Double-Click the current worksheet name, type: BalletAlumniDonors Enter key
- To return to the AlumniDonors worksheet, at the bottom of the workbook,
Click the AlumniDonors tab