Articles

Learn Excel 2010 – “Hosted PowerPivot” #1432


MrExcel podcast is sponsored by Easy-XL. Learn Excel from MrExcel podcast, episode
1432: hosted PowerPivot. Well, hey, everyone. I have an amazing thing to show you today. Think about Excel 2010. One of the very cool things that we have is
PowerPivot. PowerPivot is incredibly powerful. It allows us to take multiple worksheets and
put them into a single pivot table, DAX calculations that we can never do in pivot tables before,
and it’s free with Excel 2010. That’s one feature in Excel 2010. The other feature we have in Excel 2010 is
the ability to take an Excel workbook and put it up on the web, so that way other people
can use it. That protects your formulas, protects your
IP, it makes it looks very professional. Unfortunately, those two don’t match. You can’t take a PowerPivot workbook and put
it up on the web. It just doesn’t work. So, here we have two of the greatest features
in Excel 2010 that don’t work well with each other. That’s bad, but I have an amazing thing to
show you today. This is from folks of a hosted PowerPivot. So, Rob Collie, you might have mentioned–
heard me mention Rob before. Rob was mentioned in my book on PowerPivot
for the Excel Data Analyst. He is formerly of Microsoft, worked on the
PowerPivot product, was an Excel project manager for a while. So, for you watching this podcast as Excel
people, Rob is right there with us. This is running in a browser, okay, but I
didn’t build this as a web application. This was completely built in Excel using PowerPivot. Lots and lots and lots of data; 40,000 rows
of data, a couple of different tables mashed up, we have slicers here. So, just check this out. I’m going to choose from slicer 1990 and
let’s look at 1995 and the summer of 1995, so months six, seven, and eight. I’ll just leave the day the way it is. This is the Hot 100 database; the Billboard
Hot 100 database showing all of the songs that were in the Hot 100 that summer. It shows where they peaked. That was a hard calculation to do; you had
to use DAX for that, their average and the artist, number of weeks on the chart. Also, hey, you even– check this out. That’s a little trick there in Excel using
the icon set to star anything that managed to hit number one. All right, all this is cool Excel trickery
and, you know, being Excel tricksters, we can all do this, but then I took this whole
thing, uploaded it to the web. Now, it’s running, it’s beautiful, it looks
good. No one can get in and see my data. No one can get in and see my formulas. No one can get in and see how I did any of
this. This is all completely protected. Absolutely amazing way to go. Now, why do I think this is amazing? If you’re at a big company, you probably recognize
this. This is– this is SharePoint, but for all
of us who don’t have a big IT department over there, who can’t figure out how to get SharePoint
running, who don’t have the money for SharePoint, or more importantly, the money to hire the
three IT people to keep SharePoint running, this gives us the ability to put our workbooks
on the web without having to figure out how to use SharePoint. I think this is so cool. I want to encourage you to do it and it’s
kind of a hassle for you to sign in and do everything. So, I’m going to bribe you. I have a whole box here full of PowerPivot
stuff. PowerPivot stuff. I have t-shirts, I have decals, I have bookmarks,
I have all kinds of stuff. So, I’m going to bribe you to sign in and
check this out. All right, so here’s what I want you to do. I want you to go to this URL. http://mrexcel.hostedpowerpivot.com/Pages/default.aspx. The login– it’s going to ask you for a login. It’s uppercase MRXL– like MrExcel, @pivotstream.com. Password: this is really, really tricky. Capital P, zero, lowercase w-e-r, capital
P and exclamation point and then lowercase v-o-t. It’s like PowerPivot but with a few extra
characters thrown in to make sure that we have a digit and a symbol. All right, once you’re in, I want you to click
on that soundtrack of your life. I want you to use slicers to go back to your
high school graduation month. All right, pull up– here we go. Let me do it. All right, so, I graduated from high school
in 1980 and then 1983 and then I’ll choose June, leave all the days alone and there we
go. So, Irene Cara: Flashdance What A Feeling,
Culture Club, David Bowie: Let’s Dance, Men At Work: Overkill. Okay, this is like taking me right back to
those great high school days. So, pull this up for your high school graduation
and then capture a screenshot of that. You can just use print screen. Go to your email, Ctrl V to paste that in,
send me your US postal mailing address, t-shirt size to [email protected] Subject line: PowerPivot stuff. So, right now today, September 21st, I’m going
to say this expires either a month from now or when the PowerPivot stuff is gone. I know the podcasts are out there on YouTube
for years and years and years. I don’t want to be sending t-shirts out, but
for the next month, we’ll get rid of these t-shirts by anyone who’s interested. Or if you want to really, really try it out,
create a PowerPivot workbook of general interest. Just anything; don’t put any secret confidential
information in there, but something that people would be interested to email to me. Our address is on our sites. You can try it out. Others can try it out. What we’ll do here is the first five people
that send in a workbook, I’ll send them an autographed copy of PowerPivot for the
Excel Data Analyst, all right? So, to kind of just again summarize why I’m
so excited about this, this technology allows me to take a PowerPivot workbook that I develop
in Excel, upload it to the website. All of the formulas and data is completely
protected. Looks very professional, I can add some artwork
here. Life is very, very good. This is an amazing thing from the hosted PowerPivot. You can click here for more information on
using hosted PowerPivot. Hey, I want to thank your stopping by. We’ll see you next time for another netcast
from MrExcel.

Leave a Reply

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