Filtering a pivot table

Pivot tables offer a lot of flexibility in how the data is seen. Each column and row can be filtered to show a more specific view.

We will use a new dataset about fictional alumni donors to create a pivot table that answers the question, "What were the donations by school?"

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'll begin by moving to the AlumniDonors worksheet and examining the data.

To move to the AlumniDonors worksheet, at the bottom of the workbook,
Click the AlumniDonors worksheet tab

This dataset contains information about university alumni who have made donations to the university. These are one-time donors. They graduated between 2010 and 2015 and made their donation between 2016 and 2020. There is a lot of information about each donor including their University ID, Campus, School, Major, and Graduation year. In addition, we can see the amount the person donated and when that donation was made. The Acknowledgement column tells us if an acknowledgment was sent. And the Emails column shows us how many emails were sent before the person donated.

We will use this dataset to answer the question, "What were the donations by school?"

To begin creating a new 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 select the current worksheet name, at the bottom of the worksheet,
Double-Click the new worksheet tab
To name the new worksheet, type:
DonationsBySchoolDate Enter key

With the new worksheet created and named, we are ready to add fields to the pivot table.

To add the School field to the Rows area, to the right of the Rows heading,
Click , Click School
To add the Donation Amount to the Values area, to the right of the Values area,
Click , Click Donation Amount

To widen column A, in the column headers,
Double-Click
To widen column B, in the column headers,
Double-Click

This pivot table is small and basic. But it answers the question we asked of our data, "What were the donations by school?"

The pivot table shows the schools in the first column and the donation amounts in the second column.

Before we move on, let's create a custom name to replace SUM of Donation Amount.

To select the value field, in the pivot table,
Click cell B1
To change the name of the value field, type:
Donations Enter key

Adding a filter to a pivot table

Now that the pivot table has been created and formatted, we're ready to add a filter. We'll do this by adding a field to the Filters area of the Pivot table editor.

To begin, let's add the Campus field to the Filter area. This will allow us to see data related to specific campuses.

To add the Campus field to the Filters area, to the right of the Filters heading,
Click, , Click Campus

The Campus field appears in the Filters area of the Pivot table editor.

The Campus filter in the Filters area. The current value is "showing all items"

We can use the Status drop-down menu to select the specific campuses we wish to see.

To see the list of campuses, in the Campus field in the Filters area,
Click
To deselect all of the campuses,
Click Clear
To filter the pivot table to see just IU South Bend (IUSB),
Click IUSB, Click

The pivot table adjusts and now shows the donations for each school from IU South Bend. The grand total of the donations is $8,038.00.

The Status area in the Campus filter now says that it is "Showing 1 item."

Before we move on, let's remove that filter and see the donations for all of the campuses.

To remove the filter, in the Campus field,
Click , Click Select all, Click

The filter is removed and the donations for all of the campuses are showing.

Filtering a pivot table using multiple criteria

Let's use filters to find out how many 2013 graduates made donations within five years of graduation. We will add the Graduation Year and Donation Date fields to the Filter area.

To add the Graduation Year field to the Filters area, to the right of the Filters heading,
Click , Click Graduation Year

To see the list of graduation years, in the Graduation Year field in the Filters area,
Click
To deselect all of the years,
Click Clear
To filter the pivot table to see only the 2013 graduates,
Click 2013, Click

The pivot table adjusts and now shows the donations from alumni who graduated in 2013.

Filtering by condition

We want to see the donations made by 2013 graduates within five years of graduation. We will filter the Donation Date field to see the donations made before January 1, 2019.

To add the Donation Date field to the Filters area, to the right of the Filters heading,
Click , Click Donation Date

To open the Donation Date filter options, in the Filters area,
Click

To begin filtering by condition, in the Donation Date filter options,
Click
To see the Filter by condition options,
Click
To filter by date,
Click Date is before
To begin choosing a date,
Click , Click exact date
To set the date,
Click in the "Value or formula" field, type: 1/1/2019
To filter by the date,
Click

The Filters look like this:

The Campus filter status is showing all items. The Graduation Year filter status is showing 1 items. The Donation Date filter status is Date is before 1/1/2019.

The pivot table has been adjusted to show just the 2013 graduates who made donations before 1/1/2019.

Pivot table showing the total amount of donations by 2013 graduates made before 1/1/2019, organized by school.

Filtering a pivot table

Copying the practice workbook

Copy the practice workbook to your Google Drive.

Step 1. Move to the AlumniDonors worksheet.

Step 2. Create a new pivot table.

Step 3. Name the new worksheet DonationsBySchoolDate.

Step 4. Add the School and Donation Amount fields to the table.

Step 5. Widen the columns to fit the text.

Step 6. Change the name of the Value field to Donations.

Adding a filter to a pivot table

Step 1. Add the Campus field to the Filters area.

Step 2. Filter the pivot table to see only the donations from IU South Bend.

Step 3. Remove the filter.

Filtering a pivot table using multiple criteria

Step 1. Add the Graduation Year field to the Filter area.

Step 2. Filter the pivot table to see 2013 graduates.

Filtering by condition

Step 1. Add the Donation Date field to the Filters area.

Step 2. Open the Donation Date filter options.

Step 3. Filter the Donation Date field by condition.