Excel? Where we’re going, we don’t need Excel…

Posted on by Jeff Weir

Do yourself a favor: Watch this, from 28:00 minutes in, to the very end.

If my calculations are correct, when this baby hits 88 miles per hour… you’re gonna see some serious s#!t.

24 thoughts on “Excel? Where we’re going, we don’t need Excel…”

  3. OK, so I’ve watched (bits of) the video to (somewhere near) the end, and I’ve downloaded Power BI and Power BI publisher for Excel, and MS Access Database Engine (which apparently you need if you want to open Office 365 documents).

    So now I just need to work out how I can use Power BI as Power EI, and if it is worth the effort.

  5. All well and good but power BI isn’t available for those of us who use excel on a Mac platform

  7. Jeff – Engineering Intelligence :)

    Extracting the hidden meaning from numerical data is just as much a part of an engineer’s job, as it is for those working with business data. It has always seemed strange to me that spreadsheets are seen as primarily financial analysis tools when they are also widely used by almost every engineer and scientist.

  8. Hannah: While at least one person in an organization needs a PC environment to use the ‘Report Designer’ version of PowerBI (called PowerBI Desktop) to create interactive reports, other users of any ilk can consume those reports on any device (including Mac, Phone) that runs a modern web browser, once appropriate licensing is in place. Granted, if you’re a one person shop that just wants to do some BI on a mac and not share the results, you’d have to use Parallels to run Windows in order to use PowerBI Desktop. But if you’re an organization with scores of Mac users, those Mac users aren’t locked out of the BI consumption experience one bit. And that’s pretty cool, given the proliferation of Bring-Your-Own-Device policies.

    Who knows…MS might introduce a Mac version of PowerBI Desktop sometime soon.

  9. Doug: Ah. Gotcha. I don’t think you can go past Matt Allington’s blogs on the topic of when to spread your wings from Excel to PowerBI.
    This is a good start: http://exceleratorbi.com.au/use-power-bi-excel/

    I guess the bottom line is that if your users are ‘Smart’ and you think you can train them to do some of their own analysis of trends, PowerBI is a damn good vehicle for that. But if they are ‘Dumb’ in that you’re going to be the one doing their thinking for them and then just disseminating static reports to their inbox, there is little in PowerBI that you can’t replicate in Excel/PowerPivot (with the exception of GIS stuff).

    That said, the open source visualizations engine in PowerBI is awesome. Ever wanted to do a geographical flow map in Excel? Too bad. Got PowerBI? Great…here’s a dude who coded one up and released it to the community for free:
    https://weiweicui.github.io/PowerBI-Flowmap/

    Now that is cool. Not to mention that PowerBI now integrates with R. So there’s a whole world of stuff out there just waiting to be incorporated into what ships with PowerBI out-of-the-box. Which reminds me of the revolution that Excel went through when VBA was exposed to the user: suddenly savvy users could roll their own tools, rather than going cap in hand to MS. And less-savvy users could incorporate these new tools into their own projects, even though they have no idea how to write their own code.

  12. OK, I watched it over lunch yesterday. I don’t get it. Who is having problems that this solves? Here are some red flags:

    * He says “this is really cool” and has to constantly pimp the audience for applause
    * They say “without any programming”. Man, I hate that phrase. If you want me to call you a “data scientist” without being ironic, then quit looking for the non-programming panacea. Just learn to program. It’s not that freaking hard and it’s a tool of your trade.
    * The demo is about movies and there are pictures of movie stars. There’s just too much visual fluff for me to take this seriously.

    The AI part had some merits. I always say: Don’t have humans do things computers are good at and don’t have computers do things that humans are good at. AI is blurring that line to some degree. I fear that the AI may make people lazy about understanding the data, but that’s a personnel problem not an AI problem. If it can suggest some correlations that I didn’t see for myself, all the better. It’s still on me to determine if they mean anything and I wouldn’t have that opportunity if I didn’t know about them.

    Someday, someone will convince be to spend some significant time with these tools. Maybe it will change my thinking. But for now, I have a list of problems to solve as long as my arm. And none of them are comparing Jennifer Aniston to Angelina Jolie. (I know I’m unfairly using the subject matter of the demo to belittle it). None of the problems are that we have all this good data and no proper way to visualize it.

  13. Jeez Dick: did you have worms for lunch or something? :-)

    In response to your “Who is having problems that this solves”, I’d answer “Anyone who doesn’t have a Dick Kusleika-level VBA genius on staff who needs to be able to easily mash dirty data together and/or share the kinds of dashboards across mulitple geographical sites – something that would otherwise require a Dick Kusleika-level VBA genius”.

    Heck, even if they currently own a Dick Kusleika-level VBA genius, that genius could set up this tool so that their wider team of mere mortals can do lots of important analytical discovery without hounding said Dick Kusleika-level VBA genius type constantly. Not to mention help push some of the more savvy users into learning how to shape their own end-to-end solutions, so that list of problems to solve as long as his arm is replaced by a short-sleeved golf shirt.

    In short, I think this tool is like having a VBA/SQL/ETL/SSRS/PowerPoint gimp living on your desktop. I’m already a VBA/SQL/ETL gimp, so I can live without it to some degree by rolling some of my own tools. But I’m no Data Architect, so rolling out my tools to the masses – especially when connecting securely to databases – is just beyond my ability to competently pull off.

    What’s not shown in that video is the astounding ETL engine (PowerQuery) and data modelling (DAX) tools that are baked in to both PowerBI and Excel 2016/365 alike. You should see Ken Puls’ presentation on how these are game-changers for accountants who aren’t programmers. Heck, even for a somewhat savvy programmer like me, these ETL and data modelling tools have radically simplified my recent projects in Excel/PowerPivot. And they work exactly the same in PowerBI. What PowerBI has in addition to that is the ability to safely and securely share appropriate subsets of *very* confidential data with just those authorized to need it. Not to mention it gives me some graphical stuff that just ain’t in Excel for no good reason, such as mapping of GIS information.

    I think this tool gives much more people the ability to do the kinds of things that I personally find challenging if not daunting to build to anywhere near a robust level using my existing toolkit. And by ‘robust’ I mean “Likely to work without breaking after I’ve left for some time”.

    Re the visual fluff element, the takeout for me is that you can make this thing look every bit as bling as say the slide-deck that one of my colleague spends upwards of a week sweating over each month. He lives in cut-and-paste hell, just so he can send some static deck out to a bunch of senior managers. Screw that: I say bring on PowerBI, build that report with the types of drill-through shown in the Movie demo, and free up this guy to do real work. (Yes, I could automate that stuff in Excel for him, but my time is already spoken for. PowerBI means this guy can automate that stuff for himself).

    I agree that if you want to be a ‘proper’ Data Scientist, then you best learn to program. But what if you’re a manager or analyst that has neither the time nor desire to become a Data Scientist, and yet you’d like to be able to do the 20% of things that said Data Scientist uses to 80% of their net worth? This tool is perfect for that, as far as I can see. And over time, that Manager or Analyst will probably learn to code just like Excel users do, as they start to pick up bits of advanced DAX and M, and work out how to optimise them. In 15 years later they’ll look in the mirror at someone who can program. Like I’ve done with Excel.

  14. I saw several features that could solve problems I have today:
    – Power BI dashboards on SharePoint ~ This easily implements a dashboard distribution solution. I’m presuming too that the mobile view in SharePoint plays nice with PowerBI so dashboard interaction is possible across all the possible screens.
    – Write-back feature ~ a big deal – allows users to annotate data / add context to metrics / assessments
    – Word Clouds linked to data ~ I’m not a fan of word clouds myself (probably not fair – the data I swim in is usually numeric so I just don’t relate) but at least you can drill into these Word Clouds as opposed to static picture versions.
    – Bookmarks / Playback feature: the “PowerPoint for Data” analogy struck a chord. I can explore the data and tease out what’s driving a certain metric but I need to be able to communicate that to others.

    Note on “demos”:
    The many SKUs of Microsoft lead to exchanges with IT like this:
    User: Just saw a cool demo that shows I can have live Excel charts on my SharePoint site. How do I do that?
    IT: Ummm. You can’t. The Company didn’t pay for those flavors of SharePoint/Office.
    (User walks away frustrated with IT. IT self-esteem takes yet another hit)

    Note on “Data Scientist”:
    A re-branding attempt to lend gravitas to your “data analytics” organization … grrrrrrr, nevermind. I could write pages on why this is so wrong from so many different perspectives. I’ll go vent over a beer tonight instead.

  15. Two more:
    – Visio process diagrams linked to data ~ another big deal. Yeah, you could build charts to represent process steps but forcing the process out in the open on a Visio diagram then linking those to metrics / color coding makes for a compelling and transparent story.
    – Data tables with data bars overlaid ~ intuitive and efficient use of space

  16. Yes, I’m turning into (have turned into) a grumpy old man. It was inevitable, I just thought I had a few more years.

    I know people who have been using Excel for, literally, 20 years. They’ve never bothered to learn a few keyboard shortcuts much less a worksheet function besides SUM or, god forbid, VBA. Now they’re going to learn PowerQuery and DAX? I say spend some time mastering INDEX and MATCH before you jump to the new bling.

  17. I forgot about the annotating thing. I agree that has some definite merits.

    Word clouds are cool and useless. I’ve never heard of a legitimate use case other than entertaining curiosity.

    SKUs? Preach, brother.

  18. Great discussion! I agree with both Jeff and Dick. I do think you need a Jeff Weir-level highly energetic enthusiast on staff to make this happen. I wish I had one. My experience with many Excel users is similar to Dick’s as stated above; I’d just substitute “pivot tables” for “INDEX and MATCH” in his comment.

    What I need is something that lays out all the possibilities in an Excel 365/SQL Server/SharePoint environment. Does anybody know of a resource, preferably text, that lays out what combinations of Power … to use with given configurations and objectives?
    I’m currently messing with PerformancePoint in SharePoint and connecting it to a SQL Server database using a Secure Store ID. I went away from this for a few months and it took a while to even remember what that I was working with something called “PerformancePoint.” :-)

  19. PerformancePoint is dead. Don’t waste time flogging that horse. With “all the possibilities laid out” you may also want to include Azure as a data platform, AI platform and other goodies, too. Just “Excel 365” (if there even is such a thing) is not going to cut it. Office 365 comes in so many flavors and SKUs that it’s not funny. The BEST magic happens when all the online services are combined, and with a data gateway that connects to your on-premise data sources. This will typically mean a full license all around for a lot of things. But there’s a SERIOUS of magic that can happen without the full bells and whistles license, too. So, what’s available to you will always be directly related to what online services you subscribe to.

    I won’t attempt to write about what’s what and what’s required where to combine X with Z, because these things are just emerging and the best resource for that will be always be Microsoft.

    The only constant in this scenario is that there will always be rapid change. In the past, Microsoft put out a version of a product and then you could dig in and run with that as a non-changing entity for the next three years. These days are gone. Unless you are on a perpetual license, things now happen in monthly increments, with much more agility and responsiveness. That’s a BIG win for the consumers, but it also requires a shift in both the consumer and developer change readiness.

    Dick, don’t get hung up on the movie topic. It’s just a dataset that a lot of people can understand and easily relate to. You could use production facilities with equipment pieces from different manufacturers and different managers in charge of production divisions instead, but Angelina Jolie is simply much nicer to look at.

    I love Power BI and the other Power tools. I’m not a developer. I don’t like writing code. I can string together a VBA Worksheet_Change event and copy stuff from here to there, but I find it painful. With Power BI, though, I have created a series of dashboards that combine data from two different Excel workbooks, and Active Directory, and a SQL Server database and two different SharePoint 2010 on premise lists. I’ve done that by clicking commands and icons in the Power BI user interface. If I had to write that with VBA, it would take me 6 months and a lot of frustration, if it is possible at all. With Power BI, I did it within a few days.

    There is a learning curve, of course, and I’m one of the people who keeps the hit counter on Ken’s blog in the high numbers.

    But you don’t need to be an Excel power user to start with Power BI. You don’t need to know about Vlookup or Index/Match or why they differ. You can get data from different sources, clean it up, combine it with other data from other sources and build dashboards without ever hurting a single formula. If you’re capable of clicking a ribbon icon, you can start with Power BI.

    And the time it takes to go from zero to “wow” with Power BI is truly amazing, and the kind of “wow” is very, VERY much more WOW than any VBA thing I’ve ever seen. And that includes Charles Williams and Jan Karel P.

    And, of course, the managers that I wrote these dashboards for can view them on their mobile phones and tablets, while they are at the airport, waiting for their plane, walking from terminal A to B. They no longer need to be in the office, or VPN into our domain so they can use the fat client that can run the VBA. The data gets refreshed on a schedule automatically, so they always have the latest and greatest in the browser, tablet or phone. >>>THAT<<< is the scenario that wins the most buy-in for the Power BI projects I run.

    cheers, teylyn

  20. Designing sensible questions and possible actions based on the answer seems to me the core business; providing too much too senseless information diminishes the quality of decisonmaking. Improving the ‘pull’ side will turn out to be the crucial factor.
    ‘Toys for ‘push’ nerds’ looks very marketing driven.

  21. @ Jeff – check this out as well (if you have Excel 2016) – Its basically flash fill on steroids.

    https://blogs.office.com/2017/05/17/transform-data-by-example-a-microsoft-garage-project-for-excel/

    @Dick

    PQ and PP are revolutionizing the way we use Excel.

    In the ETL world VBA’s role is reduced to automating refresh upon change of a cell content or a sheet/workbook activate

    In the Data Push world – (From Excel to dB) VBA is still very much needed. May be Power Apps will change it – too early to say.

    PBI Desktop (SSAS on a shoestring) holds the potential to replace Access as a “free” Desktop database for the masses

    The Visuals in PBI Desktop get better each month – In a year or two they will be as flexible as they are in Excel right now – but we will have a far greater variety of visuals

    Power BI (.com) is still a dream and the dream is turning to a nightmare with the recent Power BI Premium

  22. Sam – The Transform-by-Example thing look great, but looking around the Microsoft Garage I see that again VBA is something that they just don’t mention these days.

    I wouldn’t mind, but how come they don’t tell us where to find the new technology that does what VBA does, only quicker and better?

  23. @Dough – Office 2002 – Developer Version was the beginning of the end.

    From Excel 2003 Microsoft stopped thinking of Office as a Development platform.
    It was .Net hell from there on

    The Power Tools are not replacing VBA – they are just reducing its role only in the ETL part of the world.
    Earlier all the heavy-lifting has to be done via code alone – but now you can let PQ and PP do the heavy-lifting and vba can just automate the refresh.

    If Microsoft introduced “Scheduled” refresh in PQ / PP inside Excel (currently done by third party products like Power Update) then VBA’s role is reduced to zero.

    I wish MS would introduce Power Apps Desktop – Which would allow us to create apps on the desktop – that can push data back to all the sources to which PQ can connect then publish that app to the web – That would be truly revolutionary – But MS currently does not have the vision at the top to do this

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.