Как сделать shared excel на английском
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 More. Less
Important: "Shared Workbooks" is an older feature that allows you to collaborate on a workbook with multiple people. This feature has many limitations, and has been replaced by co-authoring. Excel co-authoring is available in Microsoft 365 Apps Excel desktop client and Excel for the Web for Microsoft 365 subscribers.
Enabling the Shared Workbook feature
Be certain that you want to use this method before continuing. Shared Workbooks have limitations, and one in particular is the inability to edit using Excel for the web. Therefore we highly recommend co-authoring, which is the replacement for Shared Workbooks.
Create a new workbook or open an existing workbook. Then place it on a network location. For example, put it on a location like \\server_name\folder_name. Don't put the file on OneDrive or SharePoint. If you prefer those locations for the file, co-author the workbook instead.
Click Review > Share Workbook.
Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.
On the Editing tab, select the Allow changes by more than one user . check box.
On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
If this is a new workbook, type a name in the File name box. Or, if this is an existing workbook, click OK to save the workbook.
If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken.
Click File > Save.
When you're done, - Shared will appear at the top of the Excel window, next to the filename.
Additional information
Please be aware that Shared Workbooks is an older method of sharing. Therefore, many items and actions are not supported when this method is used. The table below is only a partial list of features not supported.
Unsupported items:
Unsupported actions:
Creating or inserting tables
Inserting or deleting blocks of cells
Adding or changing conditional formats
Adding or changing data validation
Merging cells or splitting merged cells
Creating or changing charts or PivotChart reports
Sorting or filtering by format
Inserting or changing pictures or other objects
Using drawing tools
Inserting or changing hyperlinks
Assigning, changing, or removing passwords
Creating, changing, or viewing scenarios
Protecting or unprotecting worksheets or the workbook
Inserting automatic subtotals
Grouping or outlining data
Creating data tables
Writing, recording, changing, viewing, or assigning macros
Creating or changing PivotTable reports
Changing or deleting array formulas
Creating or applying slicers
Adding, renaming, or deleting XML maps
Creating or modifying sparklines
Mapping cells to XML elements
Adding or changing Microsoft Excel 4 dialog sheets
Using the XML Source task pane, XML toolbar, or XML commands on the Data menu
Using a data form to add new data
Adding threaded comments
Editing, or deleting threaded comments
Before you disconnect users, make sure that they have completed their work on the workbook. If you remove active users, any unsaved work of theirs will be lost.
Click Review > Share Workbook.
Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.
On the Editing tab, in the Who has this workbook open now list, review the names of users.
Select the name of the user who you want to disconnect, and then click Remove User. Please note that although this action disconnects the user from the workbook, it does not prevent that user from editing the workbook again.
To delete any personal view settings of the removed user, click View > Custom Views and delete any other user views.
Before you turn off this feature, you might want to make a copy of the change history. First make sure that all other users have completed their work.
Click Review > Track Changes > Highlight Changes.
Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.
In the When list, select All.
Clear the Who and Where check boxes.
Select the List changes on a new sheet check box, and then click OK.
Now you can either print the History worksheet, or you can copy the history and paste it into another workbook.
Click Review > Share Workbook.
Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.
On the Editing tab, make sure that you are the only person listed in the Who has this workbook open now list.
Clear the Allow changes by more than one user . check box. If this check box is not available, you must first unprotect the workbook. Do the following:
Click OK to close the Share Workbook dialog box.
On the Review tab, in the Changes group, click Unprotect Shared Workbook.
If you are prompted, enter the password, and then click OK.
On the Review tab, in the Changes group, click Share Workbook.
On the Editing tab, clear the Allow changes by more than one user . check box.
When you are prompted about the effects on other users, click Yes.
Enabling the Shared Workbook feature
Be certain that you want to use this method before continuing. Shared Workbooks have limitations, and therefore we highly recommend co-authoring, which is the replacement for Shared Workbooks.
Create a new workbook or open an existing workbook. Then place it on a network location. Don't put the file on OneDrive or SharePoint. If you prefer those locations for the file, co-author the workbook instead.
Click Review > Share Workbook.
On the Editing tab, select the Allow changes by more than one user . check box.
On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
If this is a new workbook, type a name in the Save As box. Or, if this is an existing workbook, click OK to save the workbook.
If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken.
Click File > Save.
When you're done, - Shared will appear at the top of the Excel window, next to the filename.
Additional information
Please be aware that Shared Workbooks is an older method of sharing. Therefore, many items and actions are not supported when this method is used. The table below is only a partial list of features not supported.
Unsupported items:
Unsupported actions:
Creating or inserting tables
Inserting or deleting blocks of cells
Adding or changing conditional formats
Adding or changing data validation
Merging cells or splitting merged cells
Creating or changing charts or PivotChart reports
Sorting or filtering by format
Inserting or changing pictures or other objects
Using drawing tools
Inserting or changing hyperlinks
Assigning, changing, or removing passwords
Creating, changing, or viewing scenarios
Protecting or unprotecting worksheets or the workbook
Inserting automatic subtotals
Grouping or outlining data
Creating data tables
Writing, recording, changing, viewing, or assigning macros
Creating or changing PivotTable reports
Changing or deleting array formulas
Creating or applying slicers
Adding, renaming, or deleting XML maps
Creating or modifying sparklines
Mapping cells to XML elements
Adding or changing Microsoft Excel 4 dialog sheets
Using the XML Source task pane, XML toolbar, or XML commands on the Data menu
Importing, refreshing, and exporting XML data
Using a data form to add new data
Before you disconnect users, make sure that they have completed their work on the workbook. If you remove active users, any unsaved work of theirs will be lost.
Click Review > Share Workbook.
On the Editing tab, in the Who has this workbook open now list, review the names of users.
Select the name of the user who you want to disconnect, and then click Remove User. Please note that although this action disconnects the user from the workbook, it does not prevent that user from editing the workbook again.
To delete any personal view settings of the removed user, click View > Custom Views and delete any other user views.
Before you turn off this feature, you might want to make a copy of the change history. First make sure that all other users have completed their work.
Click Review > Track Changes > Highlight Changes.
In the When list, select All.
Clear the Who and Where check boxes.
Select the List changes on a new sheet check box, and then click OK.
Now you can either print the History worksheet, or you can copy the history and paste it into another workbook.
Click Review > Share Workbook.
On the Editing tab, make sure that you are the only person listed in the Who has this workbook open now list.
Clear the Allow changes by more than one user . check box. If this check box is not available, you must first unprotect the workbook. Do the following:
Click OK to close the Share Workbook dialog box.
On the Review tab, click Protect Workbook.
If you are prompted, enter the password, and then click OK.
On the Review tab, Share Workbook.
On the Editing tab, clear the Allow changes by more than one user . check box.
Before you turn off this feature, you might want to make a copy of the change history. First make sure that all other users have completed their work.
Click Review > Track Changes > Highlight Changes.
In the When list, select All.
Clear the Who and Where check boxes.
Select the List changes on a new sheet check box, and then click OK.
Now you can either print the History worksheet, or you can copy the history and paste it into another workbook.
Click Review > Share Workbook.
On the Editing tab, make sure that you are the only person listed in the Who has this workbook open now list.
Clear the Allow changes by more than one user . check box. If this check box is not available, you must first unprotect the workbook. Do the following:
Click OK to close the Share Workbook dialog box.
On the Review tab, click Protect Workbook.
If you are prompted, enter the password, and then click OK.
On the Review tab, Share Workbook.
On the Editing tab, clear the Allow changes by more than one user . check box.
Share a workbook with others, right within Excel. You can let them edit the workbook or just view it.
Select Share.
Select permissions and then Apply.
Type a message if you like.
Select Send.
It takes just three steps to share a workbook.
Using a web browser, upload or create a new workbook on OneDrive, OneDrive for Business, or a SharePoint Online library.
If you uploaded the file, click the filename to open it. The workbook will open in a new tab in your web browser.
Click the Edit in Excel button. If you don't have this button, click Edit in Browser, and then click Edit in Excel after the page reloads.
Click Open Microsoft Excel.
When the file opens in the Excel program, you may see a yellow bar which says the file is in Protected View. Click the Enable Editing button if that's the case.
Click Share in the upper-right.
Click Invite People. Then type email addresses, and separate each with a semicolon. Make sure to also select Can Edit. When you’re done, click the Share button.
Tip: If you want to send the link yourself, click Share in the upper-right, and then click Copy Link > View and Edit.
If you clicked the Share button in the previous step, email messages will be sent to each person. The message will come from your email address. You will also receive a copy of the message, just so you know what it looks like.
If you clicked the Share button, people will receive an email message inviting them to open the file. They can click the link to open the workbook. A web browser will open, and the workbook will open in Excel for the web. If they want to edit in the browser, they can click Edit Workbook > Edit in Browser. If they want to open it in the Excel app, they can click Edit Workbook > Edit in Excel.
Tip: If they're using the latest version of Excel 2016 for Microsoft 365 subscribers, they can open the file directly in the Excel app. They can click File > Open. > Shared, and then select a file that has been shared by another person.
More than one person can edit a workbook at the same time. This is called co-authoring. However, everyone will need a version of the Excel app that supports co-authoring. At this time, Excel for Android, Excel for iOS, Excel Mobile, and Excel 2016 for Microsoft 365 subscribers are the versions that currently support co-authoring. If they don't have a supported version, they can click Edit Workbook > Edit in Browser to edit the file in Excel for the web.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
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:
- Creating Excel spreadsheets.
- Creating, changing, and viewing scenarios.
- Deleting worksheets.
- Combining or splitting cells.
- 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 cells | Add a row or a column |
Add or change conditional formats | Work with existing formats |
Enable or modify the «Data Validation» tool | Work with existing verification settings |
Create or edit charts, summary reports | Work with existing charts and pivot tables |
Insert or edit drawings and graphics | View existing drawings and graphics |
Insert or change hyperlinks | Click through existing hyperlinks |
Assign, edit or delete passwords | Use existing passwords |
Put or remove the protection of worksheets and workbooks | Use existing protection |
Group, structure data; insert sublevels | Work with existing groups, structures and sublevels |
Record, modify, or view macros | Run existing macros that are not associated with unavailable tasks |
Change or delete array formulas | Use existing formulas |
Add new information to the form | Search 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.
- Go to «REVIEW» tab, then to «Share Workbook» dialog box.
- In window «Share Workbook». Select check box «Allow changes by more than one user at the same time».
- Go to «Advanced» tool to configure the settings for multi-user editing.
- 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.
- Open the Microsoft Office menu. Choose «Save as» command. Choose the save file format that will be suitable for all users ‘computers.
- 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:
- «DATA» tab, «Connections».
- «Manage Sets». If there is no such button, there are no linked files in the worksheet.
- The OK button indicates the operability of the links.
Opening a shared book
- «FILE» Click on «Open» (CTRL+O).
- Choose a shared workbook.
- When the book is open, click on the Microsoft Office button. Go to «Options» tab (at the bottom of the menu).
- «General» - «Personalize your copy of Microsoft Office» - «User name:». Enter the identification information (name, nickname).
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:
- Several users edit the same part of the document. For example, they enter different data in one cell. As a result, blocking occurs.
- When a shared file is used, a change log is kept (who entered, when, what he did). As the book size increases, bugs appear.
- 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.
- Overloading of the network resource.
If sharing is blocked, you can do the following:
- Clean or delete the change log.
- Clean the file content.
- Disable, and then re-enable shared access.
- 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
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:
- «REVIEW» tab - «Track Changes» - «Highlight Changes».
- 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.
- The change log will open. It can be saved or printed.
To disable Excel file sharing, click «Share Workbook» and uncheck «Allow changes by more than one user at the same time» in «REVIEW» tab.
Совместный доступ в Microsoft Excel дает возможность работать с одним файлом сразу нескольким пользователям. Десять-двадцать человек на разных компьютерах одновременно вносят какие-то данные в один документ. Где находится определенная информация, работают определенные формулы.
«Главный пользователь» имеет возможность отслеживать действия рабочей группы, добавлять/удалять участников, редактировать противоречивые изменения. Как настроить совместную работу в Excel.
Особенности работы с общим файлом
В книге Excel со совместным доступом не все задачи можно выполнять.
- Создавать таблицы Excel.
- Создавать, менять или просматривать сценарии.
- Удалять листы.
- Объединять либо разделять ячейки.
- Работать с XML-данными (импортировать, добавлять, обновлять, удалять и т.д.).
Выход: отключить общий доступ – выполнить запрещенную задачу – снова включить доступ.
Совместное пользование ограничивает для участников еще ряд задач:
Недопустимо | Реально |
Вставлять либо удалять группу ячеек | Добавлять строку или столбец |
Добавлять либо изменять условные форматы | Работать с существующими форматами |
Включать или изменять инструмент «Проверка данных» | Работать с существующими настройками проверки |
Создавать или редактировать диаграммы, сводные отчеты | Работать с имеющимися диаграммами и сводными таблицами |
Вставлять или редактировать рисунки и графические объекты | Просматривать имеющиеся рисунки и графические объекты |
Вставлять или менять гиперссылки | Переходить по имеющимся гиперссылкам |
Назначать, редактировать либо удалять пароли | Существующие пароли функционируют |
Ставить либо снимать защиту листов и книг | Существующая защита работает |
Группировать, структурировать данные; вставлять подуровни | Работать с имеющимися группами, структурами и подуровнями |
Записывать, изменять или просматривать макросы | Запускать имеющиеся макросы, которые не связаны с недоступными задачами |
Изменять или удалять формулы массива | Пользоваться существующими формулами |
Добавлять в форму данных новую информацию | Искать информацию в форме данных |
Как сделать совместный доступ к файлу Excel?
Сначала определяемся, какую книгу мы "откроем" для редактирования сразу несколькими участниками. Создаем новый файл и наполняем его информацией. Либо открываем существующий.
- Переходим на вкладку «Рецензирование». Диалоговое окно «Доступ к книге».
- Управление доступом к файлу – правка. Ставим галочку напротив «Разрешить изменять файл нескольким пользователям одновременно».
- Переходим к инструменту «Подробнее», чтобы настроить параметры многопользовательского редактирования.
- Нажимаем ОК. Если мы открываем общий доступ к новой книге, то выбираем ей название. Если совместное пользование предполагается для существующего файла – жмем ОК.
- Открываем меню Microsoft Office. Выбираем команду «Сохранить как». Выбираем тот формат файла сохранения, который «пойдет» на всех пользовательских компьютерах.
- Местом сохранения выбираем сетевой ресурс / сетевую папку, которую откроют предполагаемые участники. Нажимаем «Сохранить».
Внимание! Нельзя использовать для сохранения общего файла веб-сервер.
Теперь проверим и обновим ссылки:
- Вкладка «Данные». «Подключения».
- Изменить ссылки / изменить связи. Если такая кнопка отсутствует, в листе нет связанных файлов.
- Переходим на вкладку «Состояние», чтобы проверить имеющиеся связи. На работоспособность связей указывает кнопка ОК.
Открытие книги с совместным доступом
- Открываем меню Microsoft Office.
- Нажимаем «Открыть».
- Выбираем общую книгу.
- Когда книга открыта, нажимаем на кнопку Microsoft Office. Переходим на вкладку «Параметры Excel» (в нижней части меню).
- «Общие» - «Личная настройка» - «Имя пользователя». Вводим опознавательную информацию (имя, ник-нейм).
Все. Можно редактировать информацию, вводить новую. После работы – сохранить.
Случается, что при открытии книги Excel с совместным доступом к файлу появляется запись «Файл заблокирован». Сохранить не получается. При последующем открытии оказывается, что общий доступ отключен. Возможные причины проблемы:
- Одну и ту же часть документа редактируют несколько пользователей. К примеру, вбивают разные данные в одну ячейку. Возникает блокировка.
- Во время пользования совместным файлом ведется журнал изменений (кто входил, когда, что делал). Книга увеличивается. Начинает «глючить».
- Удалили кого-то из пользователей, но пока ему не сказали об этом. Тогда блокировка может появиться только на его компьютере.
- Большая нагрузка на сетевой ресурс.
Что можно сделать, если совместный доступ к файлу файл заблокирован:
- Почистить либо удалить журнал изменений.
- Почистить содержимое файла.
- Отменить, а потом снова активировать совместный доступ.
- Открыть книгу xls в OpenOffice. И снова сохранить ее в xls.
Замечено, что запись «Файл заблокирован» реже появляется в новейших версиях Excel.
Как удалить пользователя
- На вкладке «Рецензирование» открываем меню «Доступ к книге».
- В разделе «Правка» мы видим перечень пользователей.
- Выбираем имя – нажимаем «Удалить».
Перед удалением проверьте, что пользователи закончили работу с файлом.
Как отключить режим совместного доступа в Excel
Все пользователи должны сохранить информацию и закрыть общий файл. Если необходимо оставить журнал, соблюдаем инструкцию:
- Вкладка «Рецензирование» - «Исправления» - «Выделить исправления».
- Устанавливаем параметры «Исправлений». По времени – «все». Галочки напротив «Пользователем» и «В диапазоне» сняты. Напротив «вносить изменения на отдельный лист» - стоит. Жмем ОК.
- Откроется Журнал изменений. Его можно сохранить либо распечатать.
Чтобы отключить совместный доступ к файлу Excel, на вкладке «Рецензирование» нажать «Доступ к книге» и снять галочку напротив «Разрешить изменять файл нескольким пользователям».
Collaboration is important in many work spaces. Since most projects require files to be accessed by a lot of users, collaborative setups have become necessary.
Microsoft Excel is one is just one of the many programs used by companies worldwide. As such, the need for shared workbooks is crucial.
In this post, you’ll learn how you and your team can collaborate using Excel 2016 and other versions of Excel online.
Share Excel Files Offline
If you have a local area connection, all users in the network can have access to any file. Not only that, any changes made to the file can be tracked. You can also set which users would have access to the file.
Start by saving the file in a location that can be accessed by everyone in your group. You can then set your file for collaboration.
Adding Back Shared Workbook Feature
Office 365 users will find the Shared Workbook feature hidden by default. This is because Microsoft encourages users to share workbooks online.
Here’s how you enable the feature. If you’re using an older version of Excel, you can skip to the succeeding section – Sharing a Document.
- Go to File > Options > Quick Access Toolbar.
- Under the Choose Commands From drop-down menu, select All Commands. Scroll through the list until you see Share Workbook (Legacy) and select that item.
- Click the Add button.
- Do the same for the following commands:
- Track Changes (Legacy)
- Protect Sharing (Legacy)
- Compare and Merge Workbooks
- Click OK when you’re done.
Sharing a Document
- On the Review tab, click the Share Workbook button.
Those who followed the steps in the Adding Back Shared Workbook Feature section will find the Share Workbook button (along with the other commands you added) in the Quick Access Toolbar instead.
- This will prompt a dialog box to appear. Tick the option that enables the shared workbooks feature.
- Head over to the Advanced tab and enable Track Changes and other settings you might want to turn on.
Protect Your File
The steps above give everyone access to your file. But it also gives everyone administrative access by default. Protecting your file means access to the file will be limited and tracked.
- Go to the Review tab and click the Protect and Share Workbook button.
- Tick the Sharing With Track Changes check box.
- You should also create a password otherwise anyone can revert the workbook to its original settings.
- Save your changes and exit.
Share Excel Files Online
While working offline is a great solution, you’re hampered by the necessity for a shared local drive. Working online is clearly the better choice. Here’s how you do it.
Using OneDrive
- Open the file and go to File > Share > Save to Cloud.
- Invite people you’d want to collaborate with by entering their email address. You can also set the permission settings.
- Click Share when you’re finished.
Using Google Drive
Google Drive is probably the easiest way to work on a project simultaneously. Anyone with a Google account can access files and edit as needed.
- Log into your account and go to Google Drive.
- Once you’re in, you can create a new file or upload an existing one.
- Once your document has been created or uploaded, click the Share button on the upper-right corner of the worksheet.
This will give you the option to share the spreadsheet by entering an email address or by sharing a link to your colleagues.
All changes are automatically tracked and saved. You can work on the document together in real-time which is very convenient.
Christopher Jan Benitez is a freelance writer for hire who provides actionable and useful web content to small businesses and startups. In his spare time, he religiously watches professional wrestling and finds solace in listening to '80s speed metal. Read Christopher's Full Bio
Did you enjoy this tip? If so, check out our YouTube channel from our sister site Online Tech Tips. We cover Windows, Mac, software and apps, and have a bunch of troubleshooting tips and how-to videos. Click the button below to subscribe!
Читайте также: