Grader - Instructions Excel 2016 Projed EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto Sales 1.15 Project Description: You work for Rockville Auto Sales and have been asked to aid in the development of a spreadsheet to manage sales and inventory information. You will start the task with a prior worksheet that contains vehicle information and sales data for 2018 You need to convert the data to a table. You will manage the large worksheet, prepare the worksheet for printing, sort and filter the table, include calculations, and then format the table. Steps to Perform: Step Instructions Points Possible Open the downloaded file exploring_e04_grader_h1.xlsx. 0 Freeze the first row on the Fleet Information worksheet. 5 tables 10 Convert the data to a table, name the table Inventory, and apply the Gold, Table Style Medium 19. 3 Remove duplicate records. 5 5 Sort the table by Make in alphabetical order, add a second level to sort by Year Smallest to Largest, and a third level to sort by Sticker Price Smallest to Largest. &kilty 5 6 Repeat the field names on all pages. 5 7 Change page breaks so each vehicle make is printed on a separate page. 6 8 Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side. 10 9 Click the Sales Information worksheet and convert the data to a table, name the table Sales, and apply the Green, Table Style Dark 11. 2 10 Type % of sticker in cell E1. 5 11 Create a formula with structured references to calculate the percentage of the Sticker Price in column E. 5 12 Format the range E2:E30 with Percent Style Number Format. 7 13 Add a total row to display the Average of % of sticker and Sum of Sticker Price and Sale Price. 0 14 AutoFit the width of columns B:E to show the total values. & 15 Select the range E2:E30. Apply Solid Fill Blue Data Bars conditional formatting to the % of sticker data. 16 With the range E2:E30 selected, create a new conditional formatting rule that uses a formula to apply yellow fill and bold font to values that sold for less than or equal to 70% of the sticker price. Be sure to select a Rule Type that will use a formula to determine which cells to format. 17 On the First Quarter Sales worksheet, rename the table FirstQuarter. 18 Filter the data to display January, February, and March sales. 1 EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto Grader - Instructions Excel 2016 Projed EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto Sales 1.15 Project Description: You work for Rockville Auto Sales and have been asked to aid in the development of a spreadsheet to manage sales and inventory information. You will start the task with a prior worksheet that contains vehicle information and sales data for 2018 You need to convert the data to a table. You will manage the large worksheet, prepare the worksheet for printing, sort and filter the table, include calculations, and then format the table. Steps to Perform: Step Instructions Points Possible Open the downloaded file exploring_e04_grader_h1.xlsx. 0 Freeze the first row on the Fleet Information worksheet. 5 tables 10 Convert the data to a table, name the table Inventory, and apply the Gold, Table Style Medium 19. 3 Remove duplicate records. 5 5 Sort the table by Make in alphabetical order, add a second level to sort by Year Smallest to Largest, and a third level to sort by Sticker Price Smallest to Largest. &kilty 5 6 Repeat the field names on all pages. 5 7 Change page breaks so each vehicle make is printed on a separate page. 6 8 Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side. 10 9 Click the Sales Information worksheet and convert the data to a table, name the table Sales, and apply the Green, Table Style Dark 11. 2 10 Type % of sticker in cell E1. 5 11 Create a formula with structured references to calculate the percentage of the Sticker Price in column E. 5 12 Format the range E2:E30 with Percent Style Number Format. 7 13 Add a total row to display the Average of % of sticker and Sum of Sticker Price and Sale Price. 0 14 AutoFit the width of columns B:E to show the total values. & 15 Select the range E2:E30. Apply Solid Fill Blue Data Bars conditional formatting to the % of sticker data. 16 With the range E2:E30 selected, create a new conditional formatting rule that uses a formula to apply yellow fill and bold font to values that sold for less than or equal to 70% of the sticker price. Be sure to select a Rule Type that will use a formula to determine which cells to format. 17 On the First Quarter Sales worksheet, rename the table FirstQuarter. 18 Filter the data to display January, February, and March sales. 1 EX16_XL_CH04_GRADER_CAP_HW - Rockville Auto