We have seen how to use the Value Field Settings dialog box 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 Values As tool 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.
Using the Show Values As tool
The Show Values As tool 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
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 Fields list,
Press & drag the Donation Amount field to the Values area under Total Donations
To make a cell in column E the active cell,
Click in cell E4
To see the Show Values As contextual menu,
Right-Click cell E4, Point Show Values As
The Show Values As contextual menu opens:
We want these values to be shown as a percentage of the grand total.
To show the values as % of Grand Total, in the contextual menu,
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 D9, $86,377. For example, $17,102 (cell D4) 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 E3, 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 G3.
Copying a pivot table
Pivot tables can be copied and pasted just like other data in Excel. 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 G3. 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 A3:E9
To copy the pivot table,
Right-Click the selected cells, Click Copy
To paste the copied pivot table,
In Microsoft Excel for Windows, Right-Click cell G3, Click Paste (P) In Microsoft Excel for macOS,Right-Click cell G3, Click Paste
To remove the marquee (marching ants) from the original pivot table, on the keyboard, press:
Escape key
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 G to K
To place the cursor to widen the columns,
Point to the border between two columns.
Your cursor should look like the following example:
To widen columns G to K, in the header row,
Double-Click between two column headers
To deselect the columns,
Click in a blank area of the worksheet
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 one 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 PivotTable Fields list and can be placed in the Values area of the pivot table.
Before we add this field, let's remove the Percent of Grand Total field from the Values area.
To see the PivotTable Fields list, if necessary,
Click in the left pivot table
To remove the Percent of Grand Total field, in the Values area,
Press & drag the Percent of Grand Total field out of the Values area
The pivot table again looks like this:
Let's add the calculated field now.
To open the Insert Calculated Field dialog box, on the PivotTable Analyze tab of the ribbon,
Click , Click Calculated Field...
The Insert Calculated Field dialog box opens:
The dialog box has an area for the field name. This name should be unique and descriptive. There is also an area to enter the formula. And a list of the pivot table fields.
To name the calculated field, in the Name area,
Press & drag the current name, press: Delete key, type: Matched Donation
To move to the Formula area, on the keyboard, press:
Tab key
To remove the current formula, on the keyboard, press:
Delete key
To begin entering the formula, in the Formula area, type:
= Spacebar
To add the Donation Amount field to the formula, in the Fields area:
Click Donation Amount, Click
To multiply the Donation Amount field by 23%, in the Formula area, after the Donation Amount field, type:
* .23
To add the calculated field to the Field List, in the Insert Calculated Field dialog box,
Click
To close the dialog box,
Click
The calculated field, Matched Donation, appears at the bottom of the PivotTable Fields list and may already be in the Values area of the pivot table.
While standard fields can be added to any of the four areas of the pivot table, calculated fields can only be added to the Values area.
To add the Matched Donation field to the Values area, in the PivotTable Fields list,
Click the Matched Donation checkbox
The matched donation amounts appear in column K. By default, Excel labels calculated fields as "Sum of..." even if a calculation besides sum is being performed. We can change this by changing the label in cell K3.
While it would make sense to name this column Matched Donation, Excel won't allow us to name a column with the same name as a field. Therefore we will have to make a slight variation on the name.
To change the name of Sum of Matched Donation,
Click in cell K3, type: Matched Donations Enter key
Paste (P)
, 
