exam 3
Save the changes and close the Visual Basic Editor.
On the Visual Basic Editor toolbar, click the Save button. Hover over the X in the upper right corner of the window until it turns red, then click the red X to close.
Add slicers for PO # and Ordered.
Click the Table Tools Design tab. In the Tools group, click the Insert Slicer button. Click the PO # and Ordered check boxes. Click Ok.
In the Total row, display the maximum value for the Quantity in Stock column.
Click in the Total row for the Quantity in Stock column. Click the arrow and select Max.
In the Total row, display the average value for the Wholesale column.
Click in the Total row for the Wholesale column. Click the arrow and select Average.
Sort the data from A-Z by the Last Name column by clicking the Data tab, and then in the Sort & Filter group, click the AZ button.
Click the Data tab, and then in the Sort & Filter group, click the AZ button.
Sort the Item column in alphabetical order from A to Z.
Click the Data tab. In the Sort & Filter group, click the Sort A to Z button.
Edit the code for the FormatData macro in the Visual Basic Editor.
Click the Developer tab. In the Code group, click the Macros button. Select FormatData and click the Edit button.
Stop recording the Macro now.
Click the Developer tab. In the Code group, click the Stop Recording button.
Add a report filter for the Employee field and use it to filter the PivotTable to show only data where the value of the Employee field is Dr. Patella.
Click the Employee field in the PivotTable Fields pane and drag it to the Filters box in the bottom part of the pane Click the filter arrow next to the report filter. Click Dr. Patella. Click OK.
Change the security setting to disable all macros and display a warning in the Message Bar so you can choose to enable them on a case-by-case basis.
Click the File Click the Options button to open Excel Options dialog. Click Trust Center, and then click the Trust Center Settings... buttton. Click Macro Settings Click the Disable all macros with notification radio button and click OK click OK to close the Excel Options dialog.
Encrypt the workbook with this password: eXp3ns$s
Click the File tab. Click Protect Workbook button. Click Encrypt with Password. Type eXp3ns$s. Click Ok. Type eXp3ns$s in the Confirm Password dialog. Click Ok.
Save this file as a macro-enabled template.
Click the File tab. Click Save As. On the Save As page, expand the list of file types. Select Excel Macro-Enabled Template. Click Save.
Save this file as a macro-enabled workbook.
Click the File tab. Click Save As. On the Save As page, expand the list of file types. Select Excel Macro-Enabled Workbook. Click Save.
Add a report filter for the Item field and use it to filter the PivotTable to show only data where the Item value is Bronzing gel.
Click the Item field in the PivotTable Fields pane and drag it to the Filters box in the bottom part of the pane. Click the filter arrow in cell B1. Click Bronzing Gel. Click Ok.
Filter the data to display only records where the value in the Ordered column is 20 or 25. Hint: Enable the Multi-Select button to apply more than one filter option in the Ordered slicer.
Click the Multi-Select button in the Ordered slicer. Click the 10, 15, and 30 buttons to turn them off.
The Old Town Spa has been protected. Unprotect it so users can make changes to the locked cells.
Click the Old Town Spa worksheet tab. On the Review tab, click the Unprotect Sheet button.
Using the slicers, filter the table to show only rows the PO # is PO1104009.
Click the PO1104009 button in the PO # slicer.
Share the document with [email protected] and include the message "Please review and edit.". The recipient should also be able to edit the document.
Click the Share button at the far right side of the Ribbon next to the user profile name. Type the email address in the Invite people box. Verify Can edit is selected. Type the message "Please review and edit." in the Include message box. Click the Share button.
Filter the Quantity in Stock column so only rows with blanks are shown. Hint: Scroll to the bottom of the filter list to find the (Blanks) option.
Click the arrow at the top of the Quantity in Stock column. Click the (Select All) check box to uncheck all options. Scroll down and click the check box in front of (Blanks). Click Ok.
Delete the comment from cell F5.
In the Comments task pane, click More thread actions for the comment in cell F5. Select Delete thread.
Reply to the comment in cell F4 with the text Done.
In the Comments task pane, type Done in the Reply box below the comment for cell F4. Click the Post button.
Add the Order Cost field to the PivotTable.
In the PivotTable Fields pane, click the Order Cost check box to add the field to the PivotTable.
Check the spelling...
In the Review tab, click the Spelling button. Click change. Click Ok.
Modify the code to enter the text Inventory ID instead of Item ID. Save your changes.
In the code ActiveCell.FormulaR1C1="Item ID" change Item ID to Inventory. Click Save button.
Modify the code to select columns D:F with a single command and then apply the Currency style. (Hint: You should end up with two lines of code instead of four.) Save your changes.
In the code Columns("D:D").Select change D:D to D:F and then delete the lines of code Columns("E:F").Select and the next line Selection.Style="Currency". On the the Visual Basic Editor toolbar, click the Save button.
Modify the code to apply the Comma style. Save your changes.
In the line of code Selection.Style="Currency" change Currency to Comma. On the Visual Basic Editor toolbar, click the Save button.
Sort the Order Cost column so the largest numbers are listed first.
On Data tab, in the Sort & Filter group, click the sort Z to A button.
Import the data from the Services text file. Allow Excel to import the data into a table in a new worksheet.
On the Data tab, in the Get & Transform Data group, click the Form Text/ CSV button. In the Import Data dialog, click Services, and click the Import button. Click the Load button.
Add subtotals: at every change in Last Name, Sum the values in the Price column. On Data tab, in the Outline group, click the Subtotal button. Click Ok.
On the Data tab, in the Outline group, click the Subtotal button. Click the Price check box to add a checkmark. Click the Location check box to remove the checkmark. Click Ok.
Clear the filter from the Quantity in Stock column.
On the Data tab, in the Sort & Filter group, click the Clear button.
Run the FormatImport macro.
On the Developer tab, in the Code group, click the Macros button. In the Macros dialog, FormatImport is selected. Click the Run button.
Edit the code for the FormatTable macro in the Visual Basic Editor.
On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button.
Begin recording a new macro. Name the new macro "AddSubtotals". Assign the keyboard shortcut Ctrl+Shift+S. Add the description: "Sort by last name and then add subtotals", and then begin recording the macro. The macro should be stored in the current workbook.
On the Developer tab, in the Code group, click the Record Macro button. Type "AddSubtotals" in the Macro name box. Press the Shift key and type "S" in the Shortcut key box. Type "Sort by last name and then add subtotals" in the Description box. Click Ok.
Add a form control button to run the ApplyDCTax macro. The button should be placed at approximately cells D1:E2.
On the Developer tab, in the Controls group, click the Insert Controls button. Click the Button (Form Control) button. Click and drag from cell D1 to cell E2 to draw the button. Click ApplyDCTax. Click Ok.
Unlock the cells B3:D6 so the user can edit the cells when the worksheet is protected.
On the Home tab, in the Cells group, click the Format button. Click Lock Cell to remove the highlight.
Enable filtering for data in this worksheet.
On the Home tab, in the Editing group, click Sort & Filter button and select Filter.
Convert the cell range to a table using table style "Green, Table Style Light 14". The data range contains a header row.
On the Home tab, in the Styles group, click Format as Table button. Click "Green, Table Style Light 14". Verify that the My table has headers checkbox is checked. Click Ok.
Insert a recommended PivotTable using the Sum of Ordered and Sum of Recieved by PO # option.
On the Insert tab, in the Tables group, click the Recommended PivotTables button. In the Recommended PivotTables dialog, select the Sum of Ordered and Sum of Recieved by PO # option. Click Ok.
Modify the Order Cost field to use the Average function instead of the Sum function. A cell in the Order Cost field is selected for you.
On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. Select Average. Click Ok.
Add a calculated field to this PivotTable to summarize the difference between values in the Ordered field and the Received field (=Ordered-Received). Name the calculated field "Difference".
On the PivotTable Tools Analyze tab, in the Calculations group, click Fields, Items, & Sets. Select Calculated Field... In the Insert Calculated Field dialog, type "Difference" in the Name box. In the Formula box, delete the 0 and type Ordered-Recieved. Do not delete the equals sign. Click Ok.
Update the data source for the PivotTable to use the table named "JulyTable".
On the PivotTable Tools Analyze tab, in the Data group, click the Change Data Source Button. Type "JulyTable" in the Table/Range box. Click Ok.
Changes have been made to the underlying data for this PivotTable. Refresh the PivotTable data.
On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.
Add a slicer to the PivotTable for the Item and Date Expected fields. Use the slicer to filter the data further to show only data where the Date Expected value is 8/12/2020.
On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Slicer button. In the Insert Slicers dialog, click the check boxes for Item and Date Expected, and then click Ok. Click 8/12/2020 in the Date Expected slicer.
Add a timeline to the PivotTable to filter the data by values in the Date Expected field. Use the timeline to filter the PivotTable to show only dates in August.
On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Timeline button. In the Insert Timeline dialog, click the check box for the Date Expected field. Click Ok. Click the August segment on the timeline.
Add grouping by month and quarter to this PivotTable.
On the PivotTable Tools Analyze tab, in the Group group, click the Group Field button. In the By box, click Quarters. (Months is already selected). Click Ok.
Insert a PivotChart using the first line chart type.
On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button. Click Line in the list of chart types at the left. Click Ok.
Apply the "Light Green, Pivot Style Light 14" Quick Style to the PivotTable.
On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the Quick Styles gallary, and select "Light Green, Pivot Style Light 14".
Enable workbook protection so users cannot change worksheet names.
On the Review tab, click the Protect Workbook button. Click Ok.
Remove workbook protection so users can add new worksheets.
On the Review tab, click the Protect Workbook button. Click Ok.
Add this comment to Cell F4: Need links to details here.
On the Review tab, in the Comments group, click the New Comment button. Type: Need links to details here. Click the Post button.
Open the comments task pane to review all the comments in this worksheet.
On the Review tab, in the Comments group, click the Show Comments button.
Enable the worksheet protection for the Summary worksheet so users can select both locked cells and unlocked cells.
On the Review tab, in the Protect group, click Protect Sheet. Click ok.
Add a Total row to the table.
On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box.
Convert the table to a normal range.
On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes.
Remove the duplicate rows where data in all the columns are identical.
On the Table Tools Design tab, in the Tools group, click the Remove Duplicates button. Click Ok. Click Ok.
Change the button text to: Show DC Tax
Right-click the button and select Edit Text. Edit the button text to "Show DC Tax" and then click anywhere outside the button.
Insert a new comment: "Change column heading".
Type "Change column heading" and press Enter.
