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 CV-1008 Modern Family 12 CV-1009 Modern Family 13 CV-1010 Modern Family 14 CV-1011 Modern Family 15 CV-1012 Gotham 16 CV-1013 Grey's Anatomy 17 CV-1014 Grey's Anatomy 18 CV-1015 Parks and Recreation 19 CV-1016 Parks and Recreation 20 CV-1017 NCIS CV-1018 NCIS 21 22 CV-1019 NCIS 23 CV-1020 Suits 24 CV-1021 Suits 25 CV-1022 Suits 26 27 CV-1023 CV-1024 20 MLADA4 1 X fx B Title Once Upon a Time Once Upon a Time Once Upon a Time The Big Bang Theory The Big Bang Theory The Big Bang Theory Doctor Who 20F Criminal Minds Criminal Minds NA TV Accessibility: Good to go C Season No./Episodes S3 E22 S4 E22 S5 E22 S8 E24 S9 E24 S10 E24 S8 E12 S5 E24 S6 E24 S7 E24 S8 E24 S2 E22 S10 E24 S11 E25 S6 E22 S7 E13 S11 E24 S12 E24 S13 E24 S1 E12 S2 E12 S3 E12 S10 E23 S11 E22 COFRA D Year Aired 2015-16 2016-16 2017-16 2014-15 2015-16 2016-17 2014 2013-14 2014-15 2015-16 2016-17 2015-16 2013-14 2014-15 2013-14 2014-15 2013-14 2014-15 2015-16 2011-12 2012-13 2013-14 2014-15 2015-16 2040 47 E F ViewRite TV Series Collection Genre DVD Yes Drama Drama Yes Yes Yes Yes Drama Comedy Comedy Comedy Sci-Fi Comedy Comedy Yes Yes Yes Yes Yes Yes Yes No Comedy Comedy Drama Drama Drama Comedy Comedy Drama Drama No No No Yes Yes Drama Yes Drama Yes No No Drama Drama Thriller/Drama No Thriller/Drama No | G Blu-ray Yes Yes Yes Yes Yes Yes Yes No No No No Yes No No No No Yes Yes Yes No Yes Yes Yes Yes AL H Download Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes M 1 Multiformat Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes No No No No Yes Yes Yes No Yes Yes Yes Yes A J Price 34.99 39.99 39.99 28.99 32.99 32.99 69.99 19.99 19.99 24.99 24.99 32.99 20.99 20.99 12.99 25.99 30.99 34.99 49.99 32.99 34.99 34.99 24.99 24.99 24.00 K Discount % 10% 7% 7% 10% 10% 7% 10% 10% 10% 10% L M 0 E N