How to make an Excel file shared for editing spreadsheet

Sharing in Microsoft Excel enables several users to work with one file. Ten to twenty people on different computers simultaneously enter some kind of data in one document.

Particular formulas work in those places where certain information is located.

The “main user” can track workgroup activities, add / remove members, and edit contradictory changes. How to set up collaboration in Excel?



Features of working with shared file

Not all tasks can be performed in an Excel workbook with shared access.

The following actions are prohibited:

  1. Creating Excel spreadsheets.
  2. Creating, changing, and viewing scenarios.
  3. Deleting worksheets.
  4. Combining or splitting cells.
  5. Working with XML data (importing, adding, updating, deleting, etc.)

The way out is to: disable shared access – to perform the prohibited function – to enable shared access again.

Sharing data restricts a number of tasks for users:

It’s impossible to:It’s possible to:
Insert or delete a group of cellsAdd a row or a column
Add or change conditional formatsWork with existing formats
Enable or modify the «Data Validation» toolWork with existing verification settings
Create or edit charts, summary reportsWork with existing charts and pivot tables
Insert or edit drawings and graphicsView existing drawings and graphics
Insert or change hyperlinksClick through existing hyperlinks
Assign, edit or delete passwordsUse existing passwords
Put or remove the protection of worksheets and workbooksUse existing protection
Group, structure data; insert sublevelsWork with existing groups, structures and sublevels
Record, modify, or view macrosRun existing macros that are not associated with unavailable tasks
Change or delete array formulasUse existing formulas
Add new information to the formSearch for information in the data form


How to share an Excel file?

First, determine which workbook you will “open” for editing by several members at the same time. Create a new file and fill it with information. Alternatively, open the existing one.

  1. Go to «REVIEW» tab, then to «Share Workbook» dialog box.
    2. Share Workbook.
  2. In window «Share Workbook». Select check box «Allow changes by more than one user at the same time».
    3. Allow changes.
  3. Go to «Advanced» tool to configure the settings for multi-user editing.
    4. Advanced.
  4. Click OK. If you share a new workbook, you should choose the title for it. If sharing is implies using an existing file - click OK.
  5. Open the Microsoft Office menu. Choose «Save as» command. Choose the save file format that will be suitable for all users ‘computers.
  6. Choose a network resource / network folder as a storage location that will be opened by the prospective participants. Click "Save".

It’s important! You cannot use a web server to save a file.

Now check and update the links:

  1. «DATA» tab, «Connections».
    2. Connections.
  2. «Manage Sets». If there is no such button, there are no linked files in the worksheet.
  3. The OK button indicates the operability of the links.

Opening a shared book

  1. «FILE» Click on «Open» (CTRL+O).
  2. Choose a shared workbook.
  3. When the book is open, click on the Microsoft Office button. Go to «Options» tab (at the bottom of the menu).
  4. «General» - «Personalize your copy of Microsoft Office» - «User name:». Enter the identification information (name, nickname).
Options.

That's all. You can edit this information, enter new one, as well as save it afterwards.

Occasionally, when you open a shared Excel workbook, «File is locked» notification appears, making saving impossible. When you later open it, it turns out that sharing is disabled. Possible causes of the problem:

  1. Several users edit the same part of the document. For example, they enter different data in one cell. As a result, blocking occurs.
  2. When a shared file is used, a change log is kept (who entered, when, what he did). As the book size increases, bugs appear.
  3. A user has been removed from shared access, but he/she don’t know about this. Then the blocking will be observed only on his/her computer.
  4. Overloading of the network resource.

If sharing is blocked, you can do the following:

  1. Clean or delete the change log.
  2. Clean the file content.
  3. Disable, and then re-enable shared access.
  4. Open the xls book in OpenOffice and save it in xls again.

It has been noticed that the notification «File is locked» appears less frequently in the latest versions of Excel.

How to delete a user

  1. Open «Share Workbook» menu in «REVIEW» tab.
    2. Share Workbook.
  2. You can see a list of users in «Edit» section.
    3. list of users.
  3. Select the name - click «Remove User».

Before deleting, check whether users have finished working with the file.

How to disable shared access in Excel

All users need to save the information and close the shared file. If you need to save the log, follow the instructions:

  1. «REVIEW» tab - «Track Changes» - «Highlight Changes».
    2. Highlight Changes.
  2. Set the parameters of «Changes». When – «All». Clear checkboxes opposite «Who» and «Where». «List changes on a new sheet» check box should be selected. Click OK.
    3. List changes on a new sheet.
  3. The change log will open. It can be saved or printed.
Allow changes.

To disable Excel file sharing, click «Share Workbook» and uncheck «Allow changes by more than one user at the same time» in «REVIEW» tab.

The single user should remain in the list. It is you.


en ru