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 .