I was having a coffee this afternoon with Darren Miller, the CEO of Sumwise and the author of their blog with the topic ‘our fascination with financial modeling’. We discussed the lack of collaboration functionality in Excel 2003 and 2007 and how there is an open whole for someone to fill when it comes to improving the current spreadsheet space.
Google have made a big effort in this sector but they are clearly aiming at the mass market of consumers and are not really catering for us professional users of spreadsheet software. Darren’s Sumwise has some interesting projects underway in this space and I think there could be some potential for future cooperation for us.
Has Excel got any functionality for collaboration?
The discussion with Darren reminded me of the Share Workbook functionality in Excel which I tested a couple of year’s ago. The Share Workbook functionality allows multiple users to work simultaneously in the same workbook without being ‘locked out’. When one users saves the file on the network gets updated and the other user gets informed of recent updated by other users upon saving the file.
This gives companies new options in terms of dividing projects between team members in ways not possible without this functionality. For example, one could ask a junior team member to code up the operational calculations of a financial model while the more senior person would focus on the cashflow waterfall and lock-up mechanics.
How to use the ‘Share Workbook’ functionality in Excel
It is pretty straight forward to test this functionality so I would recommend that you give it a go if you haven’t already tested it. Simply click Tools -> Share Workbook and you are half way there already. Check the ‘Allow changes by more than one user at the same time. This also allows workbook merging’ and you are done!
Excel Share Workbook functionality
Now, ask a colleague to open the same file from the network and have a play with performing updates and then saving the file. Excel will then identify areas modified by the other user - it is pretty good so far!
Excel Share Workbook - Advanced Options
So, what is the problem with Shared Workbooks?
Limited functionality in Excel Shared Workbooks
Well, there are two groups of problems with Shared Workbooks. The first one is well-known and properly documented in the Excel help and is the limited functionality available for Shared Workbooks.
From Microsoft Excel help
| Unavailable feature | Alternative solutions |
| Create lists | None |
| Insert or delete blocks of cells | You can insert entire rows and columns. |
| Delete worksheets | None |
| Merge cells or split merged cells | None |
| 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. |
| Add or change data validation | Cells continue to be validated when you type new values, but you can’t change existing data validation settings. |
| Create or change charts or PivotChart reports | You can view existing charts and reports. |
| Insert or change pictures or other objects | You can view existing pictures and objects. |
| Insert or change hyperlinks | Existing hyperlinks continue to work. |
| Use drawing tools | You can view existing drawings and graphics. |
| Assign, change, or remove passwords | Existing passwords remain in effect. |
| Protect or unprotect worksheets or the workbook | Existing protection remains in effect. |
| Create, change, or view scenarios | None |
| Group or outline data | You can continue to use existing outlines. |
| Insert automatic subtotals | You can view existing subtotals. |
| Create data tables | You can view existing data tables. |
| Create or change PivotTable reports | You can view existing reports. |
| Write, record, change, view, or assign macros | You can run existing macros that don’t access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook. |
| Add or change Microsoft Excel 4 dialog sheets | None |
| Change or delete array formulas | Existing array formulas continue to calculate correctly. |
Workbook corruptions and freezing Excel
The second issue with Shared Workbooks is that Excel gets really unstable and often crashes or corrupts the file. This may not be too much of an issue if you are working on simple spreadsheets at home, but it you are working under time pressure on large financial models in the project finance or PPP sectors then this alone is enough to drive you crazy.
This massive limitation to Excel is also why we are not using the Share Workbook functionality at Corality even though it would in some cases have a massive positive impact. If Microsoft ever sorts this out in Excel 2003 I would be the first to congratulate them!
[...] subsequently posted
The Sumwise blog is very
The Sumwise blog is very great! It has the advantages of Index/Match in terms of flexibility of locations of lookup array and value array. This is very interesting! Thank you very much for the excellent insight.
excel training
[...] See example here: Are
Post new comment