Coffee Talk

This Week’s Topic: The Power Pivot Pros

Welcome to an experimental new feature here at PowerPivotPro, where members of the community discuss various topics related to Power BI, Power Pivot, and Analytics/BI in general. These conversations take place during the week on a Slack channel, and are then lightly edited for publishing on Friday. In our Nth installment of our Coffee Talk series we’ll be chatting with three Power Pivot Pros. Let’s introduce the folks we brought in for this week’s Coffee Talk:

Mr Excelthe man needs no introduction. Founder of http://www.mrexcel.com/ … When you search for “bill jelen wikipedia” google returns the Wikipedia entry for “pivot tables” Damn!

Howie Dickerman

Howie Dickerman, Senior Program Manager at Microsoft, working on the Excel team. Always fighting the good fight on the Excel team to ensure that users (us, you!) have a great experience with the product.

Rob CollieFounder of the blog you’re reading right now. Shameless advocate for kicking ass with Power Pivot & Power BI.

Austin Senseman, VP Operations here at PowerPivotPro, trying to keep this company running like a well-oiled machine

Introductions

austin
2017-06-23 15:55
@austin set the channel purpose: Scheduled for 6/30/2017 @ 10:30am Central – We’re spinning up this channel to host a conversation between three Power Pivot experts – Very excited to see where this conversation goes

 

rob
2017-06-23 15:56
has joined #coffeetalk_powerpivot

 

mrexcel
2017-06-24 14:13
has joined #coffeetalk_powerpivot

 

Howie Dickerman

howiedickerman
2017-06-27 19:41
has joined #coffeetalk_powerpivot

 

austin
2017-06-29 20:28
Welcome!

 

rob
2017-07-06 20:21
i’m so excited, i’m here early :slightly_smiling_face:

 

austin
2017-07-06 20:23
Oh hey Rob! Bill and Howie are on the way.

 

mrexcel
2017-07-06 20:27
Hi Rob & Austin. I am here

 

rob
2017-07-06 20:28
wow, your first usage of Slack and you already have an avatar?

 

mrexcel
2017-07-06 20:28
I have no idea how or when I did that. Magic

 

mrexcel
2017-07-06 20:29
Maybe Slack is a pivot table fan

 

rob
2017-07-06 20:29
if it is, it prefers Compact Axis layout.

 

mrexcel
2017-07-06 20:29
Not any more!

 

rob
2017-07-06 20:30
yes yes, microsoft finally gave you a setting so that you can default to Ugly Pivots. rejoice!!!!

 

mrexcel
2017-07-06 20:30
The Excel team has freed the masses from the shackles of Compact Layout. I did rejoice.

 

rob
2017-07-06 20:31
i wonder if they’ll give you a setting to revert to 255 columns max? ya know, back to the good old days. 😉

 

mrexcel
2017-07-06 20:31
sigh

 

rob
2017-07-06 20:31
(course, with Power Pivot, i no longer can imagine needing very many columns at all, honestly)

 

mrexcel
2017-07-06 20:31
That is right… you like your tables tall and thin

 

rob
2017-07-06 20:32
bingo. but one of the reasons for needing a bajillion columns in the first place was to perform calculations that required many steps – a lot of these scenarios are super simple in DAX

 

mrexcel
2017-07-06 20:34
And the Unpivot command in Power Query makes it easy to turn the wide tables into narrow

 

rob
2017-07-06 20:34
yes, an absolute godsend of a capability :slightly_smiling_face:

 

austin
2017-07-06 20:34
Alright, alright, alright!

 

mrexcel
2017-07-06 20:35
Talking about PQ… have you seen Split By Delimiter …Into Rows?! I used it three times in week 1

 

rob
2017-07-06 20:35
ooh, can’t say that i have. sounds hot.

 

mrexcel
2017-07-06 20:36
For those situations where someone stored an invoice with a single item field: Red bike;Helmet;Water bottle;Vokda Holder

 

mrexcel
2017-07-06 20:36
Bam! 4 rows in a heartbeat

 

mrexcel
2017-07-06 20:36
I think Austin wants to start

 

Howie Dickerman

howiedickerman
2017-07-06 20:37
Sorry for the delay in getting into the session, but I’m here now. :slightly_smiling_face:

 

rob
2017-07-06 20:37
yay Howie!

 

mrexcel
2017-07-06 20:37
Hello Howie

 

Howie Dickerman

howiedickerman
2017-07-06 20:37
Hi guys

 

austin
2017-07-06 20:37
Ok just to set the stage for everyone, this is a new experiment in our “Coffee Talk” series – we’re putting industry experts together on slack to discuss a particular topic. For this week we’re discussing Power Pivot. We’ve got Rob Collie, Mr Excel, and Howie Dickerman – three of the world’s most entertaining people who also happen to know A LOT about Power Pivot.

Why is Power Pivot Amazing? Chat Amongst Yourselves.

austin
2017-07-06 20:38
So the topic is – and we can start here and just see where it goes – *why should people be paying attention to Power Pivot now?*

 

mrexcel
2017-07-06 20:38
100 million rows in an Excel grid!

 

rob
2017-07-06 20:38
you mean, as opposed to… paying attention to it 5 years ago? :slightly_smiling_face:

 

austin
2017-07-06 20:39
I mean not necessarily now vs. 5 years ago, but if you’ve never really taken the plunge into Modern Excel, what are you missing?

 

rob
2017-07-06 20:39
there are not many chances in life where you can be multiple years “late” to a party, and still be early.

 

rob
2017-07-06 20:39
this is one of them.

 

mrexcel
2017-07-06 20:39
Great point, Rob

 

Howie Dickerman

howiedickerman
2017-07-06 20:40
There’s no doubt in my mind that Power Pivot and Data Models in Excel bring capabilities that people will love once they become familiar with them.

There’s an Awareness Gap

mrexcel
2017-07-06 20:40
When I do a live seminar with people who live in Excel, 80% of the room still has no experience with Power Pivot

 

mrexcel
2017-07-06 20:40
That figure is a lot different from 4 years ago when 1 hand out of 100 would go up for “Have you heard of Power Pivot?”

 

rob
2017-07-06 20:41
that’s good. seriously, 20% raise their hands? that’s amazing progress.

 

Howie Dickerman

howiedickerman
2017-07-06 20:41
As an analogy, consider PivotTables. For many years we had some folks using PivotTables, but we had many more who had heard of them, but who thought PivotTables were for the more advanced users. There was some fear of them.

 

mrexcel
2017-07-06 20:41
Enthusiastically raise their hands, too!

 

rob
2017-07-06 20:41
anecdotally, i still think awareness is well under 20%

 

Howie Dickerman

howiedickerman
2017-07-06 20:41
Once they took the time to learn how to use them, folks would wonder how they got along without them.

What’s in a Name? 🙂

rob
2017-07-06 20:42
right on, Howie. remember my attempt to rename PivotTables? I wanted to call them Summary Tables, just to help people fear them less and understand them more.

 

Howie Dickerman

howiedickerman
2017-07-06 20:42
I think the same thing will happen with Power Pivot. Once folks learn what measures are, and how to write them, usage will skyrocket.

 

mrexcel
2017-07-06 20:42
I get people who are hoping that I will spend 9 hours of the 8 hour seminar on Power Pivot and DAX

 

rob
2017-07-06 20:43
i gave up. it was too late to make a change that drastic. but i often wonder, if the name had been different from the beginning, if adoption would have been greater.

 

rob
2017-07-06 20:43
agreed all around. awareness is problem #1.

 

mrexcel
2017-07-06 20:43
“An amazing feature that lets you summarize data in six clicks” is what I would have called them. But it wouldn’t fit in the Ribbon

 

austin
2017-07-06 20:43
haha

 

rob
2017-07-06 20:43
do a good deed today – find someone who uses Pivots and VLOOKUP. show them Power Pivot.

 

Howie Dickerman

howiedickerman
2017-07-06 20:44
Those of us who are focused on designing technology aren’t spending enough time on telling folks about what we built and how to use it, which is where you guys come in.

 

rob
2017-07-06 20:44
yep, and the marketing department of Office :slightly_smiling_face:

 

rob
2017-07-06 20:45
(who are really showing signs of coming around on this, btw – kudos to them!)

 

mrexcel
2017-07-06 20:45
So – the funny part here… I do a lot of Power Pivot in my seminars without ever saying Power Pivot.

 

rob
2017-07-06 20:45
do tell, Bill! i’m intrigued.

 

mrexcel
2017-07-06 20:45
Now that a critical mass have Excel 2013+, I show them the amazing things that happen when they choose “Add this Data to the Data Model”

 

mrexcel
2017-07-06 20:45
People have no clue they are using the Power Pivot engine

 

Howie Dickerman

howiedickerman
2017-07-06 20:46
I talk to customers today who tell me that “measures seem hard” so they just use calculated columns because of their comfort level, but the real power of this technology is in measures.

 

mrexcel
2017-07-06 20:46
But they love the features that the checkbox enables

 

rob
2017-07-06 20:46
agreed Howie. measures and relationships.

 

mrexcel
2017-07-06 20:46
Anyone who prefers Calculated Columns to X has not really tried X

Power Pivot = Money and Happiness

rob
2017-07-06 20:47
circling back to austin’s original question, i think the two most direct answers are: Money, and Happiness. those are the things that await you in Power Pivot land.

 

rob
2017-07-06 20:47
and if you aren’t “down” for more Money, and more Happiness, well… :slightly_smiling_face:

 

Howie Dickerman

howiedickerman
2017-07-06 20:47
I recall conversations with Amir about what to call measures. Then Excel thought that the term “measures” wouldn’t work and called them “calculated fields”. Then we moved back to measures to get consistency with Power BI.

 

mrexcel
2017-07-06 20:47
Then you are me…

 

mrexcel
2017-07-06 20:48
Both Measures and Calculated Fields are bad names, in my opinion

 

rob
2017-07-06 20:48
doesn’t matter if you are an Excel VLOOKUP/Pivot pro, or a business manager – both roles have more money and happiness in store.

 

Howie Dickerman

howiedickerman
2017-07-06 20:48
Yep – but coming up with an ideal name for that concept is actually much harder than it seems.

 

mrexcel
2017-07-06 20:49
How about calling them: “An amazing feature that will make pivot table calculated fields look like they were designed by someone in kindergarten”

 

mrexcel
2017-07-06 20:49
Again… lengthy for the Ribbon

 

austin
2017-07-06 20:49
Rob suggested Excel+ instead of DAX but maybe it should have been Excel$

 

rob
2017-07-06 20:49
“Best Damn Formulas on the Planet” ?

 

mrexcel
2017-07-06 20:49
Funny, Rob, but not descriptive enough

 

rob
2017-07-06 20:50
“Magic Formulas Make it Rain Money” ?

 

Howie Dickerman

howiedickerman
2017-07-06 20:50
It’s hard to comment on that, since I’ve got a bit of a vested interest in DAX. :slightly_smiling_face:

 

mrexcel
2017-07-06 20:50
“Don’t you hate when you can’t solve a problem with pivot table calculated fields? Use These Things”

 

rob
2017-07-06 20:50
I’m comfortable with that name.

 

mrexcel
2017-07-06 20:50
Find the pain. Relieve the pain.

 

mrexcel
2017-07-06 20:51
And let’s face is, who is really going to believe that a formula language is going to bring money *and* happiness?

Where are People Missing Out?

austin
2017-07-06 20:51
How about this from each of you – the 3 things that people are doing in Excel RIGHT NOW that are hugely inefficient?

 

mrexcel
2017-07-06 20:51
OMG 1. Using a calculator to sum a column of numbers

 

mrexcel
2017-07-06 20:52
2. Cutting and Pasting to line up two tables

 

rob
2017-07-06 20:52
1) Writing tons of slow VLOOKUP or INDEX MATCH just to “knit” multiple data sources together (when Power Pivot Relationships make this isntantaneous)

 

rob
2017-07-06 20:53
2) Building complex reports that cannot be modified quickly to answer variations on the original question

 

mrexcel
2017-07-06 20:53
3. SUMIFS into a kajillion rows of data to try to create a summary report

 

Howie Dickerman

howiedickerman
2017-07-06 20:53
Using autofill to create thousands of copies of the same formula, creating thousands of opportunities for bugs in the workbook

 

austin
2017-07-06 20:53
1: using access to pre-aggregate data because the data is too big for Excel

 

rob
2017-07-06 20:53
3) Coming in early in the morning just to pump the latest data through their portfolio of spreadsheets, so that their colleagues have the latest and greatest information

 

Howie Dickerman

howiedickerman
2017-07-06 20:54
I’d rename that one as: using 32bit Office instead of x64

 

rob
2017-07-06 20:54
my example #2 is the same as Bill’s #3, btw.

 

rob
2017-07-06 20:54
hasn’t 32-bit gotten a lot better though lately Howie?

 

mrexcel
2017-07-06 20:54
Those lists would’ve looked better in a spreadsheet…

 

austin
2017-07-06 20:54
2: linking lots of spreadsheets together and thne going to church every sunday to pray that the whole thing doesn’t blow up

32 bit vs. 64 bit Excel

Howie Dickerman

howiedickerman
2017-07-06 20:55
Yes – 32 bit is much better than it was, but if you’ve got a larger data set, you will still need x64, especially if you are building the data model.

 

mrexcel
2017-07-06 20:55
32 bit is better? Are they passing the hard work through to a 64-bit subset of Excel?

 

rob
2017-07-06 20:55
true story: i used to demonstrate my 300 million row workbook on 64-bit Power Pivot… on a laptop with only 4 GB of RAM.

 

rob
2017-07-06 20:56
so the improved 32-bit version of Power Pivot can certainly handle that same workbook.

 

mrexcel
2017-07-06 20:56
Why is it when you install Office 365, THE OPTION TO INSTALL 32 BIT IS MASSIVELY HUGE AND TAKES UP THE WHOLE SCREEN and the 64-bit version is hidden behind a button that says “really, you don’t need to look here”

 

Howie Dickerman

howiedickerman
2017-07-06 20:56
Not yet. 32bit and 64bit are distinct at this time. Someday we might enable an out of proc 64bit AS engine (data model) with 32bit Excel, but it’s in the future.

 

mrexcel
2017-07-06 20:57
So, this is new to me. I switched to 64-bit Excel five years ago and have never looked back. When did 32-bit get better?

 

Howie Dickerman

howiedickerman
2017-07-06 20:57
We have folks working to change the overall messaging. We need to get more folks to use x64 by default. The issue is that some customers still have old add-ins that only work on 32bit.

 

mrexcel
2017-07-06 20:58
Howie, respectfully, that is not a reason.

 

austin
2017-07-06 20:58
i was at a LARGE client recently and that was a reason

 

Howie Dickerman

howiedickerman
2017-07-06 20:58
Don’t get me wrong, I have a data model that I use every day, that works fine in 32bit, and the main data table has more than 9 million rows. But when the size of the data gets to 20 million, I’ll need to open this in 64bit Excel.

 

rob
2017-07-06 20:58
there was an improvement made to Excel 2016 so that 32-bit can utilize more RAM than it used to. and the same thing was made available as a patch for Excel 2013. (no fix for 2010).

 

rob
2017-07-06 20:59
we should include a link to that information here i think :slightly_smiling_face:
(when we publish this chat)

 

Howie Dickerman

howiedickerman
2017-07-06 20:59
Yes – 32bit can access a 4GB memory space as opposed to the older 2GB limit. (It’s still a 3GB limit on certain versions of the operating system,)

 

rob
2017-07-06 21:00
And in practical terms that is TREMENDOUS.

 

mrexcel
2017-07-06 21:00
So, we are letting a few customers who have very old add-ins cause a bad experience for hundreds of millions of people who should be embracing Modern Excel

 

rob
2017-07-06 21:00
considering that i used to have a computer with 4 GB RAM, *total*, and it could run my 300 million row demo.

 

austin
2017-07-06 21:00
when you say it that way ….

 

mrexcel
2017-07-06 21:01
But Rob, you don’t have a computer with 4 GB today, do you?

 

rob
2017-07-06 21:01
Howie is right, 64-bit is still better than 32-bit. but 32-bit is now Completely Viable, whereas it used to be Completely Unusable.

 

Howie Dickerman

howiedickerman
2017-07-06 21:01
Actually the number of corporate users who have old add-ins isn’t small. But over time this will change.

 

rob
2017-07-06 21:01
not any longer, no. but if i did… 32-bit would be just as good as 64-bit, roughly speaking.

 

mrexcel
2017-07-06 21:01
My car would never run on two wheels. But – now I have three wheels so everything is fine.

 

rob
2017-07-06 21:02
(which was definitely NOT the case for most of our history)

 

rob
2017-07-06 21:03
if we’d had these fixes from the beginning the importance of this issue would be 5% of what it was previously.

 

mrexcel
2017-07-06 21:03
We’ve had Four-Wheel Cars for Five Years. Why is the default not 64-bit for everyone with a down-grade path for the people stuck in the past

 

rob
2017-07-06 21:03
people “stuck in the past” unfortunately have lots of money :slightly_smiling_face:

 

rob
2017-07-06 21:04
let’s talk career overhaul. massive opportunity. huge impact on bottom line.

 

Howie Dickerman

howiedickerman
2017-07-06 21:04
Another reason to use this technology now is that we’ve recently done a lot of work to address a set of bugs that had piled up in this area. A lot of features had been added quickly, and there were issues with the way some of those features were integrated into the product. We went back through the product and identified and fixed a lot of issues. The overall experience is now much more stable.

 

rob
2017-07-06 21:05
YES, i can attest to that. you folks have done some amazing work there. so much more stable these days, the 2016 builds are (sayeth Yoda)

 

Howie Dickerman

howiedickerman
2017-07-06 21:05
Automatic formula fixup in DAX formulas is reason by itself to upgrade from Excel 2013 to Excel 2016.

We’re 4 Million Users Going On 10 Million

austin
2017-07-06 21:05
back to this issue of massive impact, if Power Pivot were a religion and you had to accumulate 10 million followers in the next year, what would you say?

 

mrexcel
2017-07-06 21:05
Rob – the target customers for Power Pivot can’t imagine or believe career overhaul.

 

rob
2017-07-06 21:05
Make Spreadsheets Great Again?

 

mrexcel
2017-07-06 21:06
They have to get their reports out.

 

austin
2017-07-06 21:06
haha

 

mrexcel
2017-07-06 21:06
They can’t see around the corner to Career Overhaul

 

austin
2017-07-06 21:06
if awareness is problem #1, time pressure is #2 in getting people to use the new tools

 

mrexcel
2017-07-06 21:06
We just need to entice them on the train with “This will make your current job hassles easier”

 

rob
2017-07-06 21:07
Howie and Bill, have you seen this pilot video we made? https://youtu.be/Jdh7wjIvlio

 

rob
2017-07-06 21:07
(it’s only two minutes long, if you wanna go watch real quick)

 

mrexcel
2017-07-06 21:07
I see Power Query as a gateway. Do you current job massively faster. And oh, by the way… there is a Load to Data Model thing that we will explain next week when you no longer have to spend an hour a day updating your reports.

 

Howie Dickerman

howiedickerman
2017-07-06 21:08
I think it’s incredibly powerful to take some data that the customer already has and knows, and demonstrate a PivotTable slicing and dicing that data within a few minutes of starting out. This stuff is really easy and fast with the notable exception that it takes some time and some practice to get good at authoring measures.

 

mrexcel
2017-07-06 21:08
You lost me at 0:18. I stopped believing you at 0:18.

 

rob
2017-07-06 21:09
oh boy do tell.

 

Howie Dickerman

howiedickerman
2017-07-06 21:09
I see Power Query as a separate but complementary capability that will make a huge difference for anyone who imports data from anywhere.

 

Howie Dickerman

howiedickerman
2017-07-06 21:09
I haven’t seen the video yet, but will take a look.

 

mrexcel
2017-07-06 21:09
It is a spreadsheet. At 0:17 when you say this will change my life, I refuse to believe that a spreadsheet will change my life.

 

rob
2017-07-06 21:10
ironic, considering who you are :slightly_smiling_face:

 

mrexcel
2017-07-06 21:10
Realistic.

 

rob
2017-07-06 21:10
Mr…. What, exactly?

 

austin
2017-07-06 21:10
seems like out of the 6 trillion people in the world, spreadsheets have changed your life the most

 

mrexcel
2017-07-06 21:10
But at 0:35, now you are talking my language again. I am starting to wonder if maybe I should believe you, even after your outrageous lie at 0:17

 

rob
2017-07-06 21:11
:slightly_smiling_face:

 

austin
2017-07-06 21:11
haha

 

mrexcel
2017-07-06 21:11
I don’t think you lead with Change My Life.

 

mrexcel
2017-07-06 21:11
Lead with we will make this painful part of your life less painful

 

mrexcel
2017-07-06 21:11
I will buy that

 

rob
2017-07-06 21:11
noted. this IS a pilot episode after all.

 

Howie Dickerman

howiedickerman
2017-07-06 21:13
I think the ability to eliminate all the vlookups is only a small part of the win. This really is a full blown database sitting inside a workbook, and there are all the advantages of any database. Of course if you’ve never used a database before, you may not care. (You don’t know what you don’t know.) But the stuff that can be done when you have strong data typing, a good formula language that uses a syntax you already know, and the other benefits can be pretty amazing. Getting rid of vlookups is only one of many benefits.

 

mrexcel
2017-07-06 21:13
Here is another thing: I am intrigued at 1:30ish, but then you tell me that the tools to make my life better have been around since 2010? Don’t say that. That makes me feel stupid.

 

mrexcel
2017-07-06 21:13
Say it is brand new.

 

mrexcel
2017-07-06 21:14
Say it was around for 64-bit weirdos but now works for the masses

 

rob
2017-07-06 21:14
agreed Howie, this would just be the first of a series of these

 

rob
2017-07-06 21:14
i need to write down Bill’s feedback, this is good

 

rob
2017-07-06 21:14
launching OneNote…

 

mrexcel
2017-07-06 21:15
“Yay” says the co-author of one of the top 3 best-selling books on OneNote

 

Howie Dickerman

howiedickerman
2017-07-06 21:15
By the way, there are still a LOT of folks who don’t use PivotTables because they are intimidated by them. And if you don’t use PivotTables, you’ll have trouble caring about Power Pivot. I think we need to raise awareness of PivotTables first, and then that community of folks will be able to benefit from Power Pivot.

Most People Aren’t Using Pivot Tables, but MANY People Are

austin
2017-07-06 21:16
There’s a chasm there between the non-pivot table and pivot table crowds – that’s why we say that even though Excel has lots of “advanced” functionality, a person is advanced if they’re using pivot tables

 

mrexcel
2017-07-06 21:16
Those people who don’t use Pivot Tables yet are importing ugly data and manually cleaning it. Again, I think that PQ is the on-ramp to get people in pain into Power Pivot

 

austin
2017-07-06 21:17
i could get behind that

 

rob
2017-07-06 21:17
Howie I disagree. Pivot saturation is incredibly high, on an organization basis.

 

rob
2017-07-06 21:17
Everybody’s got at least one Pivot Pro.

 

mrexcel
2017-07-06 21:17
But 40% of Excel customers have never entered a formula.

 

austin
2017-07-06 21:17
enter data gene discussion

 

Howie Dickerman

howiedickerman
2017-07-06 21:18
In the companies that are bringing in Power Pivot consultants that may be true, but in the larger community of Excel users, PivotTable users are clearly a minority.

 

mrexcel
2017-07-06 21:18
(per SQM data). So, yes – I believe 67% of Excel customers have never heard of pivot tables

 

rob
2017-07-06 21:18
I think BOTH are good missions in parallel. Continue the Pivot Awareness campaign. But don’t sequence it. Tens of millions of Pivot Pros out there who don’t know about Power Pivot – I’d argue this is the single most valuable opportunity for the entire Office organization right now.

 

rob
2017-07-06 21:19
Pivot usage develops by necessity. I’ve learned a lot since leaving MS :slightly_smiling_face:

 

mrexcel
2017-07-06 21:19
I don’t even want them to create measures at first. Click the button that says “Add this data to the data model” and life improves

 

mrexcel
2017-07-06 21:20
Here is a funny feature that I will put on http://Excel.UserVoice.Com: As soon as someone creates their fifth lifetime pivot table that includes “Add This Data to the Data Model”, I think Excel should pop up Rob’s video.

 

mrexcel
2017-07-06 21:20
Those people…. they are ready

 

Howie Dickerman

howiedickerman
2017-07-06 21:21
There are certainly a lot of folks who have Excel workbooks with many rows of data in them. And much of that data needs cleansing and transformation. And analyzing that data is what they’re planning to do. And folks who aren’t familiar with the newer features will do some very tedious manual steps without realizing that by using a combination of Power Query and PivotTables, and Power Pivot they could have something that does more for them, in a fully repeatable way, with less work. But again, we don’t know what we don’t know, so it’s all about getting the word out.

 

austin
2017-07-06 21:21
@howiedickerman I had a good talk recently with Ash about subtle ways to cue Excel users to try the new features based on what they were doing in Excel

 

austin
2017-07-06 21:22
very similar really to what Bill just suggested

 

mrexcel
2017-07-06 21:22
The Excel 2013 features like “Recommended Pivot Tables” and the “Quick Analysis Thingie” are supposed to make more people try pivot tables. But I think they are too subtle.

 

Howie Dickerman

howiedickerman
2017-07-06 21:23
Yep – this is something we’ve had folks looking at. We’d like to let people know that there might be a better way to accomplish what they just did. But we have to be careful not to be overly intrusive in the experience. It’s a delicate balance and there could be a valid reason for doing something in a particular way.

 

rob
2017-07-06 21:23
I’m Not Making This Up: I just moderated a comment wherein someone is reporting back after running the “illegal” 32-bit patch on Excel 2010, and they are Incredibly Happy: https://powerpivotpro.com/2015/05/grab-bag-of-fun/#comment-284921

 

mrexcel
2017-07-06 21:23
Jane may have had the correct approach with Flash Fill. Someone starts doing something tedious and then presto – Magic Happens

 

rob
2017-07-06 21:24
(and I mean, i approved that comment 30 seconds ago)

 

mrexcel
2017-07-06 21:24
Nice coincidence

 

mrexcel
2017-07-06 21:25
Unless you are approving similar comments every hour…

 

mrexcel
2017-07-06 21:25
Any time that someone has more than 50 SUMIFS in a workbook, a pivot table video should appear

 

mrexcel
2017-07-06 21:25
Any time that there are more than 200 VLOOKUPs, a Data Model video should appear

 

rob
2017-07-06 21:26
@rob uploaded a file:

 

Howie Dickerman

howiedickerman
2017-07-06 21:27
I’ve met people who create a table, add a totals row, then copy the totals row to another place, change the filters, copy the new totals row, change the filters, etc. They never knew PivotTables existed until I showed it to them. And PivotTables have been around for a long time. (And quite usable by mere mortals starting with Excel 2007.)

 

mrexcel
2017-07-06 21:27
Clippy wasn’t bad-ass enough. The new Clippy 2.0 shouldn’t have Go Away Please.

 

rob
2017-07-06 21:28
from https://www.pinterest.com/pin/162551867772982475/ if we want to include it :slightly_smiling_face:

Wrap It Up!

austin
2017-07-06 21:28
:slightly_smiling_face: ok so what I’m taking away from this is, since we’re headed into the “let’s wrap it up” phase … there probably is a big life-changing Power Pivot story here for people doing Excel the old way BUT folks still aren’t aware of the tool. Instead of focusing on that big story, how can me make it as simple as possible to get people started with the new way? Lots of good ideas on all fronts.

 

rob
2017-07-06 21:29
i’m a big fan of re-visiting good ideas from the past that were just Too Soon and/or Poorly Executed. i am not at all opposed to the things Bill and Austin are suggesting, but like Howie, I just think we need to be deliberate about it.

 

mrexcel
2017-07-06 21:29
Austin – yes.

 

rob
2017-07-06 21:30
yes? that was some serious insight :stuck_out_tongue:

 

austin
2017-07-06 21:30
haha

 

mrexcel
2017-07-06 21:30
I know we are near the end, but every CEO needs to be a guy I will refer to as RFM.

 

rob
2017-07-06 21:30
i need a popcorn emoji

 

mrexcel
2017-07-06 21:31
RFM was a guy who was very public about this policy: If you, dear employee, find a way to eliminate your job, I will *never* fire you. I will fire the guy next to you and let you work on eliminating *his* job

 

mrexcel
2017-07-06 21:31
People are afraid that if they find a faster way to do something, they will be out of a job

 

mrexcel
2017-07-06 21:31
Every CEO needs to communicate that innovators will not be punished

 

Howie Dickerman

howiedickerman
2017-07-06 21:32
When I show a demo based on demo data, it’s mildly interesting, but showing the same demo with the customer’s data really seems to turn the lights on. If folks are asking “How did you do that?” they’re motivated to dive in and figure out what they need to learn. But until they see it with their own data, it doesn’t have the same impact.

 

rob
2017-07-06 21:32
Agreed Howie. nothing converts quite like just going and building something that blows people away.

 

rob
2017-07-06 21:32
HDYDT – “How Did You DO That”

 

mrexcel
2017-07-06 21:33
Thanks – MrsExcel wants me to light the grill.

 

mrexcel
2017-07-06 21:33
I enjoyed being here

 

rob
2017-07-06 21:33
go be MrGrill

 

rob
2017-07-06 21:33
you wear many hats.

 

Howie Dickerman

howiedickerman
2017-07-06 21:34
I think we do this one customer at a time. I’m always looking for an easier answer, but I don’t think it exists. Eventually, word of mouth about how this stuff rocks will drive much broader adoption. We’re really only at the beginning of this trend.

 

rob
2017-07-06 21:34
Agreed again.

 

Howie Dickerman

howiedickerman
2017-07-06 21:34
Thanks guys for putting this together!

 

rob
2017-07-06 21:34
thanks for coming everyone, this was fun

 

rob
2017-07-06 21:34
:slightly_smiling_face:

 

Howie Dickerman

howiedickerman
2017-07-06 21:35
I’m going to go shop for a tricycle. :slightly_smiling_face:

 

austin
2017-07-06 21:35
To everyone reading, drop us a line in the comments – this isn’t the end of this conversation, it’s just the beginning. Thank you @mrexcel , @howiedickerman & @rob for participating!

 

rob
2017-07-06 21:35
Howie did you see what happened to me on a scooter last year?

 

austin
2017-07-06 21:35
haha

 

Howie Dickerman

howiedickerman
2017-07-06 21:35
No…?

 

austin
2017-07-06 21:35
plz do not share those pictures, they are gross

 

rob
2017-07-06 21:35
stick around just one more minute :stuck_out_tongue:

 

rob
2017-07-06 21:36
howie are we friends on facebook?

 

Howie Dickerman

howiedickerman
2017-07-06 21:36
I don’t use Facebook.

 

rob
2017-07-06 21:36
respect.

 

rob
2017-07-06 21:36
austin, avert your eyes

 

austin
2017-07-06 21:37
*averted

 

rob
2017-07-06 21:37
@rob uploaded a file:

austin
2017-07-06 21:37
agghhhh, no i looked

 

Howie Dickerman

howiedickerman
2017-07-06 21:37
Sorry to see that!

 

rob
2017-07-06 21:37
me too! that said, it was not nearly as bad as my knee injury.

 

rob
2017-07-06 21:37
short version: never let me drive anything with two wheels and an engine.

 

austin
2017-07-06 21:38
i’m signing off, pleasure spending time with this group!
  Subscribe to PowerPivotPro!
X

Subscribe

Austin Senseman

Austin spent the last five years using Modern Excel in the field to solve analytical challenges for the financial services industry, first at BBVA and later at Harbert Management Corporation. As a former analyst, Austin is focused on enabling other data professionals to experience the joys of Power Pivot & Power BI. Austin is a CFA Charterholder, former CPA, Excel Power User, and SQL Server Professional, whose involvement in data/analytics projects lowers the communication costs between business users and IT professionals and helps organizations get to solutions faster. 

This Post Has 22 Comments

  1. Ok, stupid question, I have a MS Office Standard 2016 and I can’t find power pivot, I have Power Query aka “Get and Transform”, and can’t find an add-in. I can almost understand them not including Power Pivot in my Office 365 Home, but in the vanilla MS Office? Grrrrrr…Been using Pivot tables since around 2010, and would like to make the step up, while I’m dinking around with Power Bi in tandem.

  2. Intimidation might be the biggest hurdle.

    Somewhere between 98 and 01 I purchased a Pivot table training disc from someone. Maybe via the Excel Addict. Back then, Francis even called me to help me through a problem as I was self teaching Excel and stuck working in L123. I might have gotten about as far as putting it into my machine but, if I found the box it ended up in when I left that nursery, it would be a 50/50 shot that is was open.

    Fast forward a decade…I left the nursery to work for and implement nursery software. Somewhere in the North American summer of 2011 a guy in New Zealand showed me a set of Pivot tables he was using to track his Acer production and I was blown away. I remember bungling around on a copy he sent me and royally re shaping things without a faintest clue as to what I was doing and thinking it would be nice if I had some time to figure things out.

    Then a few years went by, I had bailed on software and was back at the nursery. I found Rob via Chandoo via Mr Hayes and although I know they are different, inserting the word “Power” before the word Pivot, might have intrigued me enough to set some things aside for a day or so and dig a little more.

    On 09/24/13, I hit a stumbling block reached out to Rob and he helped me out just like Francis had in 99…he even said “BTW I love that you are using this with nursery data. A first for me.” The issue was a formatting issue of a date column. Getting the help I needed and getting over that little stumbling block turned me into a PP freak.

    I say all of that because something about a pivot table was super intimidating each time I was exposed to them but, I knew there was some power in there and I remained curious enough that when the word Power got inserted, it didn’t even matter that it was a different beast. I knew I needed more power. Cutting and pasting a set of filtered total rows to hand build a little table to get someone an answer about something was keeping me busy but, pretty mundane.

    I have thought on how to get it to the masses and you guys are the answer. Keep on preaching. The choir will keep singing, and exponentially, more will hear and then sing the song. People like myself will eventually get curious enough or run into someone like me who won’t stop talking about it.

    Stay available. Work the forums every once in a while. You guys are the Rock Stars of Modern Excel. Getting help and answers from you guys is like reaching out to Bob Dylan and getting his help on a rhyme.

    MS could add a simple questionnaire when opening to make all of the tabs hip generationally. Emoji tabs, “The Shizzle”, “You’re too old for this”? Click the PP tab and the promo video runs is not a bad idea but, I also agree with Bill. I think that the promo video needs to start with Frankentable nightmare scenario and then make my life better. People relate and kling to their fears easier than they relate to what they love

    Coffee Talk should also have a totally irrelevant wildcard discussion section always prompted by whatever someone said that is mandatory for all to respond to. For example “How do you light the grill?” Maybe revolutionary suggested questions… coffetalkquestions@ppp,com What’s your favorite color? What time is it in East Timor right now? What Is the Airspeed Velocity of an Unladen Swallow?

    1. Thanks for the kind words. We love what we do, and we’re absolutely working in a wild card question into the coffee talks 🙂

    2. I love the phrase ‘modern Excel’. i think that a lot of people think that they know what Excel is capable of (even while they acknowledge that they haven’t fully mastered its’ capabilities). I think we need a campaign analogous to ‘this is not your father’s Oldsmobile’–“this is not your boss’ Excel!”.

  3. Just a quick note about Howie:
    At the MDIS this past June I had a 15-minute Ask the Experts session scheduled with Howie. The problem was far too complex to tackle in 15 minutes so Howie spent next 90 minutes going over every aspect of my data model and giving me a master class in the process. In fact, he only ended the session because he had to run off to give a presentation. Truly one of a kind. Thanks Howie.

    1. Howie brings the energy and passion for these tools. That doesn’t surprise me at all. He inspires me every time we interact.

  4. “Summary tables” would have been so much better. Branding is everything. “Pivot” is what is happening to your data; “Summary” is what the person is trying to do.

    If Netflix was called “Streaming TCP/IP Packet Flix”, would it have garnered the same market share? :thinking_face:

  5. Mr. Excel, if you caught the recent video of Rob on the Walmart supplier channel (8th and Walton), you’d see him calmly listening as the interviewer stated that he was interested in this “new” Add-in for Excel called Power Pivot. Although PowerPivot has been around for years, Power Pivot is relatively new – note the space.

    I have an idea to increase visibility. Instead of instructing Excel-only users to select the ‘Add this data to the data model’ button while working on a regular pivot table, a warning could be given that only advanced Excel users are to select the ‘Manage Data Model’ command button on the Data ribbon. The ‘Manage Data Model’ button opens Power Pivot on machines where Power Pivot is available, but not active. Going into the background to add Power Pivot can be intimidating, but anybody can hit an existing button. Well, any advanced user can. Why ask why? Hit the button!

    By the way, congratulations to PowerPivotPro for being recognized as a Microsoft Power BI Gold Partner.

  6. Good conversation gentlemen. We all share the same frustration with the lack of awareness and adoption. For me there are 3 main hurdles to overcome:

    1. Awareness: The number of companies that send staff on Excel training is pretty small, the number that send them on a course more than once every 3 -5 years is very very small. Companies that employ a machine operator would ensure they are trained on that equipment. Companies employing office staff see Word, Power Point and Excel skills on a CV and say “tick”.

    2. Decision Makers experienced Excel 15 years ago. The majority of business leaders are of an age that they would have learned Excel 2003, maybe Excel 2007. Excel is not top of their mind when thinking of solutions to reporting and analysis problems. Power BI is catching their eye and this is actually great because you can then say “by the way, did you know a whole bunch of this functionality is now built into Excel – for free! ”

    3. After overcoming awareness you are hit with “system” limitations. Several of our big listed company clients only moved to Excel 2016 in January this year (they are now loving Power Query and Power Pivot btw). Many medium sized ones are on Excel 2010 with plans to move to Office 365 “soon”.
    Only one client has the entire company with 64 bit office installed on everyone’s machine. One. All the others have special set ups and work-arounds for their staff who need 64 Bit due to legacy system compatibility.

    Conclusion from me…. The growing adoption of Office 365 will make companies able to use Power Pivot and will also hopefully act as a trigger for companies to train their staff in the latest technology ( maybe wishful thinking). The growth of Power BI will, by osmosis, increase the awareness of Power Pivot. Not fast enough for our liking though.

    1. In training we always get a show of hands “Excel 2010? Excel 2013? Excel 2016?” 12 months ago it was 30% 2010 60% 2013, 10% 2016. Now it’s almost 100% 2016. Office 365 is making a huge difference.

  7. Thanks for this. I have dabbled with PP for the last 2 years but never spent enough time in there to really leverage the data. ….. but after reading through this Coffee talk, I will make a note to self …” go back to PP and use the power 🙂

  8. I like this Coffee Talk feature and look forward to the next few week’s blogs. Awareness is a big part of the problem I think. I’ve been the acknowledged Excel Guru (and Queen of Pivot Tables) in all my workplaces for the past 6 years but had never heard of PP until late last year. Even then, I only found PP because my new employers wanted to use Power BI so I bought Rob and Avi’s book. I’d actually attended a Power BI demo from a Microsoft account manager prior to this, but myself and the others who attended all walked away thinking it was just another way to build the same graphs as we could get in Excel, not a whole different beast. PP is the most awesome thing I’ve seen in 15 years of management accounting but I’m the only person I know who is using it (although I’m preaching PP to every other Excel user I know). The DBA I’m working with on our BI project is selling it to his IT friends too, and one other business has already followed us down the path of restoring PP models to SSAS and using them in Power BI. My DBA colleague has been part of many data warehouse projects with massive price tags and sub-optimal results and was blown away that we could get a prototype data warehouse up and running in a month (this includes the time him and I spent surfing the ‘net to work out how to use PP, Power Update and SSAS).

    By the way, Making Spreadsheets Great Again made me choke on my coffee (it was totally worth it).

    1. Pivot Table Queen 🙂 “but myself and the others who attended all walked away thinking it was just another way to build the same graphs as we could get in Excel, not a whole different beast.” Aggh, I know, we’ve seen a lot of this. You’ll be pleased to know that MS is aware this is an issue and we’ve partnered with a group of MS salespeople to help them shape the PBI message and demos to Pivot Table users.

  9. I thought I would stop reading like after a couple of minutes but I just couldn’t. You are discussing really interesting subjects, at least for me. I don’t think about me as an expert in PowerPivot/Power Query but I can already do a lot with those. I learned from you today for example that in Excel 2016 we could get ” Automatic formula fixup in DAX formulas”, I immediately wrote with our IT Department :). Thank you!

    1. Yea! Glad to know you liked it. Keep coming back and pretty soon you’ll be an expert – that’s how it worked for me 🙂

  10. Just found this CoffeeTalk channel (thanks to the email blast) so good job getting the word out. I am deep diving into PP and POwerBI. I see a huge opportunity for small business and not-for-profits to be able to access some of the same capabilities and sophistication as the ‘big boys’ have. (And by the way, having worked in a ‘big boy’ multinational, multi-billion corporation, I am not so sure that even large organizations are leveraging these capabilities the way they should be. The key, to me, is that anyone who uses Excel should be trained on PP and PowerBI. After all, it isn’t THAT big of a leap.

Leave a Comment or Question