Adding Fields to an existing Spreadsheet and viewing them in the Pivot Table

This example will show how to use your own calculated fields in a spreadsheet and then be able to view that information in the pivot table.

 

We will use the Budget with Details spreadsheet as an example and we will add in a % change for the budget and then calculate the new budget.

 

Click on the Budget tab at the bottom of the spreadsheet

 

Uncheck the “All Month to Date” and “Escrow” options to hide those columns for now.

 

Create 2 new columns at the end of the data (Change in Budget and New Budget).  Note, when adding new columns, do  NOT leave blank columns between the existing data and the new columns – the pivot table will not pick up the new columns if you do this.

 

Let’s say that we anticipate a 3% decrease in the budget from State Funds and a 5% increase from Local Funds. The remaining funds will have an increase of 1%.

 

Filter for fund 2.

 

 

 

 

 

Type in .05 in the first row for the Change in Budget (5 % increase for local funds). Copy and paste this for the remaining rows (since we are filtered for fund 2 it will only copy to the fund 2 rows)

 

Filter for fund 1

 

Type in -.03 (3% decrease for state funds).

 

Again copy this down to the remaining rows.

 

Select the remaining funds.

 

The remaining funds will have a 1% increase in budget. Type .01 in the top cell and then copy this down to the remaining cells.

 

Select All of the funds on the Fund filter.

 

Scroll back up to the top row. Enter the formula for the new budget amount (current budget * 1+change in budget)



Copy the formula down to the remaining rows.

 

 

 

 

 

Format the columns

 

Now click on the Budget Pivot Table tab

 

Click on the Refresh Data button. This is necessary for the pivot table to pick up the added fields.

 

Arrange your pivot table so that the Fund, PRC Description and Current Budget/Balance are the only items on the table.

 

To add the newly created fields from our data, right click in the pivot table and select

“Show Field List”

 

You should see the new fields listed at the bottom of the field list.

 

Drag the new field onto the DATA portion of the table













Right click and select Field Settings to format the new fields on the table

 

 

 

 

Now you can re-arrange your pivot table to suit your needs!