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...
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.
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.
Most businesses using excel don't even have an IT department. I'm talking about the LONG tail of small business where the owner is using excel and nobody, except perhaps the size of their wallet, can tell them some other piece of software is off limits.
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...
I work in industry I've seen some of the same things. The IT policies at my work do tend to be overly restrictive but I think it is somewhat understandable there is maybe two thousand people that work at the plant I'm at. 10 to 15 years ago someone made the decision to outsource IT support (before this every department had IT specialists) there is just no way external support can scale to the level its even feasible to think about giving everyone on site admin access or similar. So we've ended up in enterprise like situation with creeping bureaucracy where everything is super locked down and if you need some software installed submit a ticket get a manger to sign off on it (and be prepared to argue with them about why it's necessary to get this software installed) and maybe 1 or 2 weeks later ticket will get actioned (if you are lucky and it doesn't end up sitting for a month in purgatory somewhere).
Excel is part of standard environment so it is present on every fresh installation here so there is a tendency to reach for it. The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing. The other issue is "I know I have excel installed and I know Bob has excel installed - who knows what else Bob has installed already so if I want to collaborate with Bob I better just use excel."
Personally (I work as an Engineer - the non software kind) I think excel is fine for rapidly prototyping something and as a first pass solution for when you need to quickly answer something but once you've reached the stage a spreadsheet is needed to run process it should be rewritten in something more robust - preferably before it reaches that stage.
> The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing.
Yes, one of the major problems for which Excel is a popular solution (and perhaps the biggest in enterprise environments) is IT service request friction.
If you really want to displace Excel, you don't need alternative software (I mean you do, but not anything novel), you need IT to not be an isolated distant mystery group which can only be invoked by time consuming, arcane rituals to which they give unreliable and untimely responses.
> 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.
Exactly. If we view personal computing more like a "medium for literacy," then it becomes clear that computing power is wildly imbalanced. This is especially egregious when we consider the original goals of personal computing. Spreadsheets, Hypercard, and things like them were a totally different direction for computing that has largely since been abandoned in favor of market-dictated "wants" and shrink-wrapped "solutions." The upshot is that the people who should be most in the know -- computer people, developers, etc -- are stuck in a rigid world of epistemic closure (where, for example, Unix is more or less the "End of History")
I have yet to find a better definition of programming than "telling a computer what to do." When put this way, a lot of things that the trade-school approach consider "programming" are really only a subset -- and perhaps the most regressive and uninteresting to boot.
There's a saying: do one thing well . Software doesn't weight or take up space. Then there's high level programming where you sew together high level modules.
The problem is that not everyone has the same needs, and there aren't enough programmers to write special purpose software for each person on the planet. Making software customizable is a way to give users a powerful tool that can be adapted to their needs.
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:
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.
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.
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.
"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.
> If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.
I have (for a previous company), and it still hasn't proven worth it at other companies, including my current employer. We use Office 365 exclusively: it's not perfect but, as I say, Excel is streets ahead of Google Sheets in every other way, and that matters for our use cases.
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.
Yes, there's a trade-off. Note, I didn't say Google Docs is better for every use case. I said it's better "If you use documents and spreadsheets as tools for collaboration".
The key difference I'm talking about is between:
A. Writing a document, and sending a version of that document to one or more other people, so that they can do something with it. (This might involve sending you back feedback or suggestions.)
B. Sharing a link to a 'living' document, with which people can interact in different ways (comment, participate in comment threads, add change suggestions, accept/decline changes).
In my experience, B is much harder to achieve, and unlikely to happen organically, if you use Microsoft Office.
Has anyone here witnessed an organisation that uses Microsoft Office, and where significant progress is made on people's thinking, through their online collaboration on docs/sheets?
We use Google Sheets as a team for its collaboration functionality. However it still has a lot of room to improve with its visual customization for charts and formatting and such.
I realize some people don't care about how beautiful a spreadsheet looks, but in presentations or sharing complex information, it matters. And formatting anything complex with charts and such is a major headache in Sheets (like my latest struggle to get annotations to properly appear, or move a single peak data label so it isn't cut off from displaying).
The thing about Google Docs is that I haven't seen any changes or updates to get toward feature-parity since I was using it in high school a decade and a half ago. I assume it has been exiled to Google's island of misfit toys that they can't quite take out back and shoot just yet.
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).
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?
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.
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.
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.
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.
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...)
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.
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.
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.
Is it any different with code? Developers don’t always stick to the procedures either.
I have ‘unit tests’ in my excel files on the last tab. Eg, the sum of all lines in the Data tab must be the same as the sum of the annual revenues in the dashboard tab.
With a bit of conditional formatting it’s easy to see if a test is failing as well.
I like programming for lots of things, but ask me to do a business case or some one-off analysis and I’d use Excel in most cases.
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.
Version control is extremely useful in software development because code modification by multiple authors is a primary use-case that happens multiple times throughout the day.
Maybe your experience is different, I haven't seen a use-case where multiple authors are changing excel macros or formulas that often, or even often enough to where this is an issue. In fact I think adding version control would make things very very confusing for most people.
