Excel is pretty dang cool(buttondown.email)
buttondown.email
Excel is pretty dang cool
https://buttondown.email/hillelwayne/archive/excel-is-pretty-dang-cool/
261 comments
The one thing that drives me crazy about Excel is that function names are localized. So if you use the German version you'll have to use "SUMME" instead of "SUM" etc. this is really annoying and I wish there was a way to always use the English word. I'd love to talk to the person who made the decision that this was a good idea. In the CAS tool Rhino, you can always do _command to use an English command even in localized versions...wouldn't be that hard to provide this feature.
Excel is for end users. Not every Microsoft customer speaks English.
But you don't have to speak English to use sum() to sum values. You only have to know the language of Excel, in which sum() means adding things together.
I never understood this argument. To use a function you have to look up its name anyway*, so what does it matter which language inspired the name? I wouldn't mind if sum() was known as hezrtsh() as long as it was consistently so, I could have a chance of learning it.
----
* I still sometimes struggle to remember if it's average() or mean() and whether it's count() or length() -- different programming environments use different names. So even as an English speaker, I have to look up the word for each specific programming environment.
I never understood this argument. To use a function you have to look up its name anyway*, so what does it matter which language inspired the name? I wouldn't mind if sum() was known as hezrtsh() as long as it was consistently so, I could have a chance of learning it.
----
* I still sometimes struggle to remember if it's average() or mean() and whether it's count() or length() -- different programming environments use different names. So even as an English speaker, I have to look up the word for each specific programming environment.
I never knew Excel localized function names. If I have an xlsx that uses a German-localized function like SUMME, will it error out when ran on an English-localized machine? Since they can localize function names, effectively mapping (presumably) English function names to characters in each supported language, shouldn't an inverse function be feasible? One that would map SUMME to SUM, or some other unique identifier?
Under normal circumstances it gets mapped to the right localised variant when the sheet is opened on a different machine. So on your own machine you see and enter the things you're used to. The issues come when working on someone else's machine, or when that automatic process fails for some reason -- a friend of mine sometimes runs into that, where it just inexplicably... doesn't.
I don't know about these days, but I implemented an XLS reader 20 years ago.
The cell formulas were actually stored in Reverse Polish Notation with each function represented as a number. The actual ASCII you entered was never stored anywhere.
It's probably the most interesting and amusing file format detail I ever stumbled upon.
The cell formulas were actually stored in Reverse Polish Notation with each function represented as a number. The actual ASCII you entered was never stored anywhere.
It's probably the most interesting and amusing file format detail I ever stumbled upon.
It works 99,9% of the time.
There are few obscure functions that have parameters and those parameters can come in home language - so they will stop working. Also there are some issues with charts.
In general Excel is made for corporations and people from different countries send each other files all the time.
However it would be nice to be able to dynamically switch language. There are few websites that translates formulas.
There are few obscure functions that have parameters and those parameters can come in home language - so they will stop working. Also there are some issues with charts.
In general Excel is made for corporations and people from different countries send each other files all the time.
However it would be nice to be able to dynamically switch language. There are few websites that translates formulas.
AFAIK Excel functions do not really-really have a "name".
The name you type in - say - a German version is saved as a "function number" on file.
When the file is opened in - still say - an English version, these "function numbers" are rendered as the English names.
The name you type in - say - a German version is saved as a "function number" on file.
When the file is opened in - still say - an English version, these "function numbers" are rendered as the English names.
I am also annoyed by this and just resort to an all English office installation most times. However, for the office user market it is a clear advantage. This way anyone can pick up the tool and discover.
Sometime in the 90’s, even VBA keywords were localized.
Yes, this happened in Excel 5/95. The first version with VBA in addition to Excel 4.0-Macros. By the way, Excel 4.0-Macros were also localized. The backlash was nearly immediate because localized xls-files were not always interchangeable. Support for localized VBA was removed in Excel 97.
English is not universal, that's the main reasoning for that. You would be amazed during the decade of "Internationalization" it was the main talking points of every magazines and computer shows.
> English is not universal
I think only because Excel is not, at its core, a programming tool.
For programming languages, English is universal. You are pretty much forced to learn English in order to take your first programming steps, and -- as a non-native speaker -- I find this is a good thing. This way, we have a lingua franca of programming.
It's not because of a particular love of English. It is what it is. I would have welcomed French or Italian or whatever had it been the lingua franca instead. I would probably NOT have welcomed Japanese or Chinese written with ideograms because those writing systems are completely extraneous and hard to match for my Western brain, but anything else is fair game.
A "Tower of Babel" situation would be the worst.
I think only because Excel is not, at its core, a programming tool.
For programming languages, English is universal. You are pretty much forced to learn English in order to take your first programming steps, and -- as a non-native speaker -- I find this is a good thing. This way, we have a lingua franca of programming.
It's not because of a particular love of English. It is what it is. I would have welcomed French or Italian or whatever had it been the lingua franca instead. I would probably NOT have welcomed Japanese or Chinese written with ideograms because those writing systems are completely extraneous and hard to match for my Western brain, but anything else is fair game.
A "Tower of Babel" situation would be the worst.
They should support both spellings, and a file saved in germany should work in france.
I quote, "Excel has actual programming affordances now"...
Range names "fixes one of the biggest problems that makes spreadsheets illegible"
"Instead of writing the formula =A1B1, you can do =WidthHeight like you should have been able to 30 years ago."
Not sure what this dude is talking about. Range names have been in Excel for decades.
"Instead of writing the formula =A1B1, you can do =WidthHeight like you should have been able to 30 years ago."
Not sure what this dude is talking about. Range names have been in Excel for decades.
but it’s somehow underused. Often people don’t even realise they should be creating tables at all. Power query and friends are black magic at that point.
It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.
It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.
At my former company I first naivly tried to get more (non software) technical experts to use python and databases. That was quite an uphil battle, and we quickly pivoted to teach them how to make better use of excel. First and foremost: use tables. That easily leads to clearly deliniating input data, computations and output. Then we proceded to provide template sheets that use powerquery to fetch shared input data from centralised API's. This way we could let the end user do all the work, even though it might be a bit more messy/error prone. For well extablished workflows I could then take the excel program as a spec, and build a e.g. a webservice from that. Ss all the exceptions etc are already dealt with by the end-users, you can basically reverse engineer the spec from the excel sheet. As long as you can guide them to making legible excel sheets, it saves so much misunderstandings vs writing specs from scratch and building from that.
Bingo! Excel gives you a front row seat to the problems the business is trying to solve. It is usually not the best tool, but what they put together tells you what they need for a better solution.
I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call.
I wrote this to make that easy: https://GitHub.com
I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call.
I wrote this to make that easy: https://GitHub.com
Excel excels as an exploratory / rapid prototyping tool.
Excel fails as an app development and execution platform, and specifically one integrated into a core business process.
Everyone who's worked in enterprise long enough has seen both. It'd be great if there was an enforceable "modern mode" Excel flag that kept people from going nuts with macros and programmability, while retaining all its strengths.
Excel fails as an app development and execution platform, and specifically one integrated into a core business process.
Everyone who's worked in enterprise long enough has seen both. It'd be great if there was an enforceable "modern mode" Excel flag that kept people from going nuts with macros and programmability, while retaining all its strengths.
> I could then take the excel program as a spec, and build a e.g. a webservice from that
That sounds like a potentially great approach for consultancy business/product discovery.
That sounds like a potentially great approach for consultancy business/product discovery.
> they should be creating tables
Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including:
- Multi-row headers - Merged-cell headers - Headers with the same name (sometimes useful) - Formulas that cross rows (e.g. iteratively refer to the previous row) - Different table sections (e.g. a table-width merged row with one header) - Merged rows
The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.
Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including:
- Multi-row headers - Merged-cell headers - Headers with the same name (sometimes useful) - Formulas that cross rows (e.g. iteratively refer to the previous row) - Different table sections (e.g. a table-width merged row with one header) - Merged rows
The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.
Merged cells and multi row headers make data processing very difficult. These are best avoided in any sheet doing any computation. Only for reporting they are useful, especially when auto-generated as part if a pivot table. But I have never seen a legitimate use of merged cells in a computation.
Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot.
Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name
Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot.
Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name
Oh hard disagree, friend.
I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in.
I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in.
I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
Have you tried XLOOKUP?
I like it more than index match.
I like it more than index match.
Well I work in an industry which still uses an archaic version of Excel. 2016 for Windows I guess? No XLOOKUP for me, yet. But yeah I’ll switch when it’s available.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
These days I am working with data analysts who are using Excel, they were mind blown to discover power query...
If your data is <10k rows, Excel is great. After that lots of stuff stops working. Stick to R or Python data science libraries for real work.
i'm using a 35k row excel daily without any hiccups. Sorting, Vlookup, running formulas for all rows
Which stuff exactly?
filtering drop-down 10k list limit is the most obvious one. There is also the hard traditional 1,048,576 limit which many hobbyist Covid tracking folks ran into.
Hobbyists and the UK government.
They somehow cooked up a spreadsheet that make them hit the limits much earlier.
https://www.bbc.com/news/technology-54423988.amp
They somehow cooked up a spreadsheet that make them hit the limits much earlier.
https://www.bbc.com/news/technology-54423988.amp
This was a massively outdated version of the software - it’s not entirely surprising that if you use c10-year old software there are more limits.
Excel has grown hugely since then.
Excel has grown hugely since then.
Excel limits haven't gone away: https://support.microsoft.com/en-us/office/excel-specificati...
Of course there are still limits, the article was talking about the 2003 limit of 64k lines which has now been increased to 1m.
You can’t exceed these limits without being heavily alerted/warned by the application too.
Every spreadsheet application and database has these sort of limits anyway, eg:
* Google Sheets limits total cells, although can only handle a tiny fraction of what excel can.
* Postgres limits columns to 1600 (much less than excel)
* Mongo limits document size
You can’t exceed these limits without being heavily alerted/warned by the application too.
Every spreadsheet application and database has these sort of limits anyway, eg:
* Google Sheets limits total cells, although can only handle a tiny fraction of what excel can.
* Postgres limits columns to 1600 (much less than excel)
* Mongo limits document size
That’s completely alarming and much worse.
It's like a TIL post on the front page of Reddit that states something you assumed everybody knew, yet has 30,000 upvotes.
Yes but did you know about the yoga pose button
i still don't know what that meant
The feature name is "linked data types", under the "data" tab. Put, say, "downward dog" into a cell and click the "yoga" linked data type.
There's other, more useful data types, like cities and ZIP codes and stocks, I just listed the yoga one because it's the funniest.
There's other, more useful data types, like cities and ZIP codes and stocks, I just listed the yoga one because it's the funniest.
Looks like the yoga button is a thing of the past :(
> After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported.
https://support.microsoft.com/en-us/office/what-linked-data-...
> After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported.
https://support.microsoft.com/en-us/office/what-linked-data-...
Perhaps there are time zone differences, but it's not 2023 yet where I am =)
haha good point, but I can't see those data types?
It's the learn TLA+ person :) Awesome to hear he is exploring Excel. Is that the manual version of crunching models for formal consistency?
Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet.
Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet.
Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
XKCD 1053 is relevant here. You and I knew it, but there are going to be lots of people who learn about it for the first time today, and that's a net positive.
https://xkcd.com/1053/ (to make a link available)
[deleted]
Yeah, but not a post for HN. Did you know Excel has a programming language!!!??? Whahhh!!!
HN still accepts new accounts. It's not an exclusive club for old people.
Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
I didn’t know about named ranges, and think it’s pretty cool. Of course, when I thought of places where I could apply them I realized that by the time I use enough Excel to use named ranges, I should probably switch to a real programming language.
Just an HN formatting note... To have * characters taken literally instead of italicizing the text in between, prefix each one with a backslash, like this:
Instead of writing the formula =A1*B1, you can do =Width*Height
Instead of writing the formula =A1\*B1, you can do =Width\*Height
Instead of writing the formula =A1*B1, you can do =Width*Height
Namend cells and ranges was one of the major features of Microsoft Multiplan (1982). See p. 60 in [1].
MS Excel 2.0 (1987) could do the same, but entering names for ranges was not as easy as in Multiplan. Look in the menu for Formula | Names…
[1] https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...
MS Excel 2.0 (1987) could do the same, but entering names for ranges was not as easy as in Multiplan. Look in the menu for Formula | Names…
[1] https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...
Naming ranges in Excel is pretty darn easy, at least if you're doing full columns or rows - you select the range you want named (including by clicking the row/column header), then click into the field just to the left of the formula button that shows the selected cell. Just edit the name right there, it'll apply that name to the selected range.
Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells.
Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells.
Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
Always reminds me of this video.. https://www.youtube.com/watch?v=0nbkaYsR94c Excel has proper tables, named columns, better structured data etc.
Excel has also finally added sum() and other functions that allow the spreadsheet to do the hard work instead of making you use a calculator to add all the numbers up.
You joke, but: https://www.reddit.com/r/excel/comments/a0wot5/excelgore_sto...
>A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
>A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
I long for the days of all-in-one word processor/spreadsheet/database apps.
Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.
Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.
Side observation; I’ve found Emacs org-mode to give me a lot of the functionality I wanted from combined word processor/spreadsheet/database apps.
decimal points all lined up (hell I can't even do that now)
In Word there are different types of tab stops, notably Left, Center, Right and Decimal.
If you turn on the Ruler (View tab, Ruler checkbox) you'll see a little bold "L" at the top left where the side and top rulers meet - that's not an L, that's an indicator for the kind of tab stop that will be created when you click on the top ruler. You can click on that tab stop type indicator to switch between tab stop and margin types - or if you double-click on the top ruler to set a tab stop it should show you the tab stop dialog that also allows choosing a tab stop type for each defined stop (along with things like setting a tab stop leader for things like a dotted line . . . . . . . . . . . . . . across to a page number in a table of contents).
In Word there are different types of tab stops, notably Left, Center, Right and Decimal.
If you turn on the Ruler (View tab, Ruler checkbox) you'll see a little bold "L" at the top left where the side and top rulers meet - that's not an L, that's an indicator for the kind of tab stop that will be created when you click on the top ruler. You can click on that tab stop type indicator to switch between tab stop and margin types - or if you double-click on the top ruler to set a tab stop it should show you the tab stop dialog that also allows choosing a tab stop type for each defined stop (along with things like setting a tab stop leader for things like a dotted line . . . . . . . . . . . . . . across to a page number in a table of contents).
Meh, this is clearly fabricated. I was following along with someone using Excel as a word processor up until they were also using Word as a spreadsheet.
I wish I would be like that in 20 years (I’m in my 40s now)
That’s pretty artistic whether intended or not.
e e cummings, but in Excel.
https://en.wikipedia.org/wiki/E._E._Cummings
https://en.wikipedia.org/wiki/E._E._Cummings
I once spoke to someone who didn't know about formulas yet, so they just used the summary data on the status bar + copy/paste: https://support.microsoft.com/en-us/office/view-summary-data...
My partner and I are in the process of recording an Excel course because I walked past her desk and saw her doing her accounts in Excel … with a Casio calculator. She was reading a value, doing it on the calculator, and typing it back in.
She’s smart. She’s 43. She was a medical copywriter. But Excel: no clue whatsoever.
We just recorded a lesson where I showed her VLOOKUP and she almost cried with joy. “Oh my poor accountant…”, she said. Fun moment.
Forgive the spruik but while we’re here: https://www.learnwithlucy.rocks/courses/excel?coupon=earlych...
It’s probably not for anyone reading this thread but it might be for your partner or kids. And it definitely works, because Lucy can use Excel now.
She’s smart. She’s 43. She was a medical copywriter. But Excel: no clue whatsoever.
We just recorded a lesson where I showed her VLOOKUP and she almost cried with joy. “Oh my poor accountant…”, she said. Fun moment.
Forgive the spruik but while we’re here: https://www.learnwithlucy.rocks/courses/excel?coupon=earlych...
It’s probably not for anyone reading this thread but it might be for your partner or kids. And it definitely works, because Lucy can use Excel now.
Lol, literally came across this situation circa ~2012. I was an intern at a big but old school corporation. Someone asked me to help someone on a PM team with Excel, come to find out she was running some accounting numbers and was literally doing this. Reading numbers, putting them into a handheld calculator, and typing them back out. I had the pleasure of being the first person to ever show her sum(). Talk about blowing someone's mind.
Anecdotally I believe this sort of thing is terrifyingly common - in fact I wonder about the person-millennia of effort wasted globally as office workers across the globe huff and puff using Excel as a glorified typewriter.
He’s conflating it with LET and LAMBDA, which are new.
I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.
I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.
In 2022, there's probably a new generation working at those clients who have never seen the advanced features in Excel. You should put some feelers out to see if there's any demand.
The structure of that initial part isn't great given the headline, but I don't think its intending to claim that that's really new, just fairly unknown. And then talks about about the new stuff.
The "like you should have been able to 30 years ago" bit suggests otherwise, since you have been able to for at least 20 years.
Custom formulas have also been around for decades, it just required VBA.
If their mind is blown by named ranges, they are going to faint when learning about the nice structure a proper table (Insert -> Table) gives!
It's so handy typing
Within the table, you can do this syntax to use items on the same row of data.
It's so handy typing
=SUM(tbl_Sales_Transactions[Total])
Within the table, you can do this syntax to use items on the same row of data.
=[@[Price]*[@[Tax Rate]]Excel literally has the power to perform better than multimillion dollar consultant implementations on all kinds of things (having been on those projects and unable to supplant Excel successfully).
But is hard to audit. Web apps are committed in Git and have code reviews and client testing. If Excel made it possible to commit the formulas into Git, it would have taken over a million of accounting apps and SAP for 50 additional years.
But the lack of reliability of “a spreadsheet made by John in Accounting” sorely limits the success it can have.
But the lack of reliability of “a spreadsheet made by John in Accounting” sorely limits the success it can have.
Shameless plug:
We built xltrail[0] (cloud and self-hosted SaaS) that lets you see the diffs for sheets, VBA, and a couple other things. You put in your git repo and it just works. You can also have a manual versioning option where you upload new versions of the same file and you get the same result.
We also have the open source git extension Git XL[1] that lets you see VBA diffs locally.
[0] https://www.xltrail.com/
[1] https://www.xltrail.com/git-xl
We built xltrail[0] (cloud and self-hosted SaaS) that lets you see the diffs for sheets, VBA, and a couple other things. You put in your git repo and it just works. You can also have a manual versioning option where you upload new versions of the same file and you get the same result.
We also have the open source git extension Git XL[1] that lets you see VBA diffs locally.
[0] https://www.xltrail.com/
[1] https://www.xltrail.com/git-xl
Isn't this why people always save excel files a certain points of time? So you can go back to it and see it's state at certain "commit" points?
For me, it often looks like this.
2023 Budget v3 - 2022.08.01.xlsb
2023 Budget v3 - 2022.08.05.xlsb
2023 Budget v3 - 2022.08.08.xlsb
2023 Budget v3 - 2022.08.12.xlsb
The dates indicate minor updates/changes to the data (eg. the database is different but the logic is the same). Where as the version number is a change of business logic &/ formulas &/ file architecture (so v3 files are usually compatible with v3 files, but v4 files may break everything when comparing back to v3).
Granted, it doesn't help any with collaboration. My team still passes around the "current" file and deal with read-only locking issues (we find shared mode to be highly broken and conducive to file corruption). But, it does help with auditing because we can always go back and find why a number was what it was on any given day. So in that sense, Excel documents are as auditable as open source software. You have to want to read the code but it's all there for the reading.
For me, it often looks like this.
2023 Budget v3 - 2022.08.01.xlsb
2023 Budget v3 - 2022.08.05.xlsb
2023 Budget v3 - 2022.08.08.xlsb
2023 Budget v3 - 2022.08.12.xlsb
The dates indicate minor updates/changes to the data (eg. the database is different but the logic is the same). Where as the version number is a change of business logic &/ formulas &/ file architecture (so v3 files are usually compatible with v3 files, but v4 files may break everything when comparing back to v3).
Granted, it doesn't help any with collaboration. My team still passes around the "current" file and deal with read-only locking issues (we find shared mode to be highly broken and conducive to file corruption). But, it does help with auditing because we can always go back and find why a number was what it was on any given day. So in that sense, Excel documents are as auditable as open source software. You have to want to read the code but it's all there for the reading.
Integration into Office365 pretty much integrates collaboration and automatically saves every change, so you can go back to any version back in time.
Except "automatically" is always less transparent than explicit manual saves
We want to save after Big Change X has been made, not at a random interval in time.... so everyone at the office rightly disables autosave
We want to save after Big Change X has been made, not at a random interval in time.... so everyone at the office rightly disables autosave
Same. Also the problems I've encountered when one file tries to autosave, triggering a calculate before save, when I am actually working in something large that is calculation sensitive (manual calculation mode is on), and my whole application freezes/locks because it calculates when I'm not expecting it. Always happens 5 minutes before a deadline too. So yeah, disable that.
It's also a Workbook level setting so, you might have it turned off, but someone sends you a file with it enabled. For that reason, I have a macro that disables it at Open for all files (same for calculate before save, I really dislike that setting too).
It's also a Workbook level setting so, you might have it turned off, but someone sends you a file with it enabled. For that reason, I have a macro that disables it at Open for all files (same for calculate before save, I really dislike that setting too).
I've heard traders at banks literally yell something like "Just fire the entire useless IT department and just let me use my Excel!"
The IT department is limited by Turing's famous Halting Problem:
Given a complete set of requirements and a working prototype, will the IT department ever finish writing software that meets those requirements?
Okay, that was sarcastic, but illustrates a real problem, which is that doing it in Excel is making something, whereas getting IT involved is managing something -- a category error. The problems of managing software development are unchanged, half a century after The Mythical Man Month.
Given a complete set of requirements and a working prototype, will the IT department ever finish writing software that meets those requirements?
Okay, that was sarcastic, but illustrates a real problem, which is that doing it in Excel is making something, whereas getting IT involved is managing something -- a category error. The problems of managing software development are unchanged, half a century after The Mythical Man Month.
Haha, but traders are traders. What they say is mostly an effect of whether their stuff is performing or not.
IT departments has a perverted love of locking everything down, which makes basically everything useless except what is already provisioned.
Asking for software is like a gestapo interrogation - your always not right and in the end all motives are questioned.
Asking for software is like a gestapo interrogation - your always not right and in the end all motives are questioned.
IT solves IT's problems by externalising the costs into the teams they purportedly serve.
Haha, I can totally believe that at a big firm. I'm at a prop shop and we're much more outcome focused than that.
I'm in energy trading and wonder what the tech setups are in other companies. Can you tell me something about yours?
We currently migrate everything to the cloud, which breaks many Excels that so far got their inputs from on-prem services or the file system. What could previously be deployed without second thought now requires extensive, decentralized cloud knowledge in each team. Power is taken from the lay business user. What took seconds in VBA now requires a feature request to a dev team.. it's insane.
We currently migrate everything to the cloud, which breaks many Excels that so far got their inputs from on-prem services or the file system. What could previously be deployed without second thought now requires extensive, decentralized cloud knowledge in each team. Power is taken from the lay business user. What took seconds in VBA now requires a feature request to a dev team.. it's insane.
Yep. We have all of our backtesting and analysis in the cloud. Python notebooks for analysis. Java for backtesting. C++ for performance critical or for where Java API is not useful.
I'm in HFT so we have software engineers available to our portfolio managers. Everything is in code.
Live strategies are deployed via a script that ultimately ssh to a machine, plops a set of artifacts in from storage, and then runs. But everything needs to restart together.
I'm in HFT so we have software engineers available to our portfolio managers. Everything is in code.
Live strategies are deployed via a script that ultimately ssh to a machine, plops a set of artifacts in from storage, and then runs. But everything needs to restart together.
Looks at the 100-line SQL query that joins across 10 tables with 7 different WITH clauses and several random COALESCE statements sprinkled in to make the query work
Yup, so easy to audit.
Yup, so easy to audit.
Complex queries are audited all the time. Also, side-effects must be able to be audited/logged. Excel runs on thin air.
https://support.microsoft.com/en-us/office/overview-of-sprea...
Spreadsheet Compare, an official diff tool, is excellent
Spreadsheet Compare, an official diff tool, is excellent
We store our XLSX files as XML file on GitHub and take advantage of all GH services. Excel will re-render a modified XML file.
You can use git hooks or CI tools to unzip xlsx files when they’re committed and run an autoformatter on the resulting xml files. The xml diffs aren’t the most readable, but they’re usable.
That’s not true. You can validate outcomes. This is what accounting auditors do. Do you insist in doing a code review of your bank statements?
[deleted]
that's what internal validation processes are for
Sounds great until you have multi-million dollar programs running off single point of failure spreadsheets with little or no backup or overwrite protection.
That sounds pretty great. I'd love to have a multi-million dollar program.
how do you do versioning and review?
You give your files helpful names like:
sales-2005-(copy)-Final-2015-(copy)-real-final-(copy).xlsx
sales-2005-(copy)-Final-2015-(copy)-real-final-(copy).xlsx
LOL. And diffing?
There's always Spreadsheet Compare, which is Microsoft's attempt at a diff utility for Excel. It works fairly well.
https://support.microsoft.com/en-us/office/overview-of-sprea...
https://support.microsoft.com/en-us/office/overview-of-sprea...
Why of course you open two windows one on top of another and you Alt-Tab between them rapidly to see the differences.
Not sure if serious or sarcastic because I’m sure people do that.
I do that (not just with Excel) knowing full well how ridiculous it is, but sometimes I just can't be bothered with other tools.
We built https://www.xltrail.com to make Excel work with Git.
> Instead of writing the formula =A1*B1, you can do =Width*Height like you should have been able to 30 years ago.
I don't have access to super old versions of Excel, but I just confirmed that it worked perfectly well in Office 2003. I believe it's been the case forever.
(I wrote whole apps in Excel4 super-weird macro language in 1992-93 and distinctly remember we had named zones.)
I don't have access to super old versions of Excel, but I just confirmed that it worked perfectly well in Office 2003. I believe it's been the case forever.
(I wrote whole apps in Excel4 super-weird macro language in 1992-93 and distinctly remember we had named zones.)
Microsoft Multiplan for CP/M supported that in 1982 (http://www.bitsavers.org/pdf/microsoft/cpm/Microsoft_Multipl..., page 99)
I think later versions of VisiCalc did, too.
I think later versions of VisiCalc did, too.
Former Excel 97-XP developer here. Excel 97 had English language formulas. It automatically detected names in tables; however, it was buggy. The feature was removed in a later version (2007?).
Already Lotus 1-2-3 for MS-DOS had named ranges.
As Excel was made since the beginning as a replacement for Lotus 1-2-3, I believe it also must have had named ranges at least from its version for MS Windows 3.0.
IIRC, when I have first used Excel, which was the Windows 95 version, it had them.
As Excel was made since the beginning as a replacement for Lotus 1-2-3, I believe it also must have had named ranges at least from its version for MS Windows 3.0.
IIRC, when I have first used Excel, which was the Windows 95 version, it had them.
I recall it not have named ranges initially, but I remember them being added pretty quickly. Not sure what year, but very early.
The name manager was added in Excel 2007.
The name manager was added in Excel 2007.
I was never really a big fan of Excel, but since I started working at a company that does literally everything in 365, I've come back around - power queries, the graph API, power automate and BI put a lot of tools at your disposal. Having typescript as the builtin scripting language is nice for me, too, because I never really liked vba
https://developer.microsoft.com/en-us/graph/
https://make.powerautomate.com/
https://docs.microsoft.com/en-us/office/dev/scripts/
https://developer.microsoft.com/en-us/graph/
https://make.powerautomate.com/
https://docs.microsoft.com/en-us/office/dev/scripts/
The squicky thing about the Microsoft online ecosystem of tools is their first party vs everything else approach. Where first party is easy and everything else is a second class citizen.
I'd feel a lot more comfortable if they categorically committed to only using APIs for their products that were also open to everyone. Have they done this?
Graph also seems unstable in regards to refactoring and deprecating over the past years. Look at something simple like "set up a trigger when a user receives an email." It's been through 2+ architecture changes (nothing, webhooks, delta, subscriptions?). Or Teams API coverage (beta or not?).
But most of my feelings are probably classic HN "Has MS really turned a page, or are they extending 90s behavior via cloud lock-in, with a veneer of nice tools?" paranoia. And only the future knows the answer.
Disclaimer: Work for a company that competes with Microsoft in some areas. We see a lot of customer Azure AD admins floundering in supporting non-MS integration.
I'd feel a lot more comfortable if they categorically committed to only using APIs for their products that were also open to everyone. Have they done this?
Graph also seems unstable in regards to refactoring and deprecating over the past years. Look at something simple like "set up a trigger when a user receives an email." It's been through 2+ architecture changes (nothing, webhooks, delta, subscriptions?). Or Teams API coverage (beta or not?).
But most of my feelings are probably classic HN "Has MS really turned a page, or are they extending 90s behavior via cloud lock-in, with a veneer of nice tools?" paranoia. And only the future knows the answer.
Disclaimer: Work for a company that competes with Microsoft in some areas. We see a lot of customer Azure AD admins floundering in supporting non-MS integration.
There are a lot of connectors[1], both MS and non-MS but I think "open to everyone" is a different thing. Of course it's a lock-in. They're not doing this to be nice. What makes it an easy decision for us is that upper management already decided to lock us in (requests for getting non-approved software installed on a company laptop can take 3+ months to process...).
I don't think Graph has triggers, but powerautomate does and admittedly I find them a little flaky across the board - I wouldn't be relying on them, we just poll for changes every 60 seconds or so if it's critical.
Teams for the most part is just a wrapper around sharepoint/outlook groups. The API there seems stable enough
[1]https://make.powerautomate.com/connectors
I don't think Graph has triggers, but powerautomate does and admittedly I find them a little flaky across the board - I wouldn't be relying on them, we just poll for changes every 60 seconds or so if it's critical.
Teams for the most part is just a wrapper around sharepoint/outlook groups. The API there seems stable enough
[1]https://make.powerautomate.com/connectors
Going all in on microsoft is a solid move. It's a powerful platform.
Going half in on microsoft is terrible.
Going half in on microsoft is terrible.
Ugh. I think it’s a matter of philosophy but I can’t connect with Microsoft. It feels so alien and hostile.
This rings very true.
There are actually a couple of departments still using custom software and it doesn't talk to anything and you can't talk to it (but you can manually import/export data, in excel/csv format haha)
The other thing is that every time they want to add a feature they have to find 50k for software development and watch for 6 months as the request winds its way through the bureaucracy. We just whip it up in an afternoon and keep moving...
There are actually a couple of departments still using custom software and it doesn't talk to anything and you can't talk to it (but you can manually import/export data, in excel/csv format haha)
The other thing is that every time they want to add a feature they have to find 50k for software development and watch for 6 months as the request winds its way through the bureaucracy. We just whip it up in an afternoon and keep moving...
All in may be fine for certain businesses, but surely most companies end up needing some software MS doesn’t provide?
And ‘all in’ implies usage of Teams..,
And ‘all in’ implies usage of Teams..,
Excel is great until suddenly it's not. When I started building ProjectionLab, it began as an excel spreadsheet that (at first) was easy to share and explain to friends and family who wanted a better retirement plan. But as they asked for more flexibility and new features, it spiraled out of control into a complex web of advanced Excel features that became opaque and unmaintainable. No doubt Excel is powerful and feature-rich, but sometimes layering on more and more of those advanced features isn't the answer.
Would it be any different if you used a different tool and/or programmed everything manually?
In my experience, it would be 50x worse because Excel handles for you a lot of things that are extremely time consuming to code manually. (eg. error handling)
In my experience, it would be 50x worse because Excel handles for you a lot of things that are extremely time consuming to code manually. (eg. error handling)
Maintainability » everything else.
Basically on the long term the possibility to use real code that is easy to change and understand will save you incredible amounts of time. If you reach a point where your code is too complicated that you are afraid to change anything, you're basically stuck.
Basically on the long term the possibility to use real code that is easy to change and understand will save you incredible amounts of time. If you reach a point where your code is too complicated that you are afraid to change anything, you're basically stuck.
I guess it depends on what you mean by worse. In my case, I abandoned excel and rebuilt the tool as a web app using Vue.js, Chart.js, Firebase, etc., which is what ProjectionLab is now. More overall development work, but the end result is way better than a spreadsheet.
But would you say using that web stack initially would have been overkill?
It is and it isn't.
For one of my first jobs, I had to spend a lot of time converting excel into python.
Excel is great for small places, but the moment you need to have oversight and accountability in an organization. You need a different centralized, permission controlled, and auditable solution.
For one of my first jobs, I had to spend a lot of time converting excel into python.
Excel is great for small places, but the moment you need to have oversight and accountability in an organization. You need a different centralized, permission controlled, and auditable solution.
I think the fatal flaw is that there is no upgrade path towards sanity. If I start from a jupyter notebook or a script or whatever, it's trivial to build upon it. In Excel it's just an endless descent into madness.
All those funky features are just bandaids over a fundamentally wrong abstraction.
We have to acknowledge it's pretty much the only real tool for citizen developers out there, and as such it deserves some credit. But I don't really think it has any real advantage over $(your favorite scripting language); if you are proficient at coding I don't see why you would implement anything nontrivial in Excel given the choice.
The truly infuriating thing is that it wouldn't take much for spreadsheets to become literal 10x magic, but it's one of those path-dependent things. Sad.
All those funky features are just bandaids over a fundamentally wrong abstraction.
We have to acknowledge it's pretty much the only real tool for citizen developers out there, and as such it deserves some credit. But I don't really think it has any real advantage over $(your favorite scripting language); if you are proficient at coding I don't see why you would implement anything nontrivial in Excel given the choice.
The truly infuriating thing is that it wouldn't take much for spreadsheets to become literal 10x magic, but it's one of those path-dependent things. Sad.
> But I don't really think it has any real advantage over $(your favorite scripting language); if you are proficient at coding I don't see why you would implement anything nontrivial in Excel given the choice.
If you wanted to implement a spreadsheet, you'd be wasting your time with Python though. I'd rather manage and visualise my monthly budget in Excel.
If you wanted to implement a spreadsheet, you'd be wasting your time with Python though. I'd rather manage and visualise my monthly budget in Excel.
I think this is the missing piece. Excel is great at user input.
Getting the same level of copy-paste, drag-drop, drag-auto-complete, "tweak until it looks right before I move on" data-entry experience is non-trivial.
Getting the same level of copy-paste, drag-drop, drag-auto-complete, "tweak until it looks right before I move on" data-entry experience is non-trivial.
It seems like quite a flaw in Microsoft's ecosystem that those requirements can't be met by some integration between Excel and, like, Sharepoint or whatever.
Totally agree that audibility is an important part of the story here!
Having spent the past 2 years building a spreadsheet [1], it's really interesting how often we run into design problems that pit "audibility" against "what you expect from a spreadsheet."
A simple example: imagine you add a filter to column to remove null values, you then go and create a new formula that is dependent on this filtered column, before finally going back to edit the filter.
On one hand, effective audibility usually implies a nice, linear story you can follow and understand. On the other hand, users expect filters to update based on the most recent values - but the filter is applied in a way-old step. In practice, there's a bunch of extra state you have to store to make sure things work properly, and even then, there are lots of foot guns!
[1] https://trymito.io
Having spent the past 2 years building a spreadsheet [1], it's really interesting how often we run into design problems that pit "audibility" against "what you expect from a spreadsheet."
A simple example: imagine you add a filter to column to remove null values, you then go and create a new formula that is dependent on this filtered column, before finally going back to edit the filter.
On one hand, effective audibility usually implies a nice, linear story you can follow and understand. On the other hand, users expect filters to update based on the most recent values - but the filter is applied in a way-old step. In practice, there's a bunch of extra state you have to store to make sure things work properly, and even then, there are lots of foot guns!
[1] https://trymito.io
> You need a different centralized, permission controlled, and auditable solution.
So, Access? ;)
But seriously, I really miss "PC" databases, Clipper etc.; Would often fill niches between spreadsheets and webapps. (Airtable etc. are trying to do part of that, but I'd say that's more a symptom than a solution)
So, Access? ;)
But seriously, I really miss "PC" databases, Clipper etc.; Would often fill niches between spreadsheets and webapps. (Airtable etc. are trying to do part of that, but I'd say that's more a symptom than a solution)
At my work, we do a lot of IT ticketing in ServiceNow. They've been reporting out tickets/incidents through Excel and so far I haven't gotten them to /just/ /look/ /at/ /the/ /dashboard/ /in/ /servicenow/. I automated these reports 3 weeks ago. So until I can get them to give up on copying ticket information into Excel, I started converting the tables into "Tables", and now when they insert the incident number in 1 sheet it autopopulates with the information they entered from other sheets using a formula someone who comes after me can understand. They literally have a "closed tickets", "daily tickets", "cancelled tickets", etc. Excel has been wonderful for showing how much i don't need Excel. Also, being able to do structured references in the "online 365 Excel" has been wonderful. I love looking at 2 separate worksheets in 2 separate browser tabs. I can't view multiple sheets side-by-side in the desktop Excel.
I will XLOOKUP() this job into the ground.
PS: = INDIRECT("YourTable[@header]") for structured references in online Excel
I will XLOOKUP() this job into the ground.
PS: = INDIRECT("YourTable[@header]") for structured references in online Excel
It's crazy how resistant people are to change (or just learning new things)
I'll take plain text over WYSIWYG any day, thanks. Excel gives you some obvious way to do some things and stacks up a huge load of features so you can hack your problem into the Excel way. But the Excel way tends to be terrible from start to finish. People stopped doing Double-entry bookkeeping because it's somehow hard in the world's most used accounting software while saner programs like ledger-cli and beancount make it very easy. For any moderately complex work, coupling data and data manipulation is a very bad idea, and having both in an opaque, non-diffable, non-VCS-able file is just making hell out of everyone's life for no good reason.
I understand if you just want to put your data in a table and do some math with it. Sometimes it's all you know how to use and you end up doing something important with it, that's okay. Sometimes it's all your peers know how to use and you choose Excel because of them, that's great. Laziness and inertia are just facts of the world and we have to accept it exists, but please don't pretend we can't do better than Excel most of the time.
I understand if you just want to put your data in a table and do some math with it. Sometimes it's all you know how to use and you end up doing something important with it, that's okay. Sometimes it's all your peers know how to use and you choose Excel because of them, that's great. Laziness and inertia are just facts of the world and we have to accept it exists, but please don't pretend we can't do better than Excel most of the time.
> I understand if you just want to put your data in a table and do some math with it.
But this is huge and what most people use Excel/Sheets for, in their day-to-day work.
I have used Excel (and nowadays Sheets, because it’s free) for years in both my personal and professional life, just for this purpose. I’ve probably used this more than any single tool/app.
Plain text has its place, but saying that it is a replacement for Excel/Sheets is overlooking a huge reason it’s so useful.
But this is huge and what most people use Excel/Sheets for, in their day-to-day work.
I have used Excel (and nowadays Sheets, because it’s free) for years in both my personal and professional life, just for this purpose. I’ve probably used this more than any single tool/app.
Plain text has its place, but saying that it is a replacement for Excel/Sheets is overlooking a huge reason it’s so useful.
> and nowadays Sheets, because it’s free … in my professional life
It isn’t. For pro use it’s by and large the same cost as O365, $12/user/month if you get a usable plan. (Both offer a $6/user/month plan, but on MS side that doesn’t have desktop apps, and on Google side it doesn’t have storage.)
// As of 1 August, all the grace periods for Workspaces expired. There remains a three month no charge period from your individual cutover day, and then a year of 1/2 price.
It isn’t. For pro use it’s by and large the same cost as O365, $12/user/month if you get a usable plan. (Both offer a $6/user/month plan, but on MS side that doesn’t have desktop apps, and on Google side it doesn’t have storage.)
// As of 1 August, all the grace periods for Workspaces expired. There remains a three month no charge period from your individual cutover day, and then a year of 1/2 price.
Never said everyone should simply replace it. I, myself, would prefer not to use Excel because I am proficient in ledger and org-mode which absolutely does supplant that use case (and much more). The criticism here is when you have complex data and manipulation and attempt to use a terrible tool for that job. Excel in fact IS good for some things, but unfortunately happens to be usable, and commonly used, for things it's terrible at.
> I'll take plain text over WYSIWYG any day, thanks.
I couldn't agree more. It's a bit convoluted, but one thing you can do is write code in plain text files, in the Power Query M language, and load it using a combination of Folder.Files [0], BinaryFormat.Text [1], and Expression.Evaluate [2] (in the #shared environment).
On the downside, everything is no longer self-contained in the workbook. On the upside, everything is no longer self-contained in the workbook, and you can actually, like, check the code into git.
[0]: https://docs.microsoft.com/en-us/powerquery-m/folder-files
[1]: https://docs.microsoft.com/en-us/powerquery-m/binaryformat-t...
[2]: https://docs.microsoft.com/en-us/powerquery-m/expression-eva...
I couldn't agree more. It's a bit convoluted, but one thing you can do is write code in plain text files, in the Power Query M language, and load it using a combination of Folder.Files [0], BinaryFormat.Text [1], and Expression.Evaluate [2] (in the #shared environment).
On the downside, everything is no longer self-contained in the workbook. On the upside, everything is no longer self-contained in the workbook, and you can actually, like, check the code into git.
[0]: https://docs.microsoft.com/en-us/powerquery-m/folder-files
[1]: https://docs.microsoft.com/en-us/powerquery-m/binaryformat-t...
[2]: https://docs.microsoft.com/en-us/powerquery-m/expression-eva...
Excel as a product and a brand is very confusing.
On the one hand you have all these great features, but on the other I can't even import a simple spreadsheet with checkboxes to excel online without breaking it because of active objects not being supported online.
It rides on the massive weight Microsoft market share has, so it doesn't have to be portable, open and consistent.
On the one hand you have all these great features, but on the other I can't even import a simple spreadsheet with checkboxes to excel online without breaking it because of active objects not being supported online.
It rides on the massive weight Microsoft market share has, so it doesn't have to be portable, open and consistent.
100% agree. Is there any good alternative with the same kind of functionality? I don't know of anything.
I really have no idea. I sometimes receive excel forms from clients and contractors that even though they are nothing fancy they won't open on excel online. I am a full time DevOps and systems architect can't work without Linux you see.
So sometimes I have to resort to a Windows VM and Excel, the real deal to fill out those forms.
I used wine a long time ago, but it would only support a very specific, very ancient version of Excel, so a VM is actually less of a hassle (when already prepared. The process of installing windows on a VM was quite frustrating for me).
But no, I don't think there is any substitute to excel. Microsoft made sure of that.
So sometimes I have to resort to a Windows VM and Excel, the real deal to fill out those forms.
I used wine a long time ago, but it would only support a very specific, very ancient version of Excel, so a VM is actually less of a hassle (when already prepared. The process of installing windows on a VM was quite frustrating for me).
But no, I don't think there is any substitute to excel. Microsoft made sure of that.
Can you please describe your use case?
I know for me, I have a personal budgeting spreadsheet that is fairly complex. I'd love to simplify aspects of it with macros and checkboxes. However I can't use any macro-related features because then it becomes incompatible with the web interface, and I sometimes like to use that when working on Linux.
The talk, Rethinking Reactivity by Rich Harris[0] is an amazing talk which talks about how spreadsheets were the OG reactivity software. Highly recommended.
0: https://www.youtube.com/watch?v=AdNJ3fydeao
0: https://www.youtube.com/watch?v=AdNJ3fydeao
A lot of SaaS startups with a shitty React frontend should actually exist as a PDF report that gets emailed out every morning plus a site with a button to upload a CSV/XLSX file for tomorrow's report.
If everything that could be implemented as a spreadsheet/PDF combination was, what would we do for work?
Python's pandas makes working with large numbers of Excel / LibreOfficeCalc files a lot easier. Excel is very convenient for the non-programming user of course, for example making graphic visualization is far easier. With pandas you need to interface programmatically with matplotlib or similar.
If you want to up your skills in this area, Wes McKinney's "Python for Data Analysis" is pretty great.
If you want to up your skills in this area, Wes McKinney's "Python for Data Analysis" is pretty great.
+1 for “Python for Data Analysis”.
And Wes (the creator of the Pandas library) has made the book available for free on his website!
And Wes (the creator of the Pandas library) has made the book available for free on his website!
My use of sheets is crazy insane, my team of 180, use the sheets for everything, instead of create a crud for control my system, I use sheets and connect with retool ou via api and have instant app, instead of create a complex app for assign tasks, generate the queue with app scripts and big query, and have a nice and simple interface for the team use
Over the years I have probably tried (and used) every feature that was added to Excel.
With growing wisdom I came to the conclusion that I will only use the most basic features (i.e. simple formulas using adding, subtraction, multiplication, division) when I need to share a sheet with third parties. Nearly all users are not proficient in the more advanced features - making collaboration an error prone hell.
To make things worse: Excel by nature is not made to be audited. That’s why I tend to add a generous amount of checksums or similar (visual) flags to my worksheets in order to catch errors early.
Excel is dangerous!
With growing wisdom I came to the conclusion that I will only use the most basic features (i.e. simple formulas using adding, subtraction, multiplication, division) when I need to share a sheet with third parties. Nearly all users are not proficient in the more advanced features - making collaboration an error prone hell.
To make things worse: Excel by nature is not made to be audited. That’s why I tend to add a generous amount of checksums or similar (visual) flags to my worksheets in order to catch errors early.
Excel is dangerous!
The dangerous things about excel, imho, are:
1. Too many different places for functionality to hide. Are these numbers updated by external links? VBA? Pivot tables? Equations? Some addin?
2. Difficult to version control. To capture all the functionality its not enough to just strip the VBA code into git, you need to track a lot of other config variables related to the features in 1.
3. Crap accretion. Complex spreadsheets have a tendency to accumulate broken external links, redundant named ranges, and extraneous scratchpad sheets as people copy data around. This makes troubleshooting harder just by increasing the amount of noise.
1. Too many different places for functionality to hide. Are these numbers updated by external links? VBA? Pivot tables? Equations? Some addin?
2. Difficult to version control. To capture all the functionality its not enough to just strip the VBA code into git, you need to track a lot of other config variables related to the features in 1.
3. Crap accretion. Complex spreadsheets have a tendency to accumulate broken external links, redundant named ranges, and extraneous scratchpad sheets as people copy data around. This makes troubleshooting harder just by increasing the amount of noise.
It may be the greatest, most feature rich application ever written
I did a stint in document management for a company.
Let me disagree: All of Office is a very very very bad thing from a "shared information" standpoint. If you put information into an office document/spreadsheet/etc, it effectively locks it from use in the rest of the company, publishing, and programmatic analysis.
Suuuuure, there's lots of products out there that will offer services to extract data from your word docs, or all that adhoc data in your spreadsheet.
Extracting data from word documents is definitely in the "with a million lines of code you can do anything" camp. It's just a lot of ugly crap but its doable.
But... why?
Why have companies allowed Microsoft to make their storage formats utterly inaccessible and integration resistant? Well, it made them mints of money in monopolizing/stopping switchover to competitors, and allowed them to make money on the tools to extract/use their insufferable storage formats.
Soooo much efficiency lost.
Excel the programming and UI app is an amazing achievement (here's to Lotus 123 for making the first spreadsheet, as with everything, Microsoft didn't create it, it copied it).
But for a data creation, manipulation, and "database", it is a tragedy.
Let me disagree: All of Office is a very very very bad thing from a "shared information" standpoint. If you put information into an office document/spreadsheet/etc, it effectively locks it from use in the rest of the company, publishing, and programmatic analysis.
Suuuuure, there's lots of products out there that will offer services to extract data from your word docs, or all that adhoc data in your spreadsheet.
Extracting data from word documents is definitely in the "with a million lines of code you can do anything" camp. It's just a lot of ugly crap but its doable.
But... why?
Why have companies allowed Microsoft to make their storage formats utterly inaccessible and integration resistant? Well, it made them mints of money in monopolizing/stopping switchover to competitors, and allowed them to make money on the tools to extract/use their insufferable storage formats.
Soooo much efficiency lost.
Excel the programming and UI app is an amazing achievement (here's to Lotus 123 for making the first spreadsheet, as with everything, Microsoft didn't create it, it copied it).
But for a data creation, manipulation, and "database", it is a tragedy.
I don’t understand. How is putting information in a document or spreadsheet “lock it from use in the rest of the company “?
Are you talking about some specific use case, such as data that should be in an ERP or CRM being stored in a spreadsheet instead?
Please elaborate.
Are you talking about some specific use case, such as data that should be in an ERP or CRM being stored in a spreadsheet instead?
Please elaborate.
Sharing sheets works just great. You distribute a link and that's it.
> All of the online comparisons say they’re about equal, but Sheets doesn’t even have proper tables, much less lambdas or a “get yoga pose” button.
I was on the Google Sheets team from about 2011 to 2018 (opinions are, of course, VERY much my own). I worked in finance before that, so don't worry, I'm aware of Excel's features.
One thing that I found interesting is that, over my time on Sheets, I didn't perceive much change in the volume* of "Sheets is missing critical features" criticism, but I did notice a marked difference in the features that the critics brought up. It's pretty cool that "get yoga pose" now makes the list - it's a long way, for example, from the things Joel Spolsky noted (in 2006) that "you can’t really do well in a web application": https://www.joelonsoftware.com/2004/06/13/how-microsoft-lost....
* In either sense, really.
I was on the Google Sheets team from about 2011 to 2018 (opinions are, of course, VERY much my own). I worked in finance before that, so don't worry, I'm aware of Excel's features.
One thing that I found interesting is that, over my time on Sheets, I didn't perceive much change in the volume* of "Sheets is missing critical features" criticism, but I did notice a marked difference in the features that the critics brought up. It's pretty cool that "get yoga pose" now makes the list - it's a long way, for example, from the things Joel Spolsky noted (in 2006) that "you can’t really do well in a web application": https://www.joelonsoftware.com/2004/06/13/how-microsoft-lost....
* In either sense, really.
This blog post by Joel is a wealth of knowledge that does a great job of presciently explaining the last 15 years of Microsoft.
He even offers a great reason for why IE stagnated for years: MS was afraid of the web and felt sabotage was the best way to prevent it from flourishing. The only thing they actually accomplished was giving google a browser monopoly.
He even offers a great reason for why IE stagnated for years: MS was afraid of the web and felt sabotage was the best way to prevent it from flourishing. The only thing they actually accomplished was giving google a browser monopoly.
I'm surprised this thread got so large without anyone mentioning Lotus Improv or Quantrix. Quantrix was the later re-implementation of Lotus by lighthouse design, after Lotus had canceled Improv.
Improv/Quantrix was a multidimensional spreadsheet, Each model could contain multiple tables, and of course multiple views. Formulas were separate from data, so it was easy to audit a model. Clicking on a formula (in 1991) showed you all the related input and output sources.
It was the first spreadsheet with pivot tables.
Of course since it's secretly a database down below, you could do databasey things like joins and group by. In 1991.
Videos of using Improv.
https://www.youtube.com/watch?v=TbsfvdZXE7s
https://www.youtube.com/watch?v=TbsfvdZXE7s
https://www.youtube.com/watch?v=lTko_Pt2ZZg
Improv/Quantrix was a multidimensional spreadsheet, Each model could contain multiple tables, and of course multiple views. Formulas were separate from data, so it was easy to audit a model. Clicking on a formula (in 1991) showed you all the related input and output sources.
It was the first spreadsheet with pivot tables.
Of course since it's secretly a database down below, you could do databasey things like joins and group by. In 1991.
Videos of using Improv.
https://www.youtube.com/watch?v=TbsfvdZXE7s
https://www.youtube.com/watch?v=TbsfvdZXE7s
https://www.youtube.com/watch?v=lTko_Pt2ZZg
> Excel has actual programming affordances now
In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referring to cells containing a calculation. Not in a separate window, but in sheet, so you can use multiple cells to break it up nicely.
I often have complicated, multi step calculations. Currently I copy-paste them for every row (and Excel helps with keeping the formulas in sync). But it would be great if you could do it once on an example (maybe even on another sheet) and then turn it into a custom formula.
(I used to think ancient Excel 4 macros were that from what it looked like, but unfortunately they are something completely different.)
In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referring to cells containing a calculation. Not in a separate window, but in sheet, so you can use multiple cells to break it up nicely.
A B C
+-------------------------
1 | =VARIABLE(X)
2 | =A1*2
3 | =A2+1
I often have complicated, multi step calculations. Currently I copy-paste them for every row (and Excel helps with keeping the formulas in sync). But it would be great if you could do it once on an example (maybe even on another sheet) and then turn it into a custom formula.
(I used to think ancient Excel 4 macros were that from what it looked like, but unfortunately they are something completely different.)
It's not a perfect solution to your problem, but you can simplify multistep calculations with LET. So it would be:
LET(X, A1, Y, X*2, Y+1)
My experimental spreadsheet does something like this. You can refer to the value of a cell given some different values in other cells, and it calculates the value of the cell given just those changes. Syntax is cellname{othercell=othervalue, ...}.
So you can use a group of cells like a function with parameters (with defaults).
Fizzbuzz example: https://visibot.com/webdemo/#/%2Fexamples%2Ffizzbuzz.vb Use right and left arrow keys to scrub through the program's execution. The help link in the NE corner gives more details.
(This is still evolving, and uses WASM to run in the browser so it's desktop only. LMK if it doesn't work for you.)
So you can use a group of cells like a function with parameters (with defaults).
Fizzbuzz example: https://visibot.com/webdemo/#/%2Fexamples%2Ffizzbuzz.vb Use right and left arrow keys to scrub through the program's execution. The help link in the NE corner gives more details.
(This is still evolving, and uses WASM to run in the browser so it's desktop only. LMK if it doesn't work for you.)
Use the LET formula. It gives you lexically scoped variables inside a formula.
=LET(foo, ComplicatedExpression(),
bar, AnotherComplicatedExpression(),
baz, AnExpressionInvolvingPreviousVariables(foo, bar),
ExpressionThatIsTheReturnValue())
You can also use lambdas:
=LAMBDA(... args, calculation)
Will LAMBDA be available for the standalone Office 2021 though? Or is it only going to be there for the subscrption based Office 365?
[deleted]
That’s more or less how Lotus-123 did that kind of thing.
As others said, you can use lambda, but the ‘old modern way’ to do that is to write a function in VBA and call that (https://stackoverflow.com/a/16296990)
As others said, you can use lambda, but the ‘old modern way’ to do that is to write a function in VBA and call that (https://stackoverflow.com/a/16296990)
Custom formulas/functions exist using VBA
Google Sheets also has custom functions via Apps Script (including autocomplete and function help text).
Sure, now try to open a CSV that has line breaks in some cells (which is something basic and that follows the CSV spec) and tell me if Excel is still cool.
https://stackoverflow.com/questions/2668678/importing-csv-wi...
It is literally impossible. The best solution is "Import it in Google Sheets and export to Excel format"...
Excel is powerful, but it is not cool at all, the UX of even basic features is awful and some of the most used functions have annoying behaviors (VLOOKUP when the data type of the 2 columns is not exactly the same for example).
https://stackoverflow.com/questions/2668678/importing-csv-wi...
It is literally impossible. The best solution is "Import it in Google Sheets and export to Excel format"...
Excel is powerful, but it is not cool at all, the UX of even basic features is awful and some of the most used functions have annoying behaviors (VLOOKUP when the data type of the 2 columns is not exactly the same for example).
>It is literally impossible.
You can do this in Powerquery.
>VLOOKUP when the data type of the 2 columns is not exactly the same for example).
Use XLOOKUP.
You can do this in Powerquery.
>VLOOKUP when the data type of the 2 columns is not exactly the same for example).
Use XLOOKUP.
> You can do this in Powerquery.
Yeah, pretty sure that having to use an ETL layer before Excel qualifies as "It's literally impossible to do it in Excel".
> Use XLOOKUP.
It's only available after Excel 2019, and the behavior is different. There's also nothing that seems to indicate that it doesn't have the same issue with column types?
Yeah, pretty sure that having to use an ETL layer before Excel qualifies as "It's literally impossible to do it in Excel".
> Use XLOOKUP.
It's only available after Excel 2019, and the behavior is different. There's also nothing that seems to indicate that it doesn't have the same issue with column types?
> Use XLOOKUP.
Use INDEX and MATCH.
Use INDEX and MATCH.
RFC 4180 came too late. I think CSV is more commonly taken to be just a general description of the data, than a reference to a particular standardized spec (I mean it is notorious for ad-hoc implementations, right?).
It sounds like SO isn't really an expert on Excel. The correct answer (Data > From Text/CSV) is sitting at the bottom with 0-1 votes. Most answers are from the early 2010's and outdated.
This does not work even in Excel 2019.
In any case, not supporting when you just double-click a CSV to open it with Excel means that it's not usable for the vast majority of users.
We faced the issue when we were exporting data to customers in CSV format, and "Replacing new lines with '/'" ended up being a preferred solution over having the user perform any action.
In any case, not supporting when you just double-click a CSV to open it with Excel means that it's not usable for the vast majority of users.
We faced the issue when we were exporting data to customers in CSV format, and "Replacing new lines with '/'" ended up being a preferred solution over having the user perform any action.
Maybe stop using outdated software?
The mainstream support for Office 2019 ends on October 10, 2023 and the extended support on October 14, 2025. It's not outdated software.
Supported doesn't mean current. They'll fix security holes and help paying customers use the software as it is, but that is it. Not introducing new features or backport improvements.
Xlookup exists
CSV of all types can be imported with power query.
CSV of all types can be imported with power query.
And then place in C1 =A1:A3 + B1:B3,
you’ll get C1=3, C2=7, C3=11. Now, if
you place in D1 =C1^2, you’ll just get
D1=9. But if you instead place C1#^2,
it instead applies to C1’s spillover
array, meaning you now have D1=9,
D2=49, D3=121.
Oh also if you instead do C1 = A1:A3 +
TRANPOSE(B1:B3) you get this:
Excel is great. It is so good most business apps at some point ask you to replicate many of its popular features (though most of these features are not exclusive to Excel by any means).
[deleted]
Around 1990, I used to program Excel as the UI to connect to the IBM DB2 database on a VAX. People in the company just used the excel files like they used to, but no the data was all in a central db and the excel file with reports was always up to date.
And you could extend it by creating graphs not in “dashboard” etc, because it was just excel. In some ways it was better than today’s fancy dashboards, where you can do what the devs thought of but no more.
And you could extend it by creating graphs not in “dashboard” etc, because it was just excel. In some ways it was better than today’s fancy dashboards, where you can do what the devs thought of but no more.
Now when I think somekind spreadsheet wrapper around sqlite would be pretty cool.
My dream is either what you're describing here or a company like BareBones (of BBEdit) making a data crunching app with no formatting whatsoever. People send me large spreadsheets all the time and Excel (at least on a Mac) has a hard time keeping up.
I suppose I should just get better at using numpy and pandas.
I suppose I should just get better at using numpy and pandas.
You can just connect to SQLite directly from excel via ODBC.
At one job doing data centre forensics I had spent like half a year building up excel spreadsheets on what needed to be investigated... Then I realized the date formats were American, so I changed my system clock to use dd/mm/yyyy instead...
This automatically mangled the date in every single file I opened from there on and left me having to redo all that work. Fun!
A bunch of what I do for work right now is writing code which actually generates spreadsheets for end users to use but personally I wish that Excel was more like a relational database with more data safety guarantees and I'm glad I don't have to use it every day.
Has Microsoft integrated python into it yet? Does that embedding come with a full networking stack? That choice always struck me as kinda wild, as if there isn't enough security problems as-is with this stuff... It kind of seems like Microsoft's whole plan to make Excel more secure is trying to move everyone to that cloudy office 365, but I just use libreoffice anyways
This automatically mangled the date in every single file I opened from there on and left me having to redo all that work. Fun!
A bunch of what I do for work right now is writing code which actually generates spreadsheets for end users to use but personally I wish that Excel was more like a relational database with more data safety guarantees and I'm glad I don't have to use it every day.
Has Microsoft integrated python into it yet? Does that embedding come with a full networking stack? That choice always struck me as kinda wild, as if there isn't enough security problems as-is with this stuff... It kind of seems like Microsoft's whole plan to make Excel more secure is trying to move everyone to that cloudy office 365, but I just use libreoffice anyways
I always assume that if dates/times aren't ISO 8601, then they're not in the format I'm expecting.
Better reject those ISO8601 timestamps if they don't have time zone specifiers too! In one case, I wrote an API that rejected anything that wasn't in UTC time just to make everyone's life equally uncomfortable.
Excel is the only reason I still pay money to Microsoft. I buy Office just for Excel. Word and Powerpoint have been dumpster fires for decades and both have good alternatives.
But Excel works well and nothing else even comes close to its capabilities. Now that it has lambda it's an actual dataflow programming language.
But Excel works well and nothing else even comes close to its capabilities. Now that it has lambda it's an actual dataflow programming language.
Excel is an extremely cool piece of software. As someone who spent the past two years of his life implementing a spreadsheet [1] (built to solve many of The issues with spreadsheets mentioned in this thread: data size limits, speed limits, and repeatability problems), it’s unbelievable how many different use cases are supported by just Excel.
One of the funnier Excel use cases I’ve seen in the wild, when talking to users, is someone who had implemented the game 2048 entirely within a spreadsheet (ofc VBA). And it wasn’t just for fun - they weren’t allowed to play games on their work computer, but they needed their daily gaming fix. If you can dream it, Excel can probably get it done…
[1] https://trymito.io
One of the funnier Excel use cases I’ve seen in the wild, when talking to users, is someone who had implemented the game 2048 entirely within a spreadsheet (ofc VBA). And it wasn’t just for fun - they weren’t allowed to play games on their work computer, but they needed their daily gaming fix. If you can dream it, Excel can probably get it done…
[1] https://trymito.io
I bet... if you use a column as a list of the wasd inputs (i.e. "w enter" records as an up move) you could get 2048 without VB. Maybe even without the new lambdas but it'd be significantly easier thanks to that. A new thing for the "I better not waste my time on this" list ;).
Google Sheets REGEX* and QUERY kick ass. I don't want to use PowerQuery, I want to use formulas.
If only Google Sheets let you name cells and ranges!
Wait. Can't you name ranges in Sheets??
You can name cells in the upper left corner and name ranges under `Data > Named Ranges`. You can't create a value that's not part of a cell or range (which you can do in Excel under the Name Manager.)
I use regular expressions all the time in Excel, with the relevant functions implemented in VBA.
I once thought Aldus Pagemaker WAS the Mac.
Lots of people thought AOL WAS the Internet.
And to this day many think EXCEL is everything.
Lots of people thought AOL WAS the Internet.
And to this day many think EXCEL is everything.
The most successful functional language ever.
You mean second most? JavaScript exists.
I guarantee you there are more writers of Excel than JS in the world. (Which are more important to a language? Consumers or writers?)
once you count consumers, you get into semantics. Like is C/C++ more used than javascript, because all the javascript essentially runs in browsers compiled from C++?
By that metric, all programming languages including assembly have exactly 0 consumers, since nothing human-readable actually runs on computers.
Look at that, I have saved programming from language popularity contests, no need to thank me. I will humbly accept your bitcoins though.
---
The most reasonable definition of a language consumer is as follows: If a program text P is written by a human-level intelligence in source language L, then every user of any automatically-produced-artifact from P is a consumer of L.
Suppose a programmer use typescript to write a web app:
- The programmer, who must compile the typescript to javascript, is a consumer of whatever language the typescript compiler happens to be written in.
- The user, who must run the resultant javascript files, is a consumer of
- Languages with no human-level-intelligence writers have 0 consumers
- Every producer is a consumer, since the only human-level intelligences currently writing code are humans, and human producers are always consumers as well since they run the artifacts produced from their own program texts.
- Auto-Generated code add to the consumers of the generating language, not the generated language. If part of a C++ app came from the output of a python script, every consumer of the app is a consumer of python, since some of the artifacts they consume ultimately came from python source. If the script generates (say) Rust or Fortran code that is then compiled and linked into the final executable, the only languages the users consume are C++ and Python. Auto-generated code is an automatically produced artifact.
Look at that, I have saved programming from language popularity contests, no need to thank me. I will humbly accept your bitcoins though.
---
The most reasonable definition of a language consumer is as follows: If a program text P is written by a human-level intelligence in source language L, then every user of any automatically-produced-artifact from P is a consumer of L.
Suppose a programmer use typescript to write a web app:
- The programmer, who must compile the typescript to javascript, is a consumer of whatever language the typescript compiler happens to be written in.
- The user, who must run the resultant javascript files, is a consumer of
(1) typescript, because the JS files is an artifact produced automatically
from the typescript text the programmer, who is a human-level
intelligence, wrote.
(2) The languages the browser/JS engine is source-written in, typically C++,
Rust, or Zig.
(3) Possibly javascript, because typescript is a strict superset of
javascript, and thus there is a probability that at least part of the
original text of the webapp is valid javascript, and thus qualify in the
definition. Take note that this has nothing to do with the typescript
program text compiling to javascript, this is solely due to the fact
that typescript is a strict superset of javascript, any language that is
not would not have this property.
- Languages with no human-level-intelligence writers have 0 consumers
- Every producer is a consumer, since the only human-level intelligences currently writing code are humans, and human producers are always consumers as well since they run the artifacts produced from their own program texts.
- Auto-Generated code add to the consumers of the generating language, not the generated language. If part of a C++ app came from the output of a python script, every consumer of the app is a consumer of python, since some of the artifacts they consume ultimately came from python source. If the script generates (say) Rust or Fortran code that is then compiled and linked into the final executable, the only languages the users consume are C++ and Python. Auto-generated code is an automatically produced artifact.
Excel is "written" by many more people than javascript
GP mentioned programming in Excel. That's an advanced topic. Very few people actually program in it.
I don't count computation as programming (Sum/count/avg).
I don't count computation as programming (Sum/count/avg).
Why not? Is writing a function to calculate a fibonacci number not programming? If no, why is it the introduction to a lot of functional language tutorials?
Sure, writing a function yes.
But most people use count/max/avg prebuilts, with little to no logic. If it ain't Turing Complete, it's not programming :P
But most people use count/max/avg prebuilts, with little to no logic. If it ain't Turing Complete, it's not programming :P
As someone who lives in Excel, has done quite a bit of BI as well, and is learning programming I love that Excel gives you the ability to build complex algorithms but also to sometimes bypass it and just hardcode some stuff that would require complex loops or recursion, and create exceptions by breaking a formula into a few pieces in different cells that would likewise require a lot of ugly code.
It empowered me to build what I needed, but may have held back my progress in building better mental models for data schemas and algos. You win some, you lose some I guess.
It empowered me to build what I needed, but may have held back my progress in building better mental models for data schemas and algos. You win some, you lose some I guess.
One thing I’m missing in Excel is the ability to define an auto-updating table that is basically an SQL-like view joining/filtering/projecting/grouping data from some other tables in the same Excel file. Or is there actually a way to do this? I understand that Power Query allows to do something like that with an external data source. Being able to have the result of a spillover formula automatically form a table (or a named range) would also be helpful.
PowerQuery can also use ranges inside the same worksheet as data sources. You can define either cell ranges or named tables as data sources and then use them in PowerQuery just like you would use any external data source.
This is a standard use case for ETL (extract load transform) tools such as Easy Data Transform at the budget end (which i wrote) or Alteryx at the corporate end.
I want the expressiveness of SQL queries within a single Excel file, so that data entry and derived views are within the same tool and same file. Also, EDT doesn‘t look like it can do joins?
>I want the expressiveness of SQL queries within a single Excel file, so that data entry and derived views are within the same tool and same file.
There is at least 1 tool that can run SQL within Excel. I forget the name. You can probably find it via Google.
>Also, EDT doesn‘t look like it can do joins?
Easy Data Transform can absolutely do Joins (plus 58 other transformations).
There is at least 1 tool that can run SQL within Excel. I forget the name. You can probably find it via Google.
>Also, EDT doesn‘t look like it can do joins?
Easy Data Transform can absolutely do Joins (plus 58 other transformations).
> Sheets doesn’t even have proper tables, much less lambdas
I never formally studied computer science, so it's possible that I'm missing some distinction between lambdas and functions, but doesn't [0] indicate that Google Sheets supports custom named functions?
[0] https://developers.google.com/apps-script/guides/sheets/func...
I never formally studied computer science, so it's possible that I'm missing some distinction between lambdas and functions, but doesn't [0] indicate that Google Sheets supports custom named functions?
[0] https://developers.google.com/apps-script/guides/sheets/func...
The simplest answer is that "named functions" and "lambdas" are mutually exclusive, since a simple definition of a lambda is an "anonymous function," or one that doesn't have a name. That's all both practically and technically correct but I call it the "simple" answer because it kind of misses the point that named functions and lambdas are used in different ways. One articulation would be that named functions serve primarily as a means of reuse (to avoid repeatedly writing out the same thing) while lambdas serve primarily as a mean of containment (both in terms of scope and to package up a bit of logic in a form that can easily be passed).
This is all a bit more complicated in the spreadsheet world because spreadsheets have historically maintained a separation between "the document" and "extra code." In Excel, this is the separation between the spreadsheet itself and VBScript macros which might be packaged with it. In Google Sheets, it is the separation between the spreadsheet itself and AppScript.
In Excel, it is possible to implement functions in the sheet by use of the LAMBDA function in a cell. Amusingly, because of Excel's general concept of named cells, these lambdas can actually have names if you want, and can be called by those names. This allows easy implementation of e.g. recursive logic within the sheet, something that was not historically possible without the use of VBScript.
Google Sheets still doesn't have that capability, and the functions you mention are an AppScript feature that can be called from within the sheet.
You will notice in reading this that part of the confusion is that the difference between a conventional "programming language" and the spreadsheet environment means that the terms "function" and "lambda" have somewhat different meanings in spreadsheets than in a more general-purpose programming language.
This is all a bit more complicated in the spreadsheet world because spreadsheets have historically maintained a separation between "the document" and "extra code." In Excel, this is the separation between the spreadsheet itself and VBScript macros which might be packaged with it. In Google Sheets, it is the separation between the spreadsheet itself and AppScript.
In Excel, it is possible to implement functions in the sheet by use of the LAMBDA function in a cell. Amusingly, because of Excel's general concept of named cells, these lambdas can actually have names if you want, and can be called by those names. This allows easy implementation of e.g. recursive logic within the sheet, something that was not historically possible without the use of VBScript.
Google Sheets still doesn't have that capability, and the functions you mention are an AppScript feature that can be called from within the sheet.
You will notice in reading this that part of the confusion is that the difference between a conventional "programming language" and the spreadsheet environment means that the terms "function" and "lambda" have somewhat different meanings in spreadsheets than in a more general-purpose programming language.
> a lambda is an "anonymous function," or one that doesn't have a name.
I'd frame this slightly differently. The process of defining a function involves:
1. Creating a function object.
2. (Optional) Assigning a name to it.
Lambda is Step 1, and it happens even in programming languages that don't have a so-called "lambda" feature. Such languages always automatically do both steps. Those that do have "lambda" simply make it available to the programmer explicitly, and programmers who use it usually omit Step 2.
In Scheme and occasionally in Common Lisp it's not unusual to perform both steps explicitly and use lambda to create named functions. It's fairly common in modern Javascript too.
The thing that's new in Excel (since 2020) is the ability to create new functions at all and it's reasonable to just call that "lambda."
I'd frame this slightly differently. The process of defining a function involves:
1. Creating a function object.
2. (Optional) Assigning a name to it.
Lambda is Step 1, and it happens even in programming languages that don't have a so-called "lambda" feature. Such languages always automatically do both steps. Those that do have "lambda" simply make it available to the programmer explicitly, and programmers who use it usually omit Step 2.
In Scheme and occasionally in Common Lisp it's not unusual to perform both steps explicitly and use lambda to create named functions. It's fairly common in modern Javascript too.
The thing that's new in Excel (since 2020) is the ability to create new functions at all and it's reasonable to just call that "lambda."
I think it's important to be clear that it's been possible to create new functions in Excel since time immemorial, but it used to be possible only in VBScript - the same situation as most other spreadsheet editors, e.g. Google Sheets where it must be done in AppScript.
[deleted]
That's a helpful perspective - thank you!
It’s mostly an ergonomic difference.
Google Sheets has custom functions in Javascript; excel has them in VBScript. Both are part of the 'product', but they’re not integrated into the spreadsheet user interface at all. Using them is more like writing an extension to the Excel/Google Docs app, than it is like editing a document.
To define a custom function you need to leave the spreadsheet UI and define it in a totally different programming language. It’s a major barrier to entry that most users don’t cross, and the context-switching is a hassle even if you’re comfortable with both.
Lambdas let you write a function that takes arguments in the same way you would write a formula that references other cells.
Google Sheets has custom functions in Javascript; excel has them in VBScript. Both are part of the 'product', but they’re not integrated into the spreadsheet user interface at all. Using them is more like writing an extension to the Excel/Google Docs app, than it is like editing a document.
To define a custom function you need to leave the spreadsheet UI and define it in a totally different programming language. It’s a major barrier to entry that most users don’t cross, and the context-switching is a hassle even if you’re comfortable with both.
Lambdas let you write a function that takes arguments in the same way you would write a formula that references other cells.
Excel is my Swiss army knife. I started using Lotus 123 in the late 1980s to automate data input via the serial port from scales, pH meters and such in a PCB shop. Also used it for editing Gerber drill and router files. Later, I used Excel with VBA and Sysinternals utilities to manage desktops and VMs. I still use Excel weekly with a barcode scanner to track household stuff.
People underestimate this kind of thing.
In my case it was real-time interactive data acquisition from a scientific instrument computer to the PC, into one spreadsheet as a database with live charting, followed by tabular calculation and a client requirements filter before final reportable data was selected and placed on a deliverable Word document.
All automatically to mimic the established 1000-step manual process that produces the same paperwork product.
Which was either emailed or sent to the clients from the old built-in faxmodems we all used to have. Once this was fully established, I had the "paperless" office.
The hard part was the object-oriented VBA in Excel to interface with the antique host's 1979 BASIC through the COM port, and write & read the files to disk (which the antique never had).
One cool thing since it was a COM port (when land lines were everywhere), instead of plugging the antique host into a PC in the lab for Windows data handling, you could alternatively plug the host into an external telephone modem which was set to answer mode.
Then from a remote PC, use that modem to dial in to your instrument and operate from there. Long-distance charges may apply.
Early laptops all had COM ports and good ones also had infrared for communication with business cellphones. You installed the infrared driver and it was a virtual COM port to Windows. You could really call in to the lab from anywhere, no more dependence on a land line. This was before USB or Bluetooth.
Over the years as serial mice had been replaced by PS/2 mice, and external modems replaced by convenient built-in internal ones (since there were so many people on dial-up), laptops no longer had physical COM ports, just telephone jacks. USB and Bluetooth were used as virtual COM ports for cellular sessions but then back in the lab with the same laptop you had to use its built-in modem and a plain telephone cord. You had to connect to the host's external modem by disconnecting it from the phone line, and plugging in your PC to the host modem instead of directly to the host COM port now. Then use modem commands intended for leased-line operation without a dial tone.
Without relying on the continued operation of the antique hosts, it could also open and write files in a common format used by instrument makers to this day.
In my case it was real-time interactive data acquisition from a scientific instrument computer to the PC, into one spreadsheet as a database with live charting, followed by tabular calculation and a client requirements filter before final reportable data was selected and placed on a deliverable Word document.
All automatically to mimic the established 1000-step manual process that produces the same paperwork product.
Which was either emailed or sent to the clients from the old built-in faxmodems we all used to have. Once this was fully established, I had the "paperless" office.
The hard part was the object-oriented VBA in Excel to interface with the antique host's 1979 BASIC through the COM port, and write & read the files to disk (which the antique never had).
One cool thing since it was a COM port (when land lines were everywhere), instead of plugging the antique host into a PC in the lab for Windows data handling, you could alternatively plug the host into an external telephone modem which was set to answer mode.
Then from a remote PC, use that modem to dial in to your instrument and operate from there. Long-distance charges may apply.
Early laptops all had COM ports and good ones also had infrared for communication with business cellphones. You installed the infrared driver and it was a virtual COM port to Windows. You could really call in to the lab from anywhere, no more dependence on a land line. This was before USB or Bluetooth.
Over the years as serial mice had been replaced by PS/2 mice, and external modems replaced by convenient built-in internal ones (since there were so many people on dial-up), laptops no longer had physical COM ports, just telephone jacks. USB and Bluetooth were used as virtual COM ports for cellular sessions but then back in the lab with the same laptop you had to use its built-in modem and a plain telephone cord. You had to connect to the host's external modem by disconnecting it from the phone line, and plugging in your PC to the host modem instead of directly to the host COM port now. Then use modem commands intended for leased-line operation without a dial tone.
Without relying on the continued operation of the antique hosts, it could also open and write files in a common format used by instrument makers to this day.
Excel is very cool, you can even watch videos in Excel with just a tiny bit of code!
https://youtu.be/-oOw6u291HM
Has MS ever fixed the bad arithmetic implementation Excel used, that produced explicitly wrong answers for ordinary computations? Maybe an optional setting for a worksheet "Produce right answers"? And, "New worksheets should have 'Produce right answers' set"?
(I don't recall the details about the bad arithmetic.)
(I don't recall the details about the bad arithmetic.)
Probably you should give an example. Afaik, there was never an issue about computational accuracy and you should be knowing that almost fortune 500 companies use it for their day to day sales/financial data
There are (were, cannot say if fixed and in which version) a couple ones that I remember.
One is not really an error, it is more like GIGO in the Average function:
https://exceloffthegrid.com/excels-average-function-the-hidd...
The other one is actually an error due to internal number storing (floating point):
https://docs.microsoft.com/en-us/office/troubleshoot/excel/f...
More generally there is 15 digit precision that can create issues, but the "real bug" was in Excel 2007
77.1 multiplied by 850 gave apparently 100,000 instead of 65,535
https://news.ycombinator.com/item?id=59392
https://www.journalofaccountancy.com/issues/2014/mar/excel-c...
One is not really an error, it is more like GIGO in the Average function:
https://exceloffthegrid.com/excels-average-function-the-hidd...
The other one is actually an error due to internal number storing (floating point):
https://docs.microsoft.com/en-us/office/troubleshoot/excel/f...
More generally there is 15 digit precision that can create issues, but the "real bug" was in Excel 2007
77.1 multiplied by 850 gave apparently 100,000 instead of 65,535
https://news.ycombinator.com/item?id=59392
https://www.journalofaccountancy.com/issues/2014/mar/excel-c...
None of that contradicts.
Named formulas are quite handy e.g. https://superuser.com/questions/464039/creating-a-chart-usin...
Excel has been cool for a few decades now. There are entire trading strategies run on Excel - down to actually sending and cancelling orders. Companies have banked their entire success on operating via the spreadsheet.
I always hoped things like Google Sheets would supplant Excel - but its not feature complete...
I always hoped things like Google Sheets would supplant Excel - but its not feature complete...
Can excel create csv in utf-8 nowadays?
https://stackoverflow.com/questions/4221176/excel-to-csv-wit...
That would be cool.
https://stackoverflow.com/questions/4221176/excel-to-csv-wit...
That would be cool.
Thing is, Excel could have been so much cooler, much, much earlier. Decades of unused potential.
Yes, but when are they fixing the regression in character spacing when mixing unicode and non-unicode? https://www.youtube.com/watch?v=xubbVvKbUfY
> All of the online comparisons say they’re about equal, but Sheets doesn’t even have proper tables, much less lambdas
I'm not sure what "proper tables" would look like, but Google Sheets has JavaScript integration, which is nice (if a bit slow).
I'm not sure what "proper tables" would look like, but Google Sheets has JavaScript integration, which is nice (if a bit slow).
>I'm not sure what "proper tables" would look like
This is the third time this has come up on Hackernews in the past week.
Excel lets you designate a specific rectangle of cells as a named "table" (this is not the same thing as a pivot table). Each named column in the table automatically gets its own named range, which belongs to a namespace of that table (rather than the global namespace). A table will automatically expands its bounds when you insert data into cells immediately bordering it. Inserting a formula into the first row automatically fills-down that formula into the entire column. There is special syntax for referring to rows, columns, and ranges of columns within a table. Look up "structured references".
They are very useful. They make big spreadsheets tractable and maintainable. Think of them as mutable, expandable, reactive dataframes. If you're dealing with inherently tabular data in Excel and you're not using the tables feature, you are doing it wrong.
LibreOffice Calc and Google Sheets do not have them, to my endless bafflement.
This is the third time this has come up on Hackernews in the past week.
Excel lets you designate a specific rectangle of cells as a named "table" (this is not the same thing as a pivot table). Each named column in the table automatically gets its own named range, which belongs to a namespace of that table (rather than the global namespace). A table will automatically expands its bounds when you insert data into cells immediately bordering it. Inserting a formula into the first row automatically fills-down that formula into the entire column. There is special syntax for referring to rows, columns, and ranges of columns within a table. Look up "structured references".
They are very useful. They make big spreadsheets tractable and maintainable. Think of them as mutable, expandable, reactive dataframes. If you're dealing with inherently tabular data in Excel and you're not using the tables feature, you are doing it wrong.
LibreOffice Calc and Google Sheets do not have them, to my endless bafflement.
Not having proper tables in a spreadsheet app is like a programming language not having arrays.
You can get by without them through countless crappy hacks, but nobody would ever choose a language that couldn't support arrays.
IT chooses Google Sheets for whole companies every day even though they aren't sophisticated, or often even daily, users of any of the apps.
You can get by without them through countless crappy hacks, but nobody would ever choose a language that couldn't support arrays.
IT chooses Google Sheets for whole companies every day even though they aren't sophisticated, or often even daily, users of any of the apps.
In excel you can convert any range into a dedicated table object, which lets you give it a proper name, select rows/columns (with ctrl/shift+space), and use column names in formula (with `tablename[columnname]`). You can also load the table into powerquery to do things like decompositions or augmentations without modifying the original data.
^ seconded. If you learn tables with Excel you become a demigod, and if you learn Power Query with Excel tables you become a being of pure energy and are then tasked by management to fix every data problem in your organization.
Is there a way to do the variable assignment inline? I mean, it's nice to have an overview of all of them, but it would be neat to get them in there by just "tagging" a cell (preferably textually).
Not sure if this is what you mean but IIRC you can give the current selection a name by just typing it into the box in the top-left (the one that shows what is currently selected).
I really don't get this. It does nothing when I press Ctrl+Shift+V. If they can't even implement that, what's cool about it? The resulting formatting mess certainly isn't
VisiCalc was conceived in 1978, before the internet and all the data that comes with it. Excel has had to catch up with these developments and is finally doing a good job with it.
Unfortunately all of the good stuff has been ripped out of Excel Online.
No named ranges for one.
I'm not keen to run Windows just for this, so it's a Jupyter Notebook for me.
No named ranges for one.
I'm not keen to run Windows just for this, so it's a Jupyter Notebook for me.
If it weren't for the bugs still being carried over from the early 2000s it would be a lot a cooler.
Obligatory Joel Spolsky Excel video: https://www.youtube.com/watch?v=0nbkaYsR94c
Just don't get any ideas like doing a track-and-trace system for an entire nation in Excel.
Obligatory Power Point is Turing complete plug.
https://m.youtube.com/watch?v=uNjxe8ShM-8
https://m.youtube.com/watch?v=uNjxe8ShM-8