--- Bài mới hơn ---
Phá Pass Excel ? Cách Bỏ Protect Workbook Trong Excel
√ Khóa Và Mở Khóa (Protect/unprotect) Vùng Dữ Liệu Trong Bảng Tính Excel
Esim Là Gì? Tổng Hợp Những Lợi Ích Của Esim
Enzyme Cắt Giới Hạn
Enzym Giới Hạn Trong Phân Tích Adn
In this tutorial, you will find full details on how to share Excel workbook with other people by saving it to a local network or OneDrive, how to control user access to a shared Excel file and resolve conflicting changes.
These days more and more people are using Microsoft Excel for team work. In the past, when you needed to share an Excel workbook with someone, you could send it as an email attachment or save your Excel data to PDF for printing. While fast and convenient, the former method created multiple versions of the same document, and the latter produced a secure though non-editable copy.
The recent versions of Excel 2010, 2013 and 2022 make it easy to share and collaborate on workbooks. By sharing an Excel file, you are giving other users access to the same document and allow them to make edits simultaneously, which saves you the trouble of keeping track of multiple versions.
How to share an Excel file
This section shows how to share an Excel workbook for multiple users by saving it to a local network location where other people can access it and make edits. You can keep track of those changes and accept or reject them.
With the workbook open, perform the following steps to share it:
- The Share Workbook dialog box will appear, and you select the Allow changes by more than one user at the same time. This also allows workbook merging check box on the Editing tab.
For example, you may want to have changes updated automatically every n number of minutes (all the other settings on the screenshot below are the default ones).
- Save you Excel file to a network location where other people can access it (the fastest way is by using the
Ctrl + S
shortcut).
If done correctly, the word [Shared] will appear to the right of the workbook’s name as shown in the screenshot below:
Now, you and your colleagues can work on the same Excel file at the same time. You are free to accept or reject their changes, and after the desired changes have been incorporated, you can stop sharing the workbook. Further on in this tutorial, you will find the details on how to do all this.
Note. If Microsoft Excel refuses to share a certain workbook, most likely it’s because of one of the following reasons:
- Workbooks that have tables or XML maps cannot be shared. So, be sure to convert your tables to ranges and remove the XML maps before sharing your Excel file.
If Microsoft Excel refuses to share a certain workbook, most likely it’s because of one of the following reasons:
How to share Excel workbook and protect change tracking
If you’d like not only to share an Excel file, but also to ensure that no one turns off the change history or removes the workbook from shared use, proceed in this way:
- The Protect Shared Workbook dialog window will show up, and you select the Sharing with track changes check box.
Though entering a password is optional, you’d better do it. Otherwise, there’s little sense in using this option, because anyone would be able to remove the protection and thus stop the workbook sharing.
- Save the workbook.
Note. If the workbook is already shared, and you want to protect sharing with a password, you must
If the workbook is already shared, and you want to protect sharing with a password, you must unshare the workbook first.
Protect worksheet vs. protect shared workbook
The Protect and Share Workbook option only pvents turning off change tracking in a shared workbook, but does not pvent other users from editing or deleting the workbook’s contents.
If you want to pvent people from changing important information in your Excel document, you will need to lock some areas before sharing it (“before” is an important word here since the worksheet protection cannot be applied to an Excel shared workbook). For the detailed step-by-step instructions, please see:
Excel shared workbook limitations
When deciding to share your Excel file, please be aware that it may cause some troubles to your users because not all features are fully supported in shared workbooks. Here are some of the limitations:
- Sorting and filtering by format
- Conditional formatting
- Merging cells
- Excel tables and PivotTable reports
- Charts and pictures
- Data validation
- Worksheet protection
- Grouping or outlining data
- Subtotals
- Slicers and sparklines
- Hyperlinks
- Array formulas
- Macros
- A few more things
In fact, you will be able to use the existing features, but you won’t be able to add or change them. So, if you’d like to benefit from any of the above options, be sure to apply them before sharing your Excel file. The complete list of features unsupported in shared workbooks can be found on Microsoft’s web-site.
How to edit an Excel shared workbook
After you open a shared workbook, you can enter new or change existing data in a regular way.
You can also identify your work in a shared workbook:
- In the General category, scroll down to the Personalize your copy of Office section.
Now, you can input and edit data as usual, keeping in mind the following limitations of shared workbooks.
How to resolve conflicting changes in a shared Excel file
When two or more users are editing the same workbook simultaneously, some edits can affect the same cell(s). In such situations, Excel keeps the changes of the user who saves the workbook first. When another user tries to save the workbook, Excel displays the Resolve Conflicts dialog box with the details about each conflicting change:
To resolve conflicting changes, do one of the following:
To have the most recent changes automatically override any pvious changes (made by you or by other users), without displaying the Resolve Conflicts dialog box, do the following:
To view all the changes that have been made to the shared workbook, use the Track Changes feature on the Review tab, in the Changes group. It will show you when a particular change was made, who made it, and what data was changed. For more information, please see:
How to merge different copies of a shared workbook
In some situations, it can be more convenient to save several copies of a shared workbook, and then merge changes made by different users. Here’s how:
- Share your Excel file to a local network location.
- Other users can now open the shared file and work with it, each person saving their own copy of the shared workbook to the same folder, but using a different file name.
- Add the Compare and Merge Workbooks feature to your Quick Access toolbar. The detailed steps on how to do this can be found here.
- Open the primary version the shared workbook.
Done! The changes by different users are merged into a single workbook. Now you can highlight the changes, so you can view all the edits at a glance.
How to remove users from a shared Excel workbook
Sharing an Excel file for multiple users can result in many conflicting changes. To avoid this, you may want to disconnect certain people from the shared workbook.
To remove a user from a shared workbook, do the following:
Note. This action disconnects users only for the current session, but does not pvent them from reopening and editing the shared Excel file again.
If it is you that have been disconnected, you can pserve your work by saving the shared workbook with a different name, then reopen the original shared workbook and merge your changes from the copy you’ve saved.
How to unshare an Excel file
When the teamwork has been completed, you can stop sharing the workbook in this way:
Notes:
- Before clearing this box, make sure you are the only person listed under Who has this workbook open now. If there are other users, disconnect them first.
How to share Excel workbook using OneDrive
Another way to share an Excel workbook is to save it to OneDrive, invite your colleagues to work on it, and see each other’s changes instantaneously. Microsoft calls it co-authoring.
Save and share a workbook
In Excel 2013 and Excel 2010, to save a workbook to OneDrive, perform these steps:
- Invite people to collaborate on the workbook by typing their names or email addresses in the corresponding box.
Co-author with other people
Now, as soon as other people start editing the workbook, their names will appear in the upper-right corner (sometimes pictures, initials, or even “G” that stands for guest). You can see other users’ selections in different colors, your own selection is traditionally green:
Note. You may not see other people’s selections if you are using a version other than Excel 2022 for Office 365 or Excel Online. However, all their edits to a shared workbook will appear in real time.
This is how you can share an Excel file with other users. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
--- Bài cũ hơn ---
Hướng Dẫn Cách Vẽ Shapes Giữ Đúng Định Dạng Trong Excel
Hàm Vlookup Với Một Số Ví Dụ Cơ Bản Và Nâng Cao Trong Excel
Hướng Dẫn Cách Sắp Xếp Tên Theo Abc Trong Excel 2010 Và Các Phiên Bản Khác
Hướng Dẫn Cách Sử Dụng Hàm Đa Chức Năng Split Trong Excel
Hướng Dẫn Sử Dụng Tính Năng Split Cell Trong Excel
