Calc Guide 7.1

Chapter 11
Sharing and Reviewing Spreadsheets

Copyright

This document is Copyright © 2021 by the LibreOffice Documentation Team. Contributors are listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (http://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

Felipe Viggiano

Kees Kriek

Jean Hollis Weber

To previous editions

Barbara Duprey

Jean Hollis Weber

Kees Kriek

Leo Moons

Steve Fanning

Felipe Viggiano

Feedback

Please direct any comments or suggestions about this document to the Documentation Team’s mailing list: documentation@global.libreoffice.org.

Note

Everything you send to a mailing list, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted.

Publication date and software version

Published May 2021. Based on LibreOffice 7.1 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 and Linux. The table below gives some common substitutions for the instructions in this book. For a more detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to this guide.

Windows or Linux

macOS equivalent

Effect

Tools > Options menu selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click and/or right-click depending on computer setup

Open a context menu

Ctrl (Control)

⌘ (Command)

Used with other keys

F11

+T

Open the Sidebar’s Styles deck

Introduction

This chapter covers methods for collaboration through sharing and reviewing spreadsheets: simultaneous editing, recording changes, adding comments, reviewing changes, merging and comparing 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:

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 their name 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 or disable sharing.

Figure 1: Share Document dialog

Image2

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 to activate 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 from shared 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 copy of 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 not available 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.

If the spreadsheet has been modified and saved by another user since you opened it, one of the following will occur:

Figure 2: Resolve Conflicts dialog

Image4

When you successfully save a shared spreadsheet, the document shows the latest version of all changes 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.

Reviewers can leave comments in the document, either attached to specific changes or standalone.

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 so that the editor or reviewer does not have to remember to turn on the revision marks.

After you have turned on revision marks, you can optionally password protect the spreadsheet so that 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, or press Ctrl+Shift+C. 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 can also use File > Properties > Security and click the Protect button to open the similar Enter 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

Image6

To see the changed information, hover the mouse pointer over the changed cell. See Figure 4.

Figure 4: Showing details of the change

Image5

Note

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

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 to the 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. Type your comment and click OK. See Figure 5.

Note

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

Figure 5: Entering a comment associated with a change

Image3

After you have added a comment to a changed cell, you can see the change and the comment by hovering the mouse pointer over the cell, as shown in Figure 6.

Figure 6: Comment added to cell B3

Image7

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

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 comment

Image8

3)  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

graphics19

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.

Figure 9: Viewing a comment

Image9

Note

If you hover the cursor over a cell that has both a change comment and a general 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 the context menu, or left-click on the cell and press Ctrl+Alt+C.

2)  Calc shows the cell’s comment. The cursor changes to the usual text-entry cursor, and the 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 a comment.

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 the comment formatting options. These are the same as the options for formatting graphics; see Chapter 5, 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 to dialogs where you can fine-tune the formatting; these dialogs are also discussed in Chapter 5.

3)  When done, click outside the comment to deselect it. To hide the comment again, right-click 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 two other 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. Double-click on a comment in the list to jump directly to the cell it is associated with.

Note

View > Comments and the Navigator show general comments but not the comments 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 each change 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 the reviewer added a comment to the change, it is displayed, followed by the description of the change.

If a change has been superseded by a later change (by either the same person or another person), the changes are hierarchically arranged with an expansion symbol for opening up the hierarchy.

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

Image10

Figure 11: Manage Changes dialog – Filter tab

Image11

On the Filter tab of the Manage Changes dialog (Figure 11), you can choose how to filter the list of changes: by date, author, cell range, or comments containing specific terms. After selecting the filter criteria, switch back to the List tab to see the results. Filtering the changes affects only the contents 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 this purpose, you can merge documents in Calc.

To merge documents, all of the edited documents (but not the original document) need to have recorded 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 by more than one reviewer. If you want to merge more documents, close the dialog and then repeat steps 2 and 3.

Now all of the changes are combined into one document and you can accept or reject the changes. 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 by comparing documents.

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:

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 can do this by saving a copy of the spreadsheet (under a different name) after each revision, or by using Calc’s version feature.

Caution

If 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

Image12

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.

Figure 13: Insert Version Comment dialog

Image13

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:

Figure 14: Updated version list

Image14

Contents