SimNet Exam 3

Add a calculated field to this PivotTable to calculate the average sales per visit by state (=Sales/Visits). Name the new field Average.

In the Analyze Ribbon Tab in the Calculations Ribbon Group, you clicked the Fields, Items, & Sets button. In the Fields, Items, & Sets menu, you clicked the Calculated Field... menu item. Inside the Insert Calculated Field dialog, you typed Average in the Name input, typed =Sales/Visits in the Formula input, clicked the Add button, and clicked the OK button.

Add a slicer to the PivotTable for the State field and use the slicer to show only data where the value is VA.

In the Analyze Ribbon Tab in the Filters Ribbon Group, you clicked the Insert Slicer button. Inside the Insert Slicer dialog, you checked the State check box, clicked the OK button. You clicked the VA button.

Add a timeline to the PivotTable to filter the data by values in the Date of Last Visit field. Use the timeline to filter the PivotTable to show only dates in September.

In the Analyze Ribbon Tab in the Filters Ribbon Group, you clicked the Insert Timeline button. Inside the Insert Timelines dialog, you checked the Date of Last Visit check box, clicked the OK button. You clicked the September segment.

Data that violate the validation rules in this worksheet have been circled. Clear the validation circles.

In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. In the Data Validation menu, you clicked the Clear Validation Circles menu item.

Apply data validation rules to the selected cells to allow only decimal numbers less than 5.00. Allow blank cells.

In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. Inside the Data Validation dialog in the Allow drop-down, you selected Decimal. Inside the Data Validation dialog in the Data drop-down, you selected less than. Inside the Data Validation dialog, you typed 5.00 in the Maximum: input, clicked the OK button.

In cells D2:D13, use data validation to display an in-cell drop-down list of values from the MedCodes named range. Allow blanks.

In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. Inside the Data Validation dialog in the Allow drop-down, you selected List. Inside the Data Validation dialog, you typed =MedCodes in the Source: input, clicked the OK button.

This worksheet has validation rules applied. Find and circle cells that violate those rules.

In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. Inside the Data Validation dialog, you clicked the dialog Close button. In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. In the Data Validation menu, you clicked the Circle Invalid Data menu item.

Add Increase must be less than $5. as an error alert to the validation rules for the selected cells. Do not include a title.

In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. You clicked the Error Alert tab. Inside the Data Validation dialog, you typed Increase must be less than $5. in the Error message: input, clicked the OK button.

Add Enter the dollar amount. as an input message to the validation rules for the selected cells. Do not include a title.

In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. You clicked the Input Message tab. Inside the Data Validation dialog, you typed Enter the dollar amount. in the Input message input, clicked the OK button.

Create a Forecast Sheet based on the selected data. Use a line chart and forecast values for the next month.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the Forecast Sheet button. Inside the Create Forecast Worksheet dialog, you clicked the DialogHeader view, clicked the dialog undefined button, clicked the createForecastDynamicIcon item, clicked the createForecastDynamicIcon item, clicked the createForecastDynamicIcon item, and clicked the Create button.

This workbook has track changes enabled. Accept all the changes that have not yet been reviewed.

In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Track Changes button. In the Track Changes menu, you clicked the Accept/Reject Changes menu item. Inside the Microsoft Excel dialog, you clicked the OK button. Inside the Select Changes to Accept or Reject dialog, you clicked the OK button. Inside the Accept or Reject Changes dialog, you clicked the Accept button

The Downtown worksheet has been protected. Unprotect it.

In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Unprotect Sheet button

Display all the comments in this worksheet at once.

In the Review Ribbon Tab in the Comments Ribbon group, you clicked the

Open the Compatibility Checker to check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes.

You opened the backstage view, clicked the Check for issues button. In the Protect Workbook menu, you clicked the Check Compatibility menu item. Inside the Microsoft Excel - Compatibility Checker dialog, you clicked the OK button.

Use the Document Inspector to remove all comments and annotations and personal information and hidden information from this workbook. Close the Document Inspector when you are through.

You opened the backstage view, clicked the Check for issues button. In the Protect Workbook menu, you clicked the Inspect Document menu item. Inside the Microsoft Excel dialog, you clicked the Yes button. Inside the Document Inspector dialog, you clicked the Re-Inspect button, clicked the Remove All (Comments and Annotations) button, clicked the Remove All (Document Properties and Personal Information) button, and clicked the Close button.

Create a new file based on the Personal expenses calculator template.

You opened the backstage view, clicked the New navigation button, clicked the Personal expenses calculator template, and clicked the Create button.

Add the Developer tab to the Ribbon.

You opened the backstage view, clicked the Options navigation button, and clicked the Customize Ribbon tab. Inside the Excel Options dialog, you checked the Developer check box, clicked the OK button.

Complete the one-variable data table in cells G2:H7 to calculate the total investor payout for varying percentage rates. The formula has been entered for you in cell H2. It references the original investor payout percentage in cell B9. The substitute values have been entered for you in cells G3:G7.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Data Table... menu item. Inside the Data Table dialog, you typed B9 in the Column input cell input, clicked the OK button.

Complete the two-variable data table in cells H3:K8. The formula has been entered for you in cell H3. The substitute values in cells I3:K3 reference the total expenses value in cell E5, and the substitute values in cells H4:H8 reference the original investor percentage in cell B9.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Data Table... menu item. Inside the Data Table dialog, you typed B9 in the Column input cell input, typed E5 in the Row input cell input, and clicked the OK button.

Use Goal Seek to find the value for cell B2 that will result in a value of $200 for cell B6. Accept the solution.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Goal Seek... menu item. Inside the Goal Seek dialog, you typed B2 in the By changing cell: input, typed 200 in the To value: input, and clicked the OK button. Inside the Goal Seek Status dialog, you clicked the OK button.

Using the Scenario Manager, show the New Bonus scenario. Close the Scenario Manager when you are through.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog from the Scenarios: list, you selected New Bonus. Inside the Scenario Manager dialog, you clicked the Show button, clicked the Show button, clicked the Show button, clicked the Show button, and clicked the Close button.

Create a new scenario to reflect a change in cell B8 to a value of 0.09 Name the scenario New Bonus.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Add... button. Inside the Add Scenario dialog, you typed New Bonus in the Scenario name: input, clicked the OK button. Inside the Scenario Values dialog, you typed 0.09 in the text box input, clicked the OK button.

Create a scenario summary report. Accept the recommended results cells.

In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Summary... button. Inside the Scenario Summary dialog, you clicked the OK button.

Edit the code for the FormatTable macro in the Visual Basic Editor.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog, you clicked the Edit button.

Edit the code for the FormatTable macro to change the chart style from TableStyleLight11 to TableStyleMedium4. Save your changes.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog, you clicked the Edit button. You edited the macro code, clicked the Save button.

Run the FormatTable macro.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog, you clicked the Run button.

Begin recording a new macro.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Record Macro button.

Record a new macro with the name ConvertTable. Do not assign a shortcut key or description. The macro should convert the table to a range using the command on the Table Tools Design tab, Tools group.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Record Macro button. Inside the Record Macro dialog, you typed ConvertTable in the Macro Name: input, clicked the OK button. In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Convert to Range button. Inside the Microsoft Excel dialog, you clicked the Yes button. In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Stop Recording button.

Add a form control button to run the FormatTable macro. The button should be placed at approximately cells G1:I3.

In the Developer Ribbon Tab in the Controls Ribbon Group, you clicked the Insert button. In the Insert menu, you clicked the Button (Form Control) button. You clicked the undefined view. Inside the Assign Macro dialog, you clicked the OK button.

Unlock the cells B3:D8 so the user can edit the cells when the worksheet is protected.

In the Home Ribbon Tab in the Cells Ribbon Group, you clicked the Format button. In the Format menu, you clicked the Lock Cell menu item.

Protect this worksheet so users can select but not edit locked cells. Users should be able to select and edit any cells that are unlocked.

In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Protect Sheet button. Inside the Protect Sheet dialog, you clicked the OK button.

This workbook has workbook protection applied. Remove it.

In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Protect Workbook button.

Apply workbook protection to this workbook.

In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Protect Workbook button. Inside the Protect Structure and Windows dialog, you clicked the OK button.

Enable sharing so more than one person at a time can work on this workbook.

In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Share Workbook button. Inside the Share Workbook dialog, you checked the Use the old shared workbooks feature instead of the new co-authoring experience. check box, clicked the OK button. Inside the Microsoft Excel dialog, you clicked the OK button.

Open the Trust Center and change the 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.

You opened the backstage view, clicked the Options navigation button, and clicked the Trust Center tab. Inside the Excel Options dialog, you clicked the DialogHeader view, clicked the dialog undefined button, and clicked the Trust Center Settings... button. You clicked the Macro Settings tab. Inside the Trust Center dialog, you selected the Disable all macros with notification Radio Button, clicked the OK button. Inside the Excel Options dialog, you clicked the OK button.

Add the folder named MacroEnabled Workbooks as a new trusted location.

You opened the backstage view, clicked the Options navigation button, and clicked the Trust Center tab. Inside the Excel Options dialog, you clicked the Trust Center Settings... button. Inside the Trust Center dialog, you clicked the Add New Location... button. Inside the Microsoft Office Trusted Location dialog, you clicked the Browse... button. You clicked MacroEnabled Workbooks. Inside the Browse dialog, you clicked the Open button. Inside the Microsoft Office Trusted Location dialog, you clicked the OK button. Inside the Trust Center dialog, you clicked the OK button. Inside the Excel Options dialog, you clicked the OK button.

Install the Solver add-in.

You opened the backstage view, clicked the Options navigation button, clicked the Add-Ins tab, and clicked Cell 9x0. Inside the Excel Options dialog, you clicked the Go... button. Inside the Add-ins dialog, you checked the Solver Add-in check box, clicked the OK button.

Save this file as a macro-enabled template.

You opened the backstage view, clicked the Save As navigation button, clicked the Save as type drop-down, clicked the Excel Macro-Enabled Template (*.xltm) item, and clicked the Save button

Save this file as a macro-enabled workbook.

You opened the backstage view, clicked the Save As navigation button, clicked the Save as type drop-down, clicked the Excel Macro-Enabled Workbook (*.xlsm) item, and clicked the Save button.

Save this workbook as a template

You opened the backstage view, clicked the Save As navigation button, clicked the Save as type drop-down, clicked the Excel Template (*.xltx) item, and clicked the Save button.

Change the button text to: Format Table

You right-clicked the form button. In the Form Button Right-Click menu, you clicked the Edit Text menu item. You clicked the Button 1 text, typed Format Table.