How Excel Is Leading the Charge in Analyzing the World's Data

Brian Jones is Head of Product for Excel at Microsoft. Brian was on the Raw Data with P3 Podcast to talk about the past AND exciting future of Excel. Here are some excerpts from that conversation. Transcript not reviewed by participants, and for the full context listen to the podcast.

Excel: The World's Most Popular Programming Language

Brian Jones: There are certain things though that the product was always lacking. Some of the stuff we announced recently, things like Lambda and Let, those were just basic primitives you'd expect to have in a programming language, right? The ability to have code reuse. So if you write a formula that's complex, do you have to rewrite it every time? And then if you realize you made an error, do you have to then go back and fix it everywhere? Or you use Lambda where you can just define it once, give it a name, use that name everywhere, and if you need to fix it you go to one place and you go and fix it. Lambda also added a whole bunch of really powerful stuff around like you can do recursion, it made Excel Turing complete. In theory, Excel was Turing complete because of VBA. But with Lambda, it means just the grid and calc and formulas is Turing complete, take all the VBA and all that stuff out of it.

Lamba = Reusable Formulas?

Rob Collie: The thing I really like about that feature is the name, because you hear it and you know immediately what it does for you. I mean, it just jumps off the page, right? Like I say, Lambda, and everyone's like, yeah, totally, I know what I'd do with that, (laughs).

Rob Collie: I couldn't resist.

Brian Jones: Yeah.

Rob Collie: It's like, it's not reusable formulas. It's not.

Brian Jones: No. There's a reason. If there's anything that you should take away, the things that we do or are doing are really long running bets. Lambda is— it's a geeky, underlying enabling tech that a lot of end user-facing experiences will start to show up on top of over time. And yeah, if you even look at like our blog posts, we were targeting a specific audience with Lambda that would actually get it, right? Over time we will add more.

Data Types

Brian Jones: The other big one there is data types. I don't know how much you've looked at data types, but this will actually resonate with you from back in your XML days. If you think about all the innovation that's happened in Excel, tons of innovation around things like pivot table, conditional formatting you mentioned, all that stuff, formulas then stuff like XLOOKUP and crazy fast calc, there's been so much innovation, yet what you can type into Excel is no more interesting than like a notepad. Right? It is the most basic thing. You could write a string, a number, or a formula. What the heck?

Brian Jones: It's been around for 30 years and we haven't innovated in what can go into a cell. That's what data types is. We talk about it with just things like stocks, but underneath we've plumbed Excel so that a cell can be a deep, rich value— completely jagged data. If you look at like the Wolfram data types, it's hierarchical data that's all stored in that one cell, but it still works with calc. You can now have a cell value can be an image. You can compare two images with each other. You can say, does this equal that? You get true/false. It's kind of mind blowing what data types actually does.

Rob Collie: I can do a VLOOKUP or an XLOOKUP or whatever against a table of images now?

Brian Jones: Yes.

Rob Collie: And I won't need like the camera tool.

Excel and Power BI: Better Together

Brian Jones: Right. Now, the only way right now to get images is if you have a data types that have images and then you can pull that out through a reference. But of course eventually we'll just have where you can just go and insert your own image, right? But one of the partnerships that we did with Power BI is you can have Power BI data come into Excel as a data type. So I could have a set of data defined up in Power BI, bring that in as a data type, I can look at that data, I can hover over it, I see a card, it tells me where that came from. So now if I'm like, oh shoot, is that really what the product price is? Did somebody just copy paste this? Where did it come from? I know where it came from my hover over it, I see a card. It came from that Power BI source, right?

Brian Jones: So it means Power BI can be your master data manager. Right? You can say, okay, I've got all my master data here, I bring it into Excel, in Excel I actually see where that data came from, I can trust it, and now I can go and do a whole bunch of calc around it. So the data type stuff, it's just, again, it's kind of scratching the surface a little bit in terms of the way that we go and talk about it, but it's some deep, deep plumbing.

Arrays in Excel

Brian Jones: And then the last piece, which is also about just new types of values is arrays. Right now I can go and have it where I can have a formula that returns an array of values, right? So I could have STOCKHISTORY and that one formula will return a huge set of data that goes into the grid, and then I can write another formula that references that result and I can have that formula apply to the entire thing in the array, right? So I can have an array of data that I can pass through the calc chain and do manipulations on that array of data. And so I can do that step by step by step process, manipulating that full array, where in the past, that was the beauty of Excel, is I do a step by step by step, but it was always with that single value, like I get a number and I'm going to go do a few operations on it to get the final result.

Brian Jones: Now I can have a data type like a person and do multiple operations on it using the programming language, or I can have an array of data that I can do multiple steps on it. So it's the beauty of the grid and calc, but now I'm working against the world's data, I'm working against all sorts of objects, not just numbers and strings.

The Lambda Name: not a rant…

Rob Collie: So I'm going to give you all the disclaimers. It's one person's opinion and it's an unsolicited opinion. I know that.

Brian Jones: Yeah.

Rob Collie: Right. But what better time to give you that opinion than when we're recording for the world to hear? So I think that of all the features you just mentioned that are pushing the envelope, like the state-of-the-art of Excel forward, I have this instinct that the one that's going to become most common, sort of like most ubiquitous, is Lambda. And so I want to project that sooner or later you're going to rename it, because I think it's probably more applicable to a wider audience. You know the book, or the concept, crossing the chasm? I think for early adopters, Lambda is the right name. It's not the right name for early majority. And I think there is one for Lambda. It's just so fundamental, right?

Rob Collie: This is a problem. The lack of formula reuse is a problem that basically plagues every single formula writer ever. I can imagine living my whole life without, in a particular niche, without ever needing to manipulate arrays as if they were a single value. Now, there's plenty of cases where I can imagine that I would, right?

Rob Collie: It's not that I'm saying that it's a bad feature. I think it's an awesome feature, but man, Lambda, I just see Lambda as just like incredibly mainstream. I could be completely wrong about that, of course. But I think I'd have a better chance teaching Lambda to a broader audience than I would arrays for instance, even the new dynamic ones. So anyway, end of rant. It's not even really a rant, it's—

Brian Jones: That's no rant.

Rob Collie: As rants go, it's pretty polite.

Thomas LaRock: That wasn't a rant.

Brian Jones: That's just an opinion. To me, I'm like, hey— tyranny of the OR.

Rob Collie: I know. Brian's like, I hear your business book concept and I have raised you my business book concept.

Brian Jones: All the time, man!

Rob Collie: You've read the same best 100 business book's Cliff Notes that I have.

Brian Jones: I haven't thought about this from a crossing the chasm, early adopter thing. I think of it more like in terms of user persona— it's more closer to being truly like what I would identify as a developer. And a lot of the stuff that we've done here so far is at that layer. The place that Lambda's exposed as like a name is in the formula itself you build. But clearly as we go and build a UI on top of it to make it easier to go and create those, we wouldn't go and say like have a big button that says, generate my own Lambda. That's not really the way that it'd be implemented.

Brian Jones: But then the data types in the array stuff, I think you'll be surprised over time. I get that you're not— like it wouldn't be a thing that you'd say, "Hey, I'm going to go and teach somebody arrays," but I think that what you'll find is it'll just almost naturally start to become how you work.

Brian Jones: It's just like data types is almost one of those things where you say, I don't have a class right now where I say I'm going to teach you about cells, let's talk about cells and what goes into a cell. And so this is really just saying we're just upleveling what can go into a cell. And so eventually nobody really even thinks about it. It just will seem natural. It's like, well, of course you could put that stuff in there.

Gargantuan Financial Analysis in Excel

Rob Collie: Well, so it's not about— It might've come across this way. It's not about a lack of belief in these other things. It's more like, I just think the bar size, the pie is bigger. And the example that I can't get out of my head, it's something that I wasn't exposed to until I come outside of Microsoft, all these income statement forecast spreadsheets, giant spreadsheets that cover acres of screen real estate of this like projecting forward month, month, month, month, month with different variables being fed in and different growth rates and different attrition rates and all of that, and all of that intermediate calculation, all of that real estate is just to produce a very small handful of outputs. So it's like I start with a small number of assumptions and some rules, and then I expand that, blow it out across this whole spreadsheet that thankfully can be more than 256 columns wide now, right? And then all I do is harvest those three values at the end. That's all I really care about. Or what's my break even month, how long before I break even? There's just a handful of outputs.

Rob Collie: And I end up with this gargantuan device and I can't iterate on it. It's like I changed the inputs and then I want to save off the outputs. I just want to turn that whole thing into— and then just turn it into a table on another sheet, hide that machinery behind the scenes. And I just think that's just so incredibly mainstream, like everyone has this problem. And so that's why I just can't get this one off my brain. So it's really an obsession with the Lambda feature as opposed to a lack of interest in the others. Because I've already used the others, too.

Brian Jones: Yeah. The idea around reusable logic and even going further, right, like Lambdas that I could go and create and then call from outside of Excel, a Lambda as a web service you could publish, right? There's a long way that we'll be able to go with this. And so I think that it's a huge, huge opportunity for us. Right now what we've released is just kind of the beginning underpinnings of a lot of innovation that will be coming.

Innovation: Delivering Value

Rob Collie: I have to say as a long-time observer of all this, former insider on exactly that product, I think it's really truly exciting. It's real innovation.

It's not keeping up with the Joneses. It's not like making sure you have parity with your competitors. And it's also not the old disease of, wouldn't it be cool if? It's not the old stuff like the things that we were doing with XML, right? It's grounded in true, actual value for the customer base, for the people of the world, and is innovative. That's hard to do. It's doable, it turns out, but there's a lot of discipline to it and I salute the direction that y'all been headed in. It really is exciting.

Beyond Fragility

Brian Jones: Yeah. Thanks. The thing that's been fun about that project is the combination of, you know them, there's a bunch of people on our team who their background is like financial modeling. Right? And so in addition to us researching the customer, we hired the customer. Right? Which, is great. And then we've combined that with the folks in, like Simon Peyton Jones in MSR and Andy Gordon, this combination of computer science experts. Right? And then people who deeply understand the customer problem. And like you said, it's really easy to go and see, what are those challenges? Well, one is when I'm looking at my model, can I trust the data, where'd the data come from? Right? And so this partnership with Power BI around data types helps a ton with that. Can I go and write logic that's reusable, so it's not fragile? Lambda's going to be huge for that.

Brian Jones: And then even our collab stuff, we announced that in our web app now, and it'll come to the desktop, I can now go and write— if I see a value and I don't know where the hell that came from or why did it change, I can right click and say, show changes, and it will actually show you the edit history of that.

These are a few excerpts from the conversation between Brian Jones, Rob Collie, and Thomas LaRock. Listen to the full podcast for context. Transcript has not been reviewed by participants, and emphasis has been added. P3 Adaptive is a Gold Microsoft Partner. Contact us to turn your data into action in weeks not months.

We’re built differently.

A revolution in BI tools has unlocked a new way to work: Fast. Efficient. Direct. But to unlock the full potential, you must move beyond yesterday’s methods.

P3 Adaptive saw an opportunity to create a new kind of firm: One built from scratch to embrace the new tools and deliver maximum ROI to our clients.

Contact us today to start solving your data problems.