Microsoft Excel 2016 Step by Step (2015)
Part 3: Collaborate and share in Excel
14. Collaborate with colleagues
In this chapter
Share workbooks
Save workbooks for electronic distribution
Manage comments
Track and manage colleagues’ changes
Add protection to workbooks and worksheets
Finalize workbooks
Authenticate workbooks
Save workbooks for the web
Import and export XML data
Work with OneDrive and Excel Online
Practice files
For this chapter, use the practice files from the Excel2016SBS\Ch14 folder. For practice file download instructions, see the introduction.
Many individuals have input into business decisions. You and your colleagues can enhance the Excel 2016 workbook data you share by adding comments that offer insight into the information the data represents. If the workbook in which those projections and comments are stored is available on a network or an intranet, you can allow more than one user to access the workbook at a time by turning on workbook sharing, and you can track changes.
If you prefer to limit the number of colleagues who can view and edit your workbooks, you can add password protection to a workbook, worksheet, cell range, or even an individual cell. You can also hide formulas used to calculate values. If you work in an environment in which you exchange files frequently, you can use a digital signature to help verify that your workbooks and any macros they contain are from a trusted source. Finally, if you want to display information on a website, you can do so by saving a workbook as a webpage.
This chapter guides you through procedures related to sharing a workbook, saving workbooks for electronic distribution and for the web, managing comments in workbook cells, tracking and managing colleagues’ changes, adding protection to workbooks and worksheets, finalizing and authenticating workbooks, importing and exporting XML data, and working with OneDrive and Excel Online.
Share workbooks
If you want several users to be able to edit a workbook simultaneously, you must turn on workbook sharing. Workbook sharing works well for businesses where multiple users might require access to a file at the same time. When you share a workbook, you can set the sharing options to maintain a change history and manage which changes take priority.
Define how Excel should manage your shared workbook
Important
You can’t share a workbook that contains an Excel table. If you do want to share a workbook that contains an Excel table, convert the Excel table to a regular cell range.
On the Advanced page of the Share Workbook dialog box, two settings are of particular interest. The first determines whether Excel should maintain a history of changes made to the workbook and, if so, for how many days it should keep the history. The default setting is for the app to retain a record of all changes made in the past 30 days, but you can enter any number of days you want. If you revisit your workbook on a regular basis, maintaining a list of all changes for the past 180 days might be reasonable. For a workbook that changes less frequently, a history reaching back 365 days (one year) could meet your tracking and auditing needs. Excel rejects and deletes the record of any changes made earlier than the time you set.
Tip
You should find out whether your organization has an information retention policy that would affect the amount of time you should keep your workbooks’ change histories.
The other important setting on this page deals with how Excel decides which of two conflicting changes in a cell should be applied. You can have the most recent changes win or have Excel let you review the changes to indicate which ones to keep.
When you share a workbook, you and your colleagues can turn off change tracking and have Excel stop noting changes to the file. You can, if you want, require a password to turn off change tracking in a shared workbook.
There are two main ways to share a workbook with your colleagues: you can make it available over your organization’s network, or you can send a copy of the file to your colleagues via email. Every organization’s network is different, so you should check with your network administrators to determine the best way to share a file. Although the specific command to attach a file to an email message is different in every email app, the most common method of attaching a file is to create a new email message and then click the Attach button, as in Microsoft Outlook 2016.
To share a workbook
1. On the Review tab of the ribbon, in the Changes group, click Share Workbook.
2. On the Editing tab of the Share Workbook dialog box, select the Allow changes by more than one user at the same time check box.
3. Change the settings on the Advanced tab of the Share Workbook dialog box to do any of the following:
• Control how long the change history is maintained.
• Indicate when to update changes.
• Indicate how to handle conflicting changes.
• Include print settings or personal settings in each user’s personal view of the workbook.
4. Click OK to close the Share Workbook dialog box.
5. In the confirmation dialog box that appears, click OK to save and share the workbook.
To require a password to turn off change tracking
1. In the Changes group, click Protect and Share Workbook.
Important
The Protect And Share Workbook button is activated only when the workbook is not shared.
2. In the Protect Shared Workbook dialog box, select the Sharing with track changes check box.
Require a password to turn off change tracking in a workbook
3. In the Password box, enter a password.
4. Click OK.
5. In the Confirm Password dialog box, re-enter the password and click OK.
6. Click OK to verify that you want to save the workbook.
To unprotect a shared workbook
1. In the Changes group, click Unprotect Shared Workbook.
2. In the Unprotect Sharing dialog box, enter the password.
3. Click OK.
4. In the message box that appears, click Yes to remove the password.
To end workbook sharing
1. In the Changes group, click Share Workbook.
2. On the Editing tab of the Share Workbook dialog box, clear the Allow changes by more than one user at the same time check box.
3. Click OK.
4. In the message box that appears, click Yes to remove the workbook from shared use, erase the change history, and prevent other users who are currently editing the workbook from saving their changes.
To send a workbook as an email attachment from within Excel by using Outlook
Important
You must have Outlook configured on your system to follow this procedure.
1. Display the Backstage view, and then click Share.
2. Click Email.
3. Click Send as Attachment.
4. Create and send the message in Outlook 2016.
Save workbooks for electronic distribution
Even though most businesses use Excel, there might be times when you want to distribute a copy of your data in a file other than an Excel workbook. You can create a read-only copy of a workbook for electronic distribution by saving it as a PDF or XML Paper Specification (XPS) file.
Export a workbook as a PDF or XPS file
Publishing your workbook as a PDF or XPS document gives your colleagues the information they need to make decisions in an easily readable format that also preserves the integrity of your data.
Tip
You can also save a workbook as a PDF or XPS document by clicking Save As in the Backstage view. Then, in the Save As dialog box, in the Save As Type list, select either PDF or XPS to create a file of the type you want.
To export a workbook as a PDF or XPS file
1. Display the Backstage view, and then click Export.
2. If necessary, click Create PDF/XPS Document, and then click Create PDF/XPS.
3. In the Publish as PDF or XPS dialog box, in the File name box, enter a name for the file.
4. Click the Save as type arrow and select the target file type.
5. Use the navigation tools to display the folder to which you want to export the file.
6. Set the output options you want to apply to the file, choosing either to publish it at standard size, which is appropriate for publishing online or by printing, or to minimize file size for online-only publishing.
7. Click Publish.
Manage comments
Excel makes it easy for you and your colleagues to insert comments in workbook cells, adding insights that go beyond the cell data. When you add a comment to a cell, a flag appears in the upper-right corner of the cell. When you point to a cell that contains a comment, the comment appears in a box next to the cell, along with the user name of the user who was logged on to the computer on which the comment was created.
An Excel worksheet with a comment in cell D4
Important
Note that the name attributed to a comment might not be the same as the name of the person who actually created it. Access controls, such as those that require users to enter account names and passwords when they access a computer, can help track the person who made a comment or change.
Normally, Excel only displays a cell’s comment when you point to the cell. You can change that behavior to display an individual comment or to show all comments within a worksheet. If you want to edit a comment, you can do so, or you can delete a comment from your workbook.
Manage comments by using the tools on the Review tab of the ribbon
Important
When someone other than the original user edits a comment, that person’s input is marked with the new user’s name and is added to the original comment.
You can control whether a cell displays just the comment indicator or the indicator and the comment itself. Also, if you’ve just begun to review a worksheet, you can display all of the comments on the sheet or move through them one at a time.
To add a comment to a cell
1. Click the cell where you want to add a comment.
2. On the Review tab of the ribbon, in the Comments group, click New Comment.
Or
Right-click the cell, and then click Insert Comment.
3. In the comment box that appears, enter a comment.
4. Click away from the cell to close the comment box.
To display a comment
1. Point to the cell that contains the comment.
To show or hide a comment
1. Click the cell that contains the comment.
2. In the Comments group, click Show/Hide Comment.
Or
Right-click the cell, and then click Show/Hide Comments.
To edit a comment
1. Click the cell that contains the comment.
2. In the Comments group, click Edit Comment.
Or
Right-click the cell, and then click Edit Comment.
3. In the comment box that appears, edit the text of the comment.
4. Click away from the cell to close the comment box.
To delete a comment
1. Click the cell that contains the comment.
2. In the Comments group, click Delete.
Or
Right-click the cell, and then click Delete Comment.
To change how Excel indicates that a cell contains a comment
1. Display the Backstage view, and then click Options.
2. In the Excel Options dialog box, click Advanced.
Manage how Excel displays comments
3. In the Display section of the Advanced page, select one of the three available comment display options:
• No comments or indicators
• Indicators only, and comments on hover
• Comments and indicators
To display or hide all comments
1. In the Comments group, click Show All Comments.
To move through worksheet comments
1. In the Comments group, do either of the following:
• Click Previous to display the previous comment.
• Click Next to display the next comment.
Track and manage colleagues’ changes
Whenever you collaborate with your colleagues to produce or edit a document, you should consider tracking the changes each user makes. When you turn on change tracking, any changes made to the workbook are highlighted in a color assigned to the user who made the changes. One benefit of tracking changes is that if you have a question about a change, you can quickly identify who made the change and verify that it is correct.
Turn on change tracking in the Highlight Changes dialog box
Tip
Selecting the When check box and choosing the All option has the same effect as clearing the check box.
You can use the commands in the Highlight Changes dialog box to choose which changes to track. Most commonly you will have Excel track all changes, but you can also specify a time frame, users, or areas of the workbook to limit which changes are highlighted. Each user’s changes are displayed in a unique color. When you point to a cell that contains a change, the date and time when the change was made and the name of the user who made it appear as a ScreenTip.
Point to a cell with a tracked change to see a summary of the change
After you and your colleagues finish modifying a workbook, anyone with permission to open the workbook can decide which changes to accept and which changes to reject. You can select which changes to review and then accept or reject individual changes.
Review individual changes in your workbook
Tip
When you and your colleagues have finished making changes, you should turn off workbook sharing to help ensure that you are the only person able to review the changes and decide which to accept.
Important
Clicking the Undo button on the Quick Access Toolbar or pressing Ctrl+Z will not undo accepting or rejecting a change.
To turn on change tracking
1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
2. Select the Track changes while editing check box.
3. Use the When, Who, and Where boxes to specify which changes to track.
4. Click OK.
5. In the confirmation dialog box that appears, click OK to save the workbook and start tracking changes.
Important
Turning off change tracking erases the changes you and your colleagues have made.
To accept and reject changes
1. With change tracking turned on, click Track Changes, and then click Accept/Reject Changes.
2. In the confirmation dialog box that appears, click OK to save the workbook and continue.
3. In the Select Changes to Accept or Reject dialog box, specify which changes to review.
4. Click OK.
5. In the Accept or Reject Changes dialog box, perform any of these actions:
• Click Accept to accept the current change.
• Click Reject to reject the current change.
• Click Accept All to accept all changes.
• Click Reject All to reject all changes.
• Click Close to stop reviewing changes and close the dialog box.
To turn off change tracking
1. Click Track Changes, and then click Highlight Changes.
2. Clear the Track changes while editing check box.
3. Click OK.
4. In the confirmation dialog box that appears, click Yes to stop tracking changes and remove the workbook from shared use.
Add protection to workbooks and worksheets
You can use Excel to share your workbooks on the web, on a corporate intranet, or by copying files for other users to take on business trips. An important part of sharing files, however, is ensuring that only those users you want to have access to the files can open or modify them. It doesn’t help a company to have unauthorized personnel, even those with good intentions, accessing critical workbooks.
You can limit access to your workbooks or elements within workbooks by setting passwords. When you set a password for an Excel workbook, any users who want to access the protected workbook must enter the workbook’s password in a dialog box that opens when they try to open the file. If users don’t know the password, they cannot open the workbook.
Encrypt a workbook by setting a password to open the file
To remove the passwords from a workbook, repeat these steps, but delete the passwords rather than setting them.
Tip
The best passwords are long strings of random characters, but random characters are hard to remember. One reasonable method of creating hard-to-guess passwords is to string two or more words and a number together. For example, the password genuinestarcalibration302 is 24 characters long, combines letters and numbers, and is easy to remember. If you must create a shorter password to meet a system’s constraints, avoid dictionary words and include uppercase letters, lowercase letters, numbers, and any special symbols such as ! or # if they are allowed.
If you want to allow anyone to open a workbook but want to prevent unauthorized users from editing a worksheet, you can protect an individual worksheet. You can also set a password that a user must type in before protection can be turned off, and choose which elements of the worksheet a user can change while protection is turned on.
Limit the worksheet elements that a user can edit without a password
The check box at the top of the list of allowed actions in the Protect Sheet dialog box mentions locked cells. A locked cell is a cell that can’t be changed when worksheet protection is turned on. You can lock or unlock a cell by changing the cell’s formatting. When worksheet protection is turned on, selecting the Locked check box prevents unauthorized users from changing the contents or formatting of the locked cell, whereas selecting the Hidden check box hides the formulas in the cell. You might want to hide the formula in a cell if you draw sensitive data, such as customer contact information, from another workbook and don’t want casual users to see the name of the workbook in a formula.
Finally, you can password-protect a cell range. For example, you might want to let users enter values in most worksheet cells but also want to protect the cells with formulas that perform calculations based on those values.
Define ranges users can edit after a worksheet is protected
Tip
Remember that a range of cells can mean just one cell!
To require a password to open a workbook
1. Display the Backstage view, and then click Info.
2. Click Protect Workbook, and then click Encrypt with Password.
3. In the Encrypt Document dialog box, enter a password for the file.
4. Click OK.
5. In the Confirm Password dialog box, re-enter the password, and then click OK.
To remove a password from a workbook
1. Open the password-protected workbook.
2. On the Info page of the Backstage view, click Protect Workbook, and then click Encrypt with Password.
3. In the Encrypt Document dialog box, delete the existing password.
4. Click OK.
To require a password to change workbook structure
1. On the Review tab of the ribbon, in the Changes group, click Protect Workbook.
2. In the Protect Structure and Windows dialog box, enter a password for the workbook.
3. Click OK.
4. In the Confirm Password dialog box, re-enter the password.
5. Click OK.
To remove a password that protects a workbook’s structure
1. Click Protect Workbook.
2. In the Unprotect Workbook dialog box, enter the workbook’s password.
3. Click OK.
To protect a worksheet by setting a password
1. In the Changes group, click Protect Sheet.
2. In the Protect Sheet dialog box, enter a password in the Password to unprotect sheet box.
3. Select the check boxes next to the actions you want to allow users to perform.
4. Click OK.
5. In the Confirm Password dialog box, re-enter the password.
6. Click OK.
To remove a worksheet password
1. In the Changes group, click Unprotect Sheet.
2. In the Unprotect Sheet dialog box, enter the worksheet’s password.
3. Click OK.
To lock a cell to prevent editing
1. Right-click the cell you want to lock, and then click Format Cells.
2. In the Format Cells dialog box, click the Protection tab.
Prevent cell editing and hide formulas when you protect a sheet
3. Select the Locked check box.
4. Click OK.
To hide cell formulas
1. Right-click the cell you want to lock, and then click Format Cells.
2. Click the Protection tab.
3. Select the Hidden check box.
4. Click OK.
Important
You must protect your worksheet for the Locked and Hidden settings to take effect.
To restrict editing of a cell range by using a password
1. In the Changes group, click Allow Users to Edit Ranges.
2. In the Allow Users to Edit Ranges dialog box, click New.
3. In the New Range dialog box, in the Title box, enter a title for the range.
4. Click in the Refers to cells box and select the cell range you want to affect.
5. In the Range password box, enter the password for the range.
6. Click OK.
7. In the Confirm Password dialog box, re-enter the password.
8. Click OK.
Define cell ranges users are allowed to edit
9. Repeat steps 2 through 8 to protect another cell range.
10. Click OK.
Important
You must protect your worksheet for the range password settings to take effect.
To remove a cell range password
1. Click Allow Users to Edit Ranges.
2. In the Allow Users to Edit Ranges dialog box, click the range you want to edit.
3. Click Delete.
4. Click OK.
Finalize workbooks
Distributing a workbook to other users carries many risks, not the least of which is the possibility that the workbook might contain private information you don’t want to share with users outside your organization. With Excel, you can inspect a workbook for information you might not want to distribute to other people, and create a read-only final version that prevents other people from making changes to the workbook content.
By using the Document Inspector, you can quickly locate comments and annotations, document properties and personal information, custom XML data, headers and footers, hidden rows and columns, hidden worksheets, and invisible content. You can then easily remove any hidden or personal information that the Document Inspector finds.
Check for personally identifiable information by using the Document Inspector
The Document Inspector checks your document for every category of information that is selected in the list. When the Document Inspector displays its results, you can select which pieces of personally identifiable information you want to remove.
When you’re done making changes to a workbook, you can mark it as final. Marking a workbook as final sets the status property to Final and turns off data entry and editing commands. If you later decide that you want to make more changes, you can do so, save your changes, and mark the worksheet final again.
To remove personally identifiable information from a workbook
1. Press Ctrl+S to save the workbook.
2. Display the Backstage view and, if necessary, click Info.
3. Click Check for Issues, and then click Inspect Document.
4. Select the check box next to each category of information you want the Document Inspector to look for.
5. Click Inspect.
6. In the results list, click the Remove All button next to any category of information you want to remove.
7. If necessary, click Reinspect and then click Inspect to ensure that no personal information remains in the file.
8. Click Close.
To mark a workbook as final
1. Press Ctrl+S to save the workbook.
2. On the Info page of the Backstage view, click Protect Workbook, and then click Mark as Final.
3. In the confirmation dialog box that appears, click OK.
4. In the informational dialog box that appears, click OK.
Tip
To edit a file that has been marked as final, open the file and then, on the message bar, click Edit Anyway.
Authenticate workbooks
The unfortunate reality of exchanging files over networks, especially over the Internet, is that you need to be sure you know the origin of the files you’re working with. One way an organization can guard against files with viruses or substitute data is to authenticate every workbook by using a digital signature. A digital signature is a character string created by combining a user’s unique digital certificate mathematically with the contents of the workbook, which apps such as Excel can recognize and use to verify the identity of the user who signed the file. A good analogy for a digital signature is a wax seal, which was used for thousands of years to verify the integrity and origin of a document.
The technical details of and procedure for managing digital certificates are beyond the scope of this book, but your network administrator should be able to create or obtain a digital certificate for you. You can also directly purchase a digital signature from a third party; these signatures can usually be renewed annually for a small fee. For the purposes of this book, you can use the selfcert.exe Microsoft Office accessory app to generate a certificate with which to perform this topic’s practice task at the end of this chapter. This type of certificate is useful for certifying a document as part of a demonstration, but other users might not accept it as a valid certificate.
Tip
When you click Add A Digital Signature in the Protect Workbook list on the Info page of the Backstage view, Excel checks your computer for usable digital certificates. If it can’t find one, Excel displays a dialog box indicating that you can buy digital signatures from third-party providers. You won’t be able to add a digital signature to a file until you acquire a digital certificate, either by generating a test certificate using the included selfcert.exe app or by purchasing one through a third-party vendor.
If you have several certificates from which to choose, and the certificate you want doesn’t appear when you attempt to sign your file, you can change the chosen certificate and start the signing process again.
Important
Editing a workbook that has a digital signature invalidates the signature. To verify the file, you must sign it again.
To display available third-party vendors of digital certificates
1. Display the Backstage view and, if necessary, click Info.
2. Click Protect Workbook.
3. Click Add a Digital Signature.
4. In the Get a Digital ID dialog box, click Yes.
Or
1. Go to https://support.office.com in your web browser.
2. Enter Digital ID in the search box.
3. Click the web resource with the title Get a digital ID.
To create a test certificate by using selfcert.exe
1. In File Explorer, open the C:\Program Files (x86)\Microsoft Office\root\Office16 folder and double-click selfcert.exe.
2. In the Create Digital Signature dialog box, enter a name for your certificate.
3. Click OK.
4. In the confirmation dialog box that appears, click OK.
To authenticate a workbook by using a digital signature
1. Press Ctrl+S to save the workbook.
2. On the Info page of the Backstage view, click Protect Workbook, and then click Add a Digital Signature.
3. In the Sign dialog box, click the Commitment Type arrow and select the role you played in creating and approving the document.
4. In the Purpose for signing this document box, enter a reason for signing the file.
Authenticate a workbook by signing it with a digital certificate
5. If necessary, click Change and use the tools in the Windows Security dialog box to select a digital certificate.
6. Click Sign.
Save workbooks for the web
You can use Excel to save your workbooks as web documents so that you and your colleagues can view workbooks over the Internet or on an organization’s intranet. For a document to be viewable on the web, it must be saved as an HTML file. HTML files, which end with either the .htm or the .html extension, include tags that tell a web browser such as Microsoft Edge how to display the contents of the file.
For example, you might want to set the data labels in a workbook apart from the rest of the data by having the labels displayed with bold text. The coding in an HTML file that indicates text to be displayed as bold text is <b>...</b>, where the ellipsis between the tags is replaced by the text to be displayed. So the following HTML fragment would be displayed as Excel in a webpage.
<b>Excel</b>
Tip
If the only sheet in your workbook that contains data is the one displayed when you save the workbook as a webpage, Excel only saves that worksheet as a webpage.
After you save an Excel workbook as a set of HTML documents, you can open it in your web browser. It’s also possible to save a workbook as a web file that retains a link to the original workbook. Whenever someone updates the workbook, Excel updates the web files to reflect the new content.
Select which elements of a workbook to publish to the web
You can select which elements of your workbook to publish to the web in the Publish As Web Page dialog box. Clicking the Choose arrow displays a list of publishable items, including the option to publish the entire workbook, items on specific sheets, or a range of cells. You can also specify which text appears on the webpage’s title bar.
To save a workbook as a web file
1. Display the Backstage view, and then click Save As.
2. Click Browse.
3. In the Save As dialog box, click the Save as type arrow, and then click Web Page.
4. If necessary, in the File name box, edit the name of the file.
5. Click Save.
6. If necessary, in the dialog box that appears, click Yes to acknowledge that some features might be lost when you save the workbook as a webpage.
To publish a workbook to the web
1. Display the Backstage view, and then click Save As.
2. Click Browse.
3. In the Save As dialog box, click the Save as type arrow, and then click Web Page.
4. If necessary, in the File name box, edit the name of the file.
5. Select Entire Workbook to publish the entire workbook.
Or
Select Selection: Sheet to publish the active worksheet.
6. Click Publish.
7. In the Publish as Web Page dialog box, click the item you want to publish.
8. If necessary, select the AutoRepublish every time this workbook is saved check box.
9. Click Publish.
Import and export XML data
HTML lets you determine how a document will be displayed in a web browser—for example, by telling Internet Explorer to display certain text in bold type or to start a new paragraph. However, HTML doesn’t tell you anything about the meaning of data in a document. Internet Explorer might “know” it should display a set of data in a table, but it wouldn’t “know” that the data represented an Excel worksheet.
You can add metadata, or data about data, to web documents by using Extensible Markup Language (XML). Though a full discussion of XML is beyond the scope of this book, the following bit of XML code shows how you might identify two sets of three values (Month, Category, and Exceptions) by using XML.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:exceptions xmlns:ns2="http://www.w3schools.com">
<exception>
<Month>January</Month>
<Category>2Day</Category>
<Exceptions>14</Exceptions>
</exception>
<exception>
<Month>January</Month>
<Category>3Day</Category>
<Exceptions>3</Exceptions>
</exception>
</ns2:exceptions>
XML is meant to be a universal language, allowing data to move freely from one app to another. Excel might display those two sets of exceptions data as rows of data in an Excel worksheet.
Data imported from an XML file can be displayed in an Excel worksheet
Other apps could display or process the XML file’s contents in other ways, but you wouldn’t have to change the underlying XML file. All of the work is done by the other apps’ programmers. To work with XML data in Excel, you must use the controls on the Developer ribbon tab, which you can display by using the ribbon customization commands available in the Excel Options dialog box.
Display the Developer tab on the ribbon from the Excel Options dialog box
You can bring XML data into Excel, either by opening a workbook saved in a compatible XML format or by importing the data from a text file. XML data is organized according to a specified schema, or structure. If the schema file isn’t available, you can have Excel look at the structure of the imported data and create one for you. If you export a worksheet to an XML file, you can have Excel create a schema for that operation, too.
Tip
If you have imported an XML file but believe that the original XML data file has changed, click the Refresh Data button in the XML group on the Developer tab to update your worksheet.
Select an XML source file by using the Import XML dialog box
To save a workbook as an XML file
1. Display the Backstage view, and then click Save As.
2. Click Browse.
3. In the Save As dialog box, click the Save as type arrow, and then click one of the following file types:
• XML Data
• XML Spreadsheet 2003
• Strict Open XML Spreadsheet
4. If necessary, in the File name box, edit the name of the file.
5. Click Save.
To import an XML data file into a workbook
1. If necessary, use the tools in the Excel Options dialog box to add the Developer tab to the ribbon.
2. On the Developer tab of the ribbon, in the XML group, click Import.
3. In the Import XML dialog box, navigate to the folder that contains the file you want to import, click the file, and then click Open.
4. If necessary, in the dialog box that indicates that the XML source file does not refer to a schema, click OK to have Excel create a schema for you.
5. In the Import Data dialog box, do either of the following:
• Select XML table in existing worksheet and click the cell where you want the XML table to start.
• Select XML table in new worksheet.
6. Click OK.
To export a cell range as an XML data file
1. Click a cell in an XML data range.
2. In the XML group, click Export.
3. In the Export XML dialog box, navigate to the folder where you want to export the XML data.
4. In the File name box, enter a name for the file.
5. Click Export.
Work with OneDrive and Excel Online
As information workers become increasingly mobile, they need to access their data from anywhere and have a single version of a file to which they can turn. Excel 2016 is integrated with OneDrive, a Microsoft cloud service that stores your files remotely and provides you with access to them over the Internet.
You can find OneDrive online at www.onedrive.com. You will need a Microsoft account to use OneDrive.
Sign in to OneDrive at www.onedrive.com
When you sign in to OneDrive, you’ll see the main directory of your OneDrive account.
OneDrive manages files and folders like File Explorer
You can manage files by using the built-in interface, performing familiar tasks such as opening, creating, uploading, downloading, and copying files. You can also navigate the file structure, moving between folders and creating or deleting them as needed.
Create new folders and documents by using the OneDrive New menu
Clicking the New button also displays links to create a folder, Word document, Excel workbook, PowerPoint presentation, OneNote notebook, Excel survey, or plain text document. When you create a new Excel workbook from this menu, Excel Online starts and you can begin adding data to the new workbook.
Create Excel workbooks in OneDrive by using Excel Online
Tip
Excel Online saves your workbook every time you edit a cell, so there’s no Save button on the Quick Access Toolbar.
Excel Online provides a rich set of capabilities you can use to create new workbooks and edit workbooks you created in the desktop edition of the app. If you find you need some features that aren’t available in Excel Online, you can always open the file in the Excel 2016 desktop app.
Important
You might see a series of dialog boxes asking you to sign back in to your Microsoft account and to provide other information. These queries are normal and expected.
If you want to collaborate with colleagues who also have OneDrive accounts, you can share your Excel workbook with them online. You can choose how to share your workbook, either by allowing your colleagues to edit the file or just view it, or you can require them to access the file from a Microsoft account or just over the web.
To sign in to OneDrive
1. In your web browser, go to www.onedrive.com.
2. Click Sign in.
3. Enter your account name (usually an email address), and then press the Enter key.
4. Enter your password, and then press Enter.
To upload a file or folder to OneDrive
1. In OneDrive, click the Upload button on the toolbar.
2. In the Open dialog box, select the files or folder you want to upload.
3. Click Open.
To download a file from OneDrive
1. Point to the icon representing the file you want to download, and select the round check box that appears in the upper-right corner of the icon.
2. On the menu bar, click Download to download the file to your computer’s Downloads folder.
To create a new Excel workbook in OneDrive
1. Open your OneDrive account in your web browser.
2. Click New, and then click Excel workbook.
To open an Excel workbook stored in OneDrive in the desktop edition of Excel
1. Open your OneDrive account in your web browser.
2. Click the file you want to work with to open it in Excel Online.
Tip
Depending on your computer’s settings, the order and appearance of dialog boxes and messages might differ slightly from what is described here.
3. Click Open in Excel.
4. In the External Protocol Request dialog box, click Launch Application.
5. In the alert dialog box that appears, click Yes.
6. If necessary, in the Sign in dialog box, enter your email address.
7. If necessary, enter your password into the Password box, and click Sign in.
8. When you’re done working with the file in Excel, close Excel and any remaining dialog boxes from Excel Online.
To collaborate with colleagues by using Excel Online
1. Open a workbook in Excel Online.
Share a workbook by using Excel Online
2. On the title bar of the workbook, click Share.
3. In the Share dialog box, in the To box, enter the email addresses of individuals with whom you want to share the workbook. To add multiple addresses, enter the first address and press the Tab key.
4. If you want to include a note, enter it into the Add a quick note box.
5. To change sharing characteristics, click Recipients can edit and then do any of the following:
• Click the Recipients can edit box, and then click Recipients can only view.
• Click the Recipients don’t need a Microsoft account box, and then click Recipients need to sign in with a Microsoft account.
6. Click Share.
7. In the confirmation dialog box that appears, click Close.
Skills review
In this chapter, you learned how to:
Share workbooks
Save workbooks for electronic distribution
Manage comments
Track and manage colleagues’ changes
Add protection to workbooks and worksheets
Finalize workbooks
Authenticate workbooks
Save workbooks for the web
Import and export XML data
Work with OneDrive and Excel Online
Practice tasks
The practice files for these tasks are located in the Excel2016SBS\Ch14 folder. You can save the results of the tasks in the same folder.
Share workbooks
Open the ShareWorkbooks workbook in Excel, and then perform the following tasks:
1. Share the workbook and, if you’re on a network, invite a colleague to edit the file.
2. Set a password that prevents unauthorized users from turning off change tracking.
3. Unshare, save, and close the workbook, and then send it to yourself or a colleague as an email attachment.
Save workbooks for electronic distribution
Open the DistributeFiles workbook in Excel, and then perform the following tasks:
1. Display the Sheet1 worksheet of the workbook and export it as a PDF file.
2. Export the entire workbook as an XPS file.
Manage comments
Open the ManageComments workbook in Excel, and then perform the following tasks:
1. Add comments to four or five cells with relatively high and low values.
2. Edit one of the comments to invite a colleague to provide input for that value.
3. Move through the comments, going forward and backward through the list.
4. Change the workbook so it displays all comments.
5. Delete a comment.
Track and manage colleagues’ changes
Open the TrackChanges workbook in Excel, and then perform the following tasks:
1. Turn on change tracking, and then edit five values in the worksheet.
2. Review the changes, accepting a few and rejecting a few.
3. Turn change tracking off.
Add protection to workbooks and worksheets
Open the ProtectWorkbooks workbook in Excel, and then perform the following tasks:
1. By using the controls on the Info page of the Backstage view, encrypt the workbook with a password.
2. On the Performance worksheet, click cell B8 and format the cell so its contents are locked and hidden.
3. By using the controls on the Review tab, protect the active worksheet with a password after clearing the Select locked cells and Select unlocked cells check boxes in the dialog box.
4. On the Weights worksheet, select cells A3:B6 and define a protected range named AllWeights.
5. Protect the Weights worksheet by requiring users to enter a password to edit it.
Finalize workbooks
Open the FinalizeWorkbooks workbook in Excel, and then perform the following tasks:
1. Inspect the workbook by using the Document Inspector, and remove any personally identifiable information from the file.
2. Use the tools on the Info page of the Backstage view to mark the file as final.
3. Close the workbook. Then reopen it and click the Edit Anyway button on the message bar to work with the file.
4. Save any changes and close the workbook.
Authenticate workbooks
Open the AuthenticateWorkbooks workbook in Excel, and then perform the following tasks:
1. Acquire or create a digital certificate.
2. Sign the workbook and give the reason for signing it as Testing procedure for later use in business.
Save workbooks for the web
Open the SaveForWeb workbook in Excel, and then perform the following tasks:
1. Display the Sheet1 worksheet in the workbook, and then save that worksheet as a web file named ShipmentSummaryWeb.
2. Close the web file and, if necessary, reopen the SaveForWeb workbook.
3. Display Sheet2 of the workbook, and then publish the PivotTable on Sheet2 to the web. Set the workbook to autorepublish the web file every time the original workbook changes.
4. Select the Open published web page in browser check box, and then publish the file.
Import and export XML data
Open the ImportXMLData workbook in Excel, and then perform the following tasks:
1. Import data from the ExceptionTracking.xml file in the Excel2016SBS\Ch14 folder.
2. Export the data you just imported to a new file named ExportXML.xml.
3. Save your workbook in one of the XML-based formats available in the Save As dialog box.
Work with OneDrive and Excel Online
Open the ManageOneDrive workbook in Excel, and then perform the following tasks:
1. If necessary, create a new OneDrive account.
2. Sign in to a OneDrive account.
3. Upload the ManageOneDrive workbook to your OneDrive account.
4. Open the ManageOneDrive file in Excel Online.
5. Open the file in the desktop edition of Excel.
6. Add a row of data showing April exceptions in the Ground category totaling 45 incidents.
7. Save your work, and then close your files.