Drag the fields to the appropriate boxes can add more

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: box). 5) Once table is created, check a few values to make sure you have entered the data properly. Column Label - Field to display in columns (generates labels across top row) Row Label - Field to display in rows (generates labels down the left hand column) Values - Column(s) of data that are summarized Report Filter - Field to filter entire pivot table by Example 1 - Total NOx for each region and each year Column Label: Year Row Label: Region Values: Sum of NOx Example 2 - Total NOx for each SIC code and each year Column Label: Year Row Label: SIC code Values: Sum of NOx Sorting and Filtering Once table is created, you can sort rows and filter rows and columns. Use the filter drop down menus. 1) Filter for only 2001 and 2002 2) Sort rows according to total emissions Value Display Options Can calculate many different statistics: sums, averages, counts, min/max, variance, etc. Use drop down menu on Field in Values box --> Value Field Settings Options on "Summarize Values By" tab 1) Calculate average NOx rather than total NOx 2) Calculate maximum NOx released by a facility for each SIC in each year Can also change how the numbers are displayed. Use drop down menu on Field in Values box --> Value Field Settings Options on "Show Values As" tab 1) Display % contribution of each SIC code to yearly NOx emissions. Lots of other display options, Explore! Report Filters Adds a filter to data used to calculate the values of the pivot table Example 3 - Total Nox for each region and each year, by plants with SIC code 4000 or 5000 Column Label: Year Row Label: Region Values: Sum of NOx Report Filter: SIC Code Simple Data Set Plant ID Region 11 North 11 North 11 North 12 North 12 North 12 North 13 North 13 North 13 North 21 South 21 South 21 South 22 South 22 South 22 South 23 South 23 South 23 South NOx (Tons) 10.5 10 9 50 48 47 45 45 44 15 16 17 18 17 14 14 13 13 Year 2001 2002 2003 2001 2002 2003 2001 2002 2003 2001 2002 2003 2001 2002 2003 2001 2002 2003 SIC code 5000 5000 5000 4000 4000 4000 4000 4000 4000 3000 3000 3000 3000 3000 3000 5000 5000 5000 Example 1 - Using Pivot Tables Answer each of the Challenge questions using pivot tables. Put your answers on "Challenge" tab. Example 2 -...
View Full Document

Ask a homework question - tutors are online