Chapter 3
In This Chapter
Preparing a workbook for distribution
Tracking changes made to a shared workbook
Adding and reviewing comments in a workbook
Adding ink annotations to a workbook on a Tablet PC
Sending a workbook out for review
G iven Excel 2016’s emphasis on saving your workbooks on your OneDrive in the cloud, it behooves you to be familiar not only with Excel’s sharing capabilities (discussed at length in the following chapter), but also with its capabilities for tracking collective editing changes (covered in depth in this chapter), which in effect, enable you and a team of co-workers to work together on creating and editing key spreadsheets.
In this chapter, you discover how to check your workbook to prepare it for distribution and then track the changes made to that workbook after you share it so that your co-workers can simultaneously edit it. You also find out how to merge changes that different workers independently make to the contents of the workbook so that you end up with a single, updated version that you can distribute within the company and outside it.
As part of the review process, you may want to just comment on aspects of the spreadsheet and suggest possible changes rather than make these changes yourself or even mark up the spreadsheet with digital ink if your computer is equipped with a graphics tablet or running Excel 2016 on some sort of touchscreen device such as a Microsoft Surface 3 or Pro tablet. In this chapter, you find out how to get your two cents in by annotating a spreadsheet with text notes that indicate suggested improvements or corrections, as well as highlight potential change areas with ink.
The Info screen in the Excel 2016 Backstage view (Alt+FI) shown in Figure 3-1 enables you to prepare your workbook for distribution by inspecting the properties of your workbook. To do this, click the Check for Issues button in the Info screen and then select any of the following options:
Below the Check for Issues button, the Info screen contains a Manage Workbook button that, when clicked, gives you the following two options for recovering or clearing up draft versions of the workbook so that only the final version is available for sharing:
At the very bottom of the Info screen, you find a Browser View Options button that, when clicked, opens the Browser View Options dialog box with a Show and Parameters tab. This tab enables you to control what parts of your workbook are displayed and can be edited when the file is shared online.
You can add information about your workbook document (called metadata) in the Info panel in the Backstage view, using the various fields displayed in its right column (refer to Figure 3-1), which you open by selecting File ⇒ Info or pressing Alt+FI. You can then use the metadata that you enter into the Title, Tags, Categories, and Author fields in the Info panel in all the Windows quick searches or file searches you perform. Doing so enables you to quickly locate the file for opening in Excel for further editing and printing or distributing to others to review.
When entering more than one piece of data into a particular field such as Title, Tags, or Categories, separate each piece with a comma. When you’re done adding metadata information to the fields, close the Info panel by clicking the File menu at the top of the panel or pressing Esc.
Click the Show All Properties link at the bottom of the right column of the Info screen containing the text boxes for particular document properties to add text boxes for additional document metadata fields including Comments, Template, Status, Subject, Hyperlink Base, Company, and Manager.
Excel 2016 enables you to add digital signatures to the workbook files that you send out for review. After checking the spreadsheet and verifying its accuracy and readiness for distribution, you can (assuming that you have the authority within your company) digitally sign the workbook in one of two ways:
By adding a digital signature, you warrant the following three things about the Excel workbook you’re about to distribute:
To make these assurances, the digital signature you add to the workbook must be valid in the following ways:
To add a digital signature to your finalized workbook, you follow these steps:
Inspect the worksheet data, save all final changes in the workbook file, and then position the cell pointer in a blank cell in the vicinity where you want the signature line graphic object to appear.
Excel adds the signature line graphic object in the area containing the cell pointer. If you don’t move the cell cursor to a blank area, you may have to move the signature line graphic so that graphic’s box doesn’t obscure existing worksheet data or other graphics or embedded charts.
Choose Insert ⇒ Add a Signature Line ⇒ Microsoft Office Signature Line in the Text group on the Ribbon (Alt+NG).
Excel displays the Signature Setup dialog box similar to the one shown in Figure 3-2.
Click OK to close the Signature Setup dialog box.
Excel adds a signature line graphic object in the vicinity of the cell cursor with a big X that contains your name and title (shown in Figure 3-3).
Double-click this signature line graphic object or right-click the object and then choose Sign from its shortcut menu.
If you don’t have a digital ID with one of the subscription services or aren’t a Windows Live subscriber, Excel opens a Get a Digital ID alert dialog box asking you if you want to get one now. Click Yes and then follow the links on the Available Digital IDs Web page to subscribe to one.
Otherwise, Excel opens the Sign dialog box similar to the one shown in Figure 3-3.
Add your signature to the list box containing the insertion point.
To add your signature, click the Select Image link on the right, select a graphic file that contains a picture of your handwritten signature in the Select Signature Image dialog box, and then click Select. If you’re using a touchscreen device or your computer has a digital tablet connected to it, you add this signature by physically signing your signature with digital ink.
Click the Commitment Type drop-down list box and choose one of the options from its drop-down menu: Created and Approved This Document, Approved This Document, or Created This Document.
If you selected the Allow the Signer to Add Comments in the Sign Dialog check box in Step 6, the Sign dialog box contains a Purpose for Signing This Document text box that you fill out in Step 12.
(Optional) Click the Change command button to open the Windows Securities dialog box and then click the name of the person whose certificate you want to use in the list box and click OK.
By default, Excel issues a digital certificate for the person whose name is entered in the Suggested Signer text box back in Step 3. If you want to use a certificate issued to someone else in the organization, follow Step 12. Otherwise, proceed to Step 13.
Click the Sign button to close the Sign dialog box.
Excel closes the Sign dialog box.
Immediately after closing the Sign dialog box, Excel adds your name to the digital signature graphic object and displays the MARKED AS FINAL alert at the top of the worksheet as shown in Figure 3-4.
If you save your workbooks online on your OneDrive or use Excel on a computer that’s connected to a local area network, you can share the spreadsheets that you create with others who have Internet or network access. Workbook sharing is perfect for spreadsheets that require frequent or regular data updates, especially for those whose data comes from several different departments, such as spreadsheets that track budgets or schedule projects that rely on input from many departments.
By sharing a workbook, you enable several people to edit its contents at the same time. Most often, you facilitate this process by saving the workbook file in a folder on your OneDrive and then sharing the workbook (see Book IV, Chapter 4 for details) or local area network drive (often mapped to a drive letter) to which everyone who needs to edit the spreadsheet has access.
You can share an Excel workbook on a local area network in one of two ways:
Whenever you share a workbook using either of these two methods, Excel automatically saves your workbook under the same filename with the shared information. The program then indicates that the workbook can now be shared by appending [Shared] to the workbook’s filename as it appears on the title bar of the Excel program window. When a second person on another computer on the network opens the shared workbook file, Excel opens a copy of the workbook file and the [Shared] indicator also appears on the title bar of his or her Excel program window appended to its filename.
This is in stark contrast to what happens when you try to open an unshared workbook on your computer that’s already open on another computer on the network. In that case, Excel displays a File in Use alert dialog box informing you that the workbook you want to open is already open. You can then choose between clicking the Read Only button to open the file in read-only mode (in which you can’t save your changes under the original filename) and clicking the Notify button to have the program open the file in read-only mode and then notify you when the other person closes the workbook so you can save your changes.
If you click the Notify button, as soon as the other person who was editing the workbook closes the file, Excel then displays a File Now Available alert dialog box, letting you know that the file is now available to save your editing. You then click its Read-Write button to close this alert dialog box, and after that, you’re free to save your editing changes to the original filename with the Save command (Ctrl+S).
Also note that when you make changes to a shared workbook, Excel uses your username to identify the modifications that you made. To modify your username, you edit the contents of the User Name text box on the General tab of the Excel Options dialog box (File ⇒ Options or Alt+FT).
When you share a workbook, Excel disables some of the program’s editing features so they can’t be used in editing the shared spreadsheet. The following tasks are not enabled in a shared workbook:
The first way to share a workbook is by turning on file sharing as follows:
Open the workbook to be shared, convert any data lists formatted as tables to cell ranges, and then make any last-minute edits to the file, especially those that are not supported in a shared workbook.
Keep in mind when making these last-minute changes that when you share a workbook, some of Excel’s editing features become unavailable to you and any others working in the file. (Refer to the list in the previous section for exactly which features are unavailable.)
Before turning on file sharing, you may want to save the workbook in a special folder on a network drive to which everyone who is to edit the file has access.
Click the Share Workbook command button on the Review tab of the Ribbon or press Alt+RW.
Excel opens a Share Workbook dialog box (similar to the one shown in Figure 3-5). This dialog box contains two tabs: an Editing tab that enables you to turn on file sharing for all the users who have the file open, and an Advanced tab, where you control how the amount of time that changes is tracked and how updates are handled.
Select the Allow Changes by More Than One User at the Same Time check box on the Editing tab.
By default, Excel maintains a Change History log for 30 days. If you wish, you can use the Advanced tab settings to modify whether Excel maintains this Change History log (necessary if you want to reconcile and merge changes) or to change how long the program saves this log. You can also change when changes are updated, how conflicts are handled, and whether your print settings and data filtering settings are shared.
(Optional) Click the Advanced tab and then change the options on this tab that affect how long a change log is maintained and how editing conflicts are handled.
See the following section, “Modifying the Workbook Share options,” for details on changing these settings.
Click the OK button to close the Share Workbook dialog box.
As soon as Excel closes the Share Workbook dialog box, an alert dialog box appears, telling you that Excel will now save the workbook and asking you if you want to continue.
Immediately after you click OK and close the alert dialog box, Excel saves the workbook, and the [Shared] indicator appears at the end of the filename on the Excel program window’s title bar.
As soon as you turn on file sharing for a workbook, Excel also turns on a Change History log that records all the changes made by different individuals to the same workbook file. You can use the Change History log to view information about the various changes made to a shared workbook and to determine which of the changes to retain if conflicting changes are made by different people to the same cells of a workbook. You can also use the Change History log when merging changes from different copies of the same workbook into a single file.
By default, Excel maintains the Change History log for a period of 30 days from the date that you first share the workbook. If you wish, you can change the length of time that Excel maintains the Change History log or even, in rare circumstances, elect to not keep the log.
To make changes to the length of time that Excel maintains the Change History log, you click the Advanced tab in the Share Workbook dialog box (Alt+RW), as shown in Figure 3-6. This tab contains the following sections with options for not only changing how long the Change History log is maintained, but also controlling when and how updates are handled:
The second way to share a workbook is by turning on change tracking. When you do this, Excel tracks all changes you make to the contents of the cells in the shared workbook by highlighting their cells and adding comments that summarize the type of change you make. When you turn on change tracking, Excel automatically turns on file sharing along with the workbook’s Change History log.
To turn on change tracking in a workbook, you take these steps:
Open the workbook for which you want to track changes and that you wish to share, convert any data lists formatted as tables to cell ranges, and then make any last-minute edits to the file, especially those that are not supported in a shared workbook.
When making these last-minute changes, keep in mind that, when you share a workbook, some of Excel’s editing features become unavailable to you and any others working in the file. (Refer to the “Workbook Sharing 101” section, earlier in this chapter, for a list of exactly which features are unavailable.)
Before turning on file sharing, you may want to save the workbook in a special folder on a network drive to which everyone who is to edit the file has access.
Choose the Highlight Changes option from the Track Changes command button’s drop-down menu on the Review tab or press Alt+RGH.
Doing this opens the Highlight Changes dialog box, shown in Figure 3-7, where you turn on change tracking and indicate which changes to highlight.
Select the Track Changes While Editing check box.
Doing this turns on change tracking and automatically turns on file sharing for the workbook.
By default, Excel selects the When combo box and chooses the All option from its drop-down menu to have all changes made to the workbook tracked. To track the changes only from the time you last saved the workbook, choose the Since I Last Saved item from the When drop-down menu. To track all changes that you’ve not yet reviewed (and decided whether to accept), you choose the Not Yet Reviewed item from the When drop-down menu. (Most often, you want this option so that you can use the Accept/Reject Changes option on the Track Changes command button’s drop-down menu to review each person’s changes and decide whether to keep them.) To track changes from a particular date, choose Since Date from the When drop-down menu: Excel then inserts the current date into the When combo box, which you can then edit, if necessary.
(Optional) If you don’t want to track all changes in the workbook, click the When drop-down button and then choose the menu item from its drop-down menu (Since I Last Saved, Not Yet Reviewed, or Since Date).
By default, Excel tracks the changes made by anybody who opens and edits the workbook (including you). If you want to exempt yourself from change tracking or restrict it to a particular user, select the Who check box and then choose Everyone But Me or the user’s name from the Who drop-down menu.
(Optional) If you want to restrict change tracking, click the name of the person to whom you want to restrict change tracking in the Who drop-down menu.
Note that selecting any option from the Who drop-down menu automatically selects the Who check box by putting a check mark in it.
By default, changes made to any and all cells in every sheet in the workbook are tracked. To restrict the change tracking to a particular range or nonadjacent cell selection, select the Where check box and then select the cells.
(Optional) If you want to restrict change tracking to a particular cell range or cell selection in the workbook, click the Where combo box and then select the cell range or nonadjacent cell selection in the workbook.
Clicking the Where text box and selecting a cell range in the workbook automatically selects the Where check box by putting a check mark in it.
By default, Excel highlights all editing changes in the cells of the worksheet on the screen by selecting the Highlight Changes on Screen check box. If you don’t want the changes marked in the cells, you need to deselect this check box.
(Optional) If you don’t want changes displayed in the cells onscreen, click the Highlight Changes on Screen check box to clear its check mark.
Note that after you finish saving the workbook as a shared file, you can return to the Highlight Changes dialog box and then select its List Changes on a New Sheet check box to have all your changes listed on a new worksheet added to the workbook. Note too, that if you select this check box when the Highlight Changes on Screen check box is selected, Excel both marks the changes in their cells and lists them on a new sheet. If you deselect the Highlight Changes on Screen check box while the List Changes on a New Sheet check box is selected, Excel just lists the changes on a new worksheet without marking them in the cells of the worksheet.
Click the OK button to close the Highlight Changes dialog box.
As soon as Excel closes the Highlight Changes dialog box, an alert dialog box appears, telling you that Excel will now save the workbook and asking you if you want to continue.
After you turn on change tracking in a shared workbook, Excel highlights the following changes:
When change tracking is turned on in a workbook, the program does not, however, highlight any of these changes:
In highlighting changes you make to the shared workbook, Excel draws a thin line (in another color — usually blue) around the borders of the cell, while at the same time placing a triangle of the same color in the cell’s upper-left corner. When you position the thick white-cross mouse or Touch pointer on a highlighted cell, Excel displays a comment indicating the change made to the cell along with the date and time it was made and who made it, as shown in Figure 3-8.
At some point after sharing a workbook, you’ll want to update the workbook to incorporate the changes made by different users. When merging changes, you may also have to deal with conflicting changes made to the same cells and decide which changes to accept and which to reject. After you’ve merged all the input and decided how to deal with all the conflicting changes, you may even want to turn off file sharing to prevent users from doing any further editing.
To be able to compare versions of a shared workbook and incorporate changes from your co-workers, you need to add the Compare and Merge Workbooks command button to the Quick Access toolbar:
Click the Customize Quick Access Toolbar button at the end of the Quick Access toolbar and then choose the More Commands option.
Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected.
Choose the Commands Not in the Ribbon option from the Choose Commands From drop-down menu.
Excel displays a list of all the commands not currently on the Ribbon in the list box beneath the Choose Commands From drop-down box.
Click the Compare and Merge Workbooks item in the list box below and then click the Add button.
The Compare and Merge Workbooks item appears below the last item in the Quick Access Toolbar list box on the right side of the Excel options dialog box.
Click the OK button to close the Excel Options dialog box.
The Compare and Merge Workbooks button appears at the end of the Quick Access toolbar. (Its icon is an outlined circle.)
When you click the Compare and Merge Workbooks button that you added to the Quick Access toolbar with the original workbook file open in Excel, the program opens the Select Files to Merge Into Current Workbook dialog box. Here, you select the drive and folder that contain the copies of the shared workbook file edited by your co-workers.
Should Excel identify cells in the workbook that contain conflicting changes (that is, different values placed in the same cell by different users) after you select their files in the Select Files to Merge Into Current Workbook dialog box, it flags the cells in the original workbook open in Excel and then displays the conflict in the Resolve Conflicts dialog box. To accept your change to the cell in question, you click the Accept Mine button. To accept the change made by another user, you click the Accept Other button instead.
After you accept your change or the other user’s change in the case of the first conflict, Excel flags the next case and displays a description of the conflicting values in the Resolve Conflicts dialog box. When you finish accepting or rejecting your change or the one made by another user for the last conflicting value, Excel automatically closes the Resolve Conflicts dialog box, and you can save your changes to the workbook by choosing File ⇒ Save or by pressing Ctrl+S.
If you want Excel to accept only your changes in all cases of conflicting values, click the Accept All Mine button. To have Excel reject all your changes and accept all those made by others, click the Accept All Others button instead.
When you turn on change tracking for a workbook, you can decide which changes to accept or reject by choosing the Accept/Reject Changes option from the Track Changes command button’s drop-down menu on the Ribbon’s Review tab (or pressing Alt+RGC). When you do this, Excel reviews all the highlighted changes made by you and others who’ve worked on the shared file, enabling you to accept or reject individual changes.
When you first choose the Accept/Reject Changes option, Excel displays the alert dialog box, informing you that Excel will save the workbook. When you click OK to close this alert dialog box, the program opens the Select Changes to Accept or Reject dialog box, which contains the same three check boxes and associated drop-down items (When, Who, and Where) as the Highlight Changes dialog box shown earlier in Figure 3-7.
By default, the When check box is selected along with the Not Yet Reviewed setting in the Select Changes to Accept or Reject dialog box. When this setting is selected, Excel displays all the changes in the workbook that you haven’t yet reviewed for everyone who has modified the shared file. To review only those changes you made on the current date, choose the Since Date item from the When drop-down menu. To review changes made since a particular date, edit the current date in the Since Date drop-down list.
To review only changes that other people have made, only those changes you’ve made, or only those changes a particular co-worker has made, choose the appropriate item (Everyone But Me, your name, or another user’s name) from the Who drop-down menu. If you want to restrict the review to a particular range or region of a worksheet, click the Where combo box and then select the range or nonadjacent cell ranges with the cells to review.
After you select which changes to review in the Select Changes to Accept or Reject dialog box, click the OK button. Excel then closes this dialog box, highlights the cell in the worksheet that contains the first change to review, and opens the Accept or Reject Changes dialog box (similar to the one shown in Figure 3-9), where you indicate whether to accept or reject the change. To accept the change, you click the Accept button. To reject the change and keep the original value, you click the Reject button instead. Excel then highlights the next cell in the worksheet that needs reviewing, while at the same time displaying a description of the change in the Accept or Reject Changes dialog box.
If you know ahead of time that you want to accept or reject all the changes that have been made since you last reviewed the workbook or the date you specified, click the Accept All button or the Reject All button, respectively. When you accept or reject the last change identified in the workbook, the Accept or Reject Changes dialog box automatically closes, and you can then save the workbook (Ctrl+S) with the editing changes made as a result of this review.
If you decide that you no longer want to share a particular workbook, you can turn off file sharing. To do this, open the Share Workbook dialog box (Alt+RW) and remove the check mark from the Allow Changes by More Than One User at the Same Time check box on the Editing tab before you click OK.
As soon as you click OK, Excel displays an Information alert dialog box indicating that your action is about to remove the workbook from shared use and, at the same time, erase the Change History log. It also informs you that users who are currently editing this workbook will be unable to save their changes even if you should later change your mind and turn the file sharing back on again.
If you’re sure that you’ve met these two conditions, you can click the Yes button in the Information alert dialog box to turn off file sharing and once again make the workbook your exclusive property. Click the No button, however, to abort this procedure if you have any doubt about having all your users’ changes erased.
Sometimes, rather than preventing everyone from sharing a particular workbook, you may only need to stop particular users from being able to edit it. To remove a specific user from sharing in the editing, you open the Share Workbook dialog box (Alt+RW), click the name of the person you want to remove in the Who Has the Workbook Open Now list box, and then click the Remove User button.
As soon as you click this button, Excel displays an alert dialog box informing you that if the user you selected is currently editing the workbook, your action will prevent him or her from saving the workbook so that all unsaved work is automatically lost. To proceed with removing the user, click the OK button in this alert dialog box. To abandon the removal until after you’ve verified that the user isn’t currently editing the file, click the Cancel button instead.
Even if you don’t save your workbooks on a OneDrive or use Excel on a network, you still can add your comments to the cells of a workbook that ask for clarification or suggest changes, and then you can distribute copies of the workbook by e-mail to other people who need to review and, perhaps, respond to your remarks. Excel makes it easy to annotate the cells of a worksheet, and the command buttons on the Review tab of the Ribbon make it easy to review these notes prior to e-mailing the workbook to others who have to review the comments, and even reply to suggested changes.
You can add comments to the current cell by clicking the New Comment command button on the Ribbon’s Review tab or by pressing Alt+RC. Excel responds by adding a Comment box (similar to the one shown in Figure 3-10) with your name listed at the top (or the name of the person who shows up in the User Name text box on the Personalize tab of the Excel Options dialog box). You can then type the text of your comment in this box. When you finish typing the text of the note, click the cell to which you’re attaching the note or any other cell in the worksheet to close the Comment box.
Excel indicates that you’ve attached a comment to a worksheet cell by adding a red triangle to the cell’s upper-right corner. To display the Comment box with its text, you position the thick, white-cross mouse pointer on this red triangle, or you can click the Show All Comments command button on the Review tab (Alt+RA) to display all comments in the worksheet.
When you display a comment by positioning the mouse pointer on the cell’s red triangle, the comment disappears as soon as you move the pointer outside the cell. When you display all the comments on the worksheet by clicking the Show All Comments command button on the Review tab, you must click the Show All Comments button a second time before Excel closes their Comment boxes (or press Alt+RA).
When you first add a comment to a cell, its Comment box appears to the right of the cell with an arrow pointing to the red triangle in the cell’s upper-right corner. If you need to, you can reposition a cell’s Comment box and/or resize it so that it doesn’t obscure certain cells in the immediate region. You can also edit the text of a comment and change the formatting of the text font.
To reposition or resize a Comment box or edit the note text or its font, you make the cell current by putting the cell cursor in it, and then click the Edit Comment command button, which replaces the New Comment button as the first button in the Comments group on the Review tab of the Ribbon, or press Alt+RT. (You can also do this by right-clicking the cell and then choosing Edit Comment from the cell’s shortcut menu.)
Whichever method you use, Excel then displays the cell’s Comment box and positions the insertion point at the end of the comment text. To reposition the Comment box, position the mouse pointer on the edge of the box (indicated with cross-hatching and open circles around the perimeter). When the mouse or Touch pointer assumes the shape of a white arrowhead pointing to a black double-cross, you can then drag the outline of the Comment box to a new position in the worksheet. After you release the mouse button, Excel draws a new line ending in an arrowhead from the repositioned Comment box to the red triangle in the cell’s upper-right corner.
When editing and formatting the comments you’ve added to the worksheet, you can do any of the following:
When you finish making your changes to the Comment box and text, close the Comment box by clicking its cell or any of the other cells in the worksheet.
When you no longer need a comment, you can delete it by selecting its cell before you do any of the following:
By default, Excel chooses the felt tip pen as the pen type for annotating the worksheet with digital ink. If you’d prefer to use a ballpoint pen or highlighter in marking up the worksheet, click the Pen command button or Highlighter command button in the Pens group.
When using the highlighter or either of the two pen types (felt tip or ballpoint pen), you can select a nib from the Pens palette displayed in the middle of the Pens tab. You can also select a new line weight for the ink by choosing the point size (running from 3/4 all the way up to 6 points) from the Thickness command button’s drop-down menu. You can also select a new ink color (yellow being the default color for the highlighter, red for the felt tip pen, and black for ballpoint pen) by clicking its color swatch on the Color command button’s drop-down palette.
After you select the pen nib, color, and line weight, you can use your finger or the stylus that comes with your digital tablet to mark up the spreadsheet as follows:
Figure 3-11 shows a copy of the Income Analysis worksheet after I used the Highlighter and Pen commands on the Pens tab to highlight cell B4 in the worksheet, circle it, and write the comment “verify” in digital ink.
If you make a mistake with ink, you need to remove it and start over again. To delete the ink, select the Eraser command button in the Write group and then tap somewhere on the highlighting, drawing, or handwriting you want to erase with the eraser mouse pointer. (Sometimes you have to drag through the ink to completely remove it.) Then, reselect the highlighter or felt tip or ballpoint pen and reapply your ink annotation.
When you finish marking up the worksheet with ink, click the Stop Inking command button on the Pens tab of the Ink Tools contextual tab. Excel then closes the Ink Tools contextual tab, once again displaying only the normal Ribbon tabs.
18.220.126.5