none
Common issues of shared workbook RRS feed

  • General discussion

  • Description: 

    When we share a workbook, multiple users can't work on it at the same time. Excel merges changes from one or more copies back into the one that you shared.

    The process works like this: You share a workbook, and as part of sharing, you save the workbook. Someone else then copies the shared workbook and adds their data to the copy. When they're done, you merge the data from the copy back into the original.

    General question overview

    In some cases we may ask why we can’t use some features? Because they may be not supported in shared workbook.

    I have collect and summarize the items from Excel 2013, Excel 2010, Excel 2007, Excel 2003.

    In   a shared workbook, you cannot

    However,   this functionality is available

    Applis   to version

    Create   an Excel table

    None

    Excel   2013, Excel 2010, Excel 2007

    Insert   or delete blocks of cells

     You can insert entire rows and columns

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Delete   worksheets

    None

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Merge   cells or split merged cells

    None

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Sort   or filter by formatting

    You   can sort or filter by number, text, or date, apply built-in filters, and   filter by using the Search box

    Excel   2013

    Add   or change conditional formats

    Existing   conditional formats continue to appear as cell values change, but you can't   change these formats or redefine the conditions.

    Excel   2010, Excel 2007, Excel 2003

    Add   or change data validation

    You   can use data validation when you type new values

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Create   or change charts or PivotChart reports

    You   can view existing charts and reports

    Excel   2013

    Insert   or change pictures or other objects

    You   can view existing pictures and objects

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Insert   or change hyperlinks

    You   can use existing hyperlinks

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Use   drawing tools

    You   can view existing drawings and graphics

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Assign,   change, or remove passwords

    Assign,   change, or remove passwords

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Protect   or unprotect worksheets or the workbook

    You   can use existing protection

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Create,   change, or view scenarios

    None

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Use   the Text to Columns command

    None

    Excel   2013

    Group   or outline data

    You   can use existing outlines

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Insert   automatic subtotals

    You   can view existing subtotals

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Create   data tables

    You   can view existing data tables

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Create   or change PivotTable reports

    You   can view existing reports

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Create   or apply slicers

    Existing   slicers in a workbook are visible after the workbook is shared, but they   cannot be changed for standalone slicers or be reapplied to PivotTable data   or Cube functions. Any filtering that was applied for the slicer remains   intact, whether the slicer is standalone or is used by PivotTable data or   Cube functions in the shared workbook.

    Excel   2013

    Create   or modify sparklines

    Existing   sparklines in a workbook are displayed after the workbook is shared, and will   change to reflect updated data. However, you cannot create new sparklines,   change their data source, or modify their properties.

    Excel   2013

    Write,   record, change, view, or assign macros

    You   can run existing macros that don't access unavailable features. You can also   record shared workbook operations into a macro stored in another nonshared   workbook.

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Add   or change Microsoft Excel 4 dialog sheets

    None

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Change   or delete array formulas

    Excel   will calculate existing array formulas correctly

    Excel   2013, Excel 2010, Excel 2007, Excel 2003

    Work   with XML data, including:
      Import, refresh, and export XML data
      Add, rename, or delete XML maps
      Map cells to XML elements
      Use the XML Source task pane, XML toolbar, or XML commands on the Data menu

    None

    Excel   2013, Excel 2010, Excel 2007

    Use   a data form to add new data

    You   can use a data form to find a record

    Excel   2013

    Quote from:

    http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HA102748977.aspx

    http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx

    http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx

    http://office.microsoft.com/en-au/excel-help/troubleshoot-shared-workbooks-HP005203537.aspx

    Cause 1 Title:  If we get the below error message during used a shared workbook

    Filename.xlsx is locked for editing by 'another user'.

    Open "Read-Only" or click "Notify" to open read-only and receive notification. when the document is no longer in use.

    Description:  What makes resolving this particular problem difficult is that there are multiple causes for the problem, and thus multiple solutions

    Solution:

    Quote:

    http://support.microsoft.com/kb/978382/en-us

    Cause 3 Title:  If we get the below error message during used a shared workbook

    Project is Unviewable

    Description:  If you try to view a Visual Basic for Applications project in the Microsoft Visual Basic Editor, the following error message may appear, even though you did not lock the project for viewing in the properties for the project. This message may also appear if you click VBAProject Properties on the Tools menu in the Microsoft Visual Basic Editor.

    Cause: This behavior occurs if you try to view a project that is in a shared workbook.

    Workaround:

    You cannot view a project in a shared workbook. If you want to view a project in a shared workbook, turn off sharing. To turn off sharing, use the following steps:

    1. Open the shared workbook.
    2. On the Tools menu, click Share Workbook.

    Quote:

    http://support.microsoft.com/kb/211792/en-us

    It also applies to Excel 2010, 2007,2003.


    Please click to vote if the post helps you. This can be beneficial to other community members reading the thread.



    • Edited by ForumFAQ Monday, October 13, 2014 9:07 AM
    Monday, October 13, 2014 9:06 AM

All replies

  • Great post on troubleshooting some problems with shared workbooks. I'd like to add my 2 cts.

    I always advise users to not use the shared workbook feature to begin with. Sharing a workbook where multiple people open the same workbook in Excel on their desktop is a very unreliable feature. You are risking loss of the entire workbook due to corruption and edit conflicts may result in unexpected data to be saved.

    On the other hand, working together on workbooks using Web Excel (as available in Sharepoint and on OneDrive) is a much more reliable and usable feature, as you will get live update of the spreadsheet and will be able to see what other users are doing in the file while you work on the file.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Monday, October 13, 2014 9:41 AM