16 ms·
Excel is pretty dang cool
- __warlord__ 4y agoYes, but when are they fixing the regression in character spacing when mixing unicode and non-unicode? https://www.youtube.com/watch?v=xubbVvKbUfY https://www.youtube.com/watch?v=xubbVvKbUfY
- bernardv 4y agoThing is, Excel could have been so much cooler, much, much earlier. Decades of unused potential.
- roflyear 4y agoExcel 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 4y ago[deleted]
- fragmede 4y ago> 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).
- hwayne 4y agoIn 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.
- quacked 4y ago^ 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.
- _dain_ 4y ago>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.
- pseudosavant 4y agoNot 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.
- jeffwask 4y agoIt may be the greatest, most feature rich application ever written
- bambax 4y ago> 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.)
- Someone 4y agoMicrosoft Multiplan for CP/M supported that in 1982 (http://www.bitsavers.org/pdf/microsoft/cpm/Microsoft_Multiplan_for_CPM_1982.pdf http://www.bitsavers.org/pdf/microsoft/cpm/Microsoft_Multipl..., page 99) I think later versions of VisiCalc did, too.
- adrian_b 4y agoAlready 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.
- pwinnski 4y agoI 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.
- wesnerm2 4y agoFormer 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?).
- antupis 4y agoNow when I think somekind spreadsheet wrapper around sqlite would be pretty cool.
- reportgunner 4y agoYou can just connect to SQLite directly from excel via ODBC.
- hairofadog 4y agoMy 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.
- xxpor 4y agoThe most successful functional language ever.
- Ygg2 4y agoYou mean second most? JavaScript exists.
- xxpor 4y agoI guarantee you there are more writers of Excel than JS in the world. (Which are more important to a language? Consumers or writers?)
- chadash 4y agoonce 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++?
- Banana699 4y agoBy 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 (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. From (3) we see that languages are not completely mutually exclusive : There is a language called Arithmetic ('+','*','-','/' and numbers) that has a vast userbase unparalled by any single programming language, since it's a subset of a lot of programming languages. Other consequences of this definition is that : - 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.
- kasajian 4y agoI 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.
- moomin 4y agoHe’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.
- chiph 4y agoIn 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.
- listenallyall 4y agoIt's like a TIL post on the front page of Reddit that states something you assumed everybody knew, yet has 30,000 upvotes.
- mathteacher1729 4y agoXKCD 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.
- ms512 4y agohttps://xkcd.com/1053/ https://xkcd.com/1053/ (to make a link available)
- deleted 4y ago[deleted]
- stuckinhell 4y agoIt 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.
- hgsgm 4y ago
- bee_rider 4y agoIt 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.
- qsort 4y agoI 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.
- plonk 4y ago> 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.
- 4y ago
- gchamonlive 4y agoExcel 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.
- april_22 4y ago100% agree. Is there any good alternative with the same kind of functionality? I don't know of anything.
- abrichr 4y agoCan you please describe your use case?
- extr 4y agoI 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.
- gchamonlive 4y agoI 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.
- smm11 4y agoI once thought Aldus Pagemaker WAS the Mac. Lots of people thought AOL WAS the Internet. And to this day many think EXCEL is everything.
- captainmuon 4y ago> 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. A B C +------------------------- 1 | =VARIABLE(X) 2 | =A1*2 3 | =A2+1 And then you could define `MYFUN()` to be A3 and use `=MYFUN(6)` in a cell to get 13. Or maybe, `=EVALUATE(A3; A1)` or `=EVALUATE(A3; 'X')` or something. 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.)
- rad_gruchalski 4y agoYou can also use lambdas: =LAMBDA(... args, calculation) Those can be defined under Formulas > Name manager.
- g8oz 4y agoWill LAMBDA be available for the standalone Office 2021 though? Or is it only going to be there for the subscrption based Office 365?
- deleted 4y ago[deleted]
- downboots 4y agoCustom formulas/functions exist using VBA
- hwayne 4y agoIt'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) I just checked and the Evaluate Formula button can sequentially step through a LET. Though this doesn't give you intermediate results you can use in other calculations.
- henning 4y agoA 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.
- bee_rider 4y agoIf everything that could be implemented as a spreadsheet/PDF combination was, what would we do for work?
- iLoveOncall 4y agoSure, 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-with-line-breaks-in-excel-2007 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).
- bzxcvbn 4y agoIt 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.
- iLoveOncall 4y agoThis 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.
- bzxcvbn 4y agoMaybe stop using outdated software?
- iLoveOncall 4y agoThe mainstream support for Office 2019 ends on October 10, 2023 and the extended support on October 14, 2025. It's not outdated software.
- 4y ago
- papandada 4y agoExcel 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).
- eastbound 4y agoBut 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.
- decafninja 4y agoI've heard traders at banks literally yell something like "Just fire the entire useless IT department and just let me use my Excel!"
- renewiltord 4y agoHaha, but traders are traders. What they say is mostly an effect of whether their stuff is performing or not.
- Seanambers 4y agoIT 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.
- renewiltord 4y agoHaha, I can totally believe that at a big firm. I'm at a prop shop and we're much more outcome focused than that.
- hgsgm 4y ago
- photochemsyn 4y agoPython'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.
- proamdev123 4y ago+1 for “Python for Data Analysis”. And Wes (the creator of the Pandas library) has made the book available for free on his website!
- AtlasBarfed 4y agoI 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.
- proamdev123 4y agoI 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.
- pillefitz 4y agoSharing sheets works just great. You distribute a link and that's it.
- DavidPeiffer 4y agoIf 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 =SUM(tbl_Sales_Transactions[Total]) And knowing you don't have to adjust the range of the named area as long as the table is the correct length. Far nicer than SUM(F:F) or SUM(F2:F1000) which can cause performance issues and could be exceeded when pulling in new data respectively. Within the table, you can do this syntax to use items on the same row of data. =[@[Price]*[@[Tax Rate]]
- dogma1138 4y agoObligatory Power Point is Turing complete plug. https://m.youtube.com/watch?v=uNjxe8ShM-8 https://m.youtube.com/watch?v=uNjxe8ShM-8
- mhd 4y agoIs 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).
- mechanical_berk 4y agoNot 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).
- vangelis 4y agoGoogle Sheets REGEX* and QUERY kick ass. I don't want to use PowerQuery, I want to use formulas.
- bbkane 4y agoIf only Google Sheets let you name cells and ranges!
- onlyrealcuzzo 4y agoWait. Can't you name ranges in Sheets??
- hwayne 4y agoYou 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.)
- oxfeed65261 4y agoI use regular expressions all the time in Excel, with the relevant functions implemented in VBA.
- scubakid 4y agoExcel 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.
- max59676 4y agoWould 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)
- scubakid 4y agoI 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.
- proamdev123 4y agoBut would you say using that web stack initially would have been overkill?
- LtWorf 4y agoMaintainability » 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.
- narush 4y agoExcel 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 https://trymito.io
- zamadatix 4y agoI 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 ;).
- janvdberg 4y agoObligatory Joel Spolsky Excel video: https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c
- jkaptur 4y ago> 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-the-api-war/ https://www.joelonsoftware.com/2004/06/13/how-microsoft-lost.... * In either sense, really.
- vxNsr 4y agoThis 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.
- kriro 4y agoThe 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.
- ezconnect 4y agoEnglish 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.
- LtWorf 4y agoThey should support both spellings, and a file saved in germany should work in france.
- the_af 4y ago> 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.
- jmt_ 4y agoI 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?
- renatovico 4y agoMy 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
- insane_dreamer 4y agoheretic
- tomjakubowski 4y agoThat's awesome. Has your team written more on it anywhere?
- renatovico 4y agoOf course
- MrsPeaches 4y agoIs this what you use? https://retool.com/ https://retool.com/
- pete_nic 4y agoVisiCalc 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.
- anonu 4y agoExcel 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...
- memcg 4y agoExcel 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.
- fuzzfactor 4y agoPeople 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.
- lvass 4y agoI'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.
- jader201 4y ago> 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.
- lvass 4y agoNever 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.
- Terretta 4y ago> 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.
- kretaceous 4y agoThe 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 https://www.youtube.com/watch?v=AdNJ3fydeao
- jamal-kumar 4y agoAt 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
- kube-system 4y agoI always assume that if dates/times aren't ISO 8601, then they're not in the format I'm expecting.
- guhidalg 4y agoBetter 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.
- di4na 4y agoI am surprised that you did not use the L1C1 syntax Hilel, as it would probably make it a bit easier too. As it allows proper relative work.
- hwayne 4y agoI never said I was good with Excel :P
- layer8 4y agoOne 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.
- MgB2 4y agoPowerQuery 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.
- hermitcrab 4y agoThis 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.
- layer8 4y agoI 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?
- hermitcrab 4y ago>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).
- hammyhavoc 4y agoJust don't get any ideas like doing a track-and-trace system for an entire nation in Excel.
- racl101 4y agoIf it weren't for the bugs still being carried over from the early 2000s it would be a lot a cooler.
- spitfire 4y agoI'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=TbsfvdZXE7s https://www.youtube.com/watch?v=TbsfvdZXE7s https://www.youtube.com/watch?v=lTko_Pt2ZZg https://www.youtube.com/watch?v=lTko_Pt2ZZg
- scubbo 4y ago> 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/functions https://developers.google.com/apps-script/guides/sheets/func...
- jcrawfordor 4y agoThe 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.
- dreamcompiler 4y ago> 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."
- mguerville 4y agoAs 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.
- nashashmi 4y agoAnd 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: When excel launched spill arrays in beta I was super excited, and immediately searched all of the ways this could be used, but the author really has shown me something I never thought of. I no longer have to drag and drop formulas like I used to. Hurray!
- dreamcompiler 4y agoExcel 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.
- cja 4y agoI 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
- cookie_monsta 4y agoI 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://developer.microsoft.com/en-us/graph/ https://make.powerautomate.com/ https://make.powerautomate.com/ https://docs.microsoft.com/en-us/office/dev/scripts/ https://docs.microsoft.com/en-us/office/dev/scripts/
- beckingz 4y agoGoing all in on microsoft is a solid move. It's a powerful platform. Going half in on microsoft is terrible.
- lostlogin 4y agoAll 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..,
- cookie_monsta 4y agoThis 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...
- SnowHill9902 4y agoUgh. I think it’s a matter of philosophy but I can’t connect with Microsoft. It feels so alien and hostile.
- ethbr0 4y agoThe 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.
- oger 4y agoOver 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!
- bloaf 4y agoThe 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.
- thrwawy74 4y agoAt 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
- throwaway787544 4y agoIt's crazy how resistant people are to change (or just learning new things)
- sroussey 4y agoAround 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.
- ncmncm 4y agoHas 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.)
- nitinreddy88 4y agoProbably 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
- ncmncm 4y agoNone of that contradicts.
- jaclaz 4y agoThere 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-hidden-pitfalls/ 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/floating-point-arithmetic-inaccurate-result 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://news.ycombinator.com/item?id=59392 https://www.journalofaccountancy.com/issues/2014/mar/excel-calculation-errors.html https://www.journalofaccountancy.com/issues/2014/mar/excel-c...
- bradwood 4y agoUnfortunately 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.
- amai 4y agoCan excel create csv in utf-8 nowadays? https://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding https://stackoverflow.com/questions/4221176/excel-to-csv-wit... That would be cool.
- keithalewis 4y agoNamed formulas are quite handy e.g. https://superuser.com/questions/464039/creating-a-chart-using-a-dynamic-named-range-in-excel-2010 https://superuser.com/questions/464039/creating-a-chart-usin...
- xlslimdev 4y agoExcel is very cool, you can even watch videos in Excel with just a tiny bit of code! https://youtu.be/-oOw6u291HM https://youtu.be/-oOw6u291HM