Adding Fields to an existing Spreadsheet /Viewing them in Pivot Table
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!
|
|
Related Articles
Filtering on pivot table calculated fields
Filtering on pivot table calculated fields: Click on the row beside the last row: Click on Filter The following should appear Filtering is available for all fields that are calculated in the pivot table.Pivot Table Cheat Sheet (Great Tips)
Use this cheat sheet for quick how-to information on pivot tables. Information includes dragging and dropping fields on and off the report, adding additional fields, creating new calculated fields, sorting, filtering, adding page breaks and more!To save pivot table data to a new spreadsheets:
To save pivot table data to a new spreadsheets: Right click on the tab for the pivot table. Select Move or Copy Select New Book Check “Create a Copy" Click on OK The pivot table data is now in a new sheet – you can save it as any name. Note – this ...Saving pivot table data to a new spreadsheet without refresh option
Pivot Table Quick Reference for Office 2007
Pivot Table Quick Reference for Office 2007 Click Attachments to see the document