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 »

Live Class, Online, Online U… Which is Right For You?

January 26, 2015

By Avi Singh

Few of you have inquired about the learning options now available. This may help you choose. Remember, that you can also mix-and-match. Many students attending Live classes also buy the Recorded course (available at a discount).

  • Live In-Person Classes (Seattle): The most engaging format. Hard to replicate the energy of a live class in any other format. 2 full days of training, so expect about 16 hours of class.
  • Live Online: If you are unable to travel to an in-person class, this is a great alternative. Fully interactive and we use online chats, polls & tests to enrich the training experience. Geared towards Power Pivot beginners. Conducted over 2 days with 2 hours of training each day, plus self-paced homework. As a bonus you are invited to a 1-hour Q&A session held each month where you can bring your questions as you progress in your Power Pivot journey
  • Online University (Recorded): 22+ hours of video! This would be equivalent to a 5+ day course but you pace yourself and can revisit topics for a full year. Greater depth and breadth than what can be covered in two days.  Often taken “standalone” or as a “supplement” to our other offerings.
  • Onsite Jumpstarts (Your Location):   We bring the class to you and your team.  For larger teams, this is more cost effective than sending everyone to the In-Person Class, plus we get to use YOUR data and biz problems as the basis of the training.  Together we build working solutions to your current problems while you learn.

    Multiple Paths All Lead to Awesome Power Smile

    A former instructor, once told me, “Every person learns in their own way”. So in the end, the choice is yours. But we encourage you, in this new year, to seize the day, grab the bull by the horns and make 2015 the year you kicked things in high gear!

    Power On!
    Avi Singh

  • Reminder: Webinar – Excel to Power Pivot Jan 20th (mini-post)

    January 19, 2015

    Folks, our Webinar: Excel to Power Pivot, is coming up today Tuesday Jan 20th (click to add to calendar). We hope to see you there. Participants would also have an opportunity to sign-up for our online class at a discounted rate.

    Here is what we would cover, see event page for more details:-

    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

    Excel Power Pivot steps we would cover in our walkthrough:-

    Pull data into Power Pivot
    Build a Data Model
    Write new Measures (Formulas)
    Write time-intelligence Measures

    Subscribe to our blog to receive updates on future events.

    Power On!
    Avi Singh

    PowerPivot Live Online Class in January and New Year Resolutions

    January 12, 2015

    By Avi Singh [Twitter]

    We wish all the readers of PowerPivotPro a very happy new year. We wish that you get your groove power on this year :-). I have never been big on New Year’s resolutions. For good reason; reportedly only 8% of people achieve their New Year’s resolution. Sharing your resolution socially and publicly, is one factor that could help you get there though (check out other factors).

    So here I am, going out on a limb and saying:

    I will help a thousand people to start using Power Pivot

    That is my BHAG (Big Hairy Audacious Goal) for the year. I had a late start in 2014, but here is how many I helped last year (via training, webinar, workshops, consulting).

    Here is a geographical representation. Yes that’s an embedded Power View (view full screen); more on that in a later post.

    In order to get to this goal, we will continue existing offerings but also add some new ones:-

  • Live In-Person classes
  • Online University
  • Consulting
  • Webinars
  • *New* Live Online Class

      New! Live Online Class in January

      I am excited to offer our first live online class. Click below for details and registration.
      There are limited seats for first online class as we ramp this up.

      Read the rest of this entry »