We have seen how to use the value field setting to modify a value field to perform different functions including COUNT, AVERAGE, and SUM.
There are additional ways we can work with calculations in a pivot table. We can also use the Show As options to show the value in different ways such as percent of the row or column, difference from, or a running total. In addition, we can also create a unique value field that will perform a calculation that isn't included in the Value Field Settings dialog box.
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,
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.
Using the Show As options
The Show As options can quickly perform built-in calculations in a pivot table. We'll work in the DonationsByDate worksheet and use this tool to see the percentage of the total donations for each year.
To move to the DonationsByDate worksheet, in the workbook,
Click the DonationsByDate worksheet
NOTE: It may be necessary to reopen the Pivot table editor. To do this, Click a cell in the pivot table then Click .
We'll add the Donation Amount field to the Values area. We'll then use the Show Values As tool to see the percentage of the Grand Total.
To add the Donation Amount field to the Values area, in the PivotTable Field List,
Press & drag the Donation Amount field to the Values area under Donation Amount (Total Donations)
The Show As options are in the Donation Amount field in the Pivot table editor.
To open the Show As options contextual menu, in the Donation Amount field,
Click
The Show As options appear:
To show the donation amount as a percent of the grand total,
Click % of grand total
The Sum of Donation Amount column now shows percentages.
These percentages are based on the Grand Total of donations in cell F7, $86,377. For example, $17,102 (cell F2) is 19.81% of $86,337.
Before we move on, let's change the name of the percentage column.
To change the name of the Sum of Donation Amount column,
Click in cell G2, type: Percent of Grand Total Enter key
We're going to see how to add a calculated field. Before we do that, let's copy and paste the current pivot table in cell J1.
Copying a pivot table
Pivot tables can be copied and pasted just like other data in Google Sheets. We can even have two instances of the same pivot table in one worksheet.
We'll copy the current pivot table. Then we'll paste the copy into cell J1. It is necessary to paste the copied pivot table to the side of the original. If we were to paste it underneath the current pivot table, it would block any expansion of the fields in that pivot table.
To select the pivot table,
Press & drag A1:G7
To copy the pivot table,
Right-Click the selected cells, Click Copy
To paste the copied pivot table,
Right-Click cell J1, Click Paste (P)
To deselect the copied pivot table,
Click a blank area of the worksheet
To select the columns of the copied pivot table, if necessary, in the header row,
Press & drag J to P
To place the cursor to widen the columns,
Point to the border between two column headers
To widen columns J to P, in the header row,
Double-Click between two column headers
To deselect the columns,
Click in a cell in the pivot table
By selecting all of the columns and double-clicking between two of them, we were able to resize each column to fit the information within that column.
The identical pivot tables are now next to each other in the worksheet. Although they look the same, they are two separate pivot tables and can be manipulated as such.
We're going to remove the Percent of Grand Total column from the new pivot table and then add a calculated field.
Entering a calculated field
A donor has matched 23% of the donation amounts for 2016-2020. We can use a calculated field to see what the matched donation amount is for those years. A calculated field is a formula that refers to a field, or fields, in the pivot table. Once created, the calculated field will appear in the pivot table.
Before we add this field, let's remove the Percent of Grand Total field from the Values area.
To see the Pivot table editor, if necessary,
Click in the new pivot table
To remove the Percent of Grand Total field, in the Values area,
Click
The pivot table again looks like this:
Let's add the calculated field now.
To add a Calculated Field to the Values area, to the right of the Values heading,
Click , Click Calculated Field
Calculated Field 1 appears in the Values area:
We can add a formula to this field. We want to calculate the amount of the 23% matched donation. To do this, we will multiply the Total Donations field by .23. We will use the text 'Amount of Donations' in the formula. Therefore, it will be necessary to put that text in single quotes (').
Remove the current text in the Formula area.
To add the formula to Calculated Field 1, in the Formula area, type:
=('Donation Amount')*.23 Enter key
Calculated Field 1 appears in column P of the pivot table. The matched donation amounts may not be formatted as currency. Because much of our pivot table is currently hidden, we will need to select the entire column P before we format the amounts as currency. If we don't do this, when the pivot table is expanded, not all of the values will be shown as dollar amounts.
To select column P, in the header row,
Click column P
To format column P as currency, in the toolbar,
Click
To deselect column P,
Click any cell in the pivot table
Google has named the calculated field "Calculated Field 1". We can change this to something more meaningful.
To change the name of Calculated Field 1,
Click in cell P1, type: Matched Donations Enter key
.
, 
