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:
Let’s start with the most useful feature nobody seems to know about. Instead of sprinkling
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
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.)
Then press ctrl+e and:
How cool is that?!
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.
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
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:
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))
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
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).