I clicked out of idle curiosity and I'm so strangely ambivalent about what I just read. Excel gets named functions: great! But then the example reinforces the fact that you're just sticking a name...
I clicked out of idle curiosity and I'm so strangely ambivalent about what I just read.
Excel gets named functions: great! But then the example reinforces the fact that you're just sticking a name on an unreadable single-line soup of nested calls, characters and commas...
And giving it a name involves clicking on a several things, apparently duplicating the entire function into one of those boxes (presumably automatic, but still super weird), and having a grid of function definitions completely separate to the grid of cells that is your spreadsheet. Except, as mentioned, the function code exists in both grids. It seems like somewhere between mediocre and terrible UX, but it also seems like a huge improvement!
I'm sure there's user demand for this, and I can certainly see how it makes complex Excel functions less awful, but surely just using an actual programming language would be much more pleasant at that point?
I know developers have a habit of ignoring the learning curve and fundamental complexity of coding - but in this case the person's already working with equal or greater complexity in Excel formulae. It just seems like a few lines of JS would be a lot easier at that point.
Yeah, I don't know. I don't use Excel and I only use Google Sheets occasionally for quick calculations. It seems neat, though. It's a bit surprising that they didn't invent any new syntax at all....
Yeah, I don't know. I don't use Excel and I only use Google Sheets occasionally for quick calculations. It seems neat, though.
It's a bit surprising that they didn't invent any new syntax at all. Everything looks like a function call, even when it's a special form. It reminds me a little of lisp with funny-looking lists.
Conceptually, I think organizing formulas on their own sheet might be pretty reasonable. Also, the fact that they're fairly unreadable one-liners might encourage people to write a lot of short functions, which at least gives them names.
I've gotta imagine there is a swath of people who use Excel to do some fairly advanced stuff, and have done so for years but because that's the only aspect of their life where they do anything...
but in this case the person's already working with equal or greater complexity in Excel formulae. It just seems like a few lines of JS would be a lot easier at that point
I've gotta imagine there is a swath of people who use Excel to do some fairly advanced stuff, and have done so for years but because that's the only aspect of their life where they do anything close to programming, they don't view themselves as programmers. As a result they limit themselves, and the mention of having to do something in a "real" programming language intimidates them enough that they don't bother trying. Giving them more tools in "normal" Excel is probably going to be more effective.
It'd be interesting to see a survey by MS of how Excel users self-describe, contrasted with what they actually do in Excel.
I think you are absolutely spot on about this. It seems like a great opportunity for Microsoft's UX people to step in with a positive nudge - maybe an icon on the formula bar to pop out a...
I think you are absolutely spot on about this. It seems like a great opportunity for Microsoft's UX people to step in with a positive nudge - maybe an icon on the formula bar to pop out a multiline input box that accepts JS (which I believe Excel fully supports already) as well as existing Excel functions, for example. Or indeed something much better, as I'm sure a team of experienced people can think up! Bring it neatly, incrementally, and gently into the workflow rather than throwing up a big spiky gate that says "programmers only beyond this point".
The option they went with seems very much not to be that, and it kind of strikes me as a missed opportunity, even while being an improvement.
Hilariously so. So much investment banking modeling is done with... Excel. Very, very complicated Excel spreadsheets. Work that has millions if not billions attached to it.
I've gotta imagine there is a swath of people who use Excel to do some fairly advanced stuff
Hilariously so. So much investment banking modeling is done with... Excel. Very, very complicated Excel spreadsheets. Work that has millions if not billions attached to it.
Oh I'm aware, I've heard of some crazy stuff. And some people (my brother) learn some advanced Excel, realize they like it, and go on to try to learn actual programming and other languages....
Oh I'm aware, I've heard of some crazy stuff. And some people (my brother) learn some advanced Excel, realize they like it, and go on to try to learn actual programming and other languages. There's that niche though, of people who do advanced Excel, but don't think of it as programming.
As someone who learned programming due to Excel's awfulness, I can divulge a bit on this. Yes, I can do things much faster, easier, and cleaner in Python. However, I'm the only person who can do...
I'm sure there's user demand for this, and I can certainly see how it makes complex Excel functions less awful, but surely just using an actual programming language would be much more pleasant at that point?
As someone who learned programming due to Excel's awfulness, I can divulge a bit on this. Yes, I can do things much faster, easier, and cleaner in Python. However, I'm the only person who can do this on my team. When it comes to handing a report or data processing script off to the next person, it is impossible and thus, I have created tech debt on my team.
I still use Python for my personal one-off projects that I know are limited use and/or something that I know I can continue on my own. If I have to, I am proficient enough in VBA that I can google whatever thing I have created as 10 line piece of code in Python and translate it into the 50+ line monstrosity. Additionally, I have found that Excel's built in Power Query tool is extremely helpful and easy to do data clean up and reporting set up that I can then hand off to others with minimal training. I have garnered quite a bit of interest among several teams in training them how to use it since no coding is involved.
I hate Excel but sadly it is entrenched quite deeply.
Excel is just going to become a full-fledged IDE isn't it? It's actually a good move. Excel is the one part of the MS Office Suite that I don't think has been properly replicated or exceeded by...
Excel is just going to become a full-fledged IDE isn't it?
It's actually a good move. Excel is the one part of the MS Office Suite that I don't think has been properly replicated or exceeded by anyone else. There are spreadsheet tools that do a better job at creating charts and generate more elegant looking tables. In fact calling excel a spreadsheet tool kind of misses what it evolved into. The actual "spreadsheet" part of excel is good but not very performant or pretty and other tools beat it in that regard. What it's really good at now is being a bootleg programming and runtime environment for non-programmers. This is basically a programming language that anyone with a firm grasp on logic and basic arithmetic can grok and make weird bootleg tools out of.
Granted those tools as kludgy and fall apart between versions and are extremely brittle against even the slightest bit of user error. But this kind of general purpose way for non-technical business users to solve their own problem can't be understated.
While I share @Greg's viewpoints I'm also wondering what's the potential for abuse. Could this enable a new wave of malware that's no longer dependent on Macros/VB code but rather use formulas?
While I share @Greg's viewpoints I'm also wondering what's the potential for abuse. Could this enable a new wave of malware that's no longer dependent on Macros/VB code but rather use formulas?
I’m not sure how it could be exploited. There is nothing that a lambda can do that you can’t already do with normal excel features. If lambdas can be exploited, excel can already be exploited. In...
I’m not sure how it could be exploited. There is nothing that a lambda can do that you can’t already do with normal excel features. If lambdas can be exploited, excel can already be exploited. In effect, it’s a quality of life feature that makes copying and pasting easier.
It seems like it would be safer than other code because it only supports pure functions and doesn’t enable any new forms of I/O? At worst it could go into an infinite loop and refuse to return a...
It seems like it would be safer than other code because it only supports pure functions and doesn’t enable any new forms of I/O? At worst it could go into an infinite loop and refuse to return a value.
Or are there Excel formulas that a function could call that have side effects?
I clicked out of idle curiosity and I'm so strangely ambivalent about what I just read.
Excel gets named functions: great! But then the example reinforces the fact that you're just sticking a name on an unreadable single-line soup of nested calls, characters and commas...
And giving it a name involves clicking on a several things, apparently duplicating the entire function into one of those boxes (presumably automatic, but still super weird), and having a grid of function definitions completely separate to the grid of cells that is your spreadsheet. Except, as mentioned, the function code exists in both grids. It seems like somewhere between mediocre and terrible UX, but it also seems like a huge improvement!
I'm sure there's user demand for this, and I can certainly see how it makes complex Excel functions less awful, but surely just using an actual programming language would be much more pleasant at that point?
I know developers have a habit of ignoring the learning curve and fundamental complexity of coding - but in this case the person's already working with equal or greater complexity in Excel formulae. It just seems like a few lines of JS would be a lot easier at that point.
Yeah, I don't know. I don't use Excel and I only use Google Sheets occasionally for quick calculations. It seems neat, though.
It's a bit surprising that they didn't invent any new syntax at all. Everything looks like a function call, even when it's a special form. It reminds me a little of lisp with funny-looking lists.
Conceptually, I think organizing formulas on their own sheet might be pretty reasonable. Also, the fact that they're fairly unreadable one-liners might encourage people to write a lot of short functions, which at least gives them names.
I've gotta imagine there is a swath of people who use Excel to do some fairly advanced stuff, and have done so for years but because that's the only aspect of their life where they do anything close to programming, they don't view themselves as programmers. As a result they limit themselves, and the mention of having to do something in a "real" programming language intimidates them enough that they don't bother trying. Giving them more tools in "normal" Excel is probably going to be more effective.
It'd be interesting to see a survey by MS of how Excel users self-describe, contrasted with what they actually do in Excel.
I think you are absolutely spot on about this. It seems like a great opportunity for Microsoft's UX people to step in with a positive nudge - maybe an icon on the formula bar to pop out a multiline input box that accepts JS (which I believe Excel fully supports already) as well as existing Excel functions, for example. Or indeed something much better, as I'm sure a team of experienced people can think up! Bring it neatly, incrementally, and gently into the workflow rather than throwing up a big spiky gate that says "programmers only beyond this point".
The option they went with seems very much not to be that, and it kind of strikes me as a missed opportunity, even while being an improvement.
Hilariously so. So much investment banking modeling is done with... Excel. Very, very complicated Excel spreadsheets. Work that has millions if not billions attached to it.
Oh I'm aware, I've heard of some crazy stuff. And some people (my brother) learn some advanced Excel, realize they like it, and go on to try to learn actual programming and other languages. There's that niche though, of people who do advanced Excel, but don't think of it as programming.
As someone who learned programming due to Excel's awfulness, I can divulge a bit on this. Yes, I can do things much faster, easier, and cleaner in Python. However, I'm the only person who can do this on my team. When it comes to handing a report or data processing script off to the next person, it is impossible and thus, I have created tech debt on my team.
I still use Python for my personal one-off projects that I know are limited use and/or something that I know I can continue on my own. If I have to, I am proficient enough in VBA that I can google whatever thing I have created as 10 line piece of code in Python and translate it into the 50+ line monstrosity. Additionally, I have found that Excel's built in Power Query tool is extremely helpful and easy to do data clean up and reporting set up that I can then hand off to others with minimal training. I have garnered quite a bit of interest among several teams in training them how to use it since no coding is involved.
I hate Excel but sadly it is entrenched quite deeply.
Excel is just going to become a full-fledged IDE isn't it?
It's actually a good move. Excel is the one part of the MS Office Suite that I don't think has been properly replicated or exceeded by anyone else. There are spreadsheet tools that do a better job at creating charts and generate more elegant looking tables. In fact calling excel a spreadsheet tool kind of misses what it evolved into. The actual "spreadsheet" part of excel is good but not very performant or pretty and other tools beat it in that regard. What it's really good at now is being a bootleg programming and runtime environment for non-programmers. This is basically a programming language that anyone with a firm grasp on logic and basic arithmetic can grok and make weird bootleg tools out of.
Granted those tools as kludgy and fall apart between versions and are extremely brittle against even the slightest bit of user error. But this kind of general purpose way for non-technical business users to solve their own problem can't be understated.
Frankly in a corporate environment (assuming office 365) Microsoft Power Apps are much more suitable for the sort of tools your are describing.
Well, the next step would be to add another sheet and write unit tests for your functions. I bet Excel already has everything needed to do it.
While I share @Greg's viewpoints I'm also wondering what's the potential for abuse. Could this enable a new wave of malware that's no longer dependent on Macros/VB code but rather use formulas?
I’m not sure how it could be exploited. There is nothing that a lambda can do that you can’t already do with normal excel features. If lambdas can be exploited, excel can already be exploited. In effect, it’s a quality of life feature that makes copying and pasting easier.
It seems like it would be safer than other code because it only supports pure functions and doesn’t enable any new forms of I/O? At worst it could go into an infinite loop and refuse to return a value.
Or are there Excel formulas that a function could call that have side effects?