skip to Main Content

Modern Excel:  Is it Just Power Pivot?

So we started this User Group…

…and, you know, it’s a real thing.  We’ve got active chapters now in five US cities, a sense of “blueprint” for spreading it rapidly to many more cities worldwide, and nearly 700 members in the LinkedIn group.  (What, you haven’t joined yet?  Darth Formulus finds your lack of faith… disturbing.  A problem easily remedied yes?)

But wouldn’t you know it.  All you analytical types started asking “hey, what IS Modern Excel, exactly?  What is considered ‘fair game’ to discuss at meetings?”  “And what the heck is the point of this LinkedIn group – you’ve made virtually no announcements whatsoever.”

Ambiguity?  That will never satisfy humans with Compulsive Data Crunching Disease.  And I wouldn’t have it any other way.  I thrive on this sort of thing.

As an aside, constantly being forced to be precise (by my peers) at Microsoft is one of the most formative and useful experiences of my life to date.  My first 22 years on the planet had been fueled strictly by Instinctive Conviction – which in hindsight is merely another form of “coasting.”  But I’ve since come to believe that if you cannot communicate your opinions effectively, not only will their impact be forever limited, but actually, even YOU don’t understand what you THINK you do.  Sloppy communication often masks sloppy thinking.  22-year old Rob arrived in Redmond with an ample supply of both.  It was as much fun as a multi-year waterboarding, but they straightened me out.

Excel 2007 Desktop is a BIG Dividing Line

Let’s start by “ruling out” a massive swath of possibilities.  Sometimes it’s easiest to start by saying what something is NOT, and this is one such case.

Here’s the acid test:  if a topic is something you could do with Excel 2007 desktop, it’s not Modern, and does not belong in the group.


Excel 2007 Desktop:  Anything You Could Do Here is NOT Modern

So, here are some Examples that are NOT modern, even when you use them in Excel 2013:

  1. Traditional pivots
  2. VLOOKUP, SUMIF, etc.
  3. Interactive solutions driven by VBA Macros

OK, So What DOES Qualify?

Here are some examples of Modern:

  1. Power Pivot, Power Query, Power View, etc. (the “Power Star” family)
  2. Excel Web App, Excel Services – “Web Excel” (especially for publishing)
  3. Topics that support the above – Designing Good Dashboards, for instance
  4. Leveraging Modern Excel for Career Growth, Org Growth, etc.

Hybrids Are VERY MUCH Modern!


Yes.  We Like Hybrids.

Practitioners of Modern Excel know that the Network Effect of Excel is a huge part of its strength, and therefore a huge part of OUR strength as professionals.

So we would never, NEVER, “rule out” techniques and approaches that blended Excel’s rich “traditional” features with its newer features.  It’s what separates us from animals after all.

With that in mind, here are some examples of Modern Hybrids:

  1. Chart tricks that are used in concert with Power Pivot (SUPER relevant!)
  2. Using VBA to automate the creation of Power Pivot reports
  3. Using in-grid formulas in concert with slicers and measures.
  4. In other words, features that existed in 2007 desktop and earlier ARE encouraged and applauded in our reindeer games (i.e. at our meetings), as long as they are used to “improve” something Modern.  They just can’t be the FOCUS of a topic.

How About Something More Poetic?

The answers above read like a checklist.  If that’s a bit too OCD for you, great.  Let’s change gears.

The reason we chose the name Modern Excel is because we’re talking about a New Way.  A Better Way.  You know, a new world:

  1. Where we spend far LESS time on repetitive, tedious tasks.
  2. Where we spend far MORE time thinking!
  3. Where we spend as much time formulating better questions as we spend answering questions.
  4. Where slight changes in our needs often require a few seconds of work, rather than a few days.
  5. Where our most impactful creations (our most insightful workbooks) don’t become anchors that we must drag around with us until we change jobs.
  6. Where we see, every day, the impact of our efforts.  And we feel that rewarding glow.
  7. Where others see the impact of our efforts.  And we see that reflected in our tangible rewards.

Questions?  Comments?  Outright Disagreement?

This is a “living definition.”  Always open for refinement.  So bring the comments Smile

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 19 Comments
    1. Hi Mike. I posted a bad link last night. Now you can get to the group by clicking the image or the link at the top of this post.

  1. The one thing that I don’t normally see mentioned are power-shell scripting to automate excel tasks. All the reports that I’m making now using powerpivot, powerquery & powerview also get auto-refreshed, saved as pdf & send via email on a scheduled basis using powershell scripts. So once I make the reports, the report requires zero intervention going forward. I get a little more time to feed my OCD…

      1. I’ve posted the code for one of the file below, It refreshes the excel file, selects slicers for store first & then runs through slices for all departments, saves each worksheet as PDF, then joins all PDFs for a particular store location in a single pdf file. and sends them via email. it’s already setup in the task scheduler to run this each month during first & second half of month

        1. Very very nice! Thanks a bunch for sharing!

          This is just the type of thing I need for the really lazy people in my organisation. Most people seem fine with dashboards. The executive management and board of directors however seem to want everything mailed to them. They don’t really trust technology and so far only PDF and mail seems to be ok with them. I think my CFO has some terrible childhood memory regarding excel… He doesn’t want to talk about it.

          1. you nailed it, almost all employees are fine with the share-point online reports that I make for my organization, it’s just the upper management that I can’t convince no matter how much I try to use it, they want it in the email and the only way to make sure they see what is see is to send them reports as PDF, I’ve tried sending excel file directly in the past but the results were disastrous (since they all have apple products only)

          2. Bellicose: Sad but also glad to see Im not the only one with problems like this. It even went so far that the board of directors had a special iOS app built for distribution of documents only for them.

        2. Hello, impossible to find your powershell script on the specfied site. Any chance to see it ? I am very interesting on it ! Thank you in advance

  2. I know pivot tables aren’t modern, but I’ve come across quite a few people who don’t know what a basic pivot table is. People who write complicated Excel formulas or T-SQL statements to analyze data when a pivot table would work faster.

    Pivot tables are the foundation of Power Pivot and other Power BI tools. I have a presentation just on pivot tables – it’s simple enough any professional can benefit and both times I’ve presented it there’s been someone in the audience who’s like “I had no idea”. I always conclude by saying – hey, you know you can’t join 2 pivot tables? Well, guess what you need to learn about next?

    Pivot tables are a gateway drug to Power Pivot, I tell ya

    1. I agree completely. So we should definitely include “recruitment” in our discussions. How do we get more people on the Modern Excel bandwagon? Teaching them normal pivots is a fantastic first step.

  3. Although I like your approach of networking, I have to say, this is not for me because it seems – and that may sound controversial – racist against “traditional” users. Yes, all these new techniques are amazing and enhance the Excel capabilities by a factor I cannot even define, but you really rule a lot of people out how had no chance so far to get used to these techniques.

    If I look at my experience with Excel I would say I’m an advanced user and the go to guy for a lot of people in my company (not trying to brag or something). However, I recently just started to dive into 2013 (which I subscribed to privately as my company still forces me to use XL2007), signed up e. g. for Chandoo’s PowerPivot course (incl. your adv. power pivot part + books) and I think it will take some months until I’m at the level you require. As a result this means for me: you don’t want me in there. I mean, in the end that’s fair, but I’m not really sure if that helps people who are new to this whole PowerBI stuff, Excel now offers.

    I would be more than happy if you correct my point of view, maybe I have misunderstood something…?!

    1. Hi Phil! No, we absolutely want you there! There is no “level” required – in fact just the opposite. This group is about helping people (and organizations) along the Awareness -> Education -> Acceptance curve, with the full expectation that many people showing up at the group aren’t even Aware yet (unlike you, who has entered the Education phase).

      The only thing “excluded” here is that the group is NOT intended to TEACH “traditional” Excel. Make sense?

      The group exists BECAUSE Awareness/Education/Acceptance is NOT widespread. If it were meant to be an exclusive club of people who already know the secret handshake, there would have been no reason to start it.

      1. Then I have to say sorry and again, I didn’t want to offend anyone. Looks like I misread your post. I’m absolutely on your site when it comes to learning new things and leave those old paths to try something new and better. 100%

        Sorry again for the misunderstanding and looking very much forward to Adv. Power Pivot… 🙂

Leave a Comment or Question