Excel is pretty dang cool

https://buttondown.email/hillelwayne/archive/excel-is-pretty-dang-cool/

by todsacerdoti • 4 years ago

376 253 4 years ago

fragmede• 4 years 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).

hwayne7• 4 years ago

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.

quacked• 4 years 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_4• 4 years 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• 4 years ago

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.

roflyear• 4 years ago

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).

jeffwask• 4 years ago

It may be the greatest, most feature rich application ever written

bambax• 4 years 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.)

Someone2• 4 years ago

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.

adrian_b• 4 years ago

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.

pwinnski• 4 years ago

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.

wesnerm2• 4 years ago

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?).

antupis• 4 years ago

Now when I think somekind spreadsheet wrapper around sqlite would be pretty cool.

reportgunner• 4 years ago

You can just connect to SQLite directly from excel via ODBC.

hairofadog• 4 years ago

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.

xxpor3• 4 years ago

The most successful functional language ever.

Ygg23• 4 years ago

You mean second most? JavaScript exists.

xxpor3• 4 years ago

I guarantee you there are more writers of Excel than JS in the world. (Which are more important to a language? Consumers or writers?)

chadash2• 4 years ago

Excel is "written" by many more people than javascript

kasajian• 4 years ago

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.

listenallyall• 4 years ago

It's like a TIL post on the front page of Reddit that states something you assumed everybody knew, yet has 30,000 upvotes.

mathteacher1729• 4 years ago

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.

hwayne7• 4 years ago

Yes but did you know about the yoga pose button

turtleyacht• 4 years ago

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.

monkey_monkey• 4 years ago

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.

_dain_4• 4 years ago

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.

jkaptur2• 4 years ago

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...

extr2• 4 years ago

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.

detaro• 4 years ago

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.

anamexis• 4 years ago

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.

andylynch• 4 years ago

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.

tda2• 4 years ago

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.

feoren• 4 years ago

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.

lenkite3• 4 years ago

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.

Foobar8568• 4 years ago

These days I am working with data analysts who are using Excel, they were mind blown to discover power query...

Stratoscope• 4 years ago

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

which formats as:

Instead of writing the formula =A1*B1, you can do =Width*Height

jbverschoor• 4 years ago

Always reminds me of this video.. https://www.youtube.com/watch?v=0nbkaYsR94c Excel has proper tables, named columns, better structured data etc.

zweifuss2• 4 years ago

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...

fencepost2• 4 years ago

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).

moomin• 4 years ago

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.

chiph2• 4 years ago

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.

mcdonje• 4 years ago

Custom formulas have also been around for decades, it just required VBA.

stuckinhell• 4 years ago

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.

bee_rider3• 4 years ago

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.

qsort• 4 years ago

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.

plonk• 4 years 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.

mhd2• 4 years ago

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)

narush2• 4 years ago

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

gchamonlive2• 4 years ago

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.

april_22• 4 years ago

100% agree. Is there any good alternative with the same kind of functionality? I don't know of anything.

abrichr• 4 years ago

Can you please describe your use case?

gchamonlive2• 4 years ago

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.

smm11• 4 years ago

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.

captainmuon• 4 years 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.)

downboots• 4 years ago

Custom formulas/functions exist using VBA

hwayne7• 4 years ago

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)

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.

_dain_4• 4 years ago

Use the LET formula. It gives you lexically scoped variables inside a formula.

    =LET(foo, ComplicatedExpression(),
         bar, AnotherComplicatedExpression(),
         baz, AnExpressionInvolvingPreviousVariables(foo, bar),
         ExpressionThatIsTheReturnValue())

Excel now also has (will have?) a LAMBDA formula for defining your own functions.

MikusR2• 4 years ago

https://www.microsoft.com/en-us/research/blog/lambda-the-ult...

tlb• 4 years ago

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.)

rad_gruchalski• 4 years ago

You can also use lambdas:

    =LAMBDA(... args, calculation)

Those can be defined under Formulas > Name manager.

g8oz• 4 years ago

Will LAMBDA be available for the standalone Office 2021 though? Or is it only going to be there for the subscrption based Office 365?

Someone2• 4 years ago

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)

molsongolden• 4 years ago

Google Sheets also has custom functions via Apps Script (including autocomplete and function help text).

henning• 4 years ago

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.

bee_rider3• 4 years ago

If everything that could be implemented as a spreadsheet/PDF combination was, what would we do for work?

iLoveOncall4• 4 years ago

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).

bzxcvbn3• 4 years ago

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.

iLoveOncall4• 4 years ago

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.

_dain_4• 4 years ago

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.

iLoveOncall4• 4 years ago

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?

cfiggers• 4 years ago

Use XLOOKUP.

Use INDEX and MATCH.

navjack27• 4 years ago

Xlookup exists

CSV of all types can be imported with power query.

bee_rider3• 4 years ago

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?).

papandada• 4 years ago

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).

eastbound• 4 years ago

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.

decafninja• 4 years ago

I've heard traders at banks literally yell something like "Just fire the entire useless IT department and just let me use my Excel!"

hbarka• 4 years ago

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?

conductr2• 4 years ago

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.

jaygray0919• 4 years ago

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.

tfehring• 4 years ago

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.

kurupt2132• 4 years ago

that's what internal validation processes are for

polmuz• 4 years ago

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

TAForObvReasons• 4 years ago

https://support.microsoft.com/en-us/office/overview-of-sprea...

Spreadsheet Compare, an official diff tool, is excellent

yen2232• 4 years ago

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.

adamsmith143• 4 years ago

Sounds great until you have multi-million dollar programs running off single point of failure spreadsheets with little or no backup or overwrite protection.

kevmo314• 4 years ago

That sounds pretty great. I'd love to have a multi-million dollar program.

insane_dreamer3• 4 years ago

how do you do versioning and review?

vlunkr• 4 years ago

You give your files helpful names like:

sales-2005-(copy)-Final-2015-(copy)-real-final-(copy).xlsx

fzumstein• 4 years ago

We built https://www.xltrail.com to make Excel work with Git.

photochemsyn• 4 years ago

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.

proamdev1233• 4 years 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• 4 years ago

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.

proamdev1233• 4 years ago

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.

pillefitz2• 4 years ago

Sharing sheets works just great. You distribute a link and that's it.

DavidPeiffer• 4 years ago

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

   =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]]

gxqoz• 4 years ago

Control + T is an easier way to do this

hwayne7• 4 years ago

With power query, named tables, linked data types, lambdas, and xlookup, you can hack structs into excel, letting you write `Person("John").age` in formulas.

dogma1138• 4 years ago

Obligatory Power Point is Turing complete plug.

https://m.youtube.com/watch?v=uNjxe8ShM-8

mhd2• 4 years ago

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).

mechanical_berk• 4 years ago

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).

__warlord__• 4 years ago

Yes, but when are they fixing the regression in character spacing when mixing unicode and non-unicode? https://www.youtube.com/watch?v=xubbVvKbUfY

vangelis• 4 years ago

Google Sheets REGEX* and QUERY kick ass. I don't want to use PowerQuery, I want to use formulas.

bbkane• 4 years ago

If only Google Sheets let you name cells and ranges!

onlyrealcuzzo• 4 years ago

Wait. Can't you name ranges in Sheets??

oxfeed65261• 4 years ago

I use regular expressions all the time in Excel, with the relevant functions implemented in VBA.

scubakid2• 4 years ago

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.

max59676• 4 years ago

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)

scubakid2• 4 years ago

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.

LtWorf2• 4 years ago

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.

narush2• 4 years ago

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

zamadatix• 4 years ago

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 ;).

bernardv• 4 years ago

Thing is, Excel could have been so much cooler, much, much earlier. Decades of unused potential.

janvdberg• 4 years ago

Obligatory Joel Spolsky Excel video: https://www.youtube.com/watch?v=0nbkaYsR94c

jkaptur2• 4 years 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....

* In either sense, really.

vxNsr• 4 years ago

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.

kriro• 4 years ago

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.

ezconnect• 4 years ago

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.

LtWorf2• 4 years ago

They should support both spellings, and a file saved in germany should work in france.

the_af• 4 years 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_• 4 years ago

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?

tomsmeding• 4 years ago

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.

simlan• 4 years ago

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.

rvba• 4 years ago

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.

koala_man• 4 years ago

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.

jaclaz2• 4 years ago

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.

layer83• 4 years ago

Sometime in the 90’s, even VBA keywords were localized.

zweifuss2• 4 years ago

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.

sbierwagen• 4 years ago

Excel is for end users. Not every Microsoft customer speaks English.

kqr• 4 years ago

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.

renatovico2• 4 years ago

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

insane_dreamer3• 4 years ago

heretic

tomjakubowski• 4 years ago

That's awesome. Has your team written more on it anywhere?

renatovico2• 4 years ago

Of course

MrsPeaches2• 4 years ago

Is this what you use?

https://retool.com/

pete_nic• 4 years ago

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.

anonu• 4 years ago

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...

memcg• 4 years ago

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.

fuzzfactor• 4 years ago

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.

lvass2• 4 years ago

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.

jader201• 4 years 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.

lvass2• 4 years ago

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.

Terretta• 4 years 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.

dkbrk• 4 years ago

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...

kretaceous• 4 years ago

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

jamal-kumar• 4 years ago

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

kube-system• 4 years ago

I always assume that if dates/times aren't ISO 8601, then they're not in the format I'm expecting.

guhidalg• 4 years ago

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.

di4na• 4 years ago

I 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.

hwayne7• 4 years ago

I never said I was good with Excel :P

layer83• 4 years ago

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.

MgB2• 4 years ago

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.

hermitcrab2• 4 years ago

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.

layer83• 4 years 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. Also, EDT doesn‘t look like it can do joins?

hammyhavoc• 4 years ago

Just don't get any ideas like doing a track-and-trace system for an entire nation in Excel.

racl101• 4 years ago

If it weren't for the bugs still being carried over from the early 2000s it would be a lot a cooler.

spitfire• 4 years ago

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=lTko_Pt2ZZg

scubbo2• 4 years 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/func...

jcrawfordor2• 4 years ago

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.

dreamcompiler2• 4 years 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."

scubbo2• 4 years ago

That's a helpful perspective - thank you!

systoll• 4 years ago

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.

mguerville• 4 years ago

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.

nashashmi• 4 years ago

  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:

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!

dreamcompiler2• 4 years ago

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.

cja• 4 years ago

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

cookie_monsta5• 4 years ago

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/

beckingz• 4 years ago

Going all in on microsoft is a solid move. It's a powerful platform.

Going half in on microsoft is terrible.

lostlogin3• 4 years ago

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..,

cookie_monsta5• 4 years ago

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...

SnowHill99023• 4 years ago

Ugh. I think it’s a matter of philosophy but I can’t connect with Microsoft. It feels so alien and hostile.

ethbr03• 4 years ago

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.

cookie_monsta5• 4 years ago

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

oger• 4 years ago

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!

bloaf2• 4 years ago

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.

thrwawy74• 4 years ago

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

throwaway787544• 4 years ago

It's crazy how resistant people are to change (or just learning new things)

sroussey• 4 years ago

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.

ncmncm2• 4 years ago

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.)

nitinreddy88• 4 years ago

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

ncmncm2• 4 years ago

None of that contradicts.

jaclaz2• 4 years ago

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...