14 ms·

Advancing Excel as a programming language [audio]

85 points by 0-_-0 5y ago

makemexx 5y ago
very interesting. By the way, sometimes for study and work I use https://audext.com/ https://audext.com/ to transcribe podcasts.
MH15 5y ago
A slight tangent but I interned at Blubrry as a software engineer a few summers back. Was a great experience!
samfisher83 5y ago
Excel is probably the single most used piece of business software in the world. People have been able to make quake. People have done 3d rendering without vba. People have done all kinds of stuff with Excel for a while now.
- sokoloff 5y ago
  I strongly suspect Word is used more, probably a lot more. I wonder if PowerPoint is used a similar amount.
  - krono 5y ago
    Is there a real difference between the classic MS office applications, though? You can do spreadsheets in Word and Outlook, have styled text in your Excel document, add animations to your titles in Word, etc. At this point they can just as wel get rid of the different GUIs facade and instead implement some contextual interaction model (and hopefully they'll include an API so we can generate these documents programatically and don't have to deal with this nth clippy generation :) )
    - icapybara 5y ago
      Might not be from MS, but automating MS Office document generation is a solved problem: https://python-docx.readthedocs.io/en/latest/ https://python-docx.readthedocs.io/en/latest/ https://www.python-excel.org/ https://www.python-excel.org/ https://python-pptx.readthedocs.io/en/latest/ https://python-pptx.readthedocs.io/en/latest/
      - krono 5y ago
        Yeah this is awesome community work, and I'm honestly thankful for MS having left these works alone. An official solution would be nice though. These open source projects are popular enough to warrant one I'd say!
    - Closi 5y ago
      > Is there a real difference between the classic MS office applications, though? Yes, there absolutely is. You can create a document with styled text in Excel instead of Word, just as you can edit a photo in Paint instead of Photoshop. You can do it, but the two tools have dramatically different capabilities. It's important to think about developing for use-cases otherwise we will end up with overly complex software that aims to be all-things to all-people.
      - krono 5y ago
        > overly complex software that aims to be all-things to all-people This is exactly what I believe the current state of the MS Office applications to be. The interface and/or implementation isn't always ideal for the purpose you would generally use a specific app for, but the functionality is there. > dramatically different capabilities Embedding a fully functional Excel spreadsheet is only a few clicks through the ribbon and some frustration away in Outlook, Word, and even PowerPoint.
        Closi 5y ago
        > Embedding a fully functional Excel spreadsheet is only a few clicks through the ribbon and some frustration away in Outlook, Word, and even PowerPoint. This is effectively opening a reduced version of excel in a very limited way, primarily for embedding one document in another and allowing limited editing. You don’t get the full functionality of the other application. I can see why I would want to change the axis on a graph even after I have pasted it into my email, but why would I want one app to be both my spreadsheet and my email inbox? Are you a user of office suite? I spend about 70% of my work life between excel, PowerPoint and Word and have never once wanted them to be one app, but quite often have wanted better integration.
  - nvrspyx 5y ago
    I'm not quite sure. Within my work team, Word is only used occasionally, specifically for anything document related. Excel, on the other hand, is used all the time from typical number crunching to keeping track of tasks (despite how much I despise using it for the latter). I also imagine that small businesses, like restaurants, don't require Word very often, but a lot of them use Excel for finances, inventory, etc. In regards to PowerPoint, I imagine it lags pretty far behind in usage compared to both Excel and Word. Not many people are making presentations in the grand scheme of things. My intuition says that it's mostly upper management and maybe a single person in a group using PowerPoint "often".
  - dimator 5y ago
    I would wager excel is used more, because spreadsheets are "live" and opened/updated much more than a written document.
    - sokoloff 5y ago
      That's true, but I posit that there are entire large categories of computer users who open Excel exactly 0 times per year but who use Word.
  - Guest42 5y ago
    For me at least, I try to avoid Word because it is so slow and cumbersome compared to notepad or notepad++ for the purpose of writing notes. It seems like most of my word processing is for my eyes only.
    - Tagbert 5y ago
      A word processor like Word is really aimed at larger, more complex documents with formatting. Notes are usually smaller with simpler structure and formatting. With notes, it helps of the app gives you a way to organize the notes. This is really a different use case.
    - jdgoesmarching 5y ago
      I didn’t realize how much I hated Word until I started using Pages. For notes specifically I prefer some sort of Markdown editor.
      - Guest42 5y ago
        I’ll have to give pages a try. I think perhaps I favor the earlier versions of office products because I learned on them, they’re faster, and they weren’t as a service. I’ll go for latex if something has to be published and certainly use power point a bit but am always on the lookout for new approaches. It’s great that power point can pull/receive graph data.
  - vagrantJin 5y ago
    Powerpoint is powerful once you get deep into it. I know people who make some crazy visuals using powerpoint and you would thing it was done with some adobe software.
  - conductr 5y ago
    My experiences in the business world is Word has been on a sharp decline for a long time. Excel is more likely to be used as Word. It’s very common to see a spreadsheet with no math and no tabular data but it’s print formatted.
    - andylynch 5y ago
      This is spot on.
  - andylynch 5y ago
    Not generally. Big firms it would be email 1st with browser for most line of business apps , excel close second, third PowerPoint, with Word behind by some distance and mainly for reading, rather then writing so Acrobat is high on the list as well. Most of the excel workbooks would be lists and trackers of various kinds, many just (big) tables without any formulae at all.
- m3kw9 5y ago
  Nothing beats easy.
- inopinatus 5y ago
  People have also made megadollar blunders. As programming environments go, Excel carefully maximises the fuckup surface, enabling all users from novice to expert to stumble unawares into hidden calculation bugs. Cell-reference slip-ups (via copypaste or otherwise) are de rigeur, of course, and they're aided and abetted by Excel's cockeyed "type" system (if we can even call it that), half-baked documentation, inconsistent function library, and mediocre/absent testing, integrity, versioning, or debugging tools. Probably the worst example I've seen first-hand was an entire retail banking loan-approval process running off of a single, shared, gigantic spreadsheet, that hundreds had tinkered with, but no-one understood or took responsibility for, and where the accompanying Word document of "things not to do" was bigger than the workbook. Even yesterday, a friend of mine discovered they'd underclaimed expenses for a total >$1,000 due to a dodgy spreadsheet. Something as simple as pasting a list of dollar amounts from a webpage into Excel can produce an incorrect SUM() if/when trailing spaces creep in, since the resulting values may be treated as strings and evaluate to zero - and so it had transpired. Not even "text to columns" could fix it; you have to a) know about this lurking monster, b) use formatting to make it casually evident, and c) use Replace to strip the whitespace. What a crock.
  - fireflies_ 5y ago
    In some ways the argument against Excel is like the argument against Electron: if you're comparing it to an elegant, purpose-built and well-tested application, yes... it falls woefully short. But is that really the right comparison? Lots of people just don't have the skills or time to build a specific applications. Without Excel, what would they do? There are some places where a 90% solution is worse than no solution at all (at least no solution is a forcing function for a "real" application), and in many ways Excel isn't the best expression of the _idea_ of an Excel-like, low-barrier-to-entry declarative programming environment with a built-in UI, but it's truly a wonderful tool. It makes a lot of automation possible for a lot of people.
  - Optimal_Persona 5y ago
    Haha, I discovered "always TRIM both leading and trailing spaces on both lookup criteria cells and source tables" the hard way too. At 3 in the morning...All of the 20-hour days/nights I've done as a data analyst were due to Excel malfeasance. Also - 15 digit NUMBER precision leading to truncated values that you only discover when it's too late; no Macro undo; no way I'm aware of to transform columns/rows from formulas to their values without intermediate cut/paste steps; lots of essential features (like "Copy only visible range containing hidden cells") buried in Find & Select > Go To Special menu without keyboard shortcuts; Excel's magic ability to change cell data types by merely touching a CSV (open/closing without saving changes)...
- croes 5y ago
  And is one of the largest source of problems because of unsupportable amateur made Excel "solutions".
- kitd 5y ago
  IIRC, there are an order of magnitude more Excel "programmers" than programmers in all other languages combined. Tbh, it is a much better source of business than most professional programmers give it credit for. To add to you list, back in the 80s, I wrote a simple x86 assembler, and a simple neural net, in Lotus 123.
- herodoturtle 5y ago
  > Excel is probably the single most used piece of business software in the world. Whilst I agree with the general sentiment of Excel's ubiquity, I'd argue that Email is in fact the most used piece of business software.
- gspr 5y ago
  But why? OK, I do understand why people make Quake in Excel – it's the same reason people make clock-perfect emulators for the Apollo guidance computer and use it as a pocket calculator or whatever – it's pure human curiosity. But why is Excel shoe-horned into solving business-relevant necessary problems also when it's not a good fit all?
kumarvvr 5y ago
Why not just provide a way to script Excel with Python / JS? Is it technically difficult?
- meibo 5y ago
  Scripting in Excel is a complex mess of VBA and COM, it's probably far from trivial to provide (sane) bindings into other languages. You can do it via .NET, but a lot of people prefer VBA for its "quick and dirty"-ness.
- jacobsenscott 5y ago
  You script it with VBA
  - mslip 5y ago
    Something that isn’t PTSD inducing would be better lol
- ant6n 5y ago
  I wish there was a Python excel.
  - saynotobloat 5y ago
    https://openpyxl.readthedocs.io/en/stable/index.html https://openpyxl.readthedocs.io/en/stable/index.html
    - Vaslo 5y ago
      Not that many features are applicable to Excel. I've tried these and eventually have to default back to VBA to do what I need.
  - fartcannon 5y ago
    With the uno library, you can directly script LibreOffice Calc with python. https://help.libreoffice.org/6.3/en-US/text/sbasic/python/main0000.html https://help.libreoffice.org/6.3/en-US/text/sbasic/python/ma... As a demonstration, I ran a ray tracing python script from Calc and it rendered to cells in the spreadsheet.
deepstack 5y ago
In some way sql programming like functions, cursor, store procedures is the Advance Excel programming. Why reinvent the wheel? In regards to having JS/Python scripting it, if you using Postgres, just add v8 or python plugin, and there you have your advance excel programming in the language of your choice.
kamocyc 5y ago
Related: Innovation by (and beyond) the numbers: A history of research collaborations in Excel https://www.microsoft.com/en-us/research/blog/innovation-by-and-beyond-the-numbers-a-history-of-research-collaborations-in-excel/ https://www.microsoft.com/en-us/research/blog/innovation-by-...
aperrien 5y ago
Are there any plans to add Let/Lambda functionality to LibreOffice/OpenOffice?
aarondia 5y ago
After talking with Excel users for the past year to understand what makes it such a ubiquitous tool, I don't think that its the ability to write complex formulas, but instead the reactive, visual environment (read: IDE). Of course, for some users the ability to nest 100 VLookups or create UDF's is the important part, but for the vast majority of people, their workbooks aren't __that__ complex. When it comes to data analytics IDEs there is a fundamental tradeoff between staring at the data or staring at the transformations. Excel makes sure you are brutally aware of each edit you make to your data at the expense of reproducibility and auditability of your transformations. Python takes the opposite end of the trade -- obscuring the underlying data, but bringing the transformations to the forefront. For non-programmers trying to learn Python (especially for data analytics), the biggest hurdle is losing touch with their data. I've been building a Python package, Mito (https://trymito.io/hnc https://trymito.io/hnc), to try to address this tradeoff for those who want to analyze data with the intuitiveness and data-first-ness of a spreadsheet, but with the power and traceability of Python. Mito is a Jupyter Lab extension which gives users an interactive spreadsheet that automatically converts your spreadsheet analysis to the equivalent pandas code. You can write Excel formulas, merge datasets, create pivot tables, etc.
- airstrike 5y ago
  I've seen Mito around HN and reddit before and I think it's an absolutely brilliant, but its applications may be more limited than the vast use cases covered by Excel. Maybe that's fine, as your goal may *be to carve out a niche rather than compete with Excel more broadly. The reason I say that is I think the major pain point in its hypothetical widespread adoption would be getting users accustomed to the ergonomics of accessing attributes in a programming language when they only understand noob-friendly Excel formulas, which is more functional in nature. Looking at the demo, I think some pseudo-code such as the following could be easier for e.g. an office worker than pure python: ADDCOLUMN(Sheet='Train Stations', Name='Accepts Bags') # defaults to appending at the end SETFORMULA(Column='Train Stations'!'Accepts Bags', As=IF('Train Stations'!'Checked Baggage' = "Y", 1, 0)) PIVOT(From='Train Stations', To='Pivot', Keys=('State'), Values=('Accepts Bags'), Formula=SUM) SORT(Target='Pivot', By='Accepts Bags', Direction='Asc', NA='Hide') # defaults to ascending, NA first Clearly, it's not like I've thought this through carefully and am not claiming this particular example is really ergonomic, but hopefully this illustrates the point I'm trying to make. It would not need to look like Excel, but I think the jump from spreadsheet -> Python may be a step too far for the average user than, say, spreadsheet -> some functional approach.
deleted 5y ago
[deleted]
inopinatus 5y ago
This is the way the world ends Not with a bang but a #VALUE! — T.S. Eliot, The Hollow Men (1925)
Andy_G11 5y ago
Excel is extremely accessible, which is both a strength and a weakness. People who are complete noobs are quite happy to open up Excel and pull together some logic and data with very little regard for the constraints of the tool. Then they can save it and email it to you and you can work with it. It is the epitomy of 'move fast and break things'. Try getting them to pull together a quick JavaScript file to show you what they are thinking... Whichever way Excel develops, I hope that it doesn't put off the noobs from their penchant to have a go because as much as there is the possibility for the tool to go horribly wrong, elevating it out of reach of the layman would be worse.
dang 5y ago
Some related threads: Lambda: The Ultimate Excel Worksheet Function - https://news.ycombinator.com/item?id=26900419 https://news.ycombinator.com/item?id=26900419 - April 2021 (109 comments) Xkcd: Excel Lambda - https://news.ycombinator.com/item?id=26899793 https://news.ycombinator.com/item?id=26899793 - April 2021 (1 comment) Lambda: The Excel Worksheet Function - https://news.ycombinator.com/item?id=25990978 https://news.ycombinator.com/item?id=25990978 - Feb 2021 (1 comment) Lambda: The ultimate Excel worksheet function - https://news.ycombinator.com/item?id=25923628 https://news.ycombinator.com/item?id=25923628 - Jan 2021 (4 comments) Lambda: Turn Excel formulas into custom functions - https://news.ycombinator.com/item?id=25318386 https://news.ycombinator.com/item?id=25318386 - Dec 2020 (135 comments) Announcing LAMBDA: Turn Excel formulas into custom functions - https://news.ycombinator.com/item?id=25312725 https://news.ycombinator.com/item?id=25312725 - Dec 2020 (1 comment) Microsoft introduces LAMBDA functions for Excel - https://news.ycombinator.com/item?id=25295120 https://news.ycombinator.com/item?id=25295120 - Dec 2020 (7 comments)
flakiness 5y ago
FYI: The transcript is available on the official site, if you prefer. https://www.microsoft.com/en-us/research/podcast/advancing-excel-as-a-programming-language-with-andy-gordon-and-simon-peyton-jones/ https://www.microsoft.com/en-us/research/podcast/advancing-e...
fungiblecog 5y ago
The most important point about Excel in the work environment is that it's often the only tool people have so they make it do things way beyond what is sensible. I'm a consultant and at every client (mostly public sector) I discover new ways of solving problems with Excel which would otherwise not get solved because the blockers to buying "the right tool" are simply too great. At my latest client I've plotted their data on a map by colouring in cells as pixels using custom formatting and zooming right out so it isn't too blocky. It's too hard to get access to GIS software even though other departments in the same organisation have it.
- CapmCrackaWaka 5y ago
  Good god, qgis wasn’t an option? I think I would rather draw it in MS paint than color an excel sheet like that.
  - fungiblecog 5y ago
    No it's not hand-coloured - it automatically updates with the data
    - hugle 5y ago
      Awesome and amazing, I guess it might be a bit hard to troubleshoot or enhance from VBA after the guy left.
      - fungiblecog 5y ago
        No VBA. Just lookups and conditional formatting
        throwaway426079 5y ago
        Wow. You're a creative person and they're wasting your energy on work arounds for absurd problems. I've been relatively fortunate in my public sector experience: Management have allowed me to use tools other than Excel.
jimmies 5y ago
A while ago I was bored and asked myself if I can actually solve leetcode problems with excel as a joke, and hence leetcode-excel was born. XLSX and everything. https://github.com/htruong/leetcode-excel https://github.com/htruong/leetcode-excel I got bored after a while and didn't try to solve more problems. Here is a fun thought: Excel has potentials in teaching non-programmers to think about algorithms. Translating a problem or a function from Excel to a programming language has the potential to break the ice for people who just "can't write a program."
deleted 5y ago
[deleted]
mcbishop 5y ago
I've been playing with Excel's new LAMBDA function the last few days, and it's taken my Excel love to the next level. I'm now able to write my own set of functions with my preferred naming and arguments, on top of the default Excel functions. For example, I have an "_EXTRACT_ITEM_X" parsing function that gives me the x'th item from a comma-separated list (using recursion).
benboughton1 5y ago
If Excel is as popular as everyone says it is, why don't we see more of the javascript addins? Excel as a platform makes sense to me. If I'm in Excel where all my data is, I want to import more external data or publish data externally etc. Makes sense to do it from inside Excel no?
- chupchap 5y ago
  Excel already has add-ons to pull in data from CRMs such as Salesforce.
teataster 5y ago
Working on a financial institution as a forecaster, I once inherited an excel spreadsheet that took 3 hours to open. It was full of bugs. At one point it mixed the Swiss franc and the Argentinian peso and that was sold to clients. That would be blatantly obvious if done in Python or R. It took me 2 years after inheriting to figure it out. I use spreadsheets but oftentimes they are not the right tool for the job.
jjgreen 5y ago
It used to be the case that Excel could not reliably calculate a standard deviation [1], is that fixed now? [1] https://eprintspublications.npl.co.uk/1793/ https://eprintspublications.npl.co.uk/1793/
tragomaskhalos 5y ago
Coming at this from another angle, I worked on a (.NET) project for a large construction company where the client wanted a subset of Excel-like functionality in the UI we were creating, but was adamant that we not actually use Excel. Ok then, we had a go, and it was actually quite fun coming up with a scheme for getting calculations to propagate successfully and without getting locked into infinite loops; inevitably though the result was a very poor man's copy of what Excel can actually do. So we went back and said look, far and away the best way to do this is to either use Excel itself, or embed it, whereupon the actual reason for their aversion to Excel came out: they had had cases, they explained, where project managers had used white ink in white cells to hide data and thereby forge financial calculations to their benefit, and they just wanted to prevent that from happening! I wanted to politely suggest that they solve their "technical" problem by getting more honest employees, but in any case the project got canned shortly thereafter.
tpoacher 5y ago
I would much prefer to treat visidata as a programming language in that regard :)

85 points by 0-_-0 5y ago

very interesting. By the way, sometimes for study and work I use https://audext.com/ https://audext.com/ to transcribe podcasts.

MH15 5y ago

A slight tangent but I interned at Blubrry as a software engineer a few summers back. Was a great experience!

samfisher83 5y ago

Excel is probably the single most used piece of business software in the world. People have been able to make quake. People have done 3d rendering without vba. People have done all kinds of stuff with Excel for a while now.

sokoloff 5y ago
I strongly suspect Word is used more, probably a lot more. I wonder if PowerPoint is used a similar amount.
- krono 5y ago
  Is there a real difference between the classic MS office applications, though? You can do spreadsheets in Word and Outlook, have styled text in your Excel document, add animations to your titles in Word, etc. At this point they can just as wel get rid of the different GUIs facade and instead implement some contextual interaction model (and hopefully they'll include an API so we can generate these documents programatically and don't have to deal with this nth clippy generation :) )
  - icapybara 5y ago
    Might not be from MS, but automating MS Office document generation is a solved problem: https://python-docx.readthedocs.io/en/latest/ https://python-docx.readthedocs.io/en/latest/ https://www.python-excel.org/ https://www.python-excel.org/ https://python-pptx.readthedocs.io/en/latest/ https://python-pptx.readthedocs.io/en/latest/
    - krono 5y ago
      Yeah this is awesome community work, and I'm honestly thankful for MS having left these works alone. An official solution would be nice though. These open source projects are popular enough to warrant one I'd say!
  - Closi 5y ago
    > Is there a real difference between the classic MS office applications, though? Yes, there absolutely is. You can create a document with styled text in Excel instead of Word, just as you can edit a photo in Paint instead of Photoshop. You can do it, but the two tools have dramatically different capabilities. It's important to think about developing for use-cases otherwise we will end up with overly complex software that aims to be all-things to all-people.
    - krono 5y ago
      > overly complex software that aims to be all-things to all-people This is exactly what I believe the current state of the MS Office applications to be. The interface and/or implementation isn't always ideal for the purpose you would generally use a specific app for, but the functionality is there. > dramatically different capabilities Embedding a fully functional Excel spreadsheet is only a few clicks through the ribbon and some frustration away in Outlook, Word, and even PowerPoint.
      - Closi 5y ago
        > Embedding a fully functional Excel spreadsheet is only a few clicks through the ribbon and some frustration away in Outlook, Word, and even PowerPoint. This is effectively opening a reduced version of excel in a very limited way, primarily for embedding one document in another and allowing limited editing. You don’t get the full functionality of the other application. I can see why I would want to change the axis on a graph even after I have pasted it into my email, but why would I want one app to be both my spreadsheet and my email inbox? Are you a user of office suite? I spend about 70% of my work life between excel, PowerPoint and Word and have never once wanted them to be one app, but quite often have wanted better integration.
- nvrspyx 5y ago
  I'm not quite sure. Within my work team, Word is only used occasionally, specifically for anything document related. Excel, on the other hand, is used all the time from typical number crunching to keeping track of tasks (despite how much I despise using it for the latter). I also imagine that small businesses, like restaurants, don't require Word very often, but a lot of them use Excel for finances, inventory, etc. In regards to PowerPoint, I imagine it lags pretty far behind in usage compared to both Excel and Word. Not many people are making presentations in the grand scheme of things. My intuition says that it's mostly upper management and maybe a single person in a group using PowerPoint "often".
- dimator 5y ago
  I would wager excel is used more, because spreadsheets are "live" and opened/updated much more than a written document.
  - sokoloff 5y ago
    That's true, but I posit that there are entire large categories of computer users who open Excel exactly 0 times per year but who use Word.
- Guest42 5y ago
  For me at least, I try to avoid Word because it is so slow and cumbersome compared to notepad or notepad++ for the purpose of writing notes. It seems like most of my word processing is for my eyes only.
  - Tagbert 5y ago
    A word processor like Word is really aimed at larger, more complex documents with formatting. Notes are usually smaller with simpler structure and formatting. With notes, it helps of the app gives you a way to organize the notes. This is really a different use case.
  - jdgoesmarching 5y ago
    I didn’t realize how much I hated Word until I started using Pages. For notes specifically I prefer some sort of Markdown editor.
    - Guest42 5y ago
      I’ll have to give pages a try. I think perhaps I favor the earlier versions of office products because I learned on them, they’re faster, and they weren’t as a service. I’ll go for latex if something has to be published and certainly use power point a bit but am always on the lookout for new approaches. It’s great that power point can pull/receive graph data.
- vagrantJin 5y ago
  Powerpoint is powerful once you get deep into it. I know people who make some crazy visuals using powerpoint and you would thing it was done with some adobe software.
- conductr 5y ago
  My experiences in the business world is Word has been on a sharp decline for a long time. Excel is more likely to be used as Word. It’s very common to see a spreadsheet with no math and no tabular data but it’s print formatted.
  - andylynch 5y ago
    This is spot on.
- andylynch 5y ago
  Not generally. Big firms it would be email 1st with browser for most line of business apps , excel close second, third PowerPoint, with Word behind by some distance and mainly for reading, rather then writing so Acrobat is high on the list as well. Most of the excel workbooks would be lists and trackers of various kinds, many just (big) tables without any formulae at all.
m3kw9 5y ago
Nothing beats easy.
inopinatus 5y ago
People have also made megadollar blunders. As programming environments go, Excel carefully maximises the fuckup surface, enabling all users from novice to expert to stumble unawares into hidden calculation bugs. Cell-reference slip-ups (via copypaste or otherwise) are de rigeur, of course, and they're aided and abetted by Excel's cockeyed "type" system (if we can even call it that), half-baked documentation, inconsistent function library, and mediocre/absent testing, integrity, versioning, or debugging tools. Probably the worst example I've seen first-hand was an entire retail banking loan-approval process running off of a single, shared, gigantic spreadsheet, that hundreds had tinkered with, but no-one understood or took responsibility for, and where the accompanying Word document of "things not to do" was bigger than the workbook. Even yesterday, a friend of mine discovered they'd underclaimed expenses for a total >$1,000 due to a dodgy spreadsheet. Something as simple as pasting a list of dollar amounts from a webpage into Excel can produce an incorrect SUM() if/when trailing spaces creep in, since the resulting values may be treated as strings and evaluate to zero - and so it had transpired. Not even "text to columns" could fix it; you have to a) know about this lurking monster, b) use formatting to make it casually evident, and c) use Replace to strip the whitespace. What a crock.
- fireflies_ 5y ago
  In some ways the argument against Excel is like the argument against Electron: if you're comparing it to an elegant, purpose-built and well-tested application, yes... it falls woefully short. But is that really the right comparison? Lots of people just don't have the skills or time to build a specific applications. Without Excel, what would they do? There are some places where a 90% solution is worse than no solution at all (at least no solution is a forcing function for a "real" application), and in many ways Excel isn't the best expression of the _idea_ of an Excel-like, low-barrier-to-entry declarative programming environment with a built-in UI, but it's truly a wonderful tool. It makes a lot of automation possible for a lot of people.
- Optimal_Persona 5y ago
  Haha, I discovered "always TRIM both leading and trailing spaces on both lookup criteria cells and source tables" the hard way too. At 3 in the morning...All of the 20-hour days/nights I've done as a data analyst were due to Excel malfeasance. Also - 15 digit NUMBER precision leading to truncated values that you only discover when it's too late; no Macro undo; no way I'm aware of to transform columns/rows from formulas to their values without intermediate cut/paste steps; lots of essential features (like "Copy only visible range containing hidden cells") buried in Find & Select > Go To Special menu without keyboard shortcuts; Excel's magic ability to change cell data types by merely touching a CSV (open/closing without saving changes)...
croes 5y ago
And is one of the largest source of problems because of unsupportable amateur made Excel "solutions".
kitd 5y ago
IIRC, there are an order of magnitude more Excel "programmers" than programmers in all other languages combined. Tbh, it is a much better source of business than most professional programmers give it credit for. To add to you list, back in the 80s, I wrote a simple x86 assembler, and a simple neural net, in Lotus 123.
herodoturtle 5y ago
> Excel is probably the single most used piece of business software in the world. Whilst I agree with the general sentiment of Excel's ubiquity, I'd argue that Email is in fact the most used piece of business software.
gspr 5y ago
But why? OK, I do understand why people make Quake in Excel – it's the same reason people make clock-perfect emulators for the Apollo guidance computer and use it as a pocket calculator or whatever – it's pure human curiosity. But why is Excel shoe-horned into solving business-relevant necessary problems also when it's not a good fit all?

kumarvvr 5y ago

Why not just provide a way to script Excel with Python / JS? Is it technically difficult?

meibo 5y ago
Scripting in Excel is a complex mess of VBA and COM, it's probably far from trivial to provide (sane) bindings into other languages. You can do it via .NET, but a lot of people prefer VBA for its "quick and dirty"-ness.
jacobsenscott 5y ago
You script it with VBA
- mslip 5y ago
  Something that isn’t PTSD inducing would be better lol
ant6n 5y ago
I wish there was a Python excel.
- saynotobloat 5y ago
  https://openpyxl.readthedocs.io/en/stable/index.html https://openpyxl.readthedocs.io/en/stable/index.html
  - Vaslo 5y ago
    Not that many features are applicable to Excel. I've tried these and eventually have to default back to VBA to do what I need.
- fartcannon 5y ago
  With the uno library, you can directly script LibreOffice Calc with python. https://help.libreoffice.org/6.3/en-US/text/sbasic/python/main0000.html https://help.libreoffice.org/6.3/en-US/text/sbasic/python/ma... As a demonstration, I ran a ray tracing python script from Calc and it rendered to cells in the spreadsheet.

deepstack 5y ago

In some way sql programming like functions, cursor, store procedures is the Advance Excel programming. Why reinvent the wheel? In regards to having JS/Python scripting it, if you using Postgres, just add v8 or python plugin, and there you have your advance excel programming in the language of your choice.

kamocyc 5y ago

Related: Innovation by (and beyond) the numbers: A history of research collaborations in Excel https://www.microsoft.com/en-us/research/blog/innovation-by-and-beyond-the-numbers-a-history-of-research-collaborations-in-excel/ https://www.microsoft.com/en-us/research/blog/innovation-by-...

aperrien 5y ago

Are there any plans to add Let/Lambda functionality to LibreOffice/OpenOffice?

aarondia 5y ago

After talking with Excel users for the past year to understand what makes it such a ubiquitous tool, I don't think that its the ability to write complex formulas, but instead the reactive, visual environment (read: IDE). Of course, for some users the ability to nest 100 VLookups or create UDF's is the important part, but for the vast majority of people, their workbooks aren't __that__ complex. When it comes to data analytics IDEs there is a fundamental tradeoff between staring at the data or staring at the transformations. Excel makes sure you are brutally aware of each edit you make to your data at the expense of reproducibility and auditability of your transformations. Python takes the opposite end of the trade -- obscuring the underlying data, but bringing the transformations to the forefront. For non-programmers trying to learn Python (especially for data analytics), the biggest hurdle is losing touch with their data. I've been building a Python package, Mito (https://trymito.io/hnc https://trymito.io/hnc), to try to address this tradeoff for those who want to analyze data with the intuitiveness and data-first-ness of a spreadsheet, but with the power and traceability of Python. Mito is a Jupyter Lab extension which gives users an interactive spreadsheet that automatically converts your spreadsheet analysis to the equivalent pandas code. You can write Excel formulas, merge datasets, create pivot tables, etc.

airstrike 5y ago
I've seen Mito around HN and reddit before and I think it's an absolutely brilliant, but its applications may be more limited than the vast use cases covered by Excel. Maybe that's fine, as your goal may *be to carve out a niche rather than compete with Excel more broadly. The reason I say that is I think the major pain point in its hypothetical widespread adoption would be getting users accustomed to the ergonomics of accessing attributes in a programming language when they only understand noob-friendly Excel formulas, which is more functional in nature. Looking at the demo, I think some pseudo-code such as the following could be easier for e.g. an office worker than pure python: ADDCOLUMN(Sheet='Train Stations', Name='Accepts Bags') # defaults to appending at the end SETFORMULA(Column='Train Stations'!'Accepts Bags', As=IF('Train Stations'!'Checked Baggage' = "Y", 1, 0)) PIVOT(From='Train Stations', To='Pivot', Keys=('State'), Values=('Accepts Bags'), Formula=SUM) SORT(Target='Pivot', By='Accepts Bags', Direction='Asc', NA='Hide') # defaults to ascending, NA first Clearly, it's not like I've thought this through carefully and am not claiming this particular example is really ergonomic, but hopefully this illustrates the point I'm trying to make. It would not need to look like Excel, but I think the jump from spreadsheet -> Python may be a step too far for the average user than, say, spreadsheet -> some functional approach.

deleted 5y ago

[deleted]

inopinatus 5y ago

This is the way the world ends Not with a bang but a #VALUE! — T.S. Eliot, The Hollow Men (1925)

Andy_G11 5y ago

Excel is extremely accessible, which is both a strength and a weakness. People who are complete noobs are quite happy to open up Excel and pull together some logic and data with very little regard for the constraints of the tool. Then they can save it and email it to you and you can work with it. It is the epitomy of 'move fast and break things'. Try getting them to pull together a quick JavaScript file to show you what they are thinking... Whichever way Excel develops, I hope that it doesn't put off the noobs from their penchant to have a go because as much as there is the possibility for the tool to go horribly wrong, elevating it out of reach of the layman would be worse.

dang 5y ago

Some related threads: Lambda: The Ultimate Excel Worksheet Function - https://news.ycombinator.com/item?id=26900419 https://news.ycombinator.com/item?id=26900419 - April 2021 (109 comments) Xkcd: Excel Lambda - https://news.ycombinator.com/item?id=26899793 https://news.ycombinator.com/item?id=26899793 - April 2021 (1 comment) Lambda: The Excel Worksheet Function - https://news.ycombinator.com/item?id=25990978 https://news.ycombinator.com/item?id=25990978 - Feb 2021 (1 comment) Lambda: The ultimate Excel worksheet function - https://news.ycombinator.com/item?id=25923628 https://news.ycombinator.com/item?id=25923628 - Jan 2021 (4 comments) Lambda: Turn Excel formulas into custom functions - https://news.ycombinator.com/item?id=25318386 https://news.ycombinator.com/item?id=25318386 - Dec 2020 (135 comments) Announcing LAMBDA: Turn Excel formulas into custom functions - https://news.ycombinator.com/item?id=25312725 https://news.ycombinator.com/item?id=25312725 - Dec 2020 (1 comment) Microsoft introduces LAMBDA functions for Excel - https://news.ycombinator.com/item?id=25295120 https://news.ycombinator.com/item?id=25295120 - Dec 2020 (7 comments)

flakiness 5y ago

FYI: The transcript is available on the official site, if you prefer. https://www.microsoft.com/en-us/research/podcast/advancing-excel-as-a-programming-language-with-andy-gordon-and-simon-peyton-jones/ https://www.microsoft.com/en-us/research/podcast/advancing-e...

fungiblecog 5y ago

The most important point about Excel in the work environment is that it's often the only tool people have so they make it do things way beyond what is sensible. I'm a consultant and at every client (mostly public sector) I discover new ways of solving problems with Excel which would otherwise not get solved because the blockers to buying "the right tool" are simply too great. At my latest client I've plotted their data on a map by colouring in cells as pixels using custom formatting and zooming right out so it isn't too blocky. It's too hard to get access to GIS software even though other departments in the same organisation have it.

CapmCrackaWaka 5y ago
Good god, qgis wasn’t an option? I think I would rather draw it in MS paint than color an excel sheet like that.
- fungiblecog 5y ago
  No it's not hand-coloured - it automatically updates with the data
  - hugle 5y ago
    Awesome and amazing, I guess it might be a bit hard to troubleshoot or enhance from VBA after the guy left.
    - fungiblecog 5y ago
      No VBA. Just lookups and conditional formatting
      - throwaway426079 5y ago
        Wow. You're a creative person and they're wasting your energy on work arounds for absurd problems. I've been relatively fortunate in my public sector experience: Management have allowed me to use tools other than Excel.

jimmies 5y ago

A while ago I was bored and asked myself if I can actually solve leetcode problems with excel as a joke, and hence leetcode-excel was born. XLSX and everything. https://github.com/htruong/leetcode-excel https://github.com/htruong/leetcode-excel I got bored after a while and didn't try to solve more problems. Here is a fun thought: Excel has potentials in teaching non-programmers to think about algorithms. Translating a problem or a function from Excel to a programming language has the potential to break the ice for people who just "can't write a program."

deleted 5y ago

[deleted]

mcbishop 5y ago

I've been playing with Excel's new LAMBDA function the last few days, and it's taken my Excel love to the next level. I'm now able to write my own set of functions with my preferred naming and arguments, on top of the default Excel functions. For example, I have an "_EXTRACT_ITEM_X" parsing function that gives me the x'th item from a comma-separated list (using recursion).

benboughton1 5y ago

If Excel is as popular as everyone says it is, why don't we see more of the javascript addins? Excel as a platform makes sense to me. If I'm in Excel where all my data is, I want to import more external data or publish data externally etc. Makes sense to do it from inside Excel no?

chupchap 5y ago
Excel already has add-ons to pull in data from CRMs such as Salesforce.

teataster 5y ago

Working on a financial institution as a forecaster, I once inherited an excel spreadsheet that took 3 hours to open. It was full of bugs. At one point it mixed the Swiss franc and the Argentinian peso and that was sold to clients. That would be blatantly obvious if done in Python or R. It took me 2 years after inheriting to figure it out. I use spreadsheets but oftentimes they are not the right tool for the job.

jjgreen 5y ago

It used to be the case that Excel could not reliably calculate a standard deviation [1], is that fixed now? [1] https://eprintspublications.npl.co.uk/1793/ https://eprintspublications.npl.co.uk/1793/

tragomaskhalos 5y ago

Coming at this from another angle, I worked on a (.NET) project for a large construction company where the client wanted a subset of Excel-like functionality in the UI we were creating, but was adamant that we not actually use Excel. Ok then, we had a go, and it was actually quite fun coming up with a scheme for getting calculations to propagate successfully and without getting locked into infinite loops; inevitably though the result was a very poor man's copy of what Excel can actually do. So we went back and said look, far and away the best way to do this is to either use Excel itself, or embed it, whereupon the actual reason for their aversion to Excel came out: they had had cases, they explained, where project managers had used white ink in white cells to hide data and thereby forge financial calculations to their benefit, and they just wanted to prevent that from happening! I wanted to politely suggest that they solve their "technical" problem by getting more honest employees, but in any case the project got canned shortly thereafter.

tpoacher 5y ago

I would much prefer to treat visidata as a programming language in that regard :)