Post by Rob Collie

clip_image002

NASA JPL is Using Modern Excel to Perform Financial Analysis On Their
Space Projects, Because Modern Excel is the Best.
(That’s me with Opportunity’s “sister” during a training engagement this Spring)

Too Hot for the Official Excel Blog!

image

This Post Was Originally Written for the Official MS Excel Blog But Never Went Live
(Their PR Censors Deleted 70% of it and wanted big changes to the rest, so I’m posting here instead)

A couple months ago I was asked if I’d like to do a post for the official Excel.  Heck YES, I said.  (Duh).

And then I realized, I couldn’t let such an opportunity go to waste.  I had to Go Big with this one.  Roll the dice.  Use it as a chance to change the entire conversation around Excel – in ways that Microsoft itself SHOULD be doing, but hasn’t.

So many products now use “we’re better than Excel” as their entire marketing campaign.  I’m swarmed by these ads on Facebook.  And none of these other products are better.  In fact, none of them are even close to being AS good as Excel, much less better.

 

Microsoft, in my opinion, has allowed the conversation around Excel to be dictated by the opposition.  The facts on the ground out here in the real world paint a VERY clear and dramatically-positive picture of Excel.  There should be no “room” for products to say things like “let’s face it, Excel is not a reporting tool” in their ad campaigns.  (Eff you, Domo, for that preposterously stupid advertisement.  You’re a vaporware piece of crap, and Excel is actually the world’s BEST reporting tool, so you’ve got a lot of work to do before you run your mouth like that.  Now go to your room.)

After years of encouraging MS to go on the offensive with this stuff (to no avail), this guest blog on the MS site seemed like a golden opportunity to show that a stronger message works.  A little bit sneaky perhaps, but not a malicious kind of sneaky.  The friendliest kind of sneaky there is.  I wrote it, submitted it, and crossed my fingers.

And their censors basically deleted everything from my draft.

image

The Room at Microsoft Where Blog Posts Go to Die

So I escalated, got some friends involved. They were a big help.  They brokered a peaceful middle ground.  I wrote a revised draft.  It made the rounds.  Things were looking good.

And then at the last minute, I was again told that it still needed a full rewrite.  Too much bravado.  A PR liability.  You know, it’s risky to say awesome things about your own product that the world loves.  Sigh.  But I get it.  PR has everywhere become a defensive game, not just at MS.  PR people lose jobs over negative incidents.  But being mediocre, staying in your lane, and not moving the needle?  That does not get people fired.  We can’t blame PR people for playing the game this way.  Don’t hate the player, hate the game.

Well I wasn’t going to soften the message any further.  That was the whole point – to change the tone and go strong.  So here it is below, uncut.

The Original Uncut Version Start Here…

When I was asked to write a guest post for the official Excel blog, I knew immediately that my topic was going to be the title above. Yes, Excel is simply the best data tool in the world. Full stop, no asterisks, no caveats. Best for individuals, best for teams, best for Fortune 500 enterprise-wide scenarios, and best at any price.

I arrived at this belief the hard way – by NOT believing it – for the bulk of my career. Yes, I worked at Microsoft for 13 years, and even worked on Excel for a big chunk of that. But I never believed anything so bold back then.

So why do I now believe that Excel conquers all? Two things have changed – my perspective and Excel itself.

An Education Provided by the Real World

I left Microsoft in 2010, and spent the next five years helping hundreds of companies with their data. I was no longer “tied” to Microsoft, and was open to using non-Microsoft tools. I was looking forward to it, actually.

But for the first time, I started to witness how all of those other, more expensive, supposedly more “serious” tools are actually used.

And the shocking truth – shocking for me, anyway – is that they’re not used. Sure, companies buy them, deploy them, and instruct people to use them. But then they get used for only a small fraction of the tasks that justified their price tag.

What they do get used for, over and over again, is as an export source for Excel! I like to say that “Export to Excel” is the third most common button in data applications – behind OK and Cancel. I’ve learned that the ubiquity of the Export button is not a detail. It’s everything. It’s an admission, by those tools, that they cannot meet your needs. Let that sink in for a moment.

“Just give me all the data and I will put it into Excel” is one of the most commonly uttered phrases in the business world. I used to think that was stubbornness on the part of the Excel users, but not anymore. Those other data tools, despite their claims of flexibility, are actually quite rigid. When the realities of your business inevitably don’t quite match the “clean room” expectations of the tool, you require lengthy/expensive intervention from administrators or consultants.

That’s when you reach for the world’s most popular programming language by far: Excel. Formulas, pivots, charts, and the grid itself are, together, a cleverly disguised programming language that is learnable by “normal” people.

Excel should be taken just as seriously as “real” programming languages like C and Java. And being a programming language, Excel can handle everything that the pesky, noisy, uncooperative real world throws at you.

No data tool measures up to that today. And if a true competitor ever does emerge, it will not be a tool, but another cleverly disguised programming language, one that is readily learnable by hundreds of millions of people. I won’t be holding my breath.

I used to be squeamishly apologetic about Excel when I worked at Microsoft. “Sure, Excel isn’t quite as good as those other data tools, but it’s good enough, most of the time!”

Ugh. I’m done saying things like that. Excel is not merely “good enough.” It’s flat out better, period. For 20 years, IT and data tools vendors have been trying to get rid of Excel. There’s a reason it’s still here, and more popular than ever: it’s just the best data tool in the world. No more apologies.

“Modern Excel” Cranks Up the Power

The other big change is that Excel has quietly undergone a major revolution in recent years. It used to be “just” formulas, charts, pivots, and the grid, and that was already good enough to be the world’s best data tool.

While you were sleeping, though, Microsoft has dramatically expanded the Excel “ecosystem” of technologies. There used to be one engineering team working on Excel, and now I estimate there might be as many as ten. My personal three favorites:

1. Excel Services: a server version of Excel that provides security, cross-platform reach, One Version of the Truth, and hands-free/scheduled data refresh, which were longtime “holes” in the Excel story.

2. Power Pivot: turbocharges analysis and reporting by adding an industrial-strength (but still learnable!) data modeling and formula engine. Introduces “portable formulas,” removes the need for VLOOKUP, and extends data capacity as high as hundreds of millions of rows. Blends the flexibility, agility, and learnability of traditional Excel with the robustness and clarity of Business Intelligence platforms.

3. Power Query: finds, imports, filters, and “re-shapes” data. No matter what tool you use, one of the precursors to any analysis work is to import data and perform “surgery” on it, to prep it for analysis. Removing “noise” rows like blanks and subtotals. Transposing columns into rows. Removing duplicates. The list goes on, and until Power Query came along, all of that was manual, repetitive work. With Power Query, it all becomes very simple, and even better – the next time you get new data? Just click refresh, and all of that data shaping happens automatically.

Given the dramatic expansion of Excel’s powers, I’ve taken to referring to it as “Modern Excel.” Just calling it “Excel” doesn’t do it justice, because I’ve found that most people aren’t aware of the new features mentioned above.

And even though “traditional” Excel was already the best data tool on the planet, I still wouldn’t have been comfortable advocating it for, say, enterprise-level BI work. Modern Excel, however, is more than up to the task, and we are using it in that capacity every day, often helping companies millions of dollars.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 44 Comments

  1. Superb article. Cannot understand Microsoft’s attitude TBH. Is it to say nothing, let users with competitor tools fall flat on their face then say ‘I told you so’? Cant think of any other reason. My only wish is that MS get on top of the instability problems with the PowerBI addins. Saying nothing is one thing but deploying functionalities that are flaky is a dangerous arrogance

  2. There is a relatively new kid on the block called Power BI Designer

    Ariel Netz says “we want the Power BI Designer to be the tool that unifies all Power BI client experiences” -here

    http://blogs.msdn.com/b/powerbi/archive/2015/06/11/we-re-on-for-a-great-designer-summer.aspx#comments

    I asked him in the comments section

    @Ariel

    Have you heard of Excel. It is already the tool that Unifies “all Power BI” Clients and more

    So why re-invent the wheel”

    When I see MS bring features like PP and PQ It makes you believe that MS finally understood that Excel should be at the center of their BI strategy….and then you see a half baked product like Power BI Designer and you are disappointed to see the time and effort spent on re-inventing the wheel

    What is also sad is to see PQ begin first updated in the Power BI and then in Excel

    1. @Sam: the official story is that Power BI Designer is for clients who do not have the right version of Exce and cannot upgrade for some reason. Excel itself should still be the main starting point, if possible.

      1. “do not have the right version” – by this are you referring to the
        2% of the Excel’s population who are on 2003 or
        4% who are on 2007 or
        6% who got confused by the 9 too many plans available for Excel 2013 and brought Professional instead of Professional plus and paid the price for not reading the small print.

        In either case MS’s generosity of creating a tool soley to take care of the above population is commendable

      2. @Koen, @Sam: “Clients who do not have the right version of Excel”. In my opinion these are all non-Windows-Clients. Microsoft wants to address all clients on all operating systems to use their services (eg. Power BI in the Cloud). Therefore they need a client-solution without Excel.

        1. @Frank
          MS has a fantastic product that they don’t advertise enough – its called Azure Remote App.

          You can read about it here

          http://www.powerpivotblog.nl/excel-2013-power-pivot-and-power-view-on-any-device-with-azure-remoteapp/

          This basically gives you Excel 2013 Professional PLUS (PP + PQ + PV + PM) on any device – Windows/iOs/Android

          It has the enormous potential of converting toys like (iPhones/iPads etc) in to Productive Business tools

          There is also a client version that you can install for free but with restrictions (you cant upload your files) and fully functional 30 day trial version

          Give it a spin and see if you still feel there is a need for “a Client solution-Without Excel”

          1. To get Azure Remote App for our 10,000 very light users (need access to dashboards once or twice a month) would cost £60,000 a month. Hardly a cheap solution compared to free or even running a Tableaux server (which would also get a better quality of dashboard in my opinion).

  3. What gets me is that the game the PR folks are playing is not “with” the outside, competitive world, but rather with the MS inside, political world–and who wins with that? Not MS, not Excel and ultimately not the get-ahead-by-playing-inside-politics PR folks!

  4. Oh no! Say it ain’t so!

    Don’t have an actual opinion and or tell us the TRUTH! You might offend the perpetually offended.

    Great stuff as always Rob. Rock out!

    ¡Viva la Revolución!

  5. This is great! And thanks for posting the full article; it needs to be seen. The major revolution of Modern Excel shouldn’t be so quiet that people sleep through it. Time to wake up!

  6. To me, Microsoft seems to be a bit schizophrenic in their strategy (and not just with Excel). On the one hand, they’re doing the absolutely amazing things with Excel that you allude to in this blog post, but on the other hand, the core Excel product has been left to languish for what feels like a decade. Outside of PowerPivot, etc, when was the last time the core product was improved or even polished in a substantial way? Why do we still have to use VBA as a language, why is the object model still so half-assed, why do we still have to write obscure permutations of various nested Index+Match functions, etc etc etc. One could go on for pages and pages listing obviously needed improvements.

    It feels like either Microsoft simply doesn’t care, or they’re saving any improvements until some time in the future when a real competitor appears on the scene. “Resting on their laurels” feels like an understatement.

    Rob, you have contacts within Microsoft, has this topic ever come up in any conversations? Could the internal consensus actually be that everything is more or less “ok” in this respect?

  7. Apologies: Excel is still not as good as it should be.
    Sure, it’s a good tool. But it’s still not as good as it damn well should be by now. So I’m a staunch supporter of Excel, as well as a staunch critic.

    Here’s an example: Structured References for PivotTables. Where are they, Microsoft? We’ve got ’em for the Tables that you introduced in 2007, but not for the PivotTables you introduced over a decade ago. Why not? Excel should have them, so that we can use the world’s most popular programming language by far not just to handle everything that the pesky, noisy, uncooperative real world throws at you but also to handle what the the average Excel spreadsheet throws at you…a PivotTable. Why, with Structured References for PivotTables, I could do a scatterplot of two values fields in a PivotTable that is resistant to PivotTable structure changes. Then I could do some great exploratory BI. PIvotTables are the best thing about old Excel. New PivotTables are the best thing about New Excel. How do I plug these best things seamlessly and robustly into my spreadsheet? Oh, I can’t.

    If Excel 2016 comes out and still doesn’t have this basic connector, then it’s a classic example of what drives people to keep searching for love elsewhere – even if they are looking for love in all the wrong places. Because Excel still doesn’t provide a seamless integration between old Excel and old Excel. Let alone old Excel and new Excel.

    1. I agree with Jeffrey….there are hundreds if not thousands of plain lazy functionality gaps like this in Excel – I’d love to move to something else purely as a matter of principle, but despite its numerous shortcomings, nothing else comes close.

    2. Here’s another example: Filtering a humble ListObject (i.e. Excel Table) based on an external list. You can’t do it natively…you either have to use VBA, or you have to set up a helper column with a fairly tricky formula compared to what most users are capable of writing. So ‘automation’ of a no-brainer task is out of the grasp of the majority of users. MS makes ’em pay a click tax. So if the average user needs to filter a Table or a PivotTable based on a list of 30 things, they’re going to have to click 30 times. (And don’t tell me to use the Advanced Filter, because that does NOT change the filter settings in the filter …it simply hides rows. In fact, it turns off filters altogether, plus it’s advanced, plus it requires you to pay still more click-tax to use it.)

      This s#!t should be native. This application should be better thought through. These users should NOT have to use clicking, vba, or helper columns to achieve such a basic result.

      This new BI stuff is all very cool. And man, it lets you filter stuff any way you like. But better filtering where 99.999999999999% of your users actually do it would much more productive enhancement to the Excel User Base as a whole. I think MS is WAY out of touch with that user base. They need to sit down with them, and watch them manually clicking things in order to do stuff that should take one click, and not a dozen. Hell, there’s simple stuff that you can’t even do manually. Want to filter a column based on more than two ‘does not equal’ conditions? You can’t. Are you kidding me? Why the hell not? What the hell is wrong at MS that they don’t know that this would reduce the manual handling of quite a few users. Want to instantly select the inverse of a filtered Table or PivotTable? You can’t. Are you kidding me? What the hell is wrong at MS that they don’t know…

      Here’s an except from my currently-on-hold book that sums this up:

      While the tools that Excel ships with right out of the box are cool, Excel often makes us jump through an awful lot of hoops—and click through an awful lot of dialog boxes—in order to actually use them. And we routinely do lots of things that Excel simply doesn’t provide handy tools for. The end result is that for every millisecond that Excel actually does some real work, you’ve probably spent hours manually prepping Excel to do it.

      Whenever you have to do lots of manual steps in order to leverage Excel’s cool built-in functionality, Excel is programming you. It’s like some kind of epic experiment in behavioral psychology, and we Excel users are the mice.

      Sure, it’s not sexy to improve existing functionality like this. But man, how hard could it possibly be? This is basic stuff that is missing. Basic bad design, bad usability.

      Is Excel the world’s best data tool? Yes. Is Excel a poster-boy for usability? Hell no. So we’ve got a very widely entrenched tool that users pay a massive click-tax to use. Bloody stupid, if you ask me. Completely avoidable, and bloody stupid. No wonder users are still looking for love in all the wrong places.

      1. 100% agree, and I’d love to know WHY Microsoft has left Excel in this sorry state for years, there’s just so many things that are poorly done. Take loading external data into Excel, there’s like 3 different ways to do it, none of them are intuitive, and they all have various shortcomings. How do you screw up something so fundamental?

        Tables and structured references – finally something semi-innovative or at least “fresh”, and actually fairly well done, but once again, certain things are either impossible or brutally difficult.

        Don’t get me started on ListObjects.

  8. In “The Pirates of Silicon Valley”, Bill Gates told Steve Jobs, “You still don’t get it, do you.” So, Microsoft, concerning Excel, you still don’t get it, do you.

  9. Rob,

    I’m glad that you called Domo out for being “a vaporware piece of crap.” They are definitely being mysterious and one has to wonder if there is anything of substance under all the marketing.

    It will be interesting to see if Josh James (Domo CEO) will be successful in applying his Omniture success formula to the world of enterprise BI. He does have a proven track record and I have a hunch that he’s going to do well with Domo because he’ll pitch to the C level executives who don’t understand data and have no problem writing a BIG check in order to outsource data analysis. It will be up to us Excel people to stop the outsourcing and keep data analysis in-house at our companies.

    I talked to a Domo sales rep and you don’t even get in the door for under $40,000. But, if they can make the case that they can fully replace a full-time data analyst, then they might be successful.

    If Domo is successful, I’d be willing to bet that much of it will be dependent on them adding an “Export to Excel” button in their software.

    1. Instead of “Power Excel”, I think we should call it something like, hmmm, let’s see, maybe “Power BI”? How does that sound?!?

      I joke of course, but I think that “Power BI” has the best chance of unifying all the complex aspects of the Microsoft BI strategy under one name. “Power BI” also allows us to fight by using our opponent’s weight against them so we can copy and paste the Domo-style marketing messages, but replace “Domo” with “Power BI”. Are you fed up with Excel, Spreadsheets, heck, numbers in general? Well, we have a solution for you and it’s called “Power BI”. Yes, no more numbers, no more thinking, just pleasant looking pictures that magically guide your company to amazing results while you play golf and get manicures.

      “Power BI” is great because it doesn’t have the word Excel in it. But, of course, once someone is hooked on it and they want to do something meaningful, we can do the equivalent of the “Export to Excel” button and show them that “Power BI” is also available in Excel so we can do pivot tables, VBA, etc.

      What do you think?

      1. POWER BI is MS BI Stack that can optionally use Excel.
        EXCEL is a Swiss knife that can optionally use Power BI.
        POWER EXCEL is a term that (for me anyway) represents Excel WITH Power BI, and the best BI tool known to the world today.

        1. I hear ya. I’m just thinking that “Power BI” is a better term to use when talking to C level people because Excel has become a bit of a bad word in their circle. Of course, we know that Excel is the Swiss army knife that ultimately makes most of the cool stuff possible in “Power BI”, but we don’t have to tell the executives that. They’ll be happy to have something called “Power BI” that doesn’t have the word Excel in it and we’ll be happy to continue using Excel to make stuff happen.

  10. I agree with Rob and appreciate this blogpost. But I want to extend the challenge to fellow bloggers: we can help challenge the people with their nonsensical claims that they finally posses the Excel-killer.

    When these claims come out, it doesn’t take long to find some problem with the claim or the product. “R” is for people who want to write frikken code. Tableau is $999 per user (unless you want your data on a public server). I was on a conference call about an Excel-killer and it was a CRM. That’s all. It wouldn’t also be a web-scraper, or any of the million of other uses that people have for Excel.

    As developers, we can help get the word out and get those muckrakers and rabble-rousers to shut up.

  11. I think the paragraphs around other tools, and how they are often used to simply export to Excel misses the point. In my experience this is not reflection of a tool’s functionality, but rather a reflection of a lack of training. People use are often exposed to Excel from an early age and like familiarity. A good, coherent education/training strategy is essential to get people to embrace alternative tools.

    Excel is a great tool, I would never dispute that, and the addition of Power Query, Power View and Power Pivot have made it even better. Even with the additions mentioned above Excel has limitations that can’t be ignored.

    Want to analyse a few billion rows of data? You need to use R (integrates quite nicely with Excel), Python or an equivalent. (I’ve tried to use Power Pivot on 500m+ rows of data and seen it fall over).

    Want a dashboard for management? Excel will do an OK job, but they will much prefer the flashy Tableau/Qlikview dashboard.

    Building an algorithm? It can be done in Excel, but the results are often inaccurate and have poor performance.

    VBA as a language is also pretty poor. Don’t think I need to elaborate on that one.

    There are more but the point I am trying to make is that in modern data there is no “best tool” because the way we use and think about data is constantly evolving and becoming more diverse. It might be the simplest and most versatile, but I don’t believe there can be a best. Only a best solution for a specific scenario or challenge.

    It might sound like I don’t like the article but I actually do. I definitely agree that Excel can save companies a lot of money. Most people will be given a new, flashy tool and never use it because the training isn’t there. The majority of the users won’t use it to do anything that can’t be done in Excel anyway.

    1. Mike C. technically you are likely correct. Export to Excel is often because there isn’t training for doing the needed work in the tool that has the source data.

      But how pragmatic is that? When I worked in an enterprise, there was the Novell database, SalesForce and SQL. Then, Oracle was tried. It wasn’t comfort that had me export stuff into Excel from all those places. The issue was typically:

      I need to get some stuff happening in the next 48 hours.

      Was I gonna sufficiently learn SQL in time to get my stuff done by my deadline? No way. Would the central focus of my day-to-day job be enhanced by me learning the back-end of SalesForce? Nope. I wouldn’t be back there consistently enough for any training to stick.
      Convince my department head to spend $999 on me having Tableau. Not gonna happen.

      But I was in Excel every doggone day. And … when a new version of Excel came out and the company was showing no signs of upgrading, I had $100 to upgrade my own laptop and use the new benefits.

      Excel has limitations, and let’s agree that there are better tools. What can’t be ignored about those other tools:
      – Tableau is expensive.
      – R is a whole different thing. That’s writing code and a whole different mindset that some people just don’t get and don’t want to get; but they can write Excel formulas. I doubt that there are many project managers, admin assistants, and grant writers who are going to take on R as a casual enhancement to their regular jobs.
      – SalesForce.com. The back-end of that thing is nuts. Companies hire someone to be their SalesForce.com developer. SalesForce is also expensive. My company had x number of licenses and I was able to get one because someone shifted roles and didn’t need his license any more.
      – Then there are all the smaller entities that might do just ONE specific thing better than Excel. They don’t have the community that Excel has for support. They’ve got some proprietary thing and support happens via email that you might have to wait days for a response.
      – Learning curves. A client of mine had the money for Tableau and the willingness. But in the 10-person company there were 2 potential users of Tableau and those 2 people felt like they were already overwhelmed with immediate fires to take on learning something new. They gave up on Tableau and had me create an Excel solution.

      1. Hi Oz

        Valid point. I guess what perhaps I missed was I suppose it depends on the user group we are talking about? I would agree that a typical end user will stick to Excel. They often don’t need the advanced/specific functionality of alternative tools. However, as a BI specialist, Excel alone simply can’t do what I need it to. Thinking about it though I don’t think there is any one tool that does.

        1. Understood. One thing that’s clear is that we all respond from our own experience. And it’s profound that we have such wide experiences and a common element is Excel.

          My world is one where there is no IT or BI specialist. Even inside an enterprise, a single department isn’t the IT department’s concern. The department’s problems are too small. Yet, there are ongoing data-related ad hoc problems to manage, and these problems are tangential to the person’s “real job.”

    2. @Mike C:

      Re your comment VBA as a language is also pretty poor. Don’t think I need to elaborate on that one.

      I wish you would. I get by in it, but maybe I don’t know what I’m missing. I know serious ‘trained’ developers that have taken solutions that involved multiple languages and technologies, and replaced the whole shebang with VBA (with some SQL being executed by that VBA). One guy I know says that now that he’s got things for a massive business humming in VBA, he can make changes in mere minutes to reporting/dashboards etc – changes that used to take his predecessor a week or two to code up in the other languages required by those technologies.

      I absolutely agree with this:
      In my experience this is not reflection of a tool’s functionality, but rather a reflection of a lack of training. People use are often exposed to Excel from an early age and like familiarity. A good, coherent education/training strategy is essential to get people to embrace alternative tools.

      I’d add that a good, coherent education/training strategy is essential to get people to embrace alternative approaches in familiar Excel, too. It pains me to see solutions from clever people who learned Excel way back when, but who don’t follow blogs or buy books. They build solutions based on an incorrect appreciation of what the tool could do last century. They build complicated stuff in VBA but don’t leverage off the off-the-shelf tools that Excel offers, including PivotTables, Dictionaries, etc. Never mind PowerPivot etc.

      When PowerPivot came out, I suggested to management where I worked that this tool lifted the bar on what the average analyst could do with humble Excel, while at the same time significantly decreased the cost of doing it. I thought that we could quite possibly ditch a whole heap of our expensive SAS licenses because the type of number-crunching that your median SAS user was doing could easily be accommodated by PowerPivot. I recognized that not every SAS license could be ditched – a few users would rub up against Excel’s limitations – but a significant portion could be. I pointed out that then we wouldn’t need to find expensive people proficient in both Excel and SAS. And I pointed out that the corporate install gave access to PowerPivot for no extra charge. But a mere staffer can’t compete with a glossy brochure or an already-entrenched tool. I wasn’t allowed to install it, even though we’d effectively already paid for it under our enterprise agreement.

      In terms of Tableau, Robert Mundigl of the Clearly and Simply blog shows how he can replicate most Tableau actions in Excel. But he’s a master craftsman. Probably cheaper for organizations to simply go buy Tableau than train a Robert Mundigl type.

      1. > I know serious ‘trained’ developers that have taken solutions that involved multiple languages and technologies, and replaced the whole shebang with VBA (with some SQL being executed by that VBA). One guy I know says that now that he’s got things for a massive business humming in VBA, he can make changes in mere minutes to reporting/dashboards etc – changes that used to take his predecessor a week or two to code up in the other languages required by those technologies.

        I expect this is more a factor of the ridiculous over-architected solutions that most developers write today. I personally use a Excel/VBA based solution I built to work with one of these over architected apps, the VBA approach is 1/10th the complexity, and has 10 times the functionality, but that’s in no way because of the superiority of VBA, that’s for sure.

        VBA itself is just an old, weak language and desperately needs an upgrade (as does the Excel object model). Sure, with some effort, you can in fact write Excel code in c# via VSTO or ExcelDNA, but there’s absolutely no good reason that Microsoft shouldn’t spend a little $ to integrate it into Excel natively.

        1. Amen to upgrade of Excel object model. Particularly where PivotTables are concerned. Want to know what PivotTable filter a user just clicked on? Good luck with that – Excel doesn’t tell you. Rather, it tells you the user clicked on every filter.

          But while that stuff helps developers make the product more flexible/easy to use, I say just make the product more easy to use.

          1. Hi Jeffrey

            I am being a little unkind to VBA. To it’s credit it is pretty easy to learn (though in my experience hard to master). It is essential to know at least the basics to tap into the power of Excel.

            I will hold my hands up and admit to not being a developer but in my experience I find VBA to be quite fiddly and messy as a language. Compared to other languages I have experience in (Python, Ruby, R) it is a pain in the arse to write quickly and produce a quick-running solution.

          2. @David H
            I am specifically wating for the day when EVALUATE will be come a native Excel funtion rather than one defined in Names 🙂

  12. I’m starting to wonder if the biggest problem with Excel is the price tag. Would Microsoft invest more in Excel if they could charge, say, a few hundred dollars more for it? Could the more expensive license only apply to a “Designer” version of Excel where normal users could still use any report generated by a “Designer” user (such is not the case currently with PP and Excel 2013 of course). I would be happy to pay a few hundred dollars more for a turbo-charged version of Excel because the higher price tag would make my Excel skills more valued in the market which would easily offset the few hundred dollars extra that I would pay for the “designer” license. And if Rob’s producer/consumer dynamic theory is correct, then 1/16 of Excel users are just like me. That’s a big pile of extra cash for Microsoft, some of which could get routed back into the Excel engineering team. Chandoo is currently running a post that is similar to this one where he’s asking users for extra features that they’d like to see in Excel: http://chandoo.org/wp/2015/06/09/what-functions-is-excel-missing/ I would pay a few hundred dollars to get all this stuff. Would you?

    1. One thing that I think gives Excel an advantage is its price tag. I wouldn’t want to see a $300 tag on Excel. That then gives people incentive to go looking for other things, and some people and businesses would go without.

      Excel is an everyman’s tool and should remain so.

      1. Thanks for the info Mary. I didn’t realize that Power BI was under C&E. I guess it’s further reason to be nervous that Power BI will completely fork from Excel moving forward.

  13. We need more civilians in this discussion. Experts have a whole different perspective than the people who we help. We can wish up training and increased professionalism for them, but I think their “professionalism” is in something else, like, “Travel Coordinator”.

    In my experience, it’s the rare person who gets to be a true Excel expert, far more people, like a Travel Coordinator, have Excel-user as one of many things that they do. I wonder what that person would have to say about why they opt to study travel blogs and not Excel blogs even though Excel is their main tool and major source of grief.

    As experts, it’s too easy to suggest what the common Excel user should be doing.

  14. I’m not sure if I qualify as a civilian, but creating sophisticated Excel solutions isn’t my day job even though I desperately want them for reporting. Nevertheless with PeopleSoft as our workhorse I have to agree that “Export to Excel” is an indispensable component. We have end user departments turning those reports into pivot tables, but only one or two among hundreds looking at PowerPivot and DAX.

    I’ve talked to our PeopleSoft team about letting the end users into the PSFT tables to execute queries, especially for the lookup/reference tables, but they’re (not unreasonably) concerned about unskilled people executing runaway queries and slowing the OLTP functions that PSFT supports. On the other hand, when we have 10 different reference tables in our Excel data model we don’t want to have to rebuild everything from scratch.It’s a different mindset – we’ll probably have to work with IT to let them build acceptable queries that our team can then connect to. But that will take some education on both sides.
    When we’re at that stage, something like Tableau is a luxury. You’ll have the same 5 or 6 Excel gurus who can build a meaningful pivot table now having to spend time massaging that data into something pretty. I love some of the Tableau features and charts, but is it worth the time when we could churn out more basic reports?

    That’s why I think Excel will remain our base of reporting for a long time. It gives a decent mix of quantity and quality. I suspect a lot of the pros on this page can do the whole soup-to-nuts of pulling data and making it pretty in 1/4 the time for most people (and hopefully be well paid for that efficiency), but we have to spend far too much time on data analysis just figuring out if we’re asking the right question of the data and whether the answer we got supports what we think it’s telling us. Not because our people are clueless – but market drivers change and operational processes adjust, and suddenly the data has to be pulled from new fields or with new qualifiers. I wonder what the time distribution would be for everyone reading (and writing) this blog in terms of how much effort goes into all those areas. Practice makes perfect of course, but again this isn’t everyone’s sole job. I know we have to spend much more time on retrieval and cleansing/analysis than I’d like, and that doesn’t leave much left over for making things pretty. Excel is good enough… and for all the things I’m asking it to do, that’s a glowing endorsement and not the faint praise it may seem!

  15. I am also very interested to see how the acquisition of Revolution Analytics by Microsoft plays out. I had heard it will not be surprising to see that technology baked into Excel down the road. That would be sweet and only be another reason to add to the list as to why Excel is the world’s best damn data tool!

  16. Well, this is very one-dimensional – surprising as we’re talking about cubes 🙂 I love the Power BI stack, too, and we’re dedicated to the full span of Power BI tools. From SSIS over SQL Server Tabular to Power Pivot and PowerUpdate/PowerPlanner.
    But while it takes me a 1/2 day to define a complex dashboard in Excel I can create this with Tableau Software in 1/2 an hour!!! in addition I get additional insights in the underlying data structure through real business intelligence.
    Really funny (just to add, although not our standard approach): Connected to a SSAS tabular model via Tableau Software our solutions run faster than via Excel frontent connected to SSAS tabular. Weird.
    So always a question of the perspective. Wouldn’t set the point to early, here.

Leave a Comment or Question