6 ms·

Excel doesn't have a compeling, standard way of doing version control that I know of, and most Excel files mix freely data and code in the same sheet, which mak

by brohee 7y ago

Excel doesn't have a compeling, standard way of doing version control that I know of, and most Excel files mix freely data and code in the same sheet, which makes debugging pretty hard. Excel files go from assets to liabilities when they start boxing over their own weight, which happens pretty fast...

_bxg1 7y ago
Makes me wonder if there's a market for an Excel clone whose files are easily machine- and human-readable, and version-control friendly.
- robbiemitchell 7y ago
  Google Sheets
  - oblio 7y ago
    Have you tried using Google Sheets? I have. You can't even cut and insert a bunch of rows. That's a primitive functionality that is very much needed that was available in Excel circa 1988 (I'm exaggerating, but it's been in Excel for at least 2 decades).
    - lrem 7y ago
      While sheets is nowhere near my idea of ergonomic, I've figured out this one every time I needed it. And, despite working for Google, I'm nowhere near a regular user. Maybe your browser, or some extension, got in your way?
  - naravara 7y ago
    Sheets is way less powerful than Excel. Losing a lot of the keyboard shortcuts also makes it way less useful for power users. It also doesn't do well at generating macros and its chart generation capabilities are much less flexible.
  - bartread 7y ago
    Google Sheets is the main reason I gave up on Google Docs and bought an Office 365 subscription for myself a few years back. It's just nowhere near as good as Excel. Frankly none of the Google Docs offerings holds a candle to Microsoft Office[1], so the latter is well worth the money to me. [1] I am tactfully not counting GMail as part of GDocs here because, honestly, Outlook is the one tool that wouldn't come off well in that comparison, and reason #1 is that Outlook's search is terrible; reason #2 is that Outlook doesn't perform at all well in the face of the modern "I want to keep all my mail so that I can search for and find relevant messages whenever I need them", which can be key when dealing with tricksy customers/partners/co-workers, and is handy even in mundane day to day use.
    - rahimnathwani 7y ago
      "Frankly none of the Google Docs offerings holds a candle to Microsoft Office" Word and Excel are great for individual productivity. If you use documents and spreadsheets as tools for collaboration, though, Google Docs is much much better overall, even though it's missing many features to which power users have become accustomed. Sure, you can save a file to OneDrive and have multiple people working on it at the same time. But: - In my experience, simultaneous editing via OneDrive (whether using the browser or the desktop apps) is more laggy and buggy than with Google Docs - The commenting functionality is missing lots of features that are essential to my desired collaboration workflow: i) Ability to give someone access to add comments and suggest changes, but not to accept changes or edit directly ii) Comment authors are identified, and only they can edit comments they have written iii) Comments can be received and replied to by email. The way commenting works in Google Docs encourages collaboration in a way that the simple comments in Word doesn't. If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.
      - kjs3 7y ago
        So Google is "much much better overall" for multiple users, as long as you can dumb what you're working on down enough to fit Docs significant limitations. That's totally fair, but isn't a trade off everyone can make.
  - robbiemitchell 7y ago
    Everyone commenting: Google Sheets is not an exact copy of Excel, but it's the closest (I know of) that exists. For plain-old Excel with version control, save your Excel files in Dropbox. MSFT might also offer a cloud version with version control but I have no experience with it.
  - ken 7y ago
    Are Google Sheets "easily machine-readable"? I've tried to add support for it to my application, and I could not get it to work at all. Maybe it's technically possible but it's sure not easy. You need to use Google's proprietary APIs, and the documentation and support are terrible.
    - lrem 7y ago
      I've pulled some data from a sheet into a python notebook by copy-pasting some code I've found. Sure, these are proprietary APIs, but how else would you talk to a proprietary web service? Disclaimer: despite working for Google, I don't know that much about the suite.
- jmkni 7y ago
  If you are using XLSX files (Excel 2007 and later) you can use an Excel diff driver with Git for not terrible version control - http://programmaticallyspeaking.com/git-diffing-excel-files.html http://programmaticallyspeaking.com/git-diffing-excel-files....
  - brighter2morrow 7y ago
    Awesome, I never knew you could change the diff drivers in git. Such a well-thought-out tool
- DoubleCribble 7y ago
  Google Sheets lets you download the workbook as an Excel file which can be automatically saved to a Google Drive folder. Hourly (?), daily, weekly, monthly snapshots of a shared workbook can all be easily scripted and stored for later retrieval. It eliminates any confusion over which is the latest workbook because the Sheets version is always the most up-to-date.
- dpoochieni 7y ago
  O365
bartread 7y ago
I met a guy at a wedding reception last year, where we were talking tech, and I was lamenting similar issues (we use fairly large multi-worksheet spreadsheets for configuration). He mentioned that his company makes some tooling that might help with this: https://www.htilabs.com/ https://www.htilabs.com/ We ended up going a slightly different route, but thought this might be worth sharing. (I am not affiliated in any way and have nothing to gain by this.)
darkpuma 7y ago
All of that is forgivable because Excel has done something very few other tools have ever done; which is allow 'non-programmers' to effectively program their computers. Excel is empowering software that lets normal users solve novel problems. With excel, a non-technical small business owner can create software(a spreadsheet) that automates the solution to a problem nobody has ever had before. Solutions to programs the creators of excel never even considered. A lot of software is like single-use kitchen gadgets. So called "unitaskers". It has one purpose envisioned by it's inventors, and you'll be lucky if you ever figure out something else it's good at. Sometimes they're pretty slick, but still limited. My mother has a device that skins, slices and cores an apple all at once. With that device, you can process enough apples to make a pie in a minute or two... but the chances of you ever finding anything else that tool can do are slim. Maybe you could use it to slice curly french fries out of a potato, but that's about it. It saves your time if you're operating in the narrow problem space envisioned by the inventor, otherwise it doesn't have much of anything to offer you. Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.
- brohee 7y ago
  In my experience, oftentime Excel is also used out of desperation from not being able to install proper tools, it's not necessarily the only programming solution the users know, it's the only one IT let them have (in the case of technical users, e.g. engineers). Restrictive IT policies are the root cause of some of the gnarliest Excel sheets I've seen.
  - la_barba 7y ago
    Out of curiosity, what industry are you in?
    - brohee 7y ago
      Consultant, so I see lots of different customers, mainly industry. Many Excel sheets are a form of shadow IT, no proper way to get the app they need in a reasonable time, so people jury rig something with what they have. I suspect I see more dysfunction than average still...
la_barba 7y ago
What common scenario do you envision where version control is useful?
- brohee 7y ago
  A file that is edited by more than one person, of which at least one doesn't quite now what he does? It's pretty hard to see what changed from one version to another, and a destroyed formula can go undetected for a while... And obviously, merge would be nice if multiple people can legitimately edit concurrently.
  - devereaux 7y ago
    In spreadsheets (excel) and notebooks (ipython/rstudio), having the data bundled with the code is a feature. It provides reproducibility. > a destroyed formula can go undetected for a while... That is a problem in methodology, not with the tools. There are many solutions that do not require abandoning spreadsheets. For the destroyed formula example, you need tests. Simple example to do that with a checklist: if you are doing a SUM(), require that the employee ticks a box saying "all the number were highlighted when clicking on the SUM formula". For the out-of-sync version, you need a central repository and another box "I retrieved the latest version from the xx repository, and this version was: ... " Then require that to be printed and signed (accountability), and you'll see mistake disappear.
    - brohee 7y ago
      Data bundled with the code is good, but code mixed with the data is a whole different thing. Notebooks are better than Excel in that regard I think. As for expecting people to religiously and accurately observe procedures, I work with human beings, you seem luckier...
      - devereaux 7y ago
        After they received training when they learn that this step is important and not just a formality, and why it is done, I found that people do observe procedures, especially when they have to sign the checklist they filled themselves. Human beings observe procedures when they understand it's part of the job and they are held accountable.
sheetjs 7y ago
Aside from Excel's built-in "Track Changes" features (which stores the revision log within the file), the "Spreadsheet Compare" tool that ships with Excel is a decent diff engine that can be paired with any VCS. Sharepoint is another "official" solution. Unstructured use of Excel is a completely orthogonal problem.
smacktoward 7y ago
IIRC, Excel XLSX files are just ZIP archives with a bunch of XML inside. Therefore, if you can come up with a way for a version control system to transparently look inside ZIP archives, it ought to be possible to version-control XLSX files using the same tools you'd use to version-control any text files, no? (Though this only solves half the problem, the other half being that even if you can wrangle Git or the like to version files inside a ZIP, Git is far from being a tool that could be considered appropriately usable to dump on the Excel audience...)
- sheetjs 7y ago
  It isn't as simple as that. There are numerous equivalent representations of the same underlying content (same data, same styling, same charts, etc) that have wildly different XML representations. You need a content-aware diffing tool. FWIW we've explored that idea in the past, using git textconv to diff spreadsheets https://www.npmjs.com/package/j#using-j-for-diffing-spreadsheet-files https://www.npmjs.com/package/j#using-j-for-diffing-spreadsh... -- it seems nice on paper, but quickly becomes hairy
jgalt212 7y ago
> mix freely data and code in the same sheet where is this unnecessary lisp hate coming from? /s

by brohee 7y ago

_bxg1 7y ago

Makes me wonder if there's a market for an Excel clone whose files are easily machine- and human-readable, and version-control friendly.

robbiemitchell 7y ago
Google Sheets
- oblio 7y ago
  Have you tried using Google Sheets? I have. You can't even cut and insert a bunch of rows. That's a primitive functionality that is very much needed that was available in Excel circa 1988 (I'm exaggerating, but it's been in Excel for at least 2 decades).
  - lrem 7y ago
    While sheets is nowhere near my idea of ergonomic, I've figured out this one every time I needed it. And, despite working for Google, I'm nowhere near a regular user. Maybe your browser, or some extension, got in your way?
- naravara 7y ago
  Sheets is way less powerful than Excel. Losing a lot of the keyboard shortcuts also makes it way less useful for power users. It also doesn't do well at generating macros and its chart generation capabilities are much less flexible.
- bartread 7y ago
  Google Sheets is the main reason I gave up on Google Docs and bought an Office 365 subscription for myself a few years back. It's just nowhere near as good as Excel. Frankly none of the Google Docs offerings holds a candle to Microsoft Office[1], so the latter is well worth the money to me. [1] I am tactfully not counting GMail as part of GDocs here because, honestly, Outlook is the one tool that wouldn't come off well in that comparison, and reason #1 is that Outlook's search is terrible; reason #2 is that Outlook doesn't perform at all well in the face of the modern "I want to keep all my mail so that I can search for and find relevant messages whenever I need them", which can be key when dealing with tricksy customers/partners/co-workers, and is handy even in mundane day to day use.
  - rahimnathwani 7y ago
    "Frankly none of the Google Docs offerings holds a candle to Microsoft Office" Word and Excel are great for individual productivity. If you use documents and spreadsheets as tools for collaboration, though, Google Docs is much much better overall, even though it's missing many features to which power users have become accustomed. Sure, you can save a file to OneDrive and have multiple people working on it at the same time. But: - In my experience, simultaneous editing via OneDrive (whether using the browser or the desktop apps) is more laggy and buggy than with Google Docs - The commenting functionality is missing lots of features that are essential to my desired collaboration workflow: i) Ability to give someone access to add comments and suggest changes, but not to accept changes or edit directly ii) Comment authors are identified, and only they can edit comments they have written iii) Comments can be received and replied to by email. The way commenting works in Google Docs encourages collaboration in a way that the simple comments in Word doesn't. If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.
    - kjs3 7y ago
      So Google is "much much better overall" for multiple users, as long as you can dumb what you're working on down enough to fit Docs significant limitations. That's totally fair, but isn't a trade off everyone can make.
- robbiemitchell 7y ago
  Everyone commenting: Google Sheets is not an exact copy of Excel, but it's the closest (I know of) that exists. For plain-old Excel with version control, save your Excel files in Dropbox. MSFT might also offer a cloud version with version control but I have no experience with it.
- ken 7y ago
  Are Google Sheets "easily machine-readable"? I've tried to add support for it to my application, and I could not get it to work at all. Maybe it's technically possible but it's sure not easy. You need to use Google's proprietary APIs, and the documentation and support are terrible.
  - lrem 7y ago
    I've pulled some data from a sheet into a python notebook by copy-pasting some code I've found. Sure, these are proprietary APIs, but how else would you talk to a proprietary web service? Disclaimer: despite working for Google, I don't know that much about the suite.
jmkni 7y ago
If you are using XLSX files (Excel 2007 and later) you can use an Excel diff driver with Git for not terrible version control - http://programmaticallyspeaking.com/git-diffing-excel-files.html http://programmaticallyspeaking.com/git-diffing-excel-files....
- brighter2morrow 7y ago
  Awesome, I never knew you could change the diff drivers in git. Such a well-thought-out tool
DoubleCribble 7y ago
Google Sheets lets you download the workbook as an Excel file which can be automatically saved to a Google Drive folder. Hourly (?), daily, weekly, monthly snapshots of a shared workbook can all be easily scripted and stored for later retrieval. It eliminates any confusion over which is the latest workbook because the Sheets version is always the most up-to-date.
dpoochieni 7y ago
O365

bartread 7y ago

I met a guy at a wedding reception last year, where we were talking tech, and I was lamenting similar issues (we use fairly large multi-worksheet spreadsheets for configuration). He mentioned that his company makes some tooling that might help with this: https://www.htilabs.com/ https://www.htilabs.com/ We ended up going a slightly different route, but thought this might be worth sharing. (I am not affiliated in any way and have nothing to gain by this.)

darkpuma 7y ago

All of that is forgivable because Excel has done something very few other tools have ever done; which is allow 'non-programmers' to effectively program their computers. Excel is empowering software that lets normal users solve novel problems. With excel, a non-technical small business owner can create software(a spreadsheet) that automates the solution to a problem nobody has ever had before. Solutions to programs the creators of excel never even considered. A lot of software is like single-use kitchen gadgets. So called "unitaskers". It has one purpose envisioned by it's inventors, and you'll be lucky if you ever figure out something else it's good at. Sometimes they're pretty slick, but still limited. My mother has a device that skins, slices and cores an apple all at once. With that device, you can process enough apples to make a pie in a minute or two... but the chances of you ever finding anything else that tool can do are slim. Maybe you could use it to slice curly french fries out of a potato, but that's about it. It saves your time if you're operating in the narrow problem space envisioned by the inventor, otherwise it doesn't have much of anything to offer you. Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.

brohee 7y ago
In my experience, oftentime Excel is also used out of desperation from not being able to install proper tools, it's not necessarily the only programming solution the users know, it's the only one IT let them have (in the case of technical users, e.g. engineers). Restrictive IT policies are the root cause of some of the gnarliest Excel sheets I've seen.
- la_barba 7y ago
  Out of curiosity, what industry are you in?
  - brohee 7y ago
    Consultant, so I see lots of different customers, mainly industry. Many Excel sheets are a form of shadow IT, no proper way to get the app they need in a reasonable time, so people jury rig something with what they have. I suspect I see more dysfunction than average still...

la_barba 7y ago

What common scenario do you envision where version control is useful?

brohee 7y ago
A file that is edited by more than one person, of which at least one doesn't quite now what he does? It's pretty hard to see what changed from one version to another, and a destroyed formula can go undetected for a while... And obviously, merge would be nice if multiple people can legitimately edit concurrently.
- devereaux 7y ago
  In spreadsheets (excel) and notebooks (ipython/rstudio), having the data bundled with the code is a feature. It provides reproducibility. > a destroyed formula can go undetected for a while... That is a problem in methodology, not with the tools. There are many solutions that do not require abandoning spreadsheets. For the destroyed formula example, you need tests. Simple example to do that with a checklist: if you are doing a SUM(), require that the employee ticks a box saying "all the number were highlighted when clicking on the SUM formula". For the out-of-sync version, you need a central repository and another box "I retrieved the latest version from the xx repository, and this version was: ... " Then require that to be printed and signed (accountability), and you'll see mistake disappear.
  - brohee 7y ago
    Data bundled with the code is good, but code mixed with the data is a whole different thing. Notebooks are better than Excel in that regard I think. As for expecting people to religiously and accurately observe procedures, I work with human beings, you seem luckier...
    - devereaux 7y ago
      After they received training when they learn that this step is important and not just a formality, and why it is done, I found that people do observe procedures, especially when they have to sign the checklist they filled themselves. Human beings observe procedures when they understand it's part of the job and they are held accountable.

sheetjs 7y ago

Aside from Excel's built-in "Track Changes" features (which stores the revision log within the file), the "Spreadsheet Compare" tool that ships with Excel is a decent diff engine that can be paired with any VCS. Sharepoint is another "official" solution. Unstructured use of Excel is a completely orthogonal problem.

smacktoward 7y ago

IIRC, Excel XLSX files are just ZIP archives with a bunch of XML inside. Therefore, if you can come up with a way for a version control system to transparently look inside ZIP archives, it ought to be possible to version-control XLSX files using the same tools you'd use to version-control any text files, no? (Though this only solves half the problem, the other half being that even if you can wrangle Git or the like to version files inside a ZIP, Git is far from being a tool that could be considered appropriately usable to dump on the Excel audience...)

sheetjs 7y ago
It isn't as simple as that. There are numerous equivalent representations of the same underlying content (same data, same styling, same charts, etc) that have wildly different XML representations. You need a content-aware diffing tool. FWIW we've explored that idea in the past, using git textconv to diff spreadsheets https://www.npmjs.com/package/j#using-j-for-diffing-spreadsheet-files https://www.npmjs.com/package/j#using-j-for-diffing-spreadsh... -- it seems nice on paper, but quickly becomes hairy

jgalt212 7y ago

> mix freely data and code in the same sheet where is this unnecessary lisp hate coming from? /s