skip to Main Content


…But Don’t Expect Us to Wear Suits OK?

Got a great question in email yesterday from a potential training/consulting client.  Paraphrasing:

“…what’s the best way for our company to get the required PowerPivot/Financial analysis skills? Should we be hiring for them, developing them in-house or outsourcing them to other organizations?”

As I was replying to the email I realized this was a good topic to discuss publicly.  How should your organization find the right PowerPivot people?

The answer to this question, of course, is also quite relevant to the budding PowerPivot professional – the “Supply side” of the equation if you will – and therefore the majority of people reading this.  But I am going to approach the question from the employer side – the Demand side – because I think that’s the best way to answer the question for both parties.

Should I Outsource PowerPivot Skills?

First, let’s be specific about what we mean by “PowerPivot Skills.”  Let’s focus specifically on who I call the “PowerPivot Author.”

A PowerPivot Author:

  1. Loads data into PowerPivot
  2. Creates relationships and formulas (we can call this “modeling”)
  3. Builds reports, dashboards, etc. based on #2
  4. Shares #3 with others

NO!  Do NOT Outsource Your PowerPivot Authors!

Now that we’re clear on definitions, I do NOT think you should outsource your PowerPivot Authors! 

The true magic of PowerPivot occurs when the authors are DEEPLY EMBEDDED in your business.

Outsourcing your PowerPivot authors is no different, really, than outsourcing your modeling and reporting to a traditional BI consultant:


When You Outsource your PowerPivot Authoring (or report-building/modeling via other tools),
You Are Primarily Paying For Communication Costs!

In other words, when you outsource these activities, you pay $20 for every $1 of real insight.  And 20 hours elapsed for every 1 hour of real insight.

Those aren’t good ratios.  But in fairness, that’s the case with ANY technical project when the subject matter expert (the “biz” expert) and the tools expert aren’t the same person – even if those two people both work for your company.  (Software engineering at Microsoft exhibits this same communication ratio as well.)

With PowerPivot, we have that rarest of opportunities – to combine the tools expert and the subject matter expert in one brain!


With PowerPivot, the Subject Matter Expert and the Tools Expert Can Be the Same Person!
The Results Are About 20x Faster AND Cheaper.  (No, I am not kidding).
You Can’t Typically Get This Via Outsourcing.

So when it comes to PowerPivot Authors, my resounding recommendation is to Hire or Grow them.

Wait, Can I Outsource Other Parts of My PowerPivot System?

Yes, absolutely.  This is an option in three primary places:

1) Data Stewardship – a little investment in the database layer can deliver a lot of value.  The cooperation of a database professional is like a force multiplier for your PowerPivot Authors, a way to further turbocharge their powers. I’ve written about this here and here.

If you have database people on staff, perfect!  Keep them!  And introduce them to your PowerPivot Authors.

If you lack that kind of expertise, no worries – PowerPivot will still return huge dividends.  Just know that if you add this ingredient later, you will see even greater results, and the investment is very much worth it. 

This is a natural place to involve a BI or database consulting firm – building, extending, and maintaining your data layer, but not in building your models and reports.

2) SharePoint – a friend of mine at Microsoft recently likened SharePoint to the Spanish Inquisition of Monty Python fame.  As in, “no one expects the SharePoint Inquisition!” 

If you plan to use the server side benefits of PowerPivot (which ultimately, everyone should), and can outsource the SharePoint component – either by hiring consultants or moving the entire SharePoint layer to the cloud – I highly recommend it.

Unlike the PowerPivot Authors, SharePoint is one place where growing/hiring in-house expertise is the wrong thing to do.  It just takes entirely too long.  And once you finally find or develop a competent SharePoint admin, they leave for a consulting firm.

If you can outsource SharePoint, do so.

3) Training and Jumpstarts –  this is, after all, a big way that I am earning a living while I assemble my next venture.  And I don’t like doing things unless they deliver a lot of value.  They must be things I believe in personally.

(In fact, this blog post is likely to result in me not being hired to do the consulting gig in question, because the questions asked of me in the email yesterday are largely being answered here in this blog post.  Doesn’t make sense, to me, to try to charge lots of money for something that I can reasonably capture in a few paragraphs of text.  The deeper dives are what you hire me for.)

Sometime in the Fall I am likely to “retire” from the training biz.  And I am figuring out what is going to happen to my training biz at that point.

No matter what you do here though, I recommend an emphasis on training rather than on hiring a consultant to build your model and report for you.  The old adage about learning to fish.

OK, Back to the Authors.  What Makes a Good Author?

Hey, I’m glad you asked.

Below I list the attributes of an ideal PowerPivot author, which sets the bar a little higher than good, but makes sure your compass is pointing in the right direction.  You do not have to reach the North Pole in order to be Traveling North.

  1. They know Excel.  Obvious, right?  But how well do they need to know it?  Two good signs are a) they are comfortable creating pivots and b) they are comfortable with the existing Excel functions VLOOKUP and SUMIF.  If someone isn’t proficient with those three things, it does NOT mean they can’t learn PowerPivot.  But I like their chances a lot better if they ARE proficient with those things.
  2. They are a good (and efficient) communicator.  Don’t overlook this attribute!  Your Authors are going to be liaisons – diplomats of a sort who circulate quite a bit.  And communication is a two-way street – they need to “send” and “receive” reasonably well.  How do you gauge these things?  Well, you talk to them about something meaningful – something relevant to the business.  Is it an easy conversation?  Do you struggle to understand each other?  Does the conversation take 30 minutes when it should take 2 minutes?  Just get a feel for them.  (And if YOU yourself struggle with communication, get someone else to be the judge of this, heh heh).
  3. They ask good questions.  Good questions about the biz.  Good questions about the data.  In general, they are comfortable asking questions, and are practiced at it.  Ultimately we need people who make good statements as well – “I believe X based on the data,” for instance – but you don’t arrive at statements without asking questions.  (This was, by the way, one of the themes of Steven Levy’s talk at PASSBACON – find people who ask good questions).
  4. They are curious about data, or otherwise just enjoy data.  This is one of those “you have it or you don’t” kind of traits and is not something that is likely to be learned over time.  Of course, if someone has never been exposed to the world of data, they may just have never discovered how much they enjoy it!  But if you’ve been around data for a few years and have never “caught the disease,” chances are, you’re immune.  Which is fine – only about 5-10% of humans are susceptible to Data Fever.
  5. They are primed for continuous refinement and improvement.  For one thing, they admit mistakes, but it doesn’t stop there.  Always hungry for Better.  The dashboard they created in Month Two may have Changed the World, and absolutely revolutionized your business.  But they come back in Month Six and say “You know what, I’ve come to realize that dashboard sucks.  What if we did this instead?”  And you find yourself nodding, like “how did we survive four long months without dashboard v2?”
  6. They understand that not everyone else meets the criteria above.  We are Data Nerds because the rest of the world is not.  Our job is to make things simple for the Others, who do NOT want to be exposed to our world full of pointy functions, formulas, relationships, and field lists.  If your PowerPivot Author gets upset at the dashboard/report consumers for “not doing it right” or “not getting it,” they are pointing the finger in the wrong direction.  See also points 8 and 9 below.
  7. They are already deeply versed in the Business, or can become so.  Absolutely Critical.  This is not a tech role you are filling.  They see things through the eyes of the business.
  8. They have some semblance of aesthetic skill.  For the record, I am not so good at making things pretty.  Many Excel Pros are MUCH better at this than I.  And yet…  most Excel Pros completely ignore this today.  I think I’m no worse than 85th percentile, which is not a good sign folks.  So I think you’re less likely to find someone who passes this test today, assuming they pass the others.  But hopefully they are willing to learn.  Because people much prefer to consume pretty dashboards and reports.
  9. They can place themselves in the shoes of the consumers.  When you build a report or dashboard, you are actually building an application.  People don’t just look at it, they use it.  How do they use it?  What kinds of questions are they trying to answer?  Could the report/dashboard be designed differently, to make those workflows more efficient/accurate/enjoyable?  For most Excel Pros, this is a New Frontier – a completely foreign way of thinking.  Seeing things through the eyes and workflows of your audience is a paradigm shift, but it’s VERY satisfying and stimulating.  Foster it, encourage it, practice it.

Phew, that’s enough for today.  Thoughts?  Questions?  Bring ‘em on!  I find this to be a fascinating topic and would love to see some discussion.

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 18 Comments
  1. This article made me realize that there is a need for Excel “Person” Banding, with some semi-real % numbers associated with it. What better person than you, Rob, to develop something like this. User, Power User, in-house Pro, Professional, Developer, MVP, topN. PowerPivot would be one of the main parameters, etc.

  2. The power of a certification…How much will the ‘Microsoft Office Specialist: Excel 2013 Expert’ weight in the near future for an Excel and Powerpivot Pro?

    1. I was unaware of such a certification. Traditionally, what has been involved in the test?

      I seriously doubt PowerPivot will be included since they limited it to the Pro Plus SKU. But who knows.

      1. read more about it here:

        and yes, the use of Powerpivot will be measured;

        “Create and Manage PivotTables.
        This objective may include but is not limited to: creating new PivotTables, modifying field selections and options, creating a slicer, grouping records, utilizing calculated fields, formatting data, utilizing PowerPivot, managing relationships ”

        I have the Excel 2010 Expert cert and with that one they ask you simple things like, use VLOOKUP, SUMIF, create a simple pivot table, consolidate all of this, use what if analysis and some other straight-forward text functions.

  3. Growing an in-house Excel PowerPivot expert is a simple strategy and a good investment for any company.

    However, millage might vary according with whom the company starts with and its ability to retain such talented people in a competitive market.

    1. I don’t think we’ve yet reached the point where PowerPivot skills are going to be poached. I look forward to that day of course, but we’re still very early. If you find or grow a PowerPivot author today, you will likely retain them for awhile, kinda like how rookie contracts in the NFL are a great deal for the team.

      Achieving greater mobility/poachability will be a boon for the authors themselves of course. I suspect we have a year or two before we see that trend emerge.

      If I am wrong about that, and you already see evidence, please let me know!

  4. This is why I LOVE my personal value prop. You see, my degree is anthropology. I picked up the technology and business in the field. So when you talk about asking questions and being curious about data, and communicating well, you are talking about anthropologists. I believe more of us should be using PowerPivot as consultants. Because here’s what I can do that you just said DON’T do: I can understand the culture and goals and internal objectives of an organization after a few hours of observation and interaction with their people.

    At big part of my planned future career revolves around going into organizations to break this stuff down for them. Because as an anthropologist I CAN naturally model the culture, business, and thus the data that fits their business. But that’s me. And that’s based on how I was trained to learn and observe how people work. It never occurred to me that this wasn’t a common trait. This post has given me a lot to think about. Thanks!

  5. While I agree with most of what you say, I do think there is a role for consultancy/IT in helping with the modelling and DAX. Maybe this is what comes under the heading of ‘training and jumpstarts”, but it’s more than that – some DAX and modelling problems are tough, and in order to crack them you need the help of someone with a more technical background, who has experience of a lot of different companies and models. It’s one thing to know what your measure should do, and another to know how to not only write the DAX but write it so that it’s fast enough to use.

    I guess I’m saying this to try to convince myself there will still be a job for me in five years!

    1. Sorry this took so long Chris, because my intended reply was “formed” instantly:

      There will ALWAYS be rich opportunities for smart people who love data and understand the toolsets. In fact, those opps only grow every day. If someones needs to perform complex analysis on a 500 Billion row dataset, who do you think they’re going to dial up? And people with that kind of data materialize every day, at an increasing rate.

      So the only “debate” is the “flavor” of those opportunities – how much they resemble the opportunities of today vs. how much they will have morphed into something new (and exciting). I don’t really think that either of us have an invested position on where on the spectrum it will fall – we all get to wait and see.

      A few days back you may have seen me refer to myself as a Carpenter and the Italians as Physicists. Growing up, I fully expected to be more in the uber-smart Physicist camp, so in some sense it pains me to admit (to myself and others) that, when it comes to DAX, I’m a Carpenter.

      But really, I mean that analogy to be flattering to both camps. (And I very much include you in the Physicist camp, Dr. Webb). In the most respectful sense possible, I’d wager that I’m more effective at teaching DAX to the average biz user, *because* MY grasp doesn’t extend far beyond THEIR “maximum future grasp.” Remember when CPU’s had optional co-processors? I feel like only 0.001% of the population has the biological DAX/MDX coprocessor possessed by Webb, Russo, Ferrari, Brueckl, and a handful of others whose omission I will blame on my post-surgery pain medication 🙂

      But the rest of us can now do a LOT of things that we couldn’t do before, and do them without having brain surgery to implant whatever it is that you guys have. Fortunately, the number of data problems is exploding, as is the complexity of those problems, so you guys aren’t in any danger. Quite the contrary.

  6. Any insight or data around the ratio of “power users” to associates, or number of people in a typical organization with the capacity to grow into a successful PowerPivot professionals? How about your feelings on the numbers an organization should have or strive for in an increasingly data-dependent environment?

    1. Great question! Allow me to field it while on strong pain meds, and then later you can tell me if I sounded wacky 🙂

      I don’t have perfect or complete answers, but I do have some pretty good starting points.

      My survey research indicates a median of 15 “consumers” for every “author.” The average is higher, thanks to some outlier situations, so I think it’s safer in this case to use the median.

      Also, that 15:1 ratio is quite well aligned with our data when I was at Microsoft, where only 5-10% of the Excel user base created pivots. 15:1 indicates about 6% authors and 94% consumers. Pretty good alignment there.

      Furthermore, this tends to “jive” with our every day experience. People who LIKE data, who LIKE Excel – 1 out of 16 sounds consistent with our day to day experience doesn’t it?

      So, as a starting point, we should expect only 1 in 16 biz folks to be candidate Authors.

      Lastly, I would say that, at minimum, you must have Author “coverage” of every important facet of the biz. To which you will say, rightly “well, DUH, Rob!”

      But there IS some substance there. What I mean is: Domain Expertise Coverage. It’s no good to have a skilled PowerPivot author from, say, the Digital Marketing team, and then assign her to building Finance or Accounting models! You either need to “absorb” her into Accounting/Finance, and literally have her split time between roles, OR you need to spin up another Author in Accounting/Finance.

      Until you have the domain expertise AND the PowerPivot skills in one brain, you just aren’t getting the PowerPivot magic.

      Helpful? I *love* this conversation and am happy to continue it.

  7. Rob I can def relate to your comments about being a DAX carpenter.. All that time as an Excel user.. squeezing data on to sheets, connecting tables with VLOOKUPs and Pivot after Pivot I can appreciate the boost PPivot and DAX give to the Excel nerd. I wouldn’t call myself a BI pro just yet, (I read the DAX book then recently tried to hang out with the Italians… not ready yet 🙂 but DAX seems to be right in my wheel house. It is exciting to think that the data we spent the last 20 years putting into the computer is now coming out in the form of valuable insights.

    Big data + Power Excel should = demand for the ‘author’. If that happens within a year or two, I would be thrilled.

    Keep up the good work!

Leave a Reply

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