Overview of sharing and collaborating on Excel data
There are many ways to share, analyze, and communicate business information and data in Microsoft Excel. The way that you choose to share data depends on many factors, including how you want others to view or work with the data. For example, do you want to keep sensitive or important information from being modified, or do you want to allow users to change and edit the data? Perhaps you need to share data with users who do not have Excel or have different versions of Excel on their computers. Maybe you just want to share a fixed version of your workbook that can easily be sent in e-mail and printed.
This article is an overview that discusses how to share data in Excel, what you and others need in order to view or work with the data, and the limitations that are associated with sharing data. It also includes links to more detailed articles.
In this article
Using SharePoint Services to share data while maintaining one version of the workbook
If you have access to an installation of SharePoint Services that is capable of running Excel Calculation Services (a server such as this is also referred to as having Excel Services enabled), you can save a workbook to that server so that other users can access all or parts of it in a web browser.
In a web browser, users can view and interact with worksheets in a grid as well as calculate, create snapshots, refresh data, and extract values from the workbooks. Workbooks can also be used in dashboard reports. (Similar to a car dashboard, a dashboard report visually presents critical data in summary form so that users can get the information that they need at a glance.) Furthermore, users are not required to have Excel installed on their local computers in order to view workbook data in the browser.
The following illustration demonstrates the use of published workbook data in a dashboard report.
When you publish a workbook, the entire workbook is saved on the server, but you can specify only the parts of the workbook (such as individual worksheets, named ranges, or charts) that you want to display in the Web browser. You can also set parameters to allow interactivity by specifying single cells that can be edited on the worksheet that is viewable in the browser. For example, you can specify cells into which users can enter values that are used by formulas in other cells, and then users can see the results that are calculated.
Share data by publishing a workbook to a SharePoint site when:
Your organization has Excel Services enabled, that is, a SharePoint server that is capable of running Excel Calculation Services. Excel Calculation Services is the same “engine” that the Excel desktop program uses for calculating data, creating and displaying charts, and performing many other actions.
You want to maintain only one copy of a workbook or worksheet in a central, secure location.
Users need to analyze, chart, pivot, and interact with data by using a browser. Excel does not have to be installed on the users' computers.
You want to help secure and protect worksheet models and the data that populates the models. You can grant users view-only permissions so that they can view the worksheet in the browser, but not access the worksheet directly by using Excel or any other client (desktop) program. Additionally, you can control the parts of the worksheet that users are allowed to view in the browser.
You want to use a workbook in a dashboard report on a Web page where you can quickly view and analyze important project information.
You want to use reporting features to alert users to updated reports.
For more information, see Publish a workbook to a SharePoint site.
Collaborating on workbooks stored on a document management server
When you want to make one or more workbooks available for collaboration in a central location, you can save them to a document management server. A document management server enables you to use document management features, such as automated workflows and shared document libraries, to check in and check out documents. Additionally, if you have SharePoint Services, you can save your document to a shared workspace to make it even easier for other users to collaborate on the document and to keep your local copy of the workbook synchronized with changes from the server.
Saving your workbook to a document management server
You can save workbooks to a document management server, such as SharePoint Services, to give users a central location to collaborate on documents and to take advantage of the many document management features offered by the document management server systems.
Collaborate by using a document management server when:
You have access to a document management server.
Users have Excel installed on their computers to open and use workbooks from the document management server.
You want to take advantage of document management server features such as:
Automated workflow, so that users who work on the document are notified of and know what phase of work they are participating in (review, feedback, approval, and so on), and so that users can provide information and expertise as needed.
Document libraries where you can store and organize workbooks.
Checking in, checking out, and versioning of workbooks.
Administration and reporting features.
Collaboration features, such as Document Workspace sites.
To publish a workbook to a document library on a SharePoint site, ensure that the site is available, and do the following:
Click the File tab, click Save & Send, and click Save to SharePoint.
In the Save As dialog box, choose a server location and the name of the workbook, and then click Save.
Before you click Save, you can click Publish Options to choose either individual worksheets or items in the workbook (such as named ranges, charts, or PivotTables) to publish, and you can also create parameters.
Regardless of whether you choose worksheets or items or create parameters, the entire workbook is always saved to the document library. However, the publish options you choose can affect what is and isn’t shown to users in the browser view.
Distributing data through e-mail, by fax, or by printing
Perhaps the most common ways to share Excel data are by sending workbooks through e-mail, by faxing workbooks, and by printing and distributing hard copies of workbooks. Use e-mail, fax, or print to distribute workbooks when:
You have an e-mail program such as Microsoft Outlook.
You have a fax machine or fax software and an Internet connection.
You have a printer and want to use or distribute hard copies of your data for reference.
Sending workbooks through e-mail
You can send a workbook from Excel or from your e-mail program. To send a workbook from Excel, open the workbook that you want to send, click the File tab, click Save & Send, and then click Send Using E-mail.
You can fax a workbook from Excel if you are signed up with a fax service provider, or if you have fax software and an Internet connection or a fax modem. You can also can print your workbook and then fax it by using a fax machine.
To fax a workbook from Excel, ensure that you have an Internet connection and that you are signed up with a fax provider. Open the workbook that contains the information that you want to fax, click the File tab, click Save & Send, and then click Send as Internet Fax.
You can easily print a workbook from Excel. Before you print a workbook that contains large amounts of data or charts, you can quickly fine-tune it in Page Layout view to achieve professional-looking results. In this view, you can change the layout and format of the data the way that you can in Normal view. But you can also use rulers to measure the width and height of the data, change the page orientation, add or change the page headers and footers, set the margins for printing, and hide or display the row and column headers.
For more information on printing from Excel, see Print a worksheet or workbook.
Exchanging workbooks with users who use earlier versions of Excel
When you want to exchange workbooks with other users who may be using earlier versions of Excel, you can save your workbook in the Excel 97-2003 format (.xls) instead of the Excel XML or binary format (.xlsx or .xlsb) and work on the document in Compatibility Mode. Compatibility Mode is automatically enabled when you open an Excel 97-2003 workbook. The visual cue Compatibility Mode appears in the program title bar when you are working in an earlier version file format.
Compatibility Mode enforces compatibility between Excel 2010 and Excel 2003, Excel 2002, Excel 2000, and Excel 2007 workbooks by suppressing features within Excel 2010 that could potentially add content that is incompatible with the earlier Office releases.
When you save a workbook that is opened in Compatibility Mode, Excel automatically runs the Compatibility Checker to identify any possible compatibility issues. If you are not using Compatibility Mode, you can also use the Compatibility Checker to assist you when you save a workbook in the .xlsx or .xlsb file format to a file format of an earlier version of Excel — for example, when converting .xlsx (Excel XML format) to .xls (Excel 97–2003 Workbook). The Compatibility Checker warns you about any content in a workbook that may not be compatible with earlier releases and that therefore may be affected by a file format conversion.
Use Compatibility Mode when:
You want to distribute copies of your workbook to other users who are working in earlier versions of Excel.
You expect that other users will want to change or manipulate the data that you distribute.
You want the recipients of your workbook to be able to use familiar Excel features to work with the data.
For more information on how to work in Compatibility Mode, see Use Office Excel 2010 with earlier versions of Excel.
Distributing workbooks to users who do not have Excel
A great way to allow users who do not have Excel to view and interact with Excel data is using Excel Services. If you don't have access to Excel Services however, you can install an add-in to save a workbook in PDF or XPS format, or you can save a workbook to other file formats and then send it to your recipients by using e-mail or fax, or by saving the file on a network share or Web share that the users can access.
Distributing fixed versions of your data for viewing and printing by using PDF or XPS format
You can save Excel data to a PDF (Portable Document Format) or XPS (XML Paper Specification) format for printing, posting, and e-mail distribution. Saving a worksheet as a PDF or XPS file lets you capture information in an easily distributed form that retains all of your formatting characteristics, but doesn't require other users to have Excel in order to review or print your output.
Save your worksheet in PDF or XPS format when:
You want to distribute fixed versions of your files by e-mail or on a network or Web server.
Recipients of the file may not have Excel installed.
You want users to be able to easily print the information.
You don't want anyone to modify the source data.
You don't expect frequent updates to the data.
For more information on converting your workbook to PDF or XPS, see Save as PDF or XPS.
Distributing workbooks in other file formats
You can save Excel workbooks in different file formats so that those who do not have Excel can open them in other programs or data systems.
You can save Excel files as XML files, text files, Web pages (HTML files), and others. For a list of formats that you can save to, see File formats that are supported in Excel.
Important Not all Excel features will be retained when you save to other file formats. Formatting is often not retained, and sometimes formulas are not retained, depending on the format. Furthermore, when you save to some formats, only the active sheet is saved. Therefore, if you have several worksheets in a workbook and you want to save all worksheets to a specific format, you will have to save each worksheet separately
Save workbooks to other file formats when:
You want to share the data but not necessarily the formatting or Excel-specific features.
You have an XML-based system, and you want to transfer data by using XML.
You need simple text files for use in other systems.
You want to post information to the Web, and you want to save Excel data as a Web page to integrate into your Web site.
To save to another file format, click the File tab, and then click Save As. Click the arrow next to the Save as type list, and then click a file type in the list.
For more information on saving to other file formats, see Save a workbook in another file format.
Allowing multiple users to edit a workbook simultaneously
If you have a workbook in which you want to allow more than one other person to add or modify data simultaneously, you can set up and save the workbook as "shared" and then make it available on a network share (not a Web server).
Use the Share Workbook feature when:
You want multiple users to be able to edit the data in one workbook simultaneously, and you are comfortable with the original data in the workbook being modified, including edits, additions, and deletions.
You have a network share available on which to store the workbook and to which users have access.
Note More than one user cannot simultaneously make changes to a shared workbook that is stored on a SharePoint Services site. If you want to store your workbook on a SharePoint Services site, you should do so only after the collaboration effort through sharing is complete.
You want to keep a record of the changes that are made in the workbook.
You do not expect to change the following features, which cannot be modified after a workbook is shared: merged cells, conditional formats, data validation, charts, pictures, objects (including drawing objects), hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros.
As the owner of the workbook, you can prepare it to be shared by entering and formatting the data that needs to be present. When you are finished, click Share Workbook in the Changes group on the Review tab, select your options, and then save the workbook on a network share (not a Web server) that is available to the intended users. Alternatively, you can click Protect and Share Workbook in the Changes group on the Review tab to prevent other users from turning off Change Tracking in the workbook.
Each time that you save the shared workbook, you are prompted with the changes that other users have saved since the last time that you saved the shared workbook. If you want to keep the shared workbook open to monitor progress, Excel can update you with the changes automatically, at timed intervals that you specify, with or without saving the workbook yourself.
When you save changes to a shared workbook, another person who is editing the workbook might have saved changes to the same cells. In this case, the changes conflict, and you are prompted with a conflict resolution dialog box so that you can choose which changes to keep.
Distributing copies of a workbook and then merging or consolidating data from each copy
Distributing workbooks and merging changes from each
To distribute copies of a workbook and then merge changes from several of the copies, you need to set up the workbook as a shared workbook.
For more information, see the section Allowing multiple users to edit a workbook simultaneously.
The difference is that instead of storing the workbook in one location for several users to edit, you can distribute copies of the workbook and allow the users to make changes and then send the changed workbooks back to you. You store the workbooks in one location and then click Compare and Merge Workbooks. You can review the changes and accept those changes that you want to keep and use in one master workbook. To add the Compare and Merge Workbooks command to the Quick Access Toolbar, do the following:
Click the File tab, click Options, and then click the Quick Access Toolbar category.
In the Choose commands from list, click All Commands.
Select Compare and Merge Workbooks, click Add, and then click OK.
Use the Compare and Merge feature when:
You want to collect input from several key users, but you want to have more control over what changes are made and accepted in the original version.
You don't want to store the workbook in one central location for several users to access, but you want to distribute copies of the workbook instead (in e-mail, for example).
You want to keep records of the changes that you accept.
You can accept the limitations of shared workbooks (see the section Allowing multiple users to edit a workbook simultaneously).
Distributing workbooks and consolidating data from multiple worksheets
If you set up worksheets to have similar or identical layouts and send copies of those worksheets to other users to work on, you can summarize and report the results from all of those users on a master worksheet. For example, if you have a worksheet of expense figures in workbooks for each of your regional offices, you can roll up these figures onto one master corporate expense worksheet.
Use Data Consolidation when:
You want to collect input from several key users and roll it up onto one master worksheet.
You don't want to store the workbook in one central location for several users to access, but you want to distribute copies of the workbook instead (in e-mail, for example).
You have or are willing to create similar or identical worksheets from which to consolidate.
To consolidate data, on the Data tab, in the Data Tools group, click Consolidate, and then type or select the references to the data that you want to consolidate.
You have several options for consolidating data. You can consolidate data by:
Position Choose this option when the data on all of the worksheets is arranged in identical order and location.
Category Choose this option when each worksheet organizes the data differently but has the same row and column labels, which you can use to match the data.
3-D formulas Choose this option when the worksheets do not have a consistent pattern. You can create formulas that refer to cells in each range of data that you are combining. Formulas that refer to cells on multiple worksheets are called 3-D formulas.
For more information, see Consolidate data from multiple worksheets in a single worksheet.