Add comments to cells F9, F10, and F11. In your comments, explain briefly in your own words what is displayed in each cell. Add gridlines and cell formatting and color formatting to cells A10: B13. Add a top and double bottom border to cell D8. Protect the worksheet so the contents cannot be accidentally changed by a user. Do not use a password. Save your work( CTRL + s)-( but leave it open to continue).
3. Data cleansing, lists, sorting, conditional formatting, and pivot tables( TCOs 4 & 8; 40 points). On the Q3 sheet, adjust the widths of the columns as needed. Column C contains each customer‘ s combined first and last name. Insert two empty columns to the right of column C, and use an Excel feature to separate the customers‘ first names and last names into the new columns. In the first row, label the new columns Customer First Name and Customer Last Name. After doing this, delete the original Customer Name column. Resize the columns. Convert the list of orders( A1 – J21) into an Excel table. Sort the table into descending order( highest to lowest) by Order Amount. Apply conditional formatting to the Order Amount column so that order amounts greater than $ 100 are highlighted in green. Convert the table back into a range. Create a pivot table on a new worksheet that uses Customer Country as the row field, Product as the column field, and the sum of Order Amount as the values. Place this sheet immediately after the Q3 sheet, labeled Q3-Pivot. Format the cells in the Pivot table using all borders with accounting or currency format with 2 decimal places. Add gridlines to the Pivot table. Save your work( CTRL + s)-( but leave it open to continue).
4. Data consolidation, analysis, and reporting( TCOs 5 & 9; 40 points) a. Group the four sheets Q4 East, Q4 Central, Q4 West, and Q4 Summary.