Exercise - Review for Exam #2
Step 1) Save As Practice Exam 2 DONE
Step 2) Perform. these tasks: Ungroup worksheets.
TASK 1: In the Documentation worksheet:
a) Enter your name and today’s date in the appropriate cells. Format date to show as: MMM YYYY
b) No date outside current month should be allowed in the Date field. Wrong Date error should appear, ifa date outside this month is entered. The message should state: “Pick a day in current month”.
TASK 2: In the Reference worksheet:
a) Connect cells A3 to A6 with their respective worksheets, cell A1. Screen Tip must show worksheet name.
b) Add this note to cell A3: “Updated by Me” . Leave it always showing.
c) Format E3 and E4 so in those cells only approved City and Product names could be entered respectively.
Display appropriate error messages if incorrect data entered. Hint: use the Product Code and V City Colors tables in the Data Tables worksheet as references of approved names.
TASK 3: In the Data Tables worksheet:
a) Create the Product Code as PN-NN-X where PN are the first two letters of Product Name, NN are the number of characters in Product Name string, X is the last letter of each name; e.g. Ar-7-t
b) Create a structure reference (define name) for OrderSize (B4 to E5).
TASK 4: In the Order History worksheet
a) The first 3 rows and first column should remain visible regardless of where one scrolls to.
b) Create a table with order history data. Name it “OrderHistoryTbl”, select Purple style, medium 5.
c) Sort ascending by Region and then City.
d) Clear all conditional formatting rules.
e) Insert a Total Row showing totals in Package Price and Quantity columns, and average in Unit Price.
f) Insert “Shipping Fees” column. Populate it using Region and the Shipping Details table in the Data Tables worksheet. Format Accounting no decimals.
g) Add “Shipper” column. Shipper is determined by Region and the Shipping Details table.
h) Add “Order Size” column. Use Quantity column and Order Size table, from Data Tables, to display a value. Write VERY LOW, when an order is below the minimum quantity (B4).
i) Insert “Orders by Product” col, which displays Product (col E) when Package Price is less than $50 and Category is either Bars or Snacks; for anything else leave blank.
j) Create Product slicer name it “ProductSlicer” with 2 columns and a Region slicer (“RegSlicer”). Style them to match the table. Place next to table; use slicer to show only Banana, Carrot, and Potato Chips.
TASK 5: In the Conclusion worksheet
a) Fill the table using the data in the Order History worksheet. Format and align accordingly.
TASK 6: From Order History worksheet, make a copy, place it after Order History, and title it “Order Subtotal”
a) Clear Filter, delete slicer and eliminate Total Row. Pick table style. None.
b) For each City, add Package Price and then, within each city, by Product show # of Products.
c) Collapse subtotals to Level 3. Widen columns as needed to show all results.
TASK 7: From Order History worksheet
a) Create a Pivot Table in new worksheet (“Order Pivot”), name table “OrderPvt”.
b) By Category (rows), display two columns “Average Package Price” and “Total Shipping Fees” . Insert a Region slicer. Label and format columns and rows appropriately. Change style. to match workbook.
c) Below pivot chart add a clustered column chart. Color accordingly.
d) Deselect Eaast Region..
Step 3) Wrap up
a) Select A1 in Documentation worksheet, group all worksheets, save workbook and close it.