6 ms·
Like MS Excel, Pivot tables never die
- stonecharioteer 1y agoI wish more people explored Excel. Not just spreadsheets. But MS Excel. It is the best piece of software that came out of MSFT.
- articsputnik 1y agoI'm a BI engineer, and I always convince people to use something else. But when I need to calculate my finances, and does not fit into Obsidian, I find myself using Excel again too :D Great product. Maybe not so much if you need to align on financial numbers - as everyone has their own truth :)
- epistasis 1y agoIt is the best, but also the very very worst in so many ways. There are all sorts of data that are nearly impossible to get into Excel because of the ways that it tries to turn everything into a date. There has been so much silent data corruption because of random misfeatures that were added decades ago and now they will never back out of the system. The string OCT4 amongst a column of alphanumeric identifiers will get changed into a date, silently, on import, and it's nigh on impossible to find out how to import without that silent conversion. It's better to write your own Python code to get data into Excel than to use its built in foot guns.
- countmora 1y agoIf you import with PowerQuery you can explicitly specify the data type for each column.
- NetMageSCW 1y agoThat can be turned off (easily Googled), or as mentioned, doesn’t affect proper import using Power Query. Also, if you control the data format and know Excel is the intended target, you can just output your CSV so Excel won’t do that (put =“ and “ around text columns). For any spreadsheet which are updated by refreshing source data such as CSV output from other systems, PowerQuery is what should be used and is very effective.
- epistasis 1y agoWhat are the web search terms you use to find to turn this off? Most people have not been able to figure this out, with the help of the web or in the application itself. The typical windows user is not going to be accomplish a successful import, except maybe by whatever the heck PowerQuery is.
- IAmBroom 1y agoI just tried an import of a CSV file containing "OCT4" in a line by itself, and Excel asked me if I wanted to convert it. I clicked "No", and it imported without conversion.
- countmora 1y agoI wish more people stop hating on Excel. It's an incredible tool with cool stuff baked in (Python support, PowerQuery, etc.). Just because some people misuse it as database or it doesn't scale well beyond a couple of 10k rows does not make it a bad product. For 90% of daily office tasks it's just fine.
- qsort 1y agoI don't think it's as bad as people make it, and certainly we can't blame non-technical users if they like it, for many tasks it's the only tool that's simultaneously usable by normal people and powerful enough. It suffers from trying to do too many things at once, though. Excel 3 is enough for those use cases without being a complete nightmare for everyone else. Electronic spreadsheets as a concepts are genius, it's the implementation I hate.
- fishmicrowaver 1y agoToo many at once? Apparently, you can insert Python code into Excel now, which then gets executed into whatever Azure's equivalent of lambda is. I was recently introduced to someone at work who vibe coded an entire Python application in it and burned through their teams worth of cloud credits.
- thewebguyd 1y agoWhen Python in Excel was announced I was initially pretty excited until I found out that it executes in Azure. WHY wouldn't Microsoft just run it in the local interpreter on the machine?
- Qem 1y ago> WHY wouldn't Microsoft just run it in the local interpreter on the machine? Probably to tighten vendor lock-in.
- happytoexplain 1y agoExcel is fantastic. I love it. But using a spreadsheet to store data is completely reasonable. We delude ourselves as technically experienced people when we imply otherwise. When Excel fucks up data (perhaps the most unforgivable sin in all of software) with unexplainably bad defaults and UX for auto-formatting (i.e. "trying to be clever"), it's absolutely out of touch to point the finger at the end user.
- ayhanfuat 1y agoI love Excel. In some ways it is similar to Jupyter Notebooks: great for exploration, horrible for using in production.
- NetMageSCW 1y agoIt can be great in production, like any tool it depends on the nature of the task.
- eimrine 1y agoMS Excel is good because it has Lisp inside. Does Pivot thing contain Lisp?
- NetMageSCW 1y agoWhy do you think Excel has LISP inside? What would that have to do with Pivot Tables?
- eimrine 1y ago1. The datatype. 2. Idk, just asking.
- jtbaker 1y agoTangential: Timeless post I go back and read through periodically: https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/ https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
- canistel 1y agoWith _array formulae_ you can live without pivot tables, to a certain extent. They would auto-refresh, too...
- NetMageSCW 1y agoThe advantage of Pivot Tables is they are interactive, but (modern) dynamic array formulas are better in cases where you are producing a static result.
- dh2022 1y agoLooks like LLM-written article. It does not explain why Pivot tables never die. Also, somehow this page completely messed up my browser history - I could not just click the back arrow to navigate back.
- NetMageSCW 1y agoThat web site is impressively terrible.
- articsputnik 1y agoYou can also read here, in case that works better: https://www.ssp.sh/blog/why-pivot-tables-never-die/ https://www.ssp.sh/blog/why-pivot-tables-never-die/. And it's definitely not LLM written. And I tried to answer the why in the chapter "Why Pivot Tables Endure": > The enduring power of pivot tables is their robustness, simple usage, and fast, interactive response. It's the Lingua Franca of data if you are not fluent in the language of SQL or Python. A common language everyone understands: the top management, domain experts, and developers. It's an interface to data; it's the first no-code interface. Instead of the multidimensional query language MDX or the newer DAX, people can use a simple drag-and-drop interface. It democratized data analysis.
- gruez 1y ago>Also, somehow this page completely messed up my browser history - I could not just click the back arrow to navigate back. It seems like the page updates the page url every time you scroll to a new section, which means you end up with 10+ history entries for the page if you scroll all the way through. To exit out of the page you'd have to click back 10+ times to go through those history entries. Google maps does something similar, where it adds a new entry to history every time you pan, which means your history is polluted with entries for google maps.
- rtkwe 1y agoNo this does it worse, try actually backing out through it after scrolling down, when I tried it went back to the beginning of the current section but then I couldn't get any further back than that so it's adding more entries each time you go back.
- Qem 1y agoPivot tables are available on LibreOffice as well: https://help.libreoffice.org/latest/en-US/text/scalc/guide/datapilot_createtable.html https://help.libreoffice.org/latest/en-US/text/scalc/guide/d...
- nyrikki 1y agoThey are in most spreadsheets and many databases now. Even Sheets and Numbers and Oracle SQL etc... maybe just without the same UI. Microsoft copied the basic concept from Lotus, and Borland also copied it etc...
- d--b 1y agoWhy would pivot tables ever die? That's just like saying line charts never die...
- bflesch 1y agoThis website hijacks my back button. I just loaded it and scrolled half a screen and every time I clicked back button on the mouse it added another history object to the stack.
- seemaze 1y agoYup. Messing with my back button is an immediate fuck off.
- skeeter2020 1y agoThe foundational premise that pivot tables went away somewhere seems flawed; they've been going consistently strong as long as I've had a job.
- bArray 1y agoI can't find it now (would appreciate a link if somebody has one), but "pivot table" I believe was the name for a process where you would follow the intersecting points on a multi-equation graph that would represent the maximal value. Essentially you have some maximal resource, i.e. money, and you are trying to figure out how it can be used most effectively under several constraints (don't have to be linear). I remember distinctly having the calculate them by hand in an A-level math exam in the UK.
- Ylpertnodi 1y ago* maths exam...
- adammarples 1y agoLinear optimisation? Ie. The excel "solver"
- alphalima 1y agoAre you talking about linear programming. https://en.wikipedia.org/wiki/Linear_programming https://en.wikipedia.org/wiki/Linear_programming
- hackingonempty 1y agoDemo of Lotus Improv https://www.youtube.com/watch?v=TbsfvdZXE7s&t=205s https://www.youtube.com/watch?v=TbsfvdZXE7s&t=205s
- hodder 1y agoWhy would either of them die when they are some of the greatest tools ever invented and there is nothing better for the typical use case?
- karmakurtisaani 1y agoIt's the best tool I never want to work with.
- nonethewiser 1y agoI have absolutely no idea why pivot tables would die before spreadsheet software. If people are using spreadsheet software, of course pivot tables will be useful. So to put my stack overflow hat on, it's asking the wrong question. Its entirely a matter of whether or not spreadsheet software has a place.
- jeremyscanvic 1y agoPivot tables rock! I wouldn't be surprised if they were studied mathematically and proven to be somewhat capable of everything you might want to do in the context of tabular data processing.
- NetMageSCW 1y agoI think a major fault in the article is the minimization of the impact Improv had on the Pivot Table concept. Much like Numbers twists the spreadsheet paradigm into independent tables on a page instead of tabs, Improv twisted the spreadsheet into everything is a Pivot Table (or Excel Table). And it was first written for NeXt workstations before being ported to Windows. It was a radical departure from Lotus’s previous products and they never realized what they had in it. Microsoft capitalized on that lack of imagination by incorporating much of the functionality into the existing spreadsheet model.
- seektable 1y agoshameless plug - online pivot tables: https://www.seektable.com https://www.seektable.com o_o
- breadwinner 1y agoThis is a better article if you are interested in the history of Pivot Tables: https://qz.com/1903322/why-pivot-tables-are-the-spreadsheets-most-powerful-tool https://qz.com/1903322/why-pivot-tables-are-the-spreadsheets...
- dang 1y agoDiscussed here: The pivot table, the spreadsheet's most powerful tool (2020) - https://news.ycombinator.com/item?id=37820877 https://news.ycombinator.com/item?id=37820877 - Oct 2023 (192 comments)
- TheHideout 1y agoSince this article successfully got me to look at an example using their software and get to the edge of their funnel, has anyone used Rill and can comment on its utility vs just using Excel which I already have or something else?