***Excel Problem***

NBA Hoops Blog Jeff DeMarco is a sports enthusiast. Several years ago, he began writing a basketball blog called NBA Hoops that provides information, opinions, and analysis related to teams, players, and games in the National Basketball Association (NBA). To help him more easily respond to comments from his readers, he created an Excel table that tracks teams, positions, and salaries for each player in the league. He asks you to analyze this data. Complete the following:

1. Open the Players workbook located in the Excel5 ?Case2 folder included with your Data Files, and then save the workbook as NBA Player Data.

2. In the Documentation worksheet, enter your name and the date.

3. In the Players worksheet, create an Excel table named NBAPlayers. Format the Salary column with the Accounting format and no decimal places. Format the NBAPlayers table with the table style of your choice.

4. Make a copy of the Players worksheet, and then rename the copied worksheet as Sort Position. (Hint: Press the Ctrl key and drag the Players sheet tab to the right of the Players sheet tab to make a copy of the worksheet.) Sort the NBAPlayers table in ascending order by position, then in descending order by salary.

5. Use conditional formatting to apply a yellow fill with dark yellow text to highlight all players with a salary greater than $10,000,000.

6. Make a copy of the Players worksheet, and then rename the copied worksheet as Filter Team. Insert a slicer to filter by Team. Place the slicer to the right of the NBAPlayers table. Select a slicer style that matches the style you used to format the NBAPlayers table. Resize the slicer?s height and width to improve its appearance.

7. Use the slicer to filter the NBAPlayers table to display all players on the NY Knicks and Miami Heat teams.

8. Expand the filter to display NY Knicks and Miami Heat players earning more than $5,000,000. Sort the filtered table in ascending order by salary.

9. Make a copy of the Players worksheet, and then rename the copied worksheet as Filter Top 15%. Filter the NBAPlayers table to display players whose salaries are in the top 15 percent. Sort the data by Salary in descending order. 10. Use the Total row to include the average salary at the bottom of the table, and then change the Total row label to Average. Add the Count of the Team column to the Total row. Remove the entry in the Division column of the Total row.

11. Make a copy of the Players worksheet, and then rename the copied worksheet as Subtotals. Use the Subtotal command to display the total salary for each team in the Salary column.

12. Based on the data in the Players worksheet, create a PivotTable in a new worksheet that totals salaries by team and position. Place the Position field in the Columns area. Rename the worksheet as PivotTable Team Position. Format the salaries in the PivotTable with the Accounting format and no decimal places. Resize the columns as needed to display all the salaries.

13. Create a Division slicer for the PivotTable. Resize the slicer object and buttons as needed, and then select a slicer style that matches the PivotTable. Use the slicer to filter the PivotTable to display teams from the Atlantic, Central, and Southeast divisions.

14. Based on the data in the Player Salary worksheet, create a PivotTable that calculates the number and average salaries by position in a new worksheet. Format the average salaries, change the label above the average salaries to Avg Salary, and then change the label above the count to Number. Resize columns as needed to display all cell contents. Rename the worksheet as PivotTa

NBA Hoops Blog Author Date Purpose To track NBA player data Data Definition Table Field Description Data Type Notes First name Last name Salary Player's salary Text Text Number Position Player's position Text C (Center), PF (Power Forward), SF (Shooting Forward), SG (Shooting Guard), PG (Point Guard) Team Player's team Text Chicago Bulls, LA Lakers, Miami Heat, NY Knicks, Oklahoma Thunder, San Antonio Spurs Division Division player's team is assigned to Text Pacific, Atlantic, Northwest, Southeast Use Accounting format with no decimal places First Name Marco Carlos Jimmy Luol Taj Richard Kirk Nazr Joakim Vladimir Nate Derrick Marcus Steve Kobe Earl Chris Devin Pau Jordan Dwight Antawn Jodie Darius Steve Metta Ray Joel Shane Chris Rodney Mario Norris Josh Udonis LeBron James Rashard Mike Dexter Garrett Dwyane Carmelo Ronnie Marcus Tyson Chris Raymond Jason Steve Pablo Iman JR Amar'e Kurt James Cole Nick Daequan Kevin James Lazar Serge Reggie Eric Kendrick Thabo Hasheem Russell DeJuan Matt Nando Boris Tim Manu Danny Stephen Cory Kawhi Patty Last Name Belinelli Boozer Butler Deng Gibson Hamilton Hinrich Mohammed Noah Radmanovic Robinson Rose Teague Blake Bryant Clark Duhon Ebanks Gasol Hill Howard Jamison Meeks Morris Nash Peace Allen Anthony Battier Bosh Carney Chalmers Cole Harrellson Haslem James Jones Lewis Miller Pittman Temple Wade Anthony Brewer Camby Chandler Copeland Felton Kidd Novak Prigioni Shumpert Smith Stoudemire Thomas White Aldrich Collison Cook Durant Harden Hayward Ibaka Jackson Maynor Perkins Sefolosha Thabeet Westbrook Blair Bonner DeColo Diaw Duncan Ginobili Green Jackson Joseph Leonard Mills Salary Position 1957000 SG 15000000 C 1066920 SG 13305000 SF 2155811 PF 5000000 SG 3941000 PG 854389 C 11300000 C 854389 PF 854389 PG 16402552 PG 857000 SG 4000000 PG 27849149 SG 1240000 PF 3500000 PG 1054389 PF 19000000 PF 3563600 PF 19536360 C 854389 PF 1500000 SG 962195 SG 8900000 PG 7258960 SF 3090000 SG 3750000 C 3135000 SF 17545000 C 854389 PF 4000000 PG 1082520 PG 762195 PF 4060000 PF 17545000 SG 1500000 SF 1352181 PF 5800000 PF 854389 C 854389 SG 17182000 PG 20463024 SF 854389 SG 4590338 C 13604188 C 473604 SF 3480453 PG 3090000 PG 4054055 SF 473604 PG 1633440 SG 2806452 SG 19948799 PF 1352181 C 854389 SF 2445480 C 2929332 PF 3090942 PG 16669630 SF 5820417 SG 1174080 SF 2253062 C 1208400 SG 2338721 PG 8300531 C 3600000 SG 1200000 C 13668750 PG 1054000 C 3630000 PF 1400000 PG 4500000 PF 9638554 C 14107492 SG 3500000 SG 10059750 SF 1074720 SG 1809840 SF 1085120 PG Team Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers LA Lakers Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat Miami Heat NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks NY Knicks Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder Oklahoma Thunder San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs San Antonio Spurs Division Central Central Central Central Central Central Central Central Central Central Central Central Central Pacific Pacific Pacific Pacific Pacific Pacific Pacific Pacific Pacific Pacific Pacific Pacific Pacific Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Atlantic Northwest Northwest Northwest Northwest Northwest Northwest Northwest Northwest Northwest Northwest Northwest Northwest Northwest Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Southeast Tony Tiago Parker Splitter 12500000 PG 3944000 C San Antonio Spurs San Antonio Spurs Southeast Southeast