Create and Format a Table 1. Open VRSeries.xlsx and save it with the name 3-VRSeries. 2. Select

Fantastic news! We've Found the answer you've been seeking!

Question:

Create and Format a Table

1. Open VRSeries.xlsx and save it with the name 3-VRSeries.

2. Select the range A3:K34 and create a table. The table has headers.

3. Name the table Inventory.

4. Apply the Purple, Table Style Medium 5 table style (fifth column, first row in the Medium section).

5. Add a calculated column to the table in column L by completing the following steps:

a. Type Discount as the column heading in cell L3.

b. Create a structured reference formula in the first record that multiplies the price in column J times the current discount in column K. The formula will copy to the rest of the rows in the table. Format the new values by applying the Comma format and specifying two digits after the decimal point.

c. Type Sale Price as the column heading in cell M3.

d. Create a structured reference formula in the first record that subtracts the discount in column L from the price in column J. The formula will copy to the rest of the rows in the table.

e. Adjust the three title rows above the table to merge and center across columns A through M. Adjust all the column widths using AutoFit.

6. Add banding to the columns, remove banding from the rows, and emphasize the last column in the table.

7. The DVD for Doctor Who cannot be located, and the manager would like you to remove the record from the table. Delete the row in the table for the record with stock number CV-1007.

8. Add a Total row to the table. Modify the sum function in cell M34 to a function that calculates the average sale price. Delete the contents of cell A34 and type Average.

9. Save, preview, and then close 3-VRSeries.xlsx.

Assessment 2

Use Data Tools

1. Open 3-VRSeries.xlsx and save it with the name 3-VRSeries-2.

2. Make the following changes:

a. Insert a new blank column to the right of the column containing the combined season number and number of episodes and change the column headings to Season No. and Episodes, respectively.

b. Split the SeasonNo./Episodes column into two columns (S# and E#). Note: At the message that pops up, click OK to confirm that there’s already data here and you want to replace it.

c. Autofit the width of columns C and D.

page 109

3. Create the following validation rules:

a. A custom format of "CV-"#### has been applied to the stock numbers. Create a validation rule for the Stock No. column that ensures all new entries are four characters in length. (With a custom number format, it is not necessary to include the characters between the quotation marks in the text length.)

b. Type Enter the last four digits of the stock number. as the input message for the Stock No. column. The title will be the column heading. Use the default error alert options.

c. Create a drop-down list for the Genre column with Drama, Comedy, Thriller/Drama, and Horror as the options. Do not enter an input message, and use the default error alert options.

4. Insert a row above the Average row and then add a record to test the data validation rules. (Initially enter incorrect values in the Stock No. and Genre columns to make sure the rules and the messages work correctly.)

5. Use the Remove Duplicates feature to find and remove any duplicate rows using Stock No. as the comparison column.

6. Add a slicer and then filter the list to display the Horror genre. Change the color of the slicer to coordinate with the color of the table. Adjust the height of the pane to be 1.5 inches and the width to be 1.7 inches.

7. Save, preview, and then close 3-VRSeries-2.xlsx.

Assessment 3

Subtotal Records

1. Open 3-VRSeries-2.xlsx and save it with the name 3-VRSeries-3.

2. Click the Clear Filter button on the Slicer pane and then delete the Slicer pane.

3. Remove the Total row and remove the emphasis from the last column in the table.

4. Convert the table to a normal range.

5. Adjust all the column widths using AutoFit.

6. Sort the list first by genre, then by title, and then by season number. Use the default sort values and sort order for each level.

7. Using the Subtotal button, add subtotals to the Sale Price column to calculate the average sale price and to count the number of TV shows by genre.

8. Display the worksheet at level 3 of the outline.

9. Apply bold formatting to only the labels and count and average totals displaying in the Sale Price column.

10. Show the details for the Comedy and Drama genres.

11. Save, preview, and then close 3-VRSeries-3.xlsx.

L3 1 ~34 2 3 Stock No. A 4 > Ready CV-1001 5 CV-1002 6 CV-1003 7 8 CV-1004 CV-1005 CV-1006 9 10 CV-1007 11 28 CV-1011 Modern Family 29 CV-1028 The Walking Dead 30 CV-1029 The Walking Dead 31 CV-1030 The Walking Dead