In-Person 2-Day Class, May 12-13, Cleveland

April 17, 2015

Post by Rob Collie

2-Day In-Person Power Pivot Class

Hey folks, just a quick reminder that I’m teaching a class next month here in Cleveland.

(Someone registered today from New Mexico!  If they can make that trip, anyone can.)

Optional Gathering/Dinner on “Middle” Night


Some Folks from the August Class Throws the “Excel Gang Sign” at Dinner
(Can You Spot MrExcel Himself, Bill Jelen?)

On the night of the first classroom day, we’ve been running an informal gathering at a local restaurant, and many people reported that it was one of their favorite social outings ever.  Just being surrounded by people with similar backgrounds, mindsets, and problems is a unique experience, especially for the Excel crowd.

Totally optional, but we’ll be doing this at all classes.

Included:  Thumb Drive and 50% Off Online University

All Students Receive a PowerPivotPro thumbdrive containing the materials from the course:


And also a 50% off discount code to the online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we don’t have time for in two days), so we have decided to offer it as a bundle.

May is actually a great time to visit Cleveland too.  Hope to see you there.

Click Here for More Info and Registration

Another Previous Cleveland Class

“Beta Test” the New Reference Card?

April 7, 2015

Post by Rob Collie

Power Pivot / Power BI / DAX Reference Card

RefCard Pt 2: Captures/Condenses Important Tips & Fundamentals from Our Years of Teaching
(Five “Sides” Pictured Above, Sixth “Side” is Still in Development)

PDF Preview Available Later This Month

Short Version:  drop us an email at the address below, and later this month we will send you a preview of the new reference card in PDF format.

Drop Us a Note to Get Your Preview Copy

“Completing” the First Ref Card

Creating the first reference card might be the smartest thing we’ve ever done. 

We can say that without hubris, because it was actually NOT our idea, it was Bill Jelen’s.

Still though, we have the wisdom to recognize Bill’s genius.  The reference card has been a CRITICAL component of our classroom teaching for years now:

Power Pivot / Power BI / DAX Reference Card

Ref Card Part 1 (Laminated Version) is Only $2.95 at – Click Image
(Preview Participants will Also Receive a PDF of Part 1)

Final “Product” is TBD

1) Given that the new reference card is six sides, it ideally would be printed in some sort of “tri-fold” laminated format, and in full color of course.  That’s might be pretty expensive to produce, so we’re still noodling on how to do it.

2) Plus, it would make sense to merge it with Part 1 to form something more like a quad-fold.  Hmm.

3) We want your feedback on Part 2 so we can improve it before we do anything rash like print 5 thousand copies.

4) I’m also contractually obligated to say that we WILL improve the aesthetics of Part 2 as well.  Because, well, *I* am designing Part 2, and while I might be a decent communicator, I’m not exactly a graphic designer.

Anyway, Sign Up for the Free Preview PDF Smile

Drop Us a Note to Get Your Preview Copy

Best way to learn Power Pivot: get over analysis paralysis

March 30, 2015

I have loved reading ever since I was a young kid. In my adult life though, reading fell on the wayside, as life got busy. Now, I have worked as a Business Analyst for a long time, and the role suited me naturally. However at times I fall victim to analysis paralysis. My inability to get my reading fix, was one casualty. Let me explain.

I felt that since I was soooo busy, I had to make the best use of my time. I wanted to pick the BEST business books, the BEST self-help books, the BEST fiction books that would enrich me. Unfortunately that meant, I would occasionally go on the hunt for the “best”, give up after a while, and repeat the cycle; without ever actually reading many books.

It was years before I realized my folly. The way out was the “Librarian’s Picks” section at our local library. I would trust their judgment, and started picking several books from that section. I did more reading in that one year than I had done in the previous ten years.

If you are anything like me, you have thought about attending Power Pivot training, but have vacillated for this long while. Let me say, a Live Instructor-Led class is the most intensive way to jumpstart your Power Pivot journey. There is still time to enroll in our class on Apr 1-2 in Seattle.

Live In-Person Class
Apr 1-2

PowerPivot for Excel Live Class (Seattle)


Power On!
Avi Singh

Course Prep: Excel 2010 and Excel 2013 PowerPivot Differences

March 20, 2015

By Avi Singh

Matt Allington, our friend from down under, had suggested the idea that we make some basic information available as course prep to students who enroll in our Live Instructor-Led classes (Upcoming: Seattle Apr 1-2, Online Apr 6-7, see Choosing your training option). Offering a course prep would enable us to get to the real fun stuff even quicker. I have taken that to heart and publishing some information in this post below and a Course Prep playlist on our YouTube channel. This is meant as a prep for students enrolled in our class, but may be helpful to others as well.

Course Prep Video Playlist (more videos to be added soon)

Key Differences between Excel 2013 and Excel 2010 User Interface

Launch Power Pivot
Create New Measure (Calculated Field)
Edit Measure (Calculated Field)
Insert a Pivot Table connected to Power Pivot Model
Add Excel Table to Power Pivot
Power Pivot Home Tab
Power Pivot Existing Connections

Launch Power Pivot

PowerPivot > Manage
PowerPivot > PowerPivot Window

Read the rest of this entry »

Thanks for attending the *beeping* webinar: Q&A From Webinar

March 18, 2015

By Avi Singh

Folks, thanks for attending our webinar. We did have a full house with 200 attendees. Here is how things started…

“Welcome to our webinar: Excel to power pivot: *BEEP* the gap. A lot of users *BEEP* Excel but don’t *BEEP* Power Pivot. We’re here to *BEEP* you up about the possibilities that Power *BEEP* offers…”

Okay, that wasn’t much fun. For some truly funny unnecessary censorship watch “This Week in Unnecessary Censorship” (Caution:  If you’re at workplace, you might want to use headphones).

I did sort out the matter eventually with the help of Citrix Support. Apparently there were issues with the last automatic update of GoToMeeting software. Uninstall/Reinstall restored my ability to turn off those annoying beeps.

Q&A from the Webinar

We published the Q&A from the last webinar and I’ll try to keep that up. So here’s the Q&A from the session on Mar 17th.

TIP: If you have other questions, use the search box. We have 750+ blog posts and most likely you will find something helpful

List of Questions (click to jump to answer):

Q: If the tables are not related, can you create a Pivot Table for each unrelated table separately?
Q: Why wouldn’t you use Microsoft Access instead?
Q: How can we automate the refresh of a Power Pivot report without using server components?
Q: How many rows/columns can Power Pivot handle? My data set is wide and flat
Q: How easy is it to share Power Pivot data with non-Power Pivot users?
Q: What are good practices for preparing the tables? The tables can have blanks, right?
Q: In my calculations, Can I exclude some of the rows where the information is not applicable and calculate for the remaining rows?
Q: Can we combine/leverage VBA with Power Pivot?
Q: How do you filter out certain fields to not bring in to PP?
Q: Why is PP not available on Excel 2013 Professional?
Q: Many questions around Date table and creating Date tables
Q: Other questions discussed
Q: Will webinar be recorded?   

Q: If the tables are not related, can you crate a Pivot Table for each unrelated table separately?
D. van Eyl

It is very common to have multiple data tables distinct from each other in the same model. Example Sales data, Budget data, Inventory data, Website traffic data etc. It is valuable to keep these data sets in one model so you can analyze them together.

Your data model should not look like mushrooms where each data set has its own lookup tables

Multiple Data Tables should NOT look like mushrooms

It should look more like a web with a set of data tables and a set of lookup tables. With the data tables connected to the relevant lookup tables.

Read the rest of this entry »

Excel Users Everywhere: Free PowerPivot Webinar Tomorrow Tue Mar 17th

March 16, 2015

By Avi Singh

Excel users are everywhere! Even at Toastmasters – a nonprofit educational organization focused on communication and public speaking. The other day, I had dropped in at our local Toastmasters as a guest, after a long break. It felt good to be back. The meeting had the familiar structure – speakers, evaluators, table topics. When it was over, as we were heading out, I struck up a conversation with another attendee. As members come from varied backgrounds, I didn’t know how technical I could get when describing my vocation. Thus, I spoke in general terms about my work at Microsoft, then leaving Microsoft to pursue a path of my own. And then I asked…

Me: So what do you do?
FTM (Fellow Toastmaster): Well, my job title says Quality Assurance, but that doesn’t quite describe what I do.

Me: Happens all the time, so what do you really do?
FTM: Well, I do a lot of data analysis.

Me (excited now): What tools do you use?
FTM: My company has a lot of data stored in these large databases, but they didn’t want to make a big investment in BI. So I just pull the data and analyze it in Excel.

Me: Did you know that Microsoft has released a new set of tools, called “Power BI”, which supercharges Excel?
FTM: Can it handle multiple data sources?
Me (trying to keep off the smug smile): Yes, indeed.

FTM: Can it handle large data sets?
Me (giving up now, a wide grin plastered on my face): It can do that and a lot more. You should attend one of our upcoming Webinars…

Free Webinar: Excel to Power Pivot – Cross the Gap

I have seen how transformative this path can be. From my own journey (read or watch) and from many others I have witnessed since joining PowerPivotPro. I believe the three steps below are needed to really set a person or organization on this path:-

  1. Awareness of Power Pivot and it’s superpowers
  2. Learning basic Power Pivot
  3. Using Power Pivot with your own data set

Typically after the third step, you have experienced the power and are motivated to pursue this path! But there are many who never make it to that step. My mission is to change that for as many people and organizations that I can.

Join me for our upcoming webinar tomorrow, where I lead you to the promised land :-)

Mar 17

We’re planning the next event in April covering a new topic. Subscribe to our blog to receive updates on future events.

Listen to Me Interview MrExcel (Bill Jelen), and See Us Both at the PASS Biz Analytics Conference

March 10, 2015

Post by Rob Collie


I Interviewed Bill Via Phone – Me in the Arctic of Ohio,
Bill in his Winter HQ Down in Florida

It’s not often that you’re asked to interview one of your friends, but that happened to me last week.  The folks at PASS thought it would be interesting if I interviewed Bill Jelen, and then we published that interview on the PASS Business Analytics Conference site.

An excuse for me to call up Bill and jaw for awhile?  “Sign me up!”  And off we went.  I was personally “hooked” by the conversation for a full 40 minutes.  Most of Bill’s answers, I had never heard before, and they stimulated some serious “wow” thinking for me.

We found out, afterwards, that the PASS folks were expecting a 10-minute interview.  Har har.  Bill and I later chuckled, in email, at the futility of such expectations.  “Let’s get two of the biggest chatterboxes we can find, put them on the phone together, and keep them to 10 minutes.”  But the results were good, and PASS decided to publish the whole thing.

They were also gracious enough to share the audio file with us, so I’m posting it here today:

(Fancy Inline Audio Player Should Work on Most Browsers, Or Let You Download the MP3)

Highlight:  Bill STARTED in Analytics!

I can’t believe I had never heard this before, but Bill first started using spreadsheets as a reporting tool.  (Not as an accounting or financial tool).

Even more delicious:  he was using spreadsheets as a replacement for an extremely expensive reporting solution that didn’t work!

That was in the 1980’s.  How little has changed between now and then!  Before the advent of what I call “Modern Excel” (aka Power BI), the world was in EXACTLY the same state 20+ years later.  (And in places that have yet to grasp the potential of Power Pivot and its kin, things STILL have not changed.  But at least now we have a way out of the decades-old mess).

Anyway, definitely listen to the interview.  Lots of interesting nuggets in there, such as the observation that more than half of analytics pros are women.

See us both at PASS BA Conference Next Month!

It’s not too late to join us both, plus other Modern Excel experts, at the PASS Analytics conference next month in Santa Clara.

Click to Visit the Modern Excel Speakers Page

Next Month’s Speakers Include Quite a Few Proponents of Modern Excel / Power BI

$150 Off – Discount Code

Register Here and Use Code BASPROB

Excel to Power Pivot Webinar Countdown – in 10 days

March 6, 2015

By Avi Singh

Happy Friday! Our next webinar is coming up in about 10 days from now, on Tue Mar 17th. Make sure you save it to your calendar. See webinar page for more information. Participants would also have an opportunity to sign-up for our online class at a discounted rate.

For the webinar, we have doubled the capacity from 100, up to 200 attendees – so if you were unable to get in on a previous webinar, take heart! There would be an opportunity to ask questions as well. See Q&A from the past webinar.

Here is what we would cover:

Walkthrough key steps in using Power Pivot and explain how Power Pivot supercharges regular Excel
Do it Yourself! We would provide you dataset and instructions to try those steps yourself at your own pace and build your first data model. Best way to learn is by doing.
Q&A: There would be opportunity to ask questions and seek guidance regarding using or deploying Power Pivot or Power BI
Resources to continue your journey

See links below for more details:-

Mar 17

We’re planning the next event in April covering a new topic. Subscribe to our blog to receive updates on future events.

PowerPivotPro Expands to Europe/Africa/Asia, Welcomes Dany Hoter!

March 4, 2015

Post by Rob Collie

My Business Intelligence “Yoda” Figure Joins the Team!


Master and Student, on a Fact Finding Mission in Denmark, 12 Years Ago!

In late 2002 back at Microsoft, I suddenly found myself in charge of most of the new BI-focused features of Excel (in the product wave that ultimately became Excel 20007).

The interesting thing is, I had ZERO idea what BI was back then.  I mean, I wasn’t even sure how to spell BI, as the old joke goes, heh heh.

I wouldn’t have survived that era without the patient tutelage of Dany Hoter.  Dany was our resident guru on the team back then, and still plays that role for Microsoft engineering teams to this day (he moved back home and now works for Microsoft Israel, but he and I met when we both worked in Redmond WA).

If He’s Still at Microsoft, How is He Joining PowerPivotPro?

imageBecause Microsoft is an amazing place, and Dany is an amazing person.  Dany is so valuable as a mentor – both for new hires and for veterans joining the Excel and BI “game” for the first time – that there’s a LOT of incentive to keep him on the engineering teams.  He makes everyone better.

So when he asked about doing something different – mentoring/teaching people outside of Microsoft, his management team was very supportive.  He keeps his job at Microsoft and can utilize his veteran-level “time off” to share his talents with organizations who are starting to adopt the Power BI suite of tools.

Win win for Microsoft, when you think about it.  Kudos to them for supporting it.

Extends Our Geographical and Timezone Reach


With Matt in Australia and Dany in Israel, the Sun Never Sets on PowerPivotPro!
(Click Here for More on Matt)

It’s been VERY hard for us to get over to Europe (for instance) to consult or teach classes – the travel time and expenses tend to be too high.  And even our remote (screenshare-based) consulting and assistance is often a bit awkward to schedule with the Europe/Africa/Mideast timezones.

Dany fills that need in a big way – I mean, he taught me, and continues to teach Microsoft employees to this day.  How could we possibly get any luckier?

You can contact Dany directly at his new email address:

More to Come Once I (Rob) Get Caught Up

I’m WAY overdue on sharing this announcement, and this is intended to be short (so that I actually get the post written and published rather than delaying further).

So there’s more to be shared here for sure:

  1. I interviewed Dany recently on Skype, and need to transcribe that for posting here.
  2. I’ve got more to say about why Dany is PERFECT for this role, how he fits in with our personality and philosophy, etc.
  3. A rundown of Dany’s many skills.  He’s fluent in basically everything, from SQL to DAX to M, and that’s just the tech stuff.  His wisdom, experience, and people skills are just as important.
  4. Dany has guest-blogged for PowerPivotPro before – you can find some of his articles here.

In the meantime, here’s his email address again:

Contact Dany Via Email

Power Pivot Blitz: Webinar, Seattle Class, Online Class

February 27, 2015

By Avi Singh [Twitter]

My New Year’s resolution to help 1000 people start using Power Pivot, will not achieve itself. So we are taking the bull by the horns and announcing the training events below. Click on the links to enroll or obtain additional information.

Need help choosing? Webinar is a free event so all are welcome to join. Read this article to help you…Choose Between: Live In-Person, Live Online and other training options. 

Mar 17

Live In-Person Class
Apr 1-2

Live Online Class
Apr 6-7

PowerPivot for Excel Live Class (Seattle)

PowerPivot for Excel Online Class

More Info on Live In-Person Class

Live In-Person class includes: Thumb Drive, 50% Off Online University and other goodies!

All Students Receive a PowerPivotPro thumb drive containing the materials from the course.

Power Pivot Seattle Class: Free Thumb Drive

You also receive 50% off our online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we just can’t get to in two days), so we now offer it as a bundle.

More goodies! You receive a physical copy of one of Rob Collie’s Books and digital copy of both books. Wait wait, there’s more Winking smile: you also get a laminated DAX Reference Card – which many have sworn is a life saver when solving a complex CALCULATE() puzzle.

Live In-Person Seattle Class Details & Registration

Visit Us at PASS BA CON! (Apr 20-22, Santa Clara)

February 5, 2015

Post by Rob Collie

Excel Speakers are a BIG Presence at This Year's PASS Business Analytics Conference

The “Modern Excel” Vibe is Taking Over This Year’s Speaker Lineup.
Come Hang Out With Us…  And Oh Yeah, Attend Our Sessions Smile

Getting the Band Back Together!

Holy cow folks, am I stoked!  Look at that lineup!  I’d go to this event (PASS Business Analytics Conference) just to HANG OUT with these people.  Other than the Excel MVP Summit at Microsoft HQ, you don’t ever really find this many “EXCELebrities” in one place.  (See what I did there?  I made a new word!  Let’s make it stick.)

But unlike the MVP Summit, this event is open to the public.  Come hang out with us.  And oh yeah, attend our sessions too!

Registration Discount Code

Register using the official PowerPivotPro discount code BASPROB and save $150.

Register Here and Use Code BASPROB

Full Day Pre-Conference Sessions by Rob/Avi… and Chandoo!

Read the rest of this entry »

Upcoming Webinar at PASS BA Marathon Tue Feb 3rd

February 2, 2015

By Avi Singh [Twitter]

Thanks to all those who attended our past Excel to Power Pivot webinar on January 20th. Some of the attendees also joined us later for our first ever online class. For those who missed out, keep reading to learn about our upcoming options.

First of all, it is a joy and a privilege to speak to all of you about my favorite topic – Power Pivot/Power BI. So thank you for giving me that opportunity. My favorite part was the Q&A session at the end where I got a chance to interact with all of you. At the bottom of this post you will see some of the key questions, that were asked and answers. I figured that might help all the readers of this blog.

We plan to be running these webinars every month; best way to stay informed of our upcoming events is to subscribe to our blog. We are locked onto the Citrix/GoToMeeting platform, which has performed flawlessly for our recent events. But we are looking to increase the maximum capacity, to accommodate more attendees.

There is a special opportunity, to attend our webinar on February 3, as part of the BA marathon, being conducted by the SQL PASS folks. Details as below:-

SQL PASS Business Analytics BA Marathon

Productivity Revolution in Excel By Avi Singh, Chandoo
Tue Feb 3rd 11AM PST (click to change Time Zone)
Register at PASS BA Marathon

Yes, I am collaborating with the “awesome” Chandoo for this Webinar. Gosh!

…now as promised here are the popular questions from our past webinar.

Questions from past Webinar

Some questions were asked over audio and others via the chat window. Some of these topics are meaty enough that we will follow up with full blog post and/or YouTube Video.

Read the rest of this entry »