I am loving using coda.io rather than Excel for simple data and documentation needs.
Excel never dies (2021)
141–150 of 275 posts
Re: Excel never dies (2021)
#142Earlier quoted context omitted.
Not sure what you mean by "power queries", but Google Sheets support SQL queries. Would be easier to see on an example.
PowerQuery. It's a tool built into Excel. It's a GUI that wraps an almost purely-functional DSL designed for ETL and data munging, called the M language. You can either use the GUI or write the code directly. It has first class functions and closures and normies are programming in it. It's great. More people should know about it. Btw it's kind of funny seeing so many HN users, many of whom must be working on software…
Your aside is exactly why I wanted to use a spreadsheet. It's the only tool that has that market penetration for non-programmers, and I wanted to see what made it tick. It seems like that may have backfired by not using Excel, however.
Re: Excel never dies (2021)
#143Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
Oh, and:
- The name manager is relatively cumbersome to use. F.e. at least some copy/clone functionality would be nice.
- Excel table columns cannot be directly used for DVL lists, you need to create additional named ranges pointing to them.
- Once Excel tables have been created, afaik it is not yet possible to extend them by additional columns, respective edit their defined ranges?
- RegExp support for DVLs, w/o having to rely on VBA (desktop only) or OfficeScript (Online only)
Re: Excel never dies (2021)
#144I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you. I was horrified to find that even with the supporting scripting capabi…
> it is very non intuitive to write a formula that retrieves all the rows in another sheet that match this rule You can retrieve an entire range of data with a single formula in either excel or Google sheets. The formula is caller FILTER https://support.microsoft.com/en-us/office/filter-function-f...
That's exactly what I did. Now separate out some columns and perform some additional transformation on that FILTERed data. Can you do it without repeating yourself (duplicating the FILTER statement, or any of the other transformations you need to do, besides just filling down a column). Can you perform these transformations only on the row height of the data, and not have extra rows with broken formulas?
I honestly wouldn't even be surprised if the functionality to do the above does exist, but for all of my searching I couldn't find it.
Re: Excel never dies (2021)
#145I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you. I was horrified to find that even with the supporting scripting capabi…
>I was horrified to find that even with the supporting scripting capabilities, the entire paradigm revolves around knowing the shape of your data in advance. Just record yourself finding the bottom of the data set (Ctrl + down arrow), then take a moment to make the code work in relative terms instead of absolute terms.
What do you mean by this? What am I "recording" as the bottom of the data set?
My point was that it is very hard to have a dynamic number of rows feed a proportionate dynamic number of rows. Scripting makes it much simpler, but at least with Google Sheet's scripting, the API seemed pretty lacking for that processing (in the very least, it's very slow, since it's running as a very constrained shared resource).
Re: Excel never dies (2021)
#146I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you. I was horrified to find that even with the supporting scripting capabi…
"Several tables and selectively joining them" ... "Enter an id and filter" Sure sounds like your creating a relational database in a spreadsheet, which is possible but not really the intended purpose?
Surely that's what lots of non-developer white collar workers use Excel for? I imagine there's orders of magnitude more people using Excel for data processing rather than Python or R. I'm well aware it's not the best tool for the job, but yet people are using it for purposes such as that. I wanted to learn more about that experience.
Re: Excel never dies (2021)
#147Earlier quoted context omitted.
Many people have tried launching an Excel-with-SQL-querying product, but it’s extremely hard to do the UI well. Also products where people write SQL are impossible to insure.
>an Excel-with-SQL-querying product This is basically what Powerquery is, and it's been built into Excel for years. And it does other things too.
yeah, but it's really not ergonomic relative to the default Excel-with-formulas experience
Re: Excel never dies (2021)
#148Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
Why doesn't CTRL+Backspace delete a word when editing a cell?
Re: Excel never dies (2021)
#149I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you. I was horrified to find that even with the supporting scripting capabi…
Can you give a more detailed description of what you were trying? I can't know for sure, but it sounds like Excel can easily handle what you've described, if you use it right. >the entire paradigm revolves around knowing the shape of your data in advance. How exactly do you program without knowing the shape of your data in advance? You need to know your database columns, or your JSON schema, etc. >(I was using Google…
Your comment about FOSS is spot on. While I'm very aware that Google Sheets is not OSS, it felt much more amenable to me than Excel (and I'm sure Excel's online free version isn't particularly fantastic anyway, though it may be better than Sheets from what people are saying here).
> How exactly do you program without knowing the shape of your data in advance? You need to know your database columns, or your JSON schema, etc.
This was a bit overloaded in my opinion, as in spreadsheets world, "shape" includes the number of rows, hence my comments. I know that the column layout needs to be known.
> Were you using dynamic array formulae
I looked into it, but couldn't figure out how to handle them without introducing a massive amount of formula duplication. The best I could figure out how to do was to do a single large FILTER (which is dynamic array) and doing a fill down on my other transformation formulas from there. I blacked out the rows past the end of the FILTER using conditional formatting rules (which felt very stupid to do, but I couldn't find anything better).
> The skill ceiling is very high
I don't doubt you, but if you can't discover the functionality, it might as well not exist. Admittedly I was clearly using the inferior tool, but in my searching for solutions I much more readily found Google's documentation over Excel's.
I also realize I'm not in the position of being forced into a corner; as most of us on this forum could, I just wave my magic wand and write the software to solve my problems. I imagine those who don't have that ability available to them will do "crazier and crazier" things to figure out how to accomplish their work in Excel, and therefore will learn much better ways than I have in my little experience with it.
----
I was building a tool to track the completion of finding parts for a given Lego set. You enter the set ID, it pulls the parts list for that set (Rebrickable nicely offers their database as a set of CSVs https://rebrickable.com/downloads/) and formats it nicely for consumption.
Re: Excel never dies (2021)
#150"Excel" became a synonym for spreadsheets. There are millions of SMEs, today, running on spreadsheets that aren't Excel. My wife runs her little SME with her paid-for Google Workspace / G Suite: she and her employees are happily filling 'em little cells from the browser in their Google world (for better or worse). There are millions of SMEs like these. I should know better and run some spreadsheet locally (or maybe u…
> I don't know anyone still using Excel.
I suspect that is very much just the crowds you move in. Every vaguely large enterprise I know is still 100% Microsoft. A huge part of Microsoft's profit these days is their Cloud department, which is really just printing money selling Office 365 licenses to enterprise.