skip to Main Content

image  image

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 videosBeautiful artMore music videosRespiration wavelengthsChess gamesWord 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 Smile

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.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 17 Comments
  1. Rob – this is a great post. I love to show off the coolness of GeoFlow and Power View, but without the deeper integration into Excel, we lose so much. The big question; now that the feature is working and doing what it is supposed to do, will MSFT do the right thing and invest in the integration. Or are all of the stakeholders already on to some cool newer project?

  2. Daily user…Excel and just everyday work in a business. I just want to improve and have the ability to solve my own problems.
    I have your book Rob and have worked through it cover to cover with my works data. TY.
    Really enjoyed it…and learned lots.
    Bill I have many books (still have Mr Excel On Excel and the guerilla book). Thanks for being a great teacher.

    IMO some of the books of others and blogs so slightly miss the point of what us casual users need. Including MS help especially.
    Send a frown 🙁
    We need more worked examples with multiple strands and that is why I look here and on Mr Excel every day nearly.
    Cheers 
    Meanwhile we should accept/expect that some “colouring in” (ie Powerview etc)needs to be thrown in to help the decision makers, daily “receivers” etc.

    I’m sure your influence will keep moving things along making it easier for us daily users. I’m sure syncronisation and integration will come. Send a smile 🙂

    Everyone needs what they need…or think they need

    1. I think I’m just gonna call this Deep Comment of the Week. I know it’s Tuesday but I’m prepared to call it that right now 🙂

      Thanks Andrew – that’s a great perspective. Oh and thanks for the kind words on the book and blog 🙂

    1. David I love you man. My first thought is “what the hell would I do with Application.Run?” As a novice macro programmer I have never used it even though I get the gist of what it does.

      But my second thought is “BINGO! Even the more obscure stuff is part of the network effect!” Neglect the integration details and everyone loses in ways no one can anticipate.

  3. “Composable” is a term also used in functional programming, which you compose small functions together to enable larger functionality. See . BTW, the best database in the world, , is much like PowerPivot in that the query work is done locally in the client/peer.

    1. Talk about flexible. My latest deliverable was a Tsp model with built in geocoding. I was amazed at what I could produce in one afternoon.

      Excel allows business folk to solve problems in days compared with weeks/months by IT. I can’t imagine how the business world would function without Excel.

      The challenge with Excel is keeping IT in the loop. I like to say to IT I’ll run with this as a prototype whilst you guys institutionalise the model.. The issue is that IT often struggle to deliver the same thing. One such prototype lasted 3 years that I know of. I sometimes feel like a dev when working with Excel (not sure if this is good or bad!). I don’t care for PowerView as I’ve been producing dashboards for years. Although if it was better integrated without the SharePoint req then I’d be all over it.

      If there’s one area in business that I don’t enjoy working with Excel is Planning. I’d love to show you some of the planning models I’ve seen in multinational companies that make the budget seem like hell. I think it is planning that gives Excel the bad rep. This is unfair as I doubt ms sell excel as a planning system.

      In business I find people at all levels want to learn it to some degree. This is a testament to the quality product that permeates most areas of business. I doubt we’ll ever see anything like it again.

  4. Spot on post Rob!

    Lee.. Rob and I had a conversation about your comment. The Tournament rules vs Prison rules. How IT is forced to follow rules while we do not. My memory was a little fuzzy that night… 🙂

  5. Hi Rob. Great post, fantastic follow up comments too….. ^ what Andrew S said. I meet so many in the “casual Excel user” world, and the constant, resounding theme is that they need more voices like yours. Meanwhile I too am really enjoying your book.. thanks to Bill J for shipping it over with my garage sale box of Excel swag!

    Now. About that network effect. Amen, High Priest. I am often asked by clients, students, and BI / IT non believers: just WHAT do I think I’m going to DO when Excel “dies”? Hmmm??!!! Have I thought about THAT?! My reply usually goes back to the GRID. I will ask them if they realize the great historical significance of the “table”. I’ll pony on about Babylonian times, when it was chisels and clay tablets, then fast forward to No 2 pencils and green graph paper, then finally (if they haven’t walked away yawning at this point) we enter the here and now, where we use a mouse to “point and click” on a digitally rendered table like image… Blah dee blah…. Point being, the common-denominator-canvas in all this stuff is still a GRID, n’est ce pas?! Indeed, the canvas is nothing without the artist, and our canvas remains a grid. Speaking of artists, who knew Kenny Rogers was such a visionary on the subject of grids and calc engines. They rely on each other, uh-huh.

  6. It comes down to a basic absence of the classic free market scenario, with no competition in the spreadsheet market there is no necessity for MS to any more than they have to.

    If you compare the development of the visual aspects of Excel to those of Tableau in the time since Tableau began then difference is stark. I understand the two things are not directly comparable but there is a fundamentally different dynamic; Tableau’s new features lead directly to additional sales and at $1,900 a seat it’s a no brainer.

    Excel’s amazing new features on the other hand may eventually lead to a ‘disruption’ of the traditional BI scene and are good for creating a lot of noise about but are likely having marginal impact in terms of driving incremental $. PowerPivot itself is still used by a tiny fraction of excel users and I have yet to meet in person someone who is using it that wasn’t put onto it by me!

    1. Sadly this is true amount business and data it guys…at least the IT data guy had heard of it but no one else has….I only stumbled on it last year by doing a google on “advanced pivot table training”

      My staff now groan when someone asks me “what is power pivot” – I evangelize on it all the time.

  7. I dream of a day when “Use chart objects as slicers” is an option in normal Excel worksheets. The real estate savings alone makes me salivate… analytically speaking, of course.

  8. After PowerPivot I hoped the next big thing for Excel would be a sort of ‘visual pivot table’ so we could do Tableau like visuals within the grid. I guess Power View is an attempt to do something similar but not being able to use Power View elements in a regular worksheet is a real let down.

  9. I can’t believe how much I love what you wrote! Every time I read one of your posts, I love what I do even more!

Leave a Reply

Your email address will not be published. Required fields are marked *