How can I set up public folders so that more than one person can save changes to an Excel spreadsheet at a time?
Although it's possible to share an Excel workbook with multiple users and allow them to work on the file simultaneously, many users have reported problems with file corruption, overwritten data within worksheets and even worksheets that have lost all data.
Problems can occur when users attempt to share workbooks using the public folder, a workgroup or a server. In addition, sharing a workbook does not work with conditional formatting, charts, pivot table reports and several other Excel features.
One alternative is to use Excel Online in OneDrive. Multiple users can edit the same worksheet, and you can see which cells other users are editing in near real time. Another option is to switch to a true multiuser database application such as Microsoft Access.
However, if you want to try sharing a workbook using public folders in Windows 8.1, for example, you must ensure that the homegroup has read/write sharing enabled. Unlike previous versions of Windows, the public folder is part of the HomeGroup feature in Windows 8.1. All users who need to access the workbook must join the homegroup.
You must also enable sharing in the workbook. To do so, click Share Workbook in the Changes group on the Review tab, and select the "Allow changes by more than one user at the same time" checkbox. Click the Advanced tab, and modify those settings as desired. Then save the workbook.
If two users are editing the workbook at the same time and User A saves the workbook, User B will receive a "Your workbook has been updated with changes made by others" message when attempting to save the workbook. Cells that were changed by User A appear with a colored border.
About the author:
Kim Lindros is a full-time writer, content developer and project manager who has worked around IT since the early 1990s. She co-authored MTA Microsoft Technology Associate Exam 98-349 Windows Operating System Fundamentals (Wiley, 2012) and PC Basics with Windows 7 and Office 2010(Jones & Bartlett Learning, 2010), among other textbooks. Lindros has also developed numerous college and corporate courses focused on IT security, Microsoft technologies and Microsoft Office.
This was first published in July 2014