By Avichal Singh

The first time you truly experience Power Pivot – not a demo, not some random public data set or someone else’s data – your own business data all lit up using Power Pivot. It is a transformative experience. And you know there is no turning back.

I remember that moment, I remember thinking this is HUGE, this gets BI in the hands of people who really need it. Power Pivot is surely is going to explode.

Sadly, it does not feel like that is happening 🙁

As I have become more involved with the Modern Excel User Group (also on LinkedIn), I have realized that there are way too many people in buckets a) and b) below and not nearly enough in c).

a) Sad smile Unaware. Have not heard of Power Pivot or Power BI
b) Disappointed smile Aware but have not truly experienced the capabilities of Power Pivot
c) Red heart In Love with Power Pivot

Note: Are there stages between b) and c)? Between experiencing Power Pivot and falling in love? Perhaps. But guided by the right hands that path should be really short 🙂

Unaware: Don’t know what they are missing

For the unaware group, I do my part; talking to anyone who would lend me an ear, about Power Pivot and Power BI at any event or gathering. But overall we would need to trust the higher powers 🙂 within Microsoft with that task. However I will make the offerings below to the powers that be:

Power Pivot should be a free add-in for all versions of Excel 2013, just the way it is for Excel 2010. I feel it even makes good business sense for Microsoft. Power Pivot has the potential to attract a large user base and would solidly anchor Office and Office 365 in the rapidly changing world. These users can then be up sold services like Power BI.

Power Pivot add-in should be easily discoverable. Both Power View and Power Map have buttons as part of the standard Excel ribbon, which can be used to easily activate these tools. Not so for Power Pivot.
It takes 8 mouse clicks to activate the add-in so you can launch Power Pivot.
File > Options > Add-Ins > Click Dropdown > COM Add-Ins > Go > Select ‘Microsoft Office Power Pivot for Excel 2013’ > Ok
As the first born in the Power BI suite, it is time for Power Pivot to claim it’s rightful place on the standard Excel ribbon and it would fit perfectly under the Data tab.

Currently in Excel 2013 Desired in Excel 2013
Currently in Excel 2013: Powe Map, Power View Desired in Excel 2013: Power Pivot or Data Model

Note: There was a shift in Excel 2013 to weave in Power Pivot so closely with the tool (and call it Data Model) that potentially many users would not even need to learn or use the Power Pivot window. IMHO, that is just keeping people away from all the goodness and richness of Power Pivot. Let them come, let them explore and experience.

Aware but not in Love: Mind the Gap!

While I may feel helpless in making a dent in the Unaware group, I feel impassioned and even obligated to help the Aware but inexperienced group.

I have spoken to many Excel users and noticed that there is a gap between knowing about Power Pivot and actually trying it out yourself to experience its true power. There is a step there that many users simply cannot take. Being on the other side, it baffles me a bit. I feel like yelling “Hey, come on over, the grass IS greener on the other side!”

From Excel to Power Pivot: Mind the gap!

I have some thoughts around, why is there a gap and what can be done to minimize the gap, but I’ll keep those to myself for now and focus on what can we do to get people to cross over from one side to the other. There may be many ways across, but I’ll pick the one that speaks strongly to me.

If someone were to just hold the new users hand and walk them through transforming their first data set using Power Pivot. Nothing fancy, a simple dimensional model with basic measures. This might take as few as two hours. It has to be their own data set though, one close to their heart, with answers to the riddles they are trying to solve. Not AdventureWorks or Tailspin Toys. Sample data sets can be good for learning but can still leave you short of crossing the gap.

That task my comrades, befalls on us. We are the enlightened, the empowered. The readers of this blog, most of you have crossed the gap, and now it falls on you to help the others. The Haves should help the Have Nots.

So look for the enthusiasts amongst your colleagues and friends – the data gene people – and encourage them to:-

– Read a book or online blogs

– Take a class (online or live in-person)

– Ask questions on forums

And when they are back at their desks and working through their first data set, help them. Just a little bit, just to get across. They will never look back.

Revolution starts with you

I am still convinced that Power Pivot and Power BI would transform the world of BI. However it would not transpire the way I had imagined. Instead of a big explosion it would be more of a trickle, till it starts to pour and then it becomes a torrent. It would spread from one person to the next…Revolution starts with you

…till it reaches the tipping point.

Revolution starts with you

Just not without your help. So help one person cross the gap from Excel to Excel+Power Pivot.

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI. He is based out of Seattle, WA.

This Post Has 42 Comments

  1. Every time I talk to someone about PowerPivot I just get blank stares and even when they do understand what it does on the basic level, it doesn’t sit in until I can demonstrate with their own data just like you mentioned.

    I always relate it to having a rocket ship and if you give a rocket ship to the ancient Egyptians they wouldn’t know what to do with it although they are very interested in outer space and planets.

    I am still waiting on the tipping point!

  2. I have 20 years experience building analytic / cube solutions on technologies like Cognos PowerPlay, Analysis Services and Power Pivot.

    For a complete solution, all those analytic / cube tools always need a data transformation tool to shape the raw data into the format/structure/granularity needed. In the Excel world, the obvious tool that meets that need (and bridges your gap) is Power Query.

    As well as being a great tool for importing, combining and reshaping data (all with a much more approachable UI than Power Pivot), it can deliver data straight into the Power Pivot Data Model.

    http://www.powerquerypro.com seems to be available … 🙂

  3. It is even sadder when you visit your BI guys in the corporation and they try to fake a general understanding but obviously don’t have any real awareness of the tool or the power it brings. I met with our BI guys and they wanted to help but kept reverting to their own tools like Cognos that doesn’t do the analysis as easily or as powerfully as PowerPivot.

    I’m starting a guerrilla campaign here. My first step is to get access to the data (hard to do.) Then I will use the tool with one or two people here learning by helping to provide insights into some long standing questions/business problems. I plan to convert the advanced Excel users one at a time. 🙂

  4. The same is true with Power Query – Initially like “DAX”, “M” looks daunting but once you get the hang of it – It grows on you and becomes your go to tool for ETL.

    It also has the advantage of having the most responsive team in MS who listen to what you have to say.

    Eventually I have a feeling that Power Query is the place where we will define the Relations (Joins) as more complex joins are possible via PQ than in PP and produce the flat Table (Sorry Rob – I know you said do not flatten 🙂 ) and Power-pivot will be the place where we do the CALCULATE(….)

  5. Earlier in the year I finally got Office 2010 at work and the first thing that I installed was PowerPivot. It was an exciting time, but then reality sunk in over the next month or two. I am the only one here with the skill set to utilize such a tool – every time I try something ‘fancy’*, PowerPivot or standard Excel formulas, I hear “that’s good/great, but you can use VLOOKUP and SUMIF too right?” or something along those lines. Even if I could showcase something powerful and time-saving, I would continuously have to maintain it or convert it to something more ‘digestible’ for whomever takes over the item in question.
    This and the files kept becoming corrupt and I would lose my data model, further diminishing the chance of cross the gap in the near future…one day….

    *Fancy: adjective: 1. Anything that is not an IF, VLOOKUP or SUMIF formula or combination thereof

  6. I’m firmly I’m group B. I work for a company that manages everything with spreadsheets including our $200M capital budget. Huge spreadsheets that are linked, confusing and near impossible to extract useful information out of that could be used for any kind of analysis or forecasting. Then there are the ‘special reports’ that are asked for that MUST be done in a spreadsheet but are so time consuming to update on a routine basis. I’m sure powerpivot can help but I’m unsure where to start.

  7. Whilst initially delighted with power pivot, I soon got frustrated with its current limitations. Issues there is no programming setup. Meaning everytime you create a new spreadsheet, you have to retype all your measures – there isnt even a block copy. This is a big issue if you want to go much beyond a quick look at the data. Why should i make the investment in learning dax if there is no programming interface. Secondly the broken interaction with power query is very frustrating: eg I change the type of a column in power query, and the whole data model is lost so i have to reenter all the measures, and recreate all the pivottables, conditional formatting etc

  8. I teach Excel and have been mentioning Powerpivot in every class I do and I’ve been teaching and introduction to ti. .but I do think people need to see it with their own data to really realise that they no longer have to do vlookups or get to grips with index/match. To be honest though, there are still quite a few people for whom a vlookup is a revelation…never mindI think Power Query (which I have only started looking at) will be of huge benefit side by side with this…(because lots of people still get their data in an pivot unfriendly way and Power Query would save them so much time….). …so maybe there is room there for us Excel lovers to offer quick demos to people with their own data.. maybe?

  9. Our small company works with small-medium enterprises; we do their BI work for them. Cognos was our original software of choice, but not since last year when we started using Power Pivot and Power Query. That said, we know that the issue is not the reports, but understanding what can be done with the reports.

    Most end users don’t have the knowledge, attitude, skills, or tools ( we call it KAST) needed to understand the reports and what must be done to use the information to change their processes. Our contracts include a major training component where we spend time with the report consumers so that they will learn how to make a difference. The great thing about the Microsoft tools is that they are usually starting from the familiar Excel base and venturing forth with a friendly interface. We also understand, and communicate that, we will be changing the Power Pivot reports when needed to reflect what was actually needed, not what was originally requested. (Get them past the “WOW” factor to the real issues…)

    Thus, group b) may well be aware, but not have the full KAST to do anything with the reports!

  10. I’m a web developer. I work with oodles of tech. Always pushing whichever company I work at to upgrade all their Microsoft related tech. (not against open source, but I know Microsoft related stuff very, very well)

    Anyway, I have a personal Office 2013 Home license, but since I can’t use it to learn PowerPivot I gave up. I also figured that since I couldn’t share anything with any of our executives anyway, even those that happened to have bought their own Office 2013 Home licenses, there really wasn’t any point. I forlornly check the PowerPivot space (and this site actually) from time to time to see if anything had improved.

    Such a pity that my current company is still using Office 2003/2007, Exchange 2003, and SharePoint 2005. Because SharePoint 2005 looks so ghetto, many talk of moving to other platforms. I’m certain I could have made a really good case for upgrading to the latest for Office and SharePoint. Hmph.

  11. Where was the option above for “Aware, but hate it!”? Unless your data sets are unrealistically small… this product does not seem to function at all.

  12. I am running 64. One thing that is important to know is that inspecting the Table Properties from within the PowerPivot DataModel Window will launch a full blown reload of the data that you have no control over. It would be great if it would just pull 10 rows of each field in that window for a preview, and then allow you to remove columns from the pull. For the 37 million records I pulled in my test, just to use the Table Properties meant losing an hour and a half. I waited it out, then it failed.

  13. I am waiting

    1. It’s still an add-in. Not everyone is going to have it – even if they have Excel.
    I want a PP Table inside Excel, not outside.
    I think everyone is waiting for something better than the current Excel.
    2. PP is a new app/software with a new language. Not everyone is going to learn another language.
    The world don’t need more new languages. We need a deeper/richer/interconnected language.
    The pieces are all good, but the integration isn’t. As noted, too many gaps/walls for comfort.
    True power doesn’t come from yet another Power something. True power comes from just One Super Easy and all Powerful Excel. All abilities interconnected, under One Name – and as proof incl. the conceptual/directional models, with one language, under one name.

    I am still waiting.

  14. I’m in group (b) – an avid, advanced Excel user who is very frustrated with the limitations of Excel. Unfortunately (or fortunately) I’ve been introduced to the suite of Qlik products which felt like the enlightenment you speak about in using PowerPivot. My experience with PowerPivot though has been extremely frustrating as I’ve tried to replicate what I’ve done with Qlik and found it super-slow and cumbersome. I hope that it’s something I’ve been doing wrong because I’d love to stay within Excel environment and help my clients do the same. I’m signed up for your webinar on the 20th – looking forward to it.

  15. Hi All,
    Happy New Year to all of you..
    I am an Excel Power user, Excel trainer, BI enthusiast slowly getting into teaching Business Intelligence using Microsoft Excel’s PowerPivot. A few years back I was bitten by a bug called Power-BI. I am not a person too good at programming languages. But I am in the business of teaching and consulting of Microsoft Excel.

    Here is the problem.. For the last few years.. I was trying hard to enter into BI world. My goal is to learn BI using Excel’s PowerPivot. But when I started looking at it..things are not so easy for me as I thought..ie. there are some concepts related to cubes, DBA concepts, running reports and queries to access data from different sources, tabular modeling..etc. It’s a big mountain which is very steep for me. Now my question is..

    To teach or to become a hardcore Power BI Analyst (using PowerPivot, Power Query, Power Maps, PowerView etc), shall I have to master all these Database concepts too deeply?? Shall I have to master concepts related SQL server’s SSIS, SSAS, SSRS services. Why because we need to master DAX code in PowerPivot which will slowly take you to SQL environment in some other way. But If I take this SQL path I will become Database guy and moreover I do not like it and I can not fit myself there. If the answer is Yes, to what extent I have to learn these database concepts. You can ask me any questions to understand my situation more clearer.

    Thanks in advance.
    Arjun M Shetty

  16. Amen to all of this but I feel a fundamental failing has been on Microsoft’s part
    I shouldn’t be having to sell the merits of these technologies and that’s my frustration, I just want to get on with building tools using them
    There seems to be a fear of embracing the unknown but I keep on pointing out that these are technologies available within 2013 and you already have them at your disposal on your desktop (and it’s now 2015)
    Self-serve BI has generated a lot of confusion and most people just interpret get dazzled by the data visualisation part until the horror realisation comes : how are we going to generate our nice presentation data set
    In this cloud of confusion vendors like Spotfire have come in and sold software because they are obviously more proactive
    So when someone like me comes in and says why have you bought that when you can already do data visualisation using Power View and you have it, it gets kind of awkward
    As mentioned above, the best way is to just delivering tools to showcase the benefits, actions speaker louder than words. Providing you have the freedom to do this of course and the support to take on monolithic IT

  17. I’m a SQL guy, and I’ve been working with Power Pivot for a couple months now. It’s good, but every time I do something, I think “I could have done that easier and quicker with a fancy SQL query.”

    It’s taking a long time to wrap my head around it, and it still hasn’t “clicked.”

    I don’t know what I don’t know, so…. I don’t know… here’s hoping.

Leave a Comment or Question