Excel is Pretty Dang Cool

It's like discovering a treasure chest under my kitchen sink

Surprise newsletter! I said there was none this week due to vacation, but that ended Monday and I'm forever cursed by a witch, so

People might remember this quote from last month's newsletter:

Interactive computation is a common enough activity for me that I'd put a lot of time into learning something like this. ...Maybe I should just get real good with Excel.

Did I? Did I! Here's some of the fun stuff I discovered:

Excel has actual programming affordances now

Let's start with the most useful feature nobody seems to know about. Instead of sprinkling A15 and B1:D94 in your code, you can just... give the cells and ranges names. Or skip storing information in a cell and just directly assign the name a value. Almost like a real programming language!

Now that's not normally something you write home about, except it fixes one of the biggest problems that makes spreadsheets illegible. Instead of writing the formula =A1*B1, you can do =Width*Height like you should have been able to 30 years ago.

Oh, and you can write custom formula now.

Yes, really. In 2020 Microsoft added LET and LAMBDA to Excel. If you assign the name MAXEXP to LAMBDA(arr,y, MAX(arr)^y), then you can use MAXEXP like any builtin formula. LET can bind new names in the middle of a formula, which can also refer to previous bindings. A really simple example is that LET(x, 1, y, x+1, z, y+x) evaluates to 3. Kinda like how every other programming language with LET works. Sure, the syntax is kinda wonky (unless you're a sexp fan), but it still. It's useful.

(It helps if you add newlines to formulae, which you can do with alt+enter.)

Flash fill!

Start with

Then press ctrl+e and:

How cool is that?!

There's good data provenance

I was pretty excited when I saw the "import from web" button:

It does almost what you'd expect: put in a url and it'll let you convert any of the HTML tables in Excel tables.

The almost bit is that it doesn't just load the table. Instead, it outputs the table as the result of a "data query" against the url. If you mangle the table beyond recognition, you can refresh the query and immediately restore the original table, along with any updates from the source.

You can do this to pretty much any data source, including XML, json, and tables in PDFs. As an experiment I dropped an old tax return PDF into the importer and it was able to pull out the right data.

Before you finish importing data, though, you get a chance to modify it in the "Power Query". You can filter out rows, fill in blank cells, split a column on a delimiter, basic data cleanup stuff like that. Your changes are modeled as a sequence of transformation steps on the data. This means that, if you refresh the query, all of your original transformations are reapplied.

Did I mention you can rename and place comments on your steps? Because you can place comments on your steps. If that's not cool enough, you can also fork a query, so that two separate queries share starting steps! So you can, for example, source a bunch of data, clean it up, and then decompose it into separate 2021 and 2022 tables. Then if you realize you need to do more cleaning, you can apply it to the forked prefix and both output tables will automatically update.

Excel is basically an APL

This might be the weirdest one for me. Excel has this thing called spillover arrays: a formula in a single cell outputs an array of values, which spill over into other cells. For example, if have this table:

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:

This opens up some fun APL-like experiences. Recently I had a data table like this:

In this case, B marked whether the value in A was supposed to be halfed or not. I wanted to calculate the sum of all values, after halving had been applied. The "proper" way to do it would be like this:

=SUM(A:A*IF(ISBLANK(B:B), 1, 0.5))

But you can also do it in an APL-like way, like this:

SUM(A:A*(1-0.5*(B:B="x")))

In the insider build there's a few formulae that will make Excel even more APL-like. In the meantime, here's game of life in Excel:

LET(x, SUM(OFFSET(cell#,-1,-1,3,3)), (x=3)+cell#*(x=4))

Miscellaneous Nuttery

• Excel comes with a constraint solver. You have to enable it in the addons.
• You can directly move or copy sheets between Excel files.
• With the linked data types, you can put a yoga pose into a cell and Excel will tell you how hard it is
• There's a button to generate 3D globe maps of your data. Apparently you can make guided tours, but I haven't tried that yet (or ever).
• There's a button that converts a selection into an image which I didn't even notice until I'd written this entire newsletter.

Overall I'm glad I put the time into learning Excel, because it's a neat tool that I can see being really useful to me. I'm also surprised at how much it outclasses Google Sheets. 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. Then again it is free, that's gotta count for something.

Also I don't think most users, or even most "power users", use many of these features. The same is true for devs, most of whom don't really use the features of their editors. It's good to explore these things!

Anyway I'd recommend checking it out if you or your company threw a 365 subscription at ya

Update for the Internets

This was sent as part of an email newsletter; you can subscribe here. Common topics are software history, formal methods, the theory of software engineering, and silly research dives. Updates are usually 1x a week. I also have a website where I put my polished writing (the newsletter is more for off-the-cuff stuff).

If you're reading this on the web, you can subscribe here. Updates are once a week. My main website is here.

My new book, Logic for Programmers, is now in early access! Get it here.