Merge or share your LibreOffice Calc spreadsheets

I get a lot of questions from small business users about migrating from Microsoft Office to LibreOffice. There has been an uptick in these questions since the release of Microsoft Office 2013.

A user recently asked me, “Does LibreOffice offer the ability to collaborate with spreadsheets?” The short answer is “yes,” and the long answer is “yes, with a caveat.” The caveat is the collaboration doesn’t work well when using the Microsoft native formats .xls and .xlsx. The collaboration works seamlessly (across platforms even) when using the open document format .ods.

When a document is shared, formatting and chart/drawing editing attributes are locked so other users cannot alter the spreadsheet’s design — they can alter data, just not the design. These feature locks also apply to the document creator. The only way to gain access to those elements again is to unshare the document. This collaboration feature also works over a network. So you can create a spreadsheet, save it to a network drive, share the spreadsheet, and anyone with access to that drive can work with the document.

In this tutorial, I demonstrate how to share your LibreOffice Calc spreadsheets. Then, I introduce you to the LibreOffice Merge tool that allows you to merge two working copies of a spreadsheet into one.

Sharing your LibreOffice Calc spreadsheets

In order to share a LibreOffice Calc spreadsheet, the document must be open and saved in the .ods file format (later on, you can save it as an .xls file if you need to send it to someone who uses Microsoft Office). After you save the files, go to Tools | Share Document. In the resulting window, click the checkbox for Share This Spreadsheet With Other Users (Figure A). Save the document, and now it’s ready for other users to open and edit. Note: While in Sharing mode, no one can change the document’s format — only the data.
Figure A

You cannot manually add users — you can only enable/disable sharing here. (Click the image to enlarge.)

When someone opens the document, they will get a warning informing them the spreadsheet is in sharing mode and certain “rules” apply (Figure B).
Figure B

Users immediately know their document is in Sharing mode. (Click the image to enlarge.)

Go to Tools | Share Document, and the Sharing window will open and indicate which users currently have the document open (Figure C).
Figure C

Two users are working within the same document. (Click the image to enlarge.)

Another benefit of using this method is that two users can have the same document open at the same time. Both users can make changes to the spreadsheet and, upon hitting Save, the documents will be updated with the changes.

If you want to make formatting changes to the document, you need to unshare the document. All sharing features will be turned off, and any changes made to the shared document by other users will no longer be automatically included.

Merging documents

If you unshare a document and find yourself in a situation where users have added data into the wrong copy of a spreadsheet, you can merge the secondary document into the primary one. Here’s how:

1. Open the primary document.
2. Go to Tools | Merge.
3. Navigate to the secondary document, select it, and click Open.

You won’t get a warning that a merge is about to happen — the documents are simply compared in the background, and the missing data will be applied to each. This should only be done with two similar documents; otherwise, you might wind up with a mess on your hands.

More LibreOffice Calc tutorials on TechRepublic

• Add drop-down lists to LibreOffice spreadsheets
• Conditional formatting in LibreOffice Calc
• Resolve formatting issues in LibreOffice Calc
• Use formatting, autoformatting, and themes in LibreOffice Calc