Manage workbook options and settings

Objective 1.2 practice tasks

The practice files for these tasks are located in the MOSExcelExpert2016\Objective1 practice file folder. The folder also contains result files that you can use to check your work.

• Open the ExcelExpert_1-2a workbook and do the following:

  • Unlock the cells in the range C3:C7.

  • Activate worksheet protection. Do not enter a password. Do not allow users to select locked cells or to format cells.

  • Ensure that users can modify the loan parameters in the range C3:C7, but cannot change anything else on the worksheet.

  • Save the ExcelExpert_1-2a workbook.

  • Open the ExcelExpert_1-2a_results workbook. Compare the two workbooks to check your work. Then close the open workbooks.

• Open the ExcelExpert_1-2b workbook and do the following:

  • Unlock the cells in the range B2:B6.

  • Protect the cells in the range B7:B8 with the password MOS123.

  • Protect the worksheet with the same password.

  • Configure the workbook so that users can change the content of the cells in the range B2:B6.

  • Configure the workbook so that users cannot change the formulas in the range B7:B8 without first entering the password.

  • Save the ExcelExpert_1-2b workbook.

  • Open the ExcelExpert_1-2b_results workbook. To unlock the range and sheet, use the password mos. Compare the two workbooks to check your work.

  • Close the ExcelExpert_1-2b_results workbook, but leave the ExcelExpert_1-2b workbook open for later use.

• Open the ExcelExpert_1-2c workbook and do the following:

  • Protect the workbook structure with the password MOS123.

  • Encrypt the workbook with the same password, and then save and close the workbook.

  • Reopen the ExcelExpert_1-2c workbook and verify that you must enter the password to open the workbook.

  • Add a worksheet to the workbook. Verify that you must enter the password before you can change the workbook structure in this way.

  • Save the ExcelExpert_1-2c workbook.

  • Open the ExcelExpert_1-2c_results workbook. To open the workbook and unlock the structure, use the password mos. Compare the two workbooks to check your work.

• Switch to the ExcelExpert_1-2b workbook and do the following:

  • Change the AutoRecover interval to one minute.

  • Edit any cell in the range B2:B6.

  • Don’t save your changes to the workbook. Wait for at least one minute to give Excel time to autosave a version of the unsaved workbook.

  • Display the workbook versions, and then restore the workbook to the version prior to when you made your edit.

• In Excel, verify that formula calculations are set to automatic and iterative calculations are turned off.

• Open the ExcelExpert_1-2d workbook, dismiss the circular reference warning, and do the following:

  • Change the formula calculation method to Manual, and turn on iterative calculations.

  • Select cell C6, which contains the circular reference formula, and manually calculate the formula result.

  • Open the ExcelExpert_1-2d_results workbook. Compare the two workbooks to check your work.

  • To preserve the initial circular reference, close the ExcelExpert_1-2d workbook without saving it.

• Close the open workbooks.