Calc Guide 7.5

Chapter 12
Sharing and Reviewing
Spreadsheets|1

CopyrightThis document is Copyright © 2023 by the LibreOffice Documentation Team. Contributors arelisted below. You may distribute it and/or modify it under the terms of either the GNU General
Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative
Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.
All trademarks within this guide belong to their legitimate owners.

Contributors
To this edition
Olivier Hallot

To previous editions
Skip Masonsmith
Barbara Duprey
Leo MoonsJean Hollis Weber

Steve Fanning

Kees Kriek
Felipe Viggiano

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration isrequired) or send an email to: loguides@community.documentfoundation.org.

Note
Everything you post to a forum, including your email address and any otherpersonal information that is written in the message, is publicly archived andcannot be deleted. E-mails sent to the forum are moderated.

Publication date and software version
Published February 2023. Based on LibreOffice 7.5 Community.
Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS
Some keystrokes and menu items are different on macOS from those used in Windows andLinux. The table below gives some common substitutions for the instructions in this document.
For a more detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to thisguide.
Windows or LinuxmacOS equivalent

EffectTools > Options
menu selection

LibreOffice > Preferences

Access setup options

Control+click and/or rightclick depending on computer
setupOpen a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

F11

⌘+T

Open the Styles deck in the
Sidebar

Right-click

2 | Chapter 12 Sharing and Reviewing Spreadsheets

Contents
Copyright.......................................................................................................................... 2
Contributors................................................................................................................................2
Feedback....................................................................................................................................2
Publication date and software version........................................................................................2
Using LibreOffice on macOS......................................................................................................2

Introduction...................................................................................................................... 4
Sharing spreadsheets..................................................................................................... 4
Setting up a spreadsheet for sharing..........................................................................................4
Opening a shared spreadsheet..................................................................................................5
Saving a shared spreadsheet.....................................................................................................5

Reviewing documents.....................................................................................................6
Preparing a document for review (optional)................................................................................6
Recording changes.....................................................................................................................7
Adding comments to changes....................................................................................................8
Editing comments on changes....................................................................................................9
Adding other (general) comments..............................................................................................9
Editing general comments........................................................................................................10
Formatting general comments..................................................................................................10
Finding general comments.......................................................................................................10
Accepting or rejecting changes.................................................................................................11

Merging documents.......................................................................................................12Comparing documents..................................................................................................12
Saving versions............................................................................................................. 13

Copyright | 3

Introduction
This chapter covers methods for collaboration through sharing and reviewing spreadsheets:
simultaneous editing, recording changes, adding comments, reviewing changes, merging andcomparing documents, saving, and using document versions.
Some menu commands are not available (grayed out) when sharing or change tracking are
active.

Sharing spreadsheets
Sharing a spreadsheet allows several users to open the same file for editing at the same time.
The spreadsheet needs to be on a shared drive, server, website, or similar. Some conditions
must be met on operating systems with user permission management:• The shared file needs to reside in a location which is accessible by all collaborators.
• The file permissions for both the document and the corresponding lock file need to be set
so that all collaborators can create, delete, and change the files.
Consult your operating system’s documentation for details on setting file permissions. See also
“Opening and saving files on remote servers” in Chapter 1, Introducing LibreOffice, in the Getting
Started Guide.

Tip
In order to correctly identify the changes, each collaborator should enter theirname in Tools > Options > LibreOffice > User Data on the Menu bar.

Setting up a spreadsheet for sharing
With the spreadsheet open, choose Tools > Share Spreadsheet on the Menu bar to enable ordisable sharing.

Figure 1: Share Document dialog

4 | Chapter 12 Sharing and Reviewing SpreadsheetsTo enable sharing, select the checkbox at the top of the dialog (Figure 1) and click OK. If the
spreadsheet has been saved previously, a message appears stating that you must save it toactivate shared mode; click Yes to continue. If the spreadsheet has not been saved previously,
the Save dialog opens. After saving, the text “(shared)” is shown on the Title bar, after the title.
Tools > Share Spreadsheet on the Menu bar can also be used to switch a spreadsheet fromshared to unshared. However, this means that other users can no longer edit it. Instead, you may
wish to save a copy of the shared spreadsheet using another name or path, thus creating a copyof the shared spreadsheet, and then switch the copy to unshared.

Opening a shared spreadsheet
When you open a shared spreadsheet, a message appears stating that some features are notavailable in this mode. After reading it, click OK to remove the message and open the document.
You can select the option to not show the warning again.
The following features are disabled in a shared spreadsheet:
Edit > Track Changes, except for Merge Document
Insert > Media > Audio or Video
Insert > Comment
Insert > Object
Insert > Chart
Insert > Named Range or Expression
Insert > Pivot Table
Insert > Floating Frame
Insert > Fontwork
Insert > Signature Line
Format > Merge Cells
Format > Print Ranges
Sheet > Move or Copy Sheet
Sheet > Delete Sheet
Sheet > Insert Sheet from File
Sheet > Named Ranges and Expressions
Sheet > Rename Sheet
Sheet > Sheet Tab Color
Data > Define Range
Data > Sort
Data > Sort Ascending
Data > Sort Descending
Data > Subtotals
Data > Validity
Data > Multiple Operations
Data > Consolidate
Data > Group and Outline
Data > Pivot Table
Tools > Protect Sheet
Tools > Protect Spreadsheet Structure

Saving a shared spreadsheet
When you save a shared spreadsheet, and if it has not been modified and saved by another user
since you opened it, it is saved as usual.

Sharing spreadsheets | 5

If the spreadsheet has been modified and saved by another user since you opened it, one of thefollowing will occur:• If the changes do not conflict, the document is saved, the message “Your spreadsheet
has been updated with changes saved by other users” appears, and any cells modified
by the other user are shown with a red border.

Figure 2: Resolve Conflicts dialog• If the changes conflict, the Resolve Conflicts dialog (Figure 2) is shown. You must decide
for each conflict which version to keep, yours or the other person’s. When all conflicts areresolved, the document is saved. While you are resolving the conflicts, no other user cansave the shared document.
• If another user is trying to save the shared spreadsheet and resolve conflicts, they see amessage that the file is locked due to a merge in progress. They can cancel the Savecommand and retry saving later.
When you successfully save a shared spreadsheet, the document shows the latest version of allchanges saved by all users.

Reviewing documents
You can use several methods to review, edit, and comment on a spreadsheet. Each reviewer
works on a separate copy of the document.
• You can use change marks to show added and deleted material. Later, you or anotherperson can review the document and accept or reject each change.
• You can make changes to a copy of the document (stored in a different folder, under adifferent name, or both), then use Calc to compare the files and show the changes. See“Comparing documents” below.
• You can save versions that are stored as part of the original file. See “Saving versions”
below.
Reviewers can leave comments in the document, either attached to specific changes orstandalone.

Preparing a document for review (optional)
Before you send a spreadsheet to someone else to review or edit, you may want to set it up sothat the editor or reviewer does not have to remember to turn on the revision marks.

6 | Chapter 12 Sharing and Reviewing SpreadsheetsAfter you have turned on revision marks, you can optionally password protect the spreadsheet sothat any user must enter the correct password in order to accept or reject changes.
1) Open the spreadsheet and select Edit > Track Changes > Record on the Menu bar. You
can also use File > Properties > Security and select Record changes.
2) If you want to protect the spreadsheet, click Edit > Track Changes > Protect on the
Menu bar. On the Protect Records dialog, type a password (twice) and click OK. You canalso use File > Properties > Security and click the Protect button to open the similarEnter Password dialog. Guidelines for choosing a suitable password are in the Help.

Recording changes
To record changes, open the spreadsheet and make sure that the Edit > Track Changes >Record menu item has a check mark next to it, indicating that change recording is active.

Caution
After making some changes, do not turn off change recording. Turning off change
recording in Calc causes all the recorded changes to be accepted and no longer
shown as changes. A message box will warn if you try to do this.
When changes are made in a cell, a colored border with a dot in the upper left-hand corner
appears around the cell (Figure 3). A deleted column or row is marked by a heavy colored bar.

Figure 3: Edited document with red border on changed cells
To see the changed information, hover the mouse pointer over the changed cell. See Figure 4.

Figure 4: Showing details of the change

NoteSome changes, for example cell formatting, are not recorded and marked.

Reviewing documents | 7

Tip
To change the color that indicates a changed cell, select Tools > Options >LibreOffice Calc > Changes on the Menu bar.
You may want to explain your rationale for the changes. You can do this by adding comments tothe changes you made or by adding general comments to the spreadsheet.

Adding comments to changes
Calc automatically adds to any recorded change a comment that describes what was changed(for example, Cell B4 changed from ‘9’ to ‘4’). Reviewers and authors can add their own
comments to explain their reasons for the changes or provide any other helpful insights.
To add a comment to a change:
1) Make the change to the spreadsheet.
2) Select the cell with the change.
3) Choose Edit > Track Changes > Comment on the Menu bar. A small dialog opens. Typeyour comment and click OK. See Figure 5.

Note
Do not use Insert > Comment on the Menu bar or the context menu for thispurpose. Comments inserted using that method are handled differently (see“Adding other (general) comments” below) and do not appear in the Manage
Changes dialog.

Figure 5: Entering a comment associated with a change
After you have added a comment to a changed cell, you can see the change and the commentby hovering the mouse pointer over the cell, as shown in Figure 6.

Figure 6: Comment added to cell B3
8 | Chapter 12 Sharing and Reviewing Spreadsheets

The comment also appears on the Manage Changes dialog when you are accepting andrejecting changes, as shown in the first line of Figure 10 on page 11.

Editing comments on changes
1) Select the cell with the change comment that you want to edit.
2) Choose Edit > Track Changes > Comment on the Menu bar. The box shown in Figure 5
opens.
3) Edit the comment and click OK.
The automatically-added comment provided by Calc cannot be edited.
You can move through the comments by using the arrows at the bottom of the dialog.

Adding other (general) comments
Calc provides another type of comment, which authors and reviewers often use to exchange
ideas or ask for suggestions.
To add a general comment:
1) Select the cell that the comment applies to.
2) Select Insert > Comment on the Menu bar, or right-click and select Insert Comment in
the context menu, or press Ctrl+Alt+C. The box shown in Figure 7 appears.

Figure 7: Inserting a comment3) Type your comment in the box, then click outside the box to close it.
Now the cell to which you added the comment has a colored dot in the upper right-hand corner,as shown in Figure 8. It does not have a colored border, unless the cell was also changed.

Figure 8: Colored dot in cell containing a comment

Tip
You can change the colors Calc uses for comments by selecting Tools > Options> LibreOffice > Application Colors and adjusting the color for Notes
background in the Spreadsheet section.
To view the comment you just added, hover the mouse pointer over the cell that has a comment.
The comment appears, as shown below.

Reviewing documents | 9

Figure 9: Viewing a comment

Note
If you hover the mouse pointer over a cell that has both a change comment and ageneral comment, both comments are shown in the displayed tip.

Editing general comments
You can edit and format the text of a general comment, just as you do for any other text.
1) Right-click on the cell containing the comment marker and choose Edit comment in thecontext menu, or left-click on the cell and press Ctrl+Alt+C.
2) Calc shows the cell’s comment. The pointer changes to the usual text-entry pointer, andthe Formatting toolbar changes to show text attributes.
3) When editing is done, click outside the comment to deselect it.

Formatting general comments
You can change the background color, border style, transparency, and other attributes of acomment.
1) Right-click on the cell containing the comment marker, and choose Show comment in
the context menu.
2) Click on the comment itself. The Formatting toolbar changes to show many of thecomment formatting options. These are the same as the options for formatting graphics;
see Chapter 6, Using Images and Graphics, for more information.
You can also right-click on the comment to see a menu of choices, some of which lead todialogs where you can fine-tune the formatting; these dialogs are also discussed in
Chapter 6.
3) When done, click outside the comment to deselect it. To hide the comment again, rightclick on the cell and choose Hide Comment in the context menu.

Finding general comments
The small comment markers in the corners of cells can be difficult to see, so Calc provides twoother ways to find them: by showing all comments and by using the Navigator. To show all
general comments, choose View > Comments on the Menu bar. To hide all comments, choose
View > Comments again.
If any general comments are in the spreadsheet, the Navigator shows a symbol (usually a + or
an arrow) next to the word Comments. Click on this symbol to display a list of comments. Doubleclick on a comment in the list to jump directly to the cell it is associated with.

10 | Chapter 12 Sharing and Reviewing SpreadsheetsNote
View > Comments and the Navigator show general comments but not thecomments associated with tracked changes.

Accepting or rejecting changes
When you receive a document with changes, you can step through each change and decide how
to proceed. To begin this process:1) Open the edited document.
2) Select Edit > Track Changes > Manage on the Menu bar. The Manage Changes dialog
shown in Figure 10 appears.
3) You can step through the changes one at a time, choosing to accept or reject eachchange as you go through. You can also accept or reject all changes at one time.
The Comment column by default contains an explanation of the change that was made. If thereviewer added a comment to the change, it is displayed, followed by the description of thechange.
If a change has been superseded by a later change (by either the same person or anotherperson), the changes are hierarchically arranged with an expansion symbol for opening up thehierarchy.
Below the list of changes to be accepted or rejected are shown any changes that have previously
been dealt with.

Figure 10: Manage Changes dialog – List tab

Reviewing documents | 11

Figure 11: Manage Changes dialog – Filter tab
On the Filter tab of the Manage Changes dialog (Figure 11), you can choose how to filter the listof changes: by date, author, cell range, or comments containing specific terms. After selecting thefilter criteria, switch back to the List tab to see the results. Filtering the changes affects only thecontents of the List tab on the dialog, you will not see any filtered results in your spreadsheet.

Merging documents
The processes discussed to this point are effective when you have one reviewer at a time.
Sometimes, however, multiple reviewers all return edited copies of a spreadsheet. In this case, it
may be faster to review all of the changes at once, rather than one review at a time. For thispurpose, you can merge documents in Calc.
To merge documents, all of the edited documents (but not the original document) need to haverecorded changes in them.
1) Open the original document into which you want to merge all the copies.
2) Select Edit > Track Changes > Merge Document on the Menu bar.
3) A file selection dialog opens. Select a file you want to merge and click Open.
4) After the documents merge, the Manage Changes dialog opens, showing changes bymore than one reviewer. If you want to merge more documents, close the dialog and thenrepeat steps 2) and 3).
Now all of the changes are combined into one document and you can accept or reject thechanges. Changes from different authors appear in cells outlined in different colors.

Comparing documents
Sometimes reviewers may not record the changes they make. Calc can find the changes bycomparing documents.

12 | Chapter 12 Sharing and Reviewing SpreadsheetsTo compare documents, you need to have the original document and the one that is edited.
1) Open the edited document that you want to compare with the original document.
2) Select Edit > Track Changes > Compare Document.
3) A file selection dialog opens. Select the original document and click Open.
4) Calc opens the Manage Changes dialog showing the changes identified.
Calc finds and marks the changes as follows:• All data that occurs in the edited document but not in the original is identified as inserted.
• All data in the original document that is not in the edited document is identified as
deleted.
• All data that is changed is marked as changed.
From this point you can go through and accept or reject changes, as described earlier.

Saving versions
For auditing and other purposes, you may need to save new versions of a spreadsheet. You cando this by saving a copy of the spreadsheet (under a different name) after each revision, or byusing Calc’s version feature.

CautionIf you select File > Save As on the Menu bar, or press Ctrl+Shift+S, for a
document with different versions stored in it, the old versions are not saved in the
new file.
To use version management in Calc:
1) Choose File > Versions on the Menu bar. A version management dialog (Figure 12)
opens, showing a list of the existing versions saved in this file.

Figure 12: Version management dialog
2) Click the Save New Version button to save a new version. The Insert Version Comment
dialog (Figure 13) opens where you can enter comments about this version.

Saving versions | 13

Figure 13: Insert Version Comment dialog
3) After you enter your comment and click OK, the new version is included in the Existing
Versions list (Figure 14).
4) Save the file. Both versions are now included in the same file.
From this point you can:• View an old version – Select the version and click Open. A read-only copy of a previous
version opens.
• Compare all versions – Clicking Compare performs an action similar to merging
documents. A Manage Changes dialog opens, showing all of the changes through thedifferent versions.
• Review the version comments – Select a version and click Show to display the fullcomments made by yourself or other reviewers.
• Delete a version – Select a version and click Delete. Be careful with this option, whendeleting a version, there is no request for confirmation.

Figure 14: Updated version list

14 | Chapter 12 Sharing and Reviewing Spreadsheets