Excel on the Left. Other Data Tools on the Right.
Today I’m going to “get my nerd on” in a big way. Buckle up.
The genesis of this post is an email I’ve been meaning to send to my contacts at Microsoft – one I’ve been thinking about writing for at least a year. But I also figure it’s the sort of thing you folks might find interesting, and I really don’t have time these days to write the same “opus” twice, so here goes – two birds with one stone. And it’s a friendly stone.
Has there ever been a tool as flexible as Excel?
Let’s take a moment and just marvel at Excel’s “range.” (VBA macro programmers – yes the joke is intentional).
You can do damn near anything in Excel. Calendar chart visualizations. Music videos. Beautiful art. More music videos. Respiration wavelengths. Chess games. Word clouds.
But those are just the outliers really – the novelties. The truly valuable examples are much less dramatic and happen hundreds of thousands of times every day. I’ll give some examples in the next section.
Feature A Was NEVER “Intended” to Be Used With Feature B!
“Hey You Got Your Slicer in My Conditional Formatting!”
(And then the whole jar fell into a bucket of DAX)
We don’t have to look far for examples: just this past Thursday I showed how Excel’s “Show Values As” feature can be used to quickly generate a Pareto-style analysis on top of a PowerPivot model.
Quiz: did anyone at Microsoft ever think about this? Probably not! It’s just… something that’s possible, waiting for us to discover it.
How about combining pivot sorting, slicers, DAX, and a hidden column to enable sorting by slicer? I’m pretty comfortable guessing that I was the first person on the planet to try that, and did so long after leaving Microsoft.
Not enough? Staying right here in just this tiny little corner of the Internet, how about conditional formatting controlled via slicer? Or using wingdings fonts to turn slicer tiles into icons? Using the hyperlink function to create “drill down into new report” functionality in reports?
None of these things was ever intended or planned by Microsoft.
But that is not a “slight” to Microsoft – in fact it’s a testament to something truly beautiful.
The Network Effect: How 20 Becomes 11 billion.
Let’s be really conservative and say that Excel has 500 functions and features (it’s actually many more than that).
And then let’s observe that we commonly see 4 such features “strung together” to create a Nifty Application. Roughly speaking then, there are 62 billion potential Nifty Applications out there in the universe.
So, no one is going be running out of clever new tricks to discover any time soon 
But let’s say Microsoft gets lazy and only adds 20 new features to the next version of Excel. How many combinations do we have now? 73 billion Nifty Applications – an increase of 11 billion.
Stated differently, a 4% increase in functionality led to an 18% increase in utility. Spend $4, get $18.
In Excel, there really IS a free lunch. (Well, “buy one get three and a half free” anyway).
Other Tools (BI Tools in Particular) Have No Network Effect!
I’ve been looking at a lot of BI tools lately. And while they often impress me in certain ways, they all lack a network effect. They all pretty much do exactly what they were designed to do and nothing else.
Why is that? Well for one thing they lack a grid.
In Excel, all of the Happy Little Features Communicate and Cooperate via the Grid
(More Accurately, via the Calculation Engine Underneath the Grid)
Islands in the Stream!
Without the grid, and the calculation engine underneath, all of Excel’s features would just be islands.
Oh I’m sure they’d be connected in small groups. Slicers would of course be connected to pivots for instance, and charts would be connected to their data.
But those would be isolated groups with no broader network effect! The grid and calc engine in Excel form the hub through which all of its features cooperate. Data in pivots can be referenced via formulas, chart titles can reference into the grid, conditional formatting thresholds can reference into the grid, etc. Excel’s features respect the calc. They respect the grid.
BI tools lack flexibility, and a network effect, because they lack that underlying hub – a highway that all features connect to in a read/write manner.
And this is one big reason why “Export to Excel” is the third most common button in BI tools – after OK and Cancel (one of my favorite all-time posts BTW).
DAX Engine: MASSIVE Expansion of the Network Effect!
PowerPivot’s DAX Engine is “Plugged In” To the Hub
(Benefit Not Drawn to Scale)
I think that picture pretty much sums it up. In some sense it’s fair to call the DAX engine “Excel’s second hub!” I like the sound of that.
Sure, the DAX engine doesn’t “listen” to the Excel grid, but it does listen to other features like Slicers, and the grid listens to it (via pivots and cube formulas, and in 2013, also via Tables). And I guess in some sense, the DAX engine does respond to the calc engine when you’re using cube formulas.
But for my money, the DAX engine is very much part of the network. It falls in the Major Clapping of Hands Department.
What Keeps Me Up At Night
Keeping with the trend of illustrations…
Many of the Recent Additions to the Excel Family Do NOT Yet Participate in the Network Effect
(In fairness, Data Explorer is pretty close to earning its “Network Badge.”)
Let me tell you a secret. Adding new features to Excel is HARD work. Shockingly difficult work. I used to joke that changing the font on the bold button would take six weeks. (I exaggerate – in reality it only would take four weeks).
You can’t just drop a new batch of program code into Excel and expect it to work. Adding features to Excel is a lot like implanting a new organ into a transplant patient – you must carefully open the patient up, make room for the new functionality, painstakingly stitch it into the circulatory system, carefully close the patient back up, and then monitor the patient for several months afterwards.
For instance, the Analysis Services team loaned some engineers to the Excel team in order to implement Slicers. (That’s right, we would NOT have slicers today if not for those brave souls who volunteered to uproot and move across campus). Those engineers were supposed to be on loan for about 9 months. They were gone for two years, and more engineers got sent over than originally planned.
I think, in hindsight, this was judged to have been a poor investment of resources, because everything done since then has a distinctly “bolt on” feel.
Save 75%, Lose 75%. Then Lose Some More.
Listen, I can absolutely sympathize with that stance. Who wants to spend 4 engineer-months on a feature when they can get it done in 1? It’s crazy – Excel features come at a staggering engineering cost.
But the losses pile up too when you bolt things on rather than deeply integrating them. You don’t gain that network effect advantage – an advantage that multiplies the value of your investment.
So you save 75% of the implementation cost. But you lose 75% of the benefit you would have gained from integration.
But only longtime Excel-heads realize that there WAS a 75% functionality loss! From the outside, you built a feature and it does what you expected! You perceive no loss! I worked on Excel for years without understanding that there was a massive loss. We should not expect the SQL teams at MS to understand it either. But we sure can hope that they try.
Furthermore, the bolt-on approach yields a feature that feels like an alien to Excel folks. It’s hard to learn to use. It’s hard to “dip your toe in” by incorporating it into a broader, more traditional spreadsheet. And then it’s supremely frustrating for the user when they discover that it’s not compatible with other Excel features like they expect.
So they walk away. I hate to say it, but in its current “island” form, Power View does not get me terribly excited. On the other hand, if suddenly I could have Card Views or animated charts as part of a normal Excel spreadsheet, with some degree of slicer and calc chain integration… I’d go from Mildly Interested to Full High Priest Mode.
“Composeable” is the 1-word summary
Another way to have worded this post would have been to say that Excel is strong because its component features are “composeable” with each other. (Yes I am making up a word).
Composeable – please think about it, dear friends in Redmond. It’s crucially important, and it’s a big difference between Excel and traditional BI tools. It’s taken me most of my career to figure that out.
