The file tax data contains information from federal tax returns filed in 2007 for all counties in the United States (3,142 counties in total). Create a PivotTable in Excel to answer the questions below. The PivotTable should have State Abbreviation as Row Labels. The Values in the PivotTable should be the sum of adjusted gross income for each state.
- 1. Find the State of “IN”, and copy the data to a new sheet. Color odd rows as red, and color even columns as orange. Which county had the smallest sum of adjusted gross income in the state of Indiana? Which county had the largest Total Number of Tax Returns in the state of Indiana?
- 2. Create the PivotTable in a new sheet, then:
- Sort the PivotTable data to display the states with the largest Average of Adjusted Gross Income on top and the smallest on the bottom. Which state had the smallest average of adjusted gross income? (Hint: To sort data in a PivotTable in Excel, right-click any cell in the PivotTable that contains the data you want to sort, and select Sort.)
- Show how many counties in each State by counting the county names (Hint: modify the PivotTable to show count)
Please also put these answers in different spreadsheets
