Improving how we manage spreadsheet data(dataingovernment.blog.gov.uk)
Spreadsheets are, in actual fact, the most widely used programming environment, and the most approachable. Most programmer-developed systems are less intuitive and less powerful than a hacky spreadsheet is to the office that created it. I have often encountered other programmers who are somewhat disdainful of user-developed spreadsheets, and I try not to have that attitude. You have to be a top-notch programmer, with a keen interest in understanding your users and their business needs, to do better than their spreadsheet, hacky as it may well be.
For a spreadsheet power user, there is huge value in being able to reverse engineer the logic in a series of calculations. It’s similar to looking at the source code for a tool to understand its output. I’ve seen some monstrously complicated sheets that with enough diligence you can see all the steps for the final output.
The CSV on the Web primer linked in the comments is pretty interesting too:
One of the worst aspects of Microsoft Office (Word, Excel, PowerPoint) is the state of version control. There seem to be different Office offerings with varying support for version control, but many of the experiences are terrible in this respect.
Example: how can I quickly discern who changed an Excel formula and when in an old version of Excel?
Out of interest, what types of version control systems have you seen people cobble together in the workplace, and to address what types of needs?
It's interesting to observe how banks and consultancies "version up" spreadsheets and slide decks by copying the document (plus all supporting documents) and incrementing a version in the name. And also interesting to observe that's worked well enough for quite a long time.
Nowadays, SharePoint should help with version control. But it's an MS product, so it doesn't work and people will just end up e-mailing each other incremented versions.
I've used SharePoint to collaboratively create and edit a few 50 page export controlled (read: cloud not an option, at least back then and for cheap) Word documents. It was surprisingly usable and worked fine. Everyone just used word itself (not the web app) and things kept updating when we pressed the sync button. Way better than like, confluence or that stuff.
I tried getting execs to use markdown and rst with git and phabricator for version control/review/approval but just haven't succeeded yet.
I think the problem includes the fact that most workplaces aren't cobbling together anything much better than that. I have found the default Google Suite experience to be much better with respect to version control.
It seems like businesses forgo office suite solutions with better version control due to concerns over file hosting location though
Really great question - can never get a good answer on this. It would be great if something like GIT worked since it’s just a compressed XML file but no dice. We have some huge unstable spreadsheets we have to save often, and it’s 100MB a save in some cases.
That's wild - where's that 100MB coming from?
Have you looked at something like xltrail (https://www.xltrail.com) or do you think not being integrated with your normal file storage is a showstopper? Looks like xltrail is its own source of truth.
The best for spreadsheets and macros is some variant of cloud storage like DropBox or Box. I believe many of them now keep versions of files.
Hello! This is Selva, and I work for Zoho Sheet :)
Ours is a cloud-based spreadsheet tool, built to make collaboration easier.
Version History in Zoho Sheet helps you keep track of the different versions of your spreadsheets. You can revisit the automatically saved versions, restore older versions, or mark important versions of a spreadsheet. The Change Log against each version will let you know what changes have been made to the version, and by whom.
About the version controls, the Audit Trail would help you view and single out changes made to the spreadsheet. Be it change made to a specific a range, made during the said period, made to a specific sheet or even the changes made by a single user.
With complete Excel compatibility, better and fine-grained collaboration controls, an AI-powered data assistant and the ability to move data across apps seamlessly, I think Zoho Sheet should serve as the right alternative :)
Do give us a try! Here's our website - https://www.zoho.com/sheet/
Google Sheets has a feature to handle this https://gsuiteupdates.googleblog.com/2019/06/edit-history-sh...
Office 2016/20019 comes with a spreadsheet compare tool, that will give a full on screen diff between spreadsheet versions.
Isn't SharePoint the canonical solution for Office document management, incl version control?
> On GOV.UK alone, we found 31,121 files with spreadsheet extensions (like .ods, .xlsx or .xls) and 19,251 CSV files, many of which were not in the suggested tidy, tabular data format.
Ugh. The linked wikipedia page  is bizarre. The "wide table" form is far more practical than the "one possible tidy version": if you want to actually compute something for each maker based on observations, you can do so easily. And they don't describe at all why one might think the "one possible tidy version" is superior. No wonder many of these files are not in the suggested format; it's stupid.