Objective 1.2 - Prepare Workbooks for Collaboration

How do you read a comment?

1. Move the mouse pointer over a cell that contains a comment (that is, a cell that displays a comment marker in the top-right corner). 2. Display the Comments pane and view the comment in the list.

Formula calculation options

1. Automatic 2. Automatic expect for data tables 3. Manual

How do you enable and configure iterative calculations?

1. Click File, click Options. 2. In the Excel Options dialog box, on the Formulas page, in the Calculation options section, select the Enable iterative calculation check box. 3. In the Maximum Iterations box, enter or select the number of iterations Excel can try before it must stop the calculation. 4. In the Maximum Change box, enter the numeric value that you want Excel to use as a threshold to determine whether the calculation has converged on a solution. 5. Click OK.

How do you start a new comment thread?

1. Click the cell to which you want to add the comment thread. 2. On the Review tab, in the Comments group, click New Comment. Excel creates the comment and displays a pop-up window for typing the comment text. 3. Type your comment in the text box provided; then click Post or press Ctrl+Enter. Excel adds the comment and displays the comment marker in the top-right corner of the cell.

What happens when you protect a workbook's structure?

1. Disables most of the worksheet-related commands on the ribbon. For example, on the Home tab, on the Format menu, the Rename Sheet and Move Or Copy Sheet commands are unavailable. 2. Disables most of the commands on the worksheet tab's shortcut menu, includ- ing Insert, Delete, Rename, and Move or Copy. 3. Keeps the Scenario Manager from creating a summary report.

How do you manually recalculate formulas in a selected cell range?

1. Display the Replace tab of the Find and Replace dialog box. 2. Enter an equal sign (=) in both the Find What and Replace With boxes. 3. Click Replace All.

How do you activate worksheet protection?

1. Do either of the following to open the Protect Sheet dialog box; - On the Review tab, in the Protect group, click Protect Sheet. - If the Allow Users To Edit Ranges dialog box is open, click the Protect Sheet button in that dialog box. 2. In the Protect Sheet dialog box, do the following, and then click OK: Select the Protect worksheet and contents of locked cells check box. If you want, for added security, enter a password in the Password to unprotect sheet box. This means that no one can turn off the worksheet's protection with- out first entering the password. In the Allow all users of this worksheet to list, select the check box beside each action you want unauthorized users to be allowed to perform. 3. If you entered a password, reenter the password, and then click OK to continue working in the worksheet.

When using the dependency tree, when you recalculate a workbook, in what order does Excel update the formulas?

1. Formulas that have changed or that have changed range names. 2. Formulas that include volatile functions. (A volatile function is one where its value changes each time you recalculate or reopen the worksheet or edit any cell on the worksheet; the RAND worksheet function is an example.) 3. Formulas that are dependent on other formulas that have changed, use range names that have changed, or use volatile functions.

What are your options when setting up protection formatting?

1. If you want to protect every cell, you can leave the formatting as it is and turn on worksheet protection. 2. If you want only certain cells to be unlocked (for data entry, for example), you can select those cells and unlock them before turning on worksheet protection. Similarly, if you want certain formulas hidden, you can select the cells and hide their formulas. 3. If you want only certain cells to be locked, first select all the cells and unlock them. Then select the cells you want protected and lock them. To keep only selected formulas visible, hide every formula and then make the formulas you want visible.

How do you protect the workbook's structure?

1. In the workbook you want to protect, on the Review tab, in the Protect group, click Protect Workbook to display the Protect Structure and Windows dialog box. 2. Enter an optional password in the Password text box, and then click OK. 3. Select the Structure check box. 4. If you specified a password, reenter the password to confirm, and then click OK.

How do you edit a comment?

1. Move the mouse pointer over a cell that contains a comment (that is, a cell that displays a comment marker in the top-right corner), move the mouse pointer into the comment box, and then click Edit. (or) 2. Display the Comments pane, click the comment you want to edit, and then click Edit.

How do you respond to a comment?

1. Move the mouse pointer over a cell that contains a comment, click inside the Reply text box, type the response, and then click Post or press Ctrl+Enter. 2. Display the Comments pane, locate the comment to which you want to reply, click inside the Reply text box, type the response, then click Post or press Ctrl+Enter.

How do you delete a comment thread?

1. Move the mouse pointer over a cell that contains a comment, click the More Thread Options (...) button, and then click Delete Thread. 2. Display the Comments pane, locate the comment you want to resolve, click the More Thread Options (...) button, and then click Delete Thread.

How do you resolve a comment thread?

1. Move the mouse pointer over a cell that contains a comment, click the More Thread Options (...) button, and then click Resolve Thread. 2. Display the Comments pane, locate the comment you want to resolve, click the More Thread Options (...) button, and then click Resolve Thread.

How do you navigate worksheet comments?

1. On the Review tab, in the Comments group, click Next Comment to display the next comment that appears in the worksheet. 2. On the Review tab, in the Comments group, click Previous Comment to display the previous comment that appears in the worksheet. 3. On the Review tab, in the Comments group, click Show Comments to open the Comments pane, which displays all the comments that appear in the worksheet. You can also click the Comments button that appears near the right edge of the ribbon.

How do you protect a range with a password?

1. On the Review tab, in the Protect group, click Allow Edit Ranges. 2. In the Allow Users to Edit Ranges dialog box, click New to open the New Range dialog box. 3. In the Title box, enter a name for the range. 4. In the Refers to cells box, enter or select the range you want to protect. 5. In the Range password box, enter a password. 6. If you want the password requirement to not apply to specific users or groups, click Permissions, and then in the Permissions dialog box, do the following: Click Add, enter the name of a user or group, and then click OK to add the user or group to the Permissions dialog box.Click the user or group, and then for the Edit range without a password permission, make sure the Allow check box is selected. Click OK to return to the New Range dialog box. 7. In the New Range dialog box, click OK, reenter the password to confirm it, and then click OK. Excel adds the range to the Allow Users To Edit Ranges dialog box. 8. Repeat steps 2 through 7 to protect other ranges, and then click OK to close the dialog box and save your changes.

How do you protect cells in Excel?

1. Protection formatting 2. Protect a range with a password

How do you show only certain formulas in worksheet cells?

1. Select all the cells in the worksheet. 2. On the Home tab, click Format, and then click Format Cells. 3. In the Format Cells dialog box, on the Protection tab, select the Hidden check box, and then click OK. 4. On the worksheet, select the cells that contain the formulas you want to show. 5. On the Home tab, click Format, and then click Format Cells. 6. In the Format Cells dialog box, on the Protection tab, clear the Hidden check box, and then click OK.

How do you lock certain worksheet cells?

1. Select all the cells in the worksheet. 2. On the Home tab, click Format, and then click to deactivate the Lock Cell command. 3. Select the cells you want to lock. 4. On the Home tab, click Format, and then click to activate the Lock Cell command.

How do you manually recalculate a single formula?

1. Select the cell containing the formula. 2. Click in the formula bar, and then press Enter or click the Enter button.

How do you hide formulas in worksheet cells?

1. Select the cells that contain the formulas you want to hide. 2. On the Home tab, click Format, and then click Format Cells. 3. In the Format Cells dialog box, on the Protection tab, select the Hidden check box, and then click OK.

How do you unlock worksheet cells?

1. Select the cells you want to unlock. 2. On the Home tab, in the Cells group, click Format, and then click to deactivate the Lock Cell command.

Automatic except for data tables calculation

In this calculation mode, Excel recalculates all formulas automatically, except for those associated with data tables. This is a good choice if your worksheet includes one or more massive data tables that are slowing down the recalculation.

How do you manually recalculate formulas in only the active worksheet?

On the Formulas tab, click Calculate Sheet (or Press Shift + F9)

How do you manually recalculate changed formulas in every open workbook?

On the Formulas tab, in the Calculation group, click Calculate Now (Or Press F9)

How do you configure the formula calculation options?

On the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic, Automatic Except for Data Tables, or Manual. (or) 1. Click File, then click Options. 2. In the Excel Options dialog box, on the Formulas page, in the Calculation Options section, under Workbook Calculation, select the option you want. 3. If you select the Manual option and want to run the calculation automatically when you save the file, select the Recalculate workbook before saving check box. 4. Click OK.

How do you manually recalculate every formula (changed or unchanged) in every open workbook?

Press Ctrl + Alt + F9

How do you manually recalculate every formula (changed or unchanged) in every open workbook and rebuild the dependency tree?

Press Ctrl + Alt + Shift + F9

Manual calculation

Select this mode to force Excel not to recalculate any formulas until you either manually recalculate or save the workbook.

Automatic Calculation

This is the default calculation mode; it means that Excel recalculates formulas as soon as you enter them and as soon as the data for a formula changes.

Maximum Iterations

This value is the number of iterations after which Excel must stop the calculation if it hasn't yet converged to a solution. The default value is 100.

Maximum Change

This value is the threshold that Excel uses to determine whether the iterative calculation has converged on a solution. If the change in the formula result from one iteration is less than this value, Excel considers the formula solved and stops the iteration. The default value is 0.001, but you can reduce this (for example, to 0.0001 or 0.00001) if you require a solution with more precision.

Formatting protection doesn't go into effect until you activate worksheet protection (T/F)

True

The range password doesn't go into effect until you activate worksheet protection (T/F)

True

Thread

a collection of one or more comments about a cell

Dependency Tree

a tool that details not only the operands in each workbook formula (such as the functions it calls), but also any formulas used in cells referenced by each formula