Challenge Project 10-10

For this project, you use your solution file from Independent Project 10-4 to build a new PivotTable for the Excel table and to assemble Excel tables in a dashboard layout.

File Needed: [your initials] Excel 10-4.xlsx

Completed Project File Name: [your initials] Excel 10-10.xlsx

[Student Learning Outcome 10.2, 10.4, 10.6, 10.7]

Open [your initials] Excel 10-4.xlsx from your working folder and save it as [your initials] Excel 10-10. Modify your workbook according to the following guidelines:

• Select the tblCentralSierra worksheet tab.
• Insert a column titled Total Clients and build the formula.
• Create a PivotTable on a new sheet for tblCentralSierra as the range.
• Show the State, City, Employed, and Total Clients fields.
• Insert a calculated field that calculates the number of employed as a percentage of the client total. The result is a decimal value. Name the column % of City Total.
• Format the PivotTable.
• Select the tblCentralSierra worksheet, click a cell in the table, and launch Power Query.
• Make a copy of the query and name the copy GroupBy. Group the records by state to sum the number of unemployed clients. Name the column Non-Working.
• Make another copy of the original query and name it ByExample. Build a column by example to display the city name, a comma, and the state in a single column. Remove the two source columns and position the new column as the second column in the query. Remove the Employed and Unemployed columns.
• Load the queries to your workbook.
• Select and copy the tables on the GroupBy and ByExample sheets to the tblCentralSierra sheet.
• Position and format the tables in a dashboard layout.
• Filter the records in the original table to show one state.