PROJECT STEPS

$1.$ Anthony and Gina Romano have started to collect items such as sports memorabilia and comic books, anticipating that their collections will appreciate in value. Anthony is using an Excel workbook to track their collectible items and asks for your help in summarizing data so he can evaluate the items gaining the most value. To do so$,$ you will use database functions and advanced PivotTable features.

Go to the Collections worksheet, which contains a table named Collectibles listing details about the items the Romanos collect. Start by clearing the filter from the Category field.

$2.$ Instead of seeing the data sorted by purchase price, Anthony and Gina would like to see the data by year. Sort the table data by the Acquired field from smallest to largest.

$3.$ In the range K$3$:N$8,$ Anthony wants to summarize item information.

Start by calculating the number of items in each category as follows:

a$.$ In cell L$4,$ enter a formula using the COUNTIF function that counts the number of comic books, checking that the Category column in the Collectibles table $($Collectibles$[$Category$])$ is equal to the value in cell K$4.$

b$.$ Fill the range L$5$:L$8$ with the formula in cell L$4.$

$4.$ In column M$,$ Anthony wants to calculate the total value of the items in each category. Determine the total values as follows:

a$.$ In cell M$4,$ enter a formula using the SUMIF function that totals the value for comic books, checking that the Category column in the Collectibles table $($Collectibles$[$Category$])$ is equal to the value in cell K$4,$ and that the formula totals all the current values $($Collectibles$[$Current Value$]).$

b$.$ Fill the range M$5$:M$8$ with the formula in cell M$4.$

$5.$ In column N$,$ Anthony wants to calculate the average value of the items in each category. Determine the average values as follows:

a$.$ In cell N$4,$ enter a formula using the AVERAGEIF function that averages the value for comic books, checking that the Category column in the Collectibles table $($Collectibles$[$Category$])$ is equal to the value in cell K$4,$ and that the formula averages all the current values $($Collectibles$[$Current Value$]).$

b$.$ Fill the range N$5$:N$8$ with the formula in cell N$4.$

$6.$ Go to the Value by Condition worksheet. Anthony has created a PivotTable on this worksheet to list the final current value of the collectible items by category, condition, and year. He grouped the year data into two$-$year spans, but wants them listed as separate years.

Ungroup the year data in the PivotTable.

$7.$ Anthony thinks the PivotTable looks crowded in its default Compact layout.

Change the report layout to show the PivotTable in Tabular Form.

$8.$ Go to the Items by Category worksheet. Anthony created a PivotTable that lists each item by category, and then counts the number of those items acquired each year. The data is sorted in alphabetic order by category, but Anthony wants to sort the data by total number of items. He also wants to focus on coins and comic books only.

Change the display of the PivotTable as follows:

a$.$ Sort the data in descending order by Grand Total.

b$.$ Apply a Label Filter that displays Category values that begin with Co$.$

$9.$ Go to the Price and Value Comparison worksheet. The PivotTable on this worksheet lists the purchase prices and current values for each category of collectible item. Anthony wants to include the number of items in each category in the second column of the PivotTable.

Make this change for Anthony as follows:

a$.$ Add the ID field to the Values area of the PivotTable, making it the first field in the Values area.

b$.$ Change the number format of the Count of ID amounts to Number with $0$ decimal places.

$10.$ Anthony wants to know the difference and the percentage of difference between the purchase prices and the current values.

Provide this information for Anthony as follows:

a$.$ Insert a calculated field named Difference that subtracts the Purchase Price field amount from the Current Value field amount.

b$.$ Insert another calculated field named $\%$ Difference that subtracts the Purchase Price field amounts from the Current Value field amounts, and then divides the result by the Purchase Price field amount.

c$.$ Change the number format of the Sum of $\%$ Difference amounts to Percentage with $2$ decimal points.

$11.$ Anthony also wants to include the average purchase price for the items in each category as the last column in the PivotTable.

Add this information to the PivotTable as follows:

a$.$ Add the Purchase Price field to the Values area of the PivotTable, making it the last field in the Values area.

b$.$ Use the Average calculation to summarize the Purchase Price field data.

$12.$ Go to the Purchases by Year worksheet. The PivotTable on this worksheet lists the purchase prices of the collectibles by year and category. Anthony wants to show the names of each collectible item and display the information about the two categories with the highest total purchase prices.

Provide this information for Anthony as follows:

a$.$ Expand the