M1 AND M2 QUIZ

In the Calculations worksheet, in cell F22, use the COUNTIF formula to find out how many employees are in the R&D Department.

In the Calculationsworksheet, select cell F22. In the Formulas tab, go to the Function Libraryand click Insert Function. In the Insert Functionwindow, search for COUNTIF and click GO. In the window, select COUNTIF and click OK. In the Function Arguments window, in the Range field, select the data in the Employee Data worksheet from F3:F98. In the Criteriafield, select cell A22 in the Calculationsworksheet and click OK.

In the Dashboard worksheet, link the Form Control above the Employee Information table to the Employee Data worksheet to the data under the first column heading. Have the cell link be at P34 on the Dashboard worksheet.

In the Dashboard worksheet, right-click on the Form Control above the Employee Information table and click Format Control. In the Format Object dialog window, in the Control tab, enter your cursor into the Input Range field, select the Employee Data worksheet, and highlight all the data from cell A3:A98. In the Format Object dialog window, click into the Cell link field. In the Dashboard worksheet, select cell P34and click OK.

In the Dashboard worksheet, fix the Salary Distribution chart to select the Bonus data from the Employee Data worksheet.

In the Dashboard worksheet, select the Salary Distributionchart. In the Design tab, go to the Data group and click Select Data. In the Select Data Sourcewindow, select the data from D3:D98 and click OK.

In the Dashboard worksheet, create a VLOOKUP function in cell N39 that finds the Bonus (Column 4) in the Employee Data worksheet for the Employee in N35 in the Dashboard worksheet.

In the Dashboardworksheet, select cell N39. In the Formulastab, go to the Function Library and click Insert Function. In the Insert Functionwindow, search for VLOOKUP and click GO. In the window, select VLOOKUP and click OK. In the Function Arguments window, in the Lookup_value field, select the data in cell N35. In the Table_array field, select the data in the Employee Data worksheet cell A3:H98. In the Col_index_num, enter 4. In the Range_lookup, enter FALSE and click OK.

Protect the Employee Data worksheet with the password ED2HR. Leave all other settings as Default.

In the Employee Data worksheet, go to the Review tab. In the Protect group, click Protect Sheet. In the Protect Sheet dialog window, in the Password to unprotect sheet field, enter ED2HR and click OK. Confirm the password by entering it again and click OK.

In the Employee Records Tableworksheet, insert a slicer for the Officefield. Change the Slicer Caption to read Offices Shown. Use the slicer to filter the PivotTable to only show records from Offices in Arizona, Utah, and Washington.

In the Employee Records Table worksheet, select any part of the PivotTable. In the PivotTable Analyzecontextual tab, locate the Filter group and click Insert Slicer. In the Insert Slicerswindow, check the box for Office and click OK In the Slicer contextual tab, locate the Slicergroup and enter Offices Shown in the Slicer Caption box. In the Slicer window, select only Arizona, Utah, and Washington.

Create a custom document property named Completed. For the Type, use Yes or No with the value set as No.

In the File tab, in the Info section, click on the dropdown for Properties in the right-hand column. Click Advanced Properties and choose the Custom tab. In Name:, type Completed. In the dropdown for Type:, choose Yes or No. In Value:, click No. Click Add. Click OK.

In the Employment Status worksheet, enable the iterative calculation formula option and set the maximum iterations to be 25 and change to .005.

In the File tab, select Options and choose the Formulas tab. Under Calculation Options, check Enable iterative calculations. In Maximum iterations, enter 25, and in Maximum change, enter .005. Click OK.

In the Invoice Tracker worksheet, create a macro that sets the width of columns B:J to be 25. Name the macro ColumnWidth and store it in only this workbook. Leave all other settings as default settings, and stop recording the macro.

In the Invoice Trackerworksheet, in the Developertab, go to the Code group and click Record Macro. In the Record Macro dialog window, in the Macro namefield, enter ColumnWidth and click OK. Select columns B:J. Right-click on any of the columns while the columns are selected and click Column Width. In the Column Width dialog window, enter 25 and click OK. In the Developer tab, go to the Code group and click Stop Recording.

In the Startup Purchases worksheet, use the SUMIF formula in the Total Amount Paid Off box to look for the Items that have been paid for and to calculate the sum of the Price data for those Items.

In the Startup Purchasesworksheet, select cell C22. In the Formulas tab, go to the Function Library group and click Insert Function. In the Insert Functionwindow, search for SUMIF and click GO. Select SUMIF from the window below and click OK. In the Function Arguments window, in the Range field, select cells D4:D20. In the Criteria field, enter Yes, and for the Sum_range field, select the data in cells C4:C20. Click OK.

In the Financial Services worksheet, create a macro that formats the row height to be 30 points and changes the font size to 12. Name the macro Height and store it in this workbook. Run this macro in the cell range A3:G9.

In the View tab, locate the Macros group, click the Macros dropdown, and select Record Macro. In the Macro name: box, enter Height. Leave the Shortcut keyand Description boxes blank. In the Store macro in: box, select This Workbook. Click OK. In the Home tab, locate the Cells group, click the Formatdropdown, and select Row Height. In the Row Heightdialog box, enter 30and click OK. In the Home tab, locate the Font group and change the Font Size dropdown to 12. In the View tab, locate the Macros group, select the Macrosdropdown, and click Stop Recording. Select the range A3:G9. In the View tab, locate the Macros group and click Macros. Run the macro Height.

Encrypt the workbook so that it requires a password to open. Use GMetrix as the password. Mark the workbook as final.

Click the File tab. In Info, select Protect Workbook. Choose Encrypt with Password. Type in GMetrix as the password and click OK. Type GMetrix again to verify the password and click OK. Select the dropdown for Protect Workbookand click Mark as Final. Click OK.

Create a new worksheet named Office Expenses, place the sheet right after Office Records. In the new worksheet, in cell A1, enter the column title Expenses. In cell A2, consolidate the data in the Office Records worksheet from cells A10:B23, E10:F23, A30:B43, E30:F43, A50:B63, E50:F63, and A70:B83 using the SUM function. Add the labels from the left column.

Click the Insert Worksheet button. In the new worksheet, right-click on the new sheet and click Rename. Type in Office Expenses. Click and drag the sheet to be right behind Office Records. In the Office Expensesworksheet, in cell A1, enter Expenses. Click into cell A2. In the Data tab, locate the Data Tools group and select Consolidate. In the Consolidate dialog box, make sure that SUMis in the Functiondropdown. Click in the Reference area. Select the Office Recordsworksheet. On the worksheet, select cells A10:B23. Click Add. Repeat step 4 for E10:F23, A30:B43, E30:F43, A50:B63, E50:F63, and A70:B83. Under Use Labels in, check Left Column. Click OK.

In the Dashboard worksheet, add a Linear Forecast Trendline to the Number of Employees by Year chart.

In the Dashboard worksheet, select the Number of Employees by Year chart. Click the Chart Elements button that appears at the top right of the chart. Hover over Trendline, click the dropdown, and click Linear Forecast. (Note: The Trendline checkbox will automatically be selected.)

Require a password to open the current workbook. Set the password to be ReviewHR9.

In the File tab, select the Protect Workbookbutton and click Encrypt with Password. In the dialog window, enter the password ReviewHR9 and click OK. Confirm the password by entering it again and click OK.

In the Family Budget worksheet, add watches to the Total row in Differencecolumn in each set of the Housing, Transportation, Loans, and Entertainment data.

In the Formulas tab, Formula Auditing group, select Watch Window. Click Add watch... Select cell D18 and click Add. Repeat steps 2 and 3 using cells D30, D40, and D50.

Using the Goal Seek data tool, set your Trip Budget for the Company to $15,000 to find out how many people are able to go on the business trip.

In the Business Trip Budget worksheet, select cell B23. In the Data tab, go to the Data Tools group, select the What-If Analysis dropdown, and click Goal Seek. In the Goal Seek window, set the Set cell field as B23, set the To value field to 15000, and set the By changing cell field to B22. Click OK twice.

Enable the Formulas referring to empty cells in the Error checking rules.

In the File tab, click Options. In the Excel Optionswindow, in the Formulas tab, go to the Error checking rules section, check Formulas referring to empty cells, and click OK.

In the Yearly Timesheet worksheet, add watches to cells I4, K4, and M4.

In the Yearly Timesheetworksheet, in the Formulastab, go to the Formula Auditing group and click Watch Window. In the Watch Window dialog window, click Add Watch. Select cell I4 and click Add. Repeat steps 2 and 3 for cells K4 and M4.

In the Employment Statusworksheet, use the Evaluate Formula tool to find and correct the error in S12.

Select Cell S12. In the Formulas tab, locate the Formula Auditing group and click Evaluate Formula. The formula in the cell is located in the Evaluation: box. Click the Evaluatebutton. The underlined portion of the formula is evaluated each time that the evaluate button is clicked. Click the Evaluate button twice. Notice that the Formula is subtracting 67895from 50509. The Total row should be adding, not subtracting. This is the error in the formula. Click Close in the Evaluate Formulawindow. Edit the Formula in cell S12 to add the 3 cells instead of subtract.

In the Sale Orders worksheet, use an AVERAGEIFS statement in G7 to find the average unit price of ItemSKUs greater than 30000. Do not include Quantities of 0.

Select cell G7. In the Formulas tab, locate the Function Library group and click Insert Function. In the Search box, type AVERAGEIFS, select it from the results, and click OK. In the Average_range box, enter D5:D24. In the Criteria_range1 box, enter B5:B24. In the Criteria1 box, enter >30000. In the Criteria_range2 box, enter C5:C24. In the Criteria2 box, enter <>0. Click OK.

In the Financial Services worksheet, add a Logarithmic Trendline based on 2005 to the Financial Data chart that forecasts 1.5 periods into the future. Add an Exponential Trendline to the Other Data chart based on 2005.

Click on the Financial Data chart and then on the Chart ElementsFlyout. Click the Flyout for Trendline and click More Options. Select the 2005 series and click OK. In the Format Trendline options, select Logarithmic for the type. Under Forecast, type in 1.5 for Forward. Select the Other Datachart, and then click on the Chart Elements Flyout. Click the Flyout for Trendline and click Exponential. Select the 2005 series and click OK.

In the Employee Records worksheet, in cell F7, add a function that counts how many people work in the Maine office.

Click the Employee Records worksheet and select cell F7. In the Formulas tab, locate the Function Library group and select Insert Function. Search for and select Countif. In Range, type or select D7:D39, and in Criteria, type Maine. Click OK.

Create a new worksheet named Cut Costs. In cell A1 of the new worksheet, consolidate the data from the Family Budget worksheet using the data in cells A6:D17, A21:D29, A33:D39, and A43:D49. Set the labels to be used from the Top Row and Left Column.

Click the New Sheet button. In the new worksheet, right-click on the new sheet and click Rename. Type in Cut Costs. In the Cut Costs worksheet, click cell A1. In the Data tab, locate the Data Tools group and select Consolidate. In the Consolidate dialog box, make sure that Sum is in the Function dropdown. Click in the Reference area. Select the Family Budgetworksheet. On this worksheet, select cells A6:D17. Click Add. Repeat step 4 for A21:D29, A33:D39, and A43:D49. Under Use Labels in, check Top Row and Left Column. Click OK.

In the Sale Orders worksheet, map the XML table elements with the name being NewOrders to also Overwrite the existing data with new data and Adjust column width. Then export the current worksheet as an XML data file named NewOrdersXML in the GMetrixTemplates folder in your Documents folder.

If you do not have the Developer tab, you will need it. Click on the File tab, select Options, and select the Customize Ribbon tab. Make sure that Developer is checked in the Main Tabssection. Click OK. In the Sale Orders worksheet, click into the table. In the Developer tab, go to the XMLgroup and click Map Properties. In the XML Map Propertieswindow, in the Name field, enter NewOrders. Under the Data formatting and layoutsection, check the Adjust column width box. Under the When refreshing or importing data section, check the Overwrite existing data with new data radio button and click OK. In the Developer tab, go to the XML group and click Export. Locate your GMetrixTemplates folder inside your Documents folder. In the file name, enter NewOrdersXML and set the type to XML Files (*.xml) and click Export.

In the Family Budget worksheet, in any open cells, create a Button Form Control named Set Data Bars and assign the button to the Set_Data_Bars macro and run it.

If you do not have the Developer tab, you will need it.Click on the File tab, select Options, and select the Customize Ribbon tab.Make sure that Developer is checked in the Main Tabssection. Click OK. In the Family Budgetworksheet, in the Developer tab, go to the Controls group, click the Insert dropdown, and select Button(Form Control). Click and drag over 1 to 2 open cells. In the Assign Macro window, click Set_Data_Bars and click OK. Enlarge the button as needed to click into it and rename it Set Data Bars. Click off the button, and then click the button to run it.

In the Business Trip Budgetworksheet, use the Trace Precedents for cell G18 to determine the problem and then fix the formula.

In the Business Trip Budget worksheet, select cell G18. In the Formulas tab, go to the Formula Auditing group and click Trace Precedents. You will see that it is skipping cell G13. Select cell G18, and then in the Hometab, go to the Editing group, click the AutoSum dropdown, select Sum, and press Enter.

In the Business Trip Budget worksheet, use the Evaluate Formula tool to correct the error in G19 and simplify and correct the complex formula.

In the Business Trip Budgetworksheet, select cell G19. In the Formulas tab, go to the Formula Auditing group and click Evaluate Formula. In the Evaluate Formula window, click Evaluate to go through the steps of the formula until it returns to the first step, and then click Close. (Look closely at what the formula is doing and how to correct it.) In cell G19, enter the formula =B4-G18.

In the Calculations worksheet, in cell H18, use the SUMIF formula to find out how many Total Sick Days are in the Finance Department.

In the Calculations worksheet, select cell H18. In the Formulas tab, go to the Function Library and click Insert Function. In the Insert Function window, search for SUMIF and click GO. In the window, select SUMIF and click OK. In the Function Arguments window, in the Range field, select the data in the Employee Data worksheet from cells F3:F98. In the Criteria field, select cell A18 in the Calculations worksheet, and in the Sum_range field, select data from the Employee Data worksheet from cells G3:G98. Click OK.

Track all changes that have ever been made by any user on this shared document, but highlight only the changes made since the last save. Leave all other settings as Default.

In the Dashboardworksheet, in the Review tab, go to the Track Changes group, and select Highlight Changes. In the Highlight Changes dialog window, click Track changes while editing. Set the Whendropdown to Since I Last Saved. Click the Whocheckbox, set the dropdown to Everyone, and click OK. Click OK.

Create and show a scenario named Less that only gives half of what the budget had for Entertainment in cell C15. Create a Scenario Summary of the Less scenario.

In the Data tab, go to the Data Tools group, select the What-If Analysis dropdown, and click Scenario Manager. In the Scenario Manager window, click Add. In the Scenario name field, enter Less. Set the Changing cells to C15 and click OK. In the Scenario Values window, set the value to 65 and click OK. In the Scenario Manager, highlight the Less Scenario and click Summary... In the Scenario Summary window, click OK.

In the Employee Data PivotChart worksheet, insert slicers for Full Name, Salary, and Department.

In the Employee Data Pivot Chart worksheet, highlight the PivotTable. In the Analyze tab, click Insert Slicer. In the Insert Slicerswindow, click Full Name, Salary, and Department. Click OK.

In the Employee Data PivotChart worksheet, edit the PivotChart, and place the Full Name field into the Axis (Categories) group and the Performance Score into the Values group.

In the Employee Data PivotChart worksheet, click the PivotChart. In the PivotChart Fields window, drag Full Nameto the Axis (Categories)group below and the Performance Score to the Values group below.

In the Employee Records Table worksheet, rearrange the PivotTable Fields to not show Office in any field and show in order of Status, First & Last Name, and ID in the Row Labelsfield.

In the Employee Records Table, select anywhere in the PivotTable. In the Pivot Table Field List, uncheck Office. Drag Status from Column Label to Row Labels, First and Last Name from Reports Filterto Row Labels, and Sum of IDfrom Values to Row Labels(this will automatically convert to ID).

Create a PivotChart on a new worksheet that displays the data from the Employee Records sheet. Do not show the First & Last Name field in the report. Make Status a Legend Field, Office an Axis Field, and Sum of ID a Value field.

In the Employee Records sheet, select cell range A6:D39. In the Insert tab, locate the Charts group, click the PivotChart dropdown, and select PivotChart. In the Create PivotChart window, click OK. In the PivotChart Fields box, drag and drop the Status field into the Legend Fields (Series) box. Drag and drop the Office field into the Axis Fields (Categories) box. Drag and drop the ID field into the Values box. This field will automatically change to Sum of ID. Be sure the First and Last Name field is unchecked.

In the Employee Records worksheet, use a COUNTIFSstatement in cell F6 to count how many employees are in the Utah office.

In the Employee Records worksheet, select cell F6. Select the Formulas tab, locate the Function Library group, and select Insert Function. Search for COUNTIFS and select it. In Criteria range 1:, select or type B7:B39. In Criteria1:, type employee. In Criteria range 2:, select or type D7:D39. In Criteria2:, type Utah. Click OK.

In the Employee Recordsworksheet, in cell F7, create a VLOOKUP function that finds the ID for Supervisor Craig Stronin.

In the Employee Records worksheet, select cell F7. In the Formulas tab, go to the Function Library group and click Insert Function. In the Insert Function window, enter VLOOKUP and click Go. Select VLOOKUP from the Select a functionsection and click OK. In the Lookup_value field, enter Craig Stronin. In the Table_array field, select or enter A7:D39. In the Col_index_num field, enter 3. Click OK.

Create a PivotTable in a new worksheet from the data in the Employee Records sheet. Display the Office column as the Report Filter and Status as a Column Label. In the Row Labels field, insert the following in the same order: ID and First & Last Name. Rename the new Sheet PivotTable.

In the Employee Recordsworksheet, in the Insert tab, go to the Tables group and click PivotTable. In the Select a table or rangefield, select A6:D39. In the Choose where you... section, select New Worksheet and click OK. In the new worksheet, in the PivotTable Field List, click and drag Office to the Filter group down below. Click and drag Status to the Column group, and drag ID and First & Last Name to the Row group. Right-click on the new worksheet, click Rename, and enter PivotTable.

In the Family Budget worksheet, create and show two scenarios named Wants and Wishes. Change the Wanting to Makevalue to be 10,000 in Wants and 15,000 in Wishes. Show Wants in the cell.

In the Family Budget worksheet, in the Data tab, go to the Forecast group, click the What-If Analysis dropdown and select Scenario Manager. In the Scenario Manager window, click Add. In the Add Scenario window, in the Scenario name, enter Wants. In Changing cell, select or enter G22 and click OK. In the Scenario Values window, enter 10000 and click OK. Repeat steps 2 to 4 using the Scenario name Wishes and the value 15000. In the Scenario Manager, under the Scenarios section, highlight Wants, click Show, and click Close.

Move the ExcelEx10.xlsx worksheet into the current workbook before the Yearly Timesheet.

In the File tab, click Open and then Computer. Locate theGMetrixTemplates folder in your documents folder. OpenExcelEx10.xlsx. In ExcelEx10.xlsx, right-click on the Invoice Tracker worksheet and click Move or Copy. In the Move or Copy dialog window, select the To bookdropdown and select ExcelEx12.xlsx. In the Before sheet field, select Yearly Timesheet and click OK.

Configure Excel Formulas to have the R1C1 reference style and enable Error Checking using the color Blue.

In the File tab, click Options. In the Excel Optionswindow, select the Formulas tab. In the Working with formulassection, check R1C1 reference style. In the Error Checking section, check Enable background error checking. In the color dropdown, select Blue and click OK.

Configure Excel to enable background checking and display detected formula errors in Red.

In the File tab, click Options. Select the Formulas tab. Under Error Checking, click to Enable background error checking. Change the color dropdown to Red. Click OK.

Save the workbook as a template named EmployeeReview in the GMetrixTemplates folder in your documents folder.

In the File tab, click Save As. In the Save As tab, click Browse. Locate your GMetrixTemplates folder in your documents folder, go to the Save as type dropdown ,and select Excel Template(.xltx). Ensure that you are still in the GMetrixTemplates folder. In the File name field, enter EmployeeReview and click Save.

Edit the custom property Reviewer to have a value of Hideki Tomo.

In the File tab, go to the Properties dropdown and select Advanced Properties. In the Properties dialog window, go to the Customtab, go to Properties, and select Reviewer. In the Value field where it displays the word None, delete the word, and enter Hideki Tomo. Click Modify and click OK.

Show all formulas in the Family Budget worksheet.

In the Formulas tab, locate the Formula Auditing group and select Show Formulas.

In the Invoice Tracker worksheet, insert a PivotTable beneath the data in cell E22 and show the Invoice # and Outstanding data.

In the Invoice Tracker worksheet, in the Insert tab, locate the Tables group and click PivotTable. In the Create PivotTable window, for select a table or range, enter B3:J18.Under the Choose where... section, click Existing Worksheet. In the Location field, select or enter E22 and click OK. Highlight the PivotTable, and in the PivotTable Fields window, select Invoice # and Outstanding.

In the Invoice Trackerworksheet, insert a PivotTablefor the named range invoicesinto a new sheet that displays Customer Name as the row and Sum of Amount as the value. Make sure the data is not added to the Data Model. Accept all other defaults.

In the Invoice Tracker worksheet, select the Invoice Track Table by selecting any cell within the table. In the Insert tab, go to the Tables group and click PivotTable. In the Create PivotTable window, in the select a table or range, type invoices. Under the Choose where... section, click New Worksheet and click OK. (Make sure the Add this data to the Data Model option is not selected.) Highlight the PivotTable. In the PivotTable Field List window on the right, select Customer Name and Amount. Ensure that Customer Name appears in the Rows area. If it does not, drag it to the approprieate area. Ensure that Sum of Amount appears in the Values area. If it does not, drag it to the appropriate area. If the Amount value shows something other than Sum, you can change it by clicking the dropdown arrow next to the field and selecting Value Field Settings. Then select Sum from the Summarize value field by list and click OK.

In the Invoice Tracker worksheet, assign the Change_Color macro to the button Change Table Color and click the button to run the Macro.

In the Invoice Trackerworksheet, right-click on the Change Table Color button below the table and click Assign Macro... In the Assign Macrodialog window, select the Change_Colormacro from the list and click OK. Click out of the Change Table Color button so that it is no longer selected. Click the Change Table Color button.

In the Invoice Tracker worksheet, under Outstanding Invoices, do a COUNTIFS for each Client to find out how many still need to make payments on their invoices.

In the Invoice Trackerworksheet, select cell C23. In the Formulas tab, go to the Function Library group and click Insert Function. In the Insert Function window, search for COUNTIFS and click GO. Select COUNTIFS in the window below and click OK. In the Function Argumentswindow, for the Criteria_range1 field, select cells J4:J17. In the Criteria1field, enter <>0. In the Criteria_range2 field, select cells E4:E17. In the Criteria2 field, select cell B23and click OK. In cell C24, repeat steps 2 to 4, changing the Criteria2 field to select cell B24. In cell C25, repeat steps 2 to 4, changing the Criteria2 field to select cell B25. In cell C26, repeat steps 2 to 4, changing the Criteria2 field to select cell B26.

In the Office Records worksheet, modify the data validation input message for the title Office Records to be Our office's income and expenses.

In the Office Records worksheet, select the title Office Records, which is cells A4:G4. In the Data tab, Data Tools group, select the dropdown menu for Data Validation and click on Data Validation... Select the Input Message tab. Under Input message:, type Our office's income and expenses. Leave all other values as they are. Click OK.

Share the current workbook so that change history is saved for 90 days. Update the changes automatically every 10 minutes.

In the Review tab, locate the Protect group and select Share Workbook.Share Workbook is now a Legacy feature and may need to be enabled:Go to the File tab and select Options.In the Customize Ribbon group, use the Choose commands from:dropdown to select All Commands.Scroll down in the left window until you find Share Workbook (Legacy).In the right window, select the Review tab and then click New Group.With New Group (Custom) selected, click the Add >>button.Click OK.Share Workbook (Legacy) should now be available under the Reviewtab, New Groupgroup. In the Advanced tab, locate Track Changes. Keep the history for 90 days. Under Update Changes, choose Automatically every: and choose 10minutes. Click OK.

Display all the changes that have ever been made by anyone to this document and not by when, who, or where. Don't highlight the changes on the screen, and list the changes on a new sheet.

In the Review tab, locate the Protect group and select the Track Changes dropdown. Select Highlight Changes...Track Changes is now a Legacy feature and may need to be enabled:Go to the File tab and then Options.In the Customize Ribbon group, use the Choose commands from: dropdown to select All Commands.Scroll down in the left window until you find Track Changes (Legacy).In the right window, select the Review tab and then click New Group.With New Group (Custom) selected, click the Add >> button.Click OK.Track Changes (Legacy) should now be available under the Review tab, New Group group. In the Highlight Changes dialog box, make sure When:, Who:, and Where: are unchecked. Uncheck Highlight changes on screen. Check List changes on a new sheet. Click OK.

In the Sale Orders worksheet, create a SUMIFS function in F7 that will return the Quantity total of the ItemSKUnumbers in the 10000-19999 range.

In the Sale Ordersworksheet, select cell F7. In the Formulas tab, go to the Function Librarygroup and click Insert Function. In the Insert Functionwindow, enter SUMIFSand click Go. Select SUMIFS from the Select a function section and click OK. In the Sum_range field, select or enter C5:C24. In the Criteria_range1field, select or enter B5:B24. In the Criteria1 field, enter >=10000. In the Criteria_range2field, select or enter B5:B24. In the Criteria2 field, enter <20000. Click OK.

In the Sales Orders worksheet, modify the Spin Button values to be between 1 and 100 and change in increments of 1.

In the Sales Orders worksheet, in cell H4, right-click on the Spin Button and click Format Control. In the Format Control window, go to the Control tab. In the Minimum value field, enter 1; in the Maximum value field, enter 100; in the Incremental changefield, enter 1. Click OK.

In the Startup Expenses worksheet, in cell G11 do a COUNTIF for all the Totals below to find out how many are greater than 125,000.

In the Startup Expenses worksheet, select cell G11. In the Formulas tab, go to the Function Library group and click Insert Function. In the Insert Function window, search for COUNTIF and click GO. Select COUNTIF from the window below and click OK. In the Function Arguments window, in the Range field, select cells G15:G28. In the Criteria field, enter >125000 and click OK.

In the Startup Expenses worksheet, create the Spin Button (Form Control) in cell F8:F9 so that it changes the values in cell F7 to numbers 1 - 100 in increments of 1. Accept all other default settings.

In the Startup Expensesworksheet, in the Developer tab, go to the Controls group. In the Insert dropdown, select Spin Button (Form Control). Draw the Spin Button on top of cell F8 to the bottom right of F9. Right-click on the Spin Button and select Format Control. In the Format Controldialog window, set the Minimum value to 1 and Maximum value to 100. Set the Cell link to cell F7 and click OK.

In the Financial Services worksheet, create a macro that applies a Currency number format and a Blue Gradiant Data Bar rule. Name the macro Formatting and store it in only this workbook. Apply the macro to the data in the Financial Data. (Note: Accept all other default settings.)

In the View tab, locate the Macrosgroup, click the Macros dropdown, and select Record Macro... In the Macro name field, enter Formatting. In the Store macro in:dropdown, select This Workbook. Leave all other settings as the default and click OK. In the Home tab, locate the number group and select Currency from the dropdown. In the Home tab, locate the Stylesgroup and select the Conditional Formating dropdown. Select Data Bars and click Blue Data Bar in the Gradient section. In the View tab, locate the Macrosgroup, click the Macros dropdown, and click Stop Recording. Select the cell range B4:G9. In the View tab, locate the Macros group, click the Macros button, and Runthe macro named Formatting.

In the Yearly Charts worksheet, change the January chart to Style 4. Save the chart as a chart template with the name TimecardChart in the GMetrixTemplates folder located in your documents folder.

In the Yearly Chartsworksheet, select the January chart. In the Chart Design tab, go to the Chart Stylesgroup and select Style 4. Right-click the Yearly Charts worksheet and select the Save As Template button. In the Save Chart Template window, locate the GMetrixTemplatesfolder located in your documents folder. In the File name field, enter TimecardChart and click Save.

In the Status Chart worksheet, change the chart layout to Layout 1, change the chart title to Employment History, and change the chart style to Style 9. Save the chart as a chart template to the GMetrixTemplates folder in your documents folder and give it the name EmploymentChart.

Select the Status Chartworksheet. On this worksheet, select the chart. In the Chart Design contextual tab, locate the Chart Layoutsgroup, Quick Layout dropdown, and select Layout 1. Click on Chart Title and type Employment History. In the Chart Design contextual tab, locate the Chart Stylesgroup and click the dropdown. Select Style 9. Right-click the chart and click Save as Template. Navigate to the GMetrixTemplates folder in your documents folder, name the template EmploymentChart, and click Save.

On the Office Records worksheet, modify the sheet protection so that when the sheet is protected, only the cell range E66:G84can be selected. All other cells should not be selectable. Protect the sheet without a password.

Select the entire worksheet by clicking the Select Allbutton in the upper-left corner of the sheet. In the Home tab, locate the Font group and click the Font dialog box launcher. In the Format cells dialog box, select the Protection tab, check the Locked checkbox once, and click OK. Select the Range E66:G84. In the Home tab, locate the Font group and click the Font dialog box launcher. In the Format cells dialog box, select the Protection tab, uncheck the Lockedcheckbox, and click OK. In the Review tab, locate the Protect group and click Protect Sheet. In the Protect Sheet dialog, in the Allow all users of this worksheet to: list, uncheck everything in the list except Select unlocked cells and click OK.