Open Letter to My Friends at Microsoft: The Power of Excel’s Internal Network Effect

May 21, 2013

 
image  image

Excel on the Left.  Other Data Tools on the Right.

Today I’m going to “get my nerd on” in a big way.  Buckle up.

The genesis of this post is an email I’ve been meaning to send to my contacts at Microsoft – one I’ve been thinking about writing for at least a year.  But I also figure it’s the sort of thing you folks might find interesting, and I really don’t have time these days to write the same “opus” twice, so here goes – two birds with one stone.  And it’s a friendly stone.

Has there ever been a tool as flexible as Excel?

Let’s take a moment and just marvel at Excel’s “range.”  (VBA macro programmers – yes the joke is intentional).

You can do damn near anything in Excel.  Calendar chart visualizations.  Music videos.  Beautiful art.  More music videos.  Respiration wavelengths.  Chess games.  Word clouds.

But those are just the outliers really – the novelties.  The truly valuable examples are much less dramatic and happen hundreds of thousands of times every day.  I’ll give some examples in the next section.

Feature A Was NEVER “Intended” to Be Used With Feature B!

image

“Hey You Got Your Slicer in My Conditional Formatting!”
(And then the whole jar fell into a bucket of DAX)

Read the rest of this entry »

8 Comments | GeoFlow, Observations, Trends, and Wild Speculation, Power View, Real Microsoft Stories | Permalink
Posted by powerpivotpro

Pareto Analysis the Easy Way

May 16, 2013

 
image

“Computer?  Tell me how many products account for the top half of all sales?”

The scene in Star Trek IV where Scotty picks up the mouse and tries to instruct the computer by speaking into it was, in my opinion, the lone bright spot in an otherwise lackluster movie.  We all feel the same way sometimes of course.  We just want to say “hey computer can you just do what I want without making me trick you into doing it?” 

(Related:  I had a professor once who liked to say “as computer scientists we are trained to communicate with the dumbest things in the world – computers – so you’d think we’d be able to communicate quite well with people.”  He also writes a science and politics blog and was one of my favorite professors.)

OK, so how do we build something like this in PowerPivot:

image

The Handful of Products Shown Account for 50% of All Sales – But How Did I Build This?

First, Don’t Make it Harder Than It Is!

Read the rest of this entry »

7 Comments | Quick Tips & Tricks | Permalink
Posted by powerpivotpro

First print Run Almost Sold Out :)

May 14, 2013

 
image

There Are More than 18 Copies Left at the Warehouse.  But Not THAT Many More Smile

When I was down at MrExcel HQ With Chandoo this weekend, Bill and I decided to check how many copies of the book were left in inventory at the distributor.

We printed 3,000 copies back in November.  The distributor only had about 350 left at the warehouse as of Friday.

Neither of us expected the inventory to be that low.  You see, book sales data isn’t exactly a timely business – Bill and I just got paid in April for the books that sold in November.  And while Nielsen does provide a window into how many books sold this past week, they only capture part of the market.  The proportion out there “in the shadows” is unknown, and it’s significant.

So much like what we see in the night sky is light that left distant stars many years ago, our only reliable sales data lags reality by 4-5 months.

Good News Bad News

Hey good news – the book has been selling even better than we knew!  Bill tells me that most books never sell their entire first print run, so to be running out so quickly is a very good sign.

Hey bad news – we gotta scramble to print a second run before the supply runs out Smile

Read the rest of this entry »

7 Comments | PowerPivot Books | Permalink
Posted by powerpivotpro

Heading Down to Uniontown (MrExcel HQ)!

May 10, 2013

 

image

Chandoo:  “It’s 46 miles to Uniontown, we’ve got a full tank of gas, half a stack of Excel books, it’s overcast, and we’re wearing sunglasses.”

Rob:  “Hit it.”

Hitting the Road.  Holding a Mini-Summit.

Chandoo and I are jumping in the car this afternoon for a short journey down I-77 to a place MrExcel calls home.  Well, at least, that’s where Bill lives and works today.  He is, sadly, moving to Florida this summer.  (The second friend of mine to leave Ohio for Florida since I moved here – traitors! Smile)

This is going to be fun, having the three of us in one place like this.  But I’m a little bummed that we couldn’t get a good picture of us sitting IN the car.  There was too much glare on the windshield.

Otherwise, we were prepared to re-enact this classic scene:

image

Me, Chandoo, and Bill in One Place?  Serious Stuff?  Maybe.

When a bunch of Excel “heavies” get together like this for a day, you might think the gathering looks like this:

image

Sometimes, that’s true.  But they just as often turn out more like the barbecue scene from Mystery Men:

image

I’m interested to see which way today goes Smile

6 Comments | Friday Bonus Posts, PowerPivotPro on Tour | Permalink
Posted by powerpivotpro

Guest Post: GeoFlow using Panama’s Census Data

May 9, 2013

imageIntro from Rob

Very busy week for me.  Fortunately Miguel is at the ready with another guest post.

It occurs to me that we’ve now had guest posts from the US, the UK, Holland, Canada, and Panama.  That’s pretty cool. 

Miguel told me that I was going to LOVE the first picture in this post.  And I do.  But I must say that, once I saw the title, I expected something along the lines of the picture at the right.  The picture below is better.

OK, over to Miguel…

image001

Figure shows the population distribution for Panama in 2010
Made Entirely in Excel!

If you read my latest guest post at Powerpivotpro’s blog, you’ll know that I’m working on a personal project trying to get a more visual aproach of the latest census Data for the country of Panama (where I’m from and I currently live in).

Read the rest of this entry »

2 Comments | GeoFlow, Power View | Permalink
Posted by Miguel Escobar

What Self-Service BI means to our world (with Excel)

May 7, 2013

image

Guest Post From Miguel Escobar!

Today we’re blessed with another guest post from Miguel Escobar.  I love the style of this one, and the movie industry tie ins of course.  I also really like that Miguel “detected” the similarity between Hans Rosling’s TED talk and the Power View demos that first emerged in 2011 – the first time I saw Power View, my immediate thought was “Amir is riffing on Hans Rosling” and I wondered how many other people were thinking the same thing.

But above all I love to hear people’s stories.  How they came to “get involved” in this stuff.  I would say Miguel falls somewhere on the more advanced end of the spectrum – even using the term “Self-Service BI” kinda gives that away – but the fact is that the worlds of BI and Excel are indeed converging.  So let’s hear Miguel’s story shall we?

-Rob

What has been the impact of Self-Service BI in our modern world?

Its self-service BI a good thing? heck yeah it is. You can bet on that BUT…is that such a good thing that would drive engagement of the users into actually transforming data into information and information into insights? and even further…decisions based on those insights?

3 years ago I couldn’t even think of having a great reporting and visualization tool at my reach because I couldn’t afford that and college tuition at the same time and then Powerpivot became available…how did it impact me and some of my friends?

Read the rest of this entry »

Leave a Comment » | Career, Observations, Trends, and Wild Speculation | Tagged: , , | Permalink
Posted by Miguel Escobar

BI, Excel, or Dark? Pilot Flying J Scam Shows Bob Marley was Right: We Need to Light up the Darkness

May 2, 2013

 
image

This Highly Scientific Chart Reflects My Experiences of the Past Four Years

So Many Things to Write About!

There are many things competing for my digital ink today.  Microsoft has released a workbook size optimizer and I owe someone named “Doody” a post on that.  I have more to add to the last two posts.  I have a “tournament rules versus prison rules” post that is dying to get onto the page, inspired by Jeremy Bartz.  My wife and I spent yesterday with Chandoo and his family.

But something in the news recently brought another long-simmering topic to the foreground…

“We Steal Millions From Our Customers, and They Have No Idea”

http://pfj.workoasis.net/images/pilot-home.jpg

Pilot Flying J Allegedly Underpaid Fuel Rebates to Multiple Trucking Firms –
a Practice That Only One Such Firm Detected!

An absolutely fascinating story is taking place in Tennessee.  The FBI has raided the headquarters of Pilot Flying J (a huge chain of gas stations and truck stops across the US), and even seized the computers of top execs, in connection with a scam.

The alleged scam goes like this:

Read the rest of this entry »

5 Comments | Career, Observations, Trends, and Wild Speculation | Permalink
Posted by powerpivotpro

Adding a Minimum Threshold Slicer to “Stores That went negative” Technique

April 30, 2013

 
Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8. Now We Vary That Threshold That With a Slicer. PowerPivot is Amazing :)

Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.
Now We Vary That Threshold That With a Slicer.

Let’s take Thursday’s post and extend it a bit.

In the picture above you’ll see that I have 5 selected as my threshold on the new slicer, and 48 months “qualify” for that threshold – there are 48 months where at least 5 stores were negative.

Now let me select 9 on the threshold slicer:

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores. Did i mention that PowerPivot Rocks? :)

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores

How’d I Do This?

Read the rest of this entry »

6 Comments | Accounting/Financial Techniques, Creative Visualizations, DAX, Quick Tips & Tricks, Slicers | Permalink
Posted by powerpivotpro

“Unable to Connect to VertiPaq Engine”

April 30, 2013

 
Unable to connect to the vertipaq engine. Reason:Failed to connect to server A connection cannot be made. Ensure that the server is running.

Do You Get This Error From Time to Time in Excel 2010?  I Do.  And it’s Quite Fixable.

This morning, when I loaded up my workbook from last Thursday to start today’s blog post, I got this error.

For awhile now I’ve been meaning to post that this is very “fixable.”

When I opened the workbook, I had a cell in the pivot selected:

image

To make the error go away:

  1. Click OK.
  2. Select a cell outside of the pivot
  3. Now select a cell inside the pivot again
  4. Usually the error goes away

image

Just click OK, Select a Cell Outside the Pivot, Then Select Back Inside the Pivot

Leave a Comment » | Uncategorized | Permalink
Posted by powerpivotpro

Chandoo Lands Tonight!

April 30, 2013

 
image

It’s an exciting day at the Collie household.  The one and only Chandoo is on a plane with his family right now, and I’m picking them up at the airport tonight!

For a huge Excel nerd like me, the chance to hang out with this guy over the next couple months is a massive gift.

Multi-Month Skills Swap!

For a long time, I had unconsciously assumed that Excel was a very structured and rigid thing – that all “experts” used in in precisely the same ways.  (At Microsoft, we were exposed disproportionately to the Wall Street crowd, and those folks tended to be rather homogeneous in their discipline).

But when I moved away from Seattle, the first time I sat down with Mr. Excel for lunch here in Ohio, we were immediately “swapping” techniques.  Sentences like “oh wow, I never thought of doing it THAT way!” were common from both of us. 

In recent years I’ve come to expect that as the norm.  Yes, we all do some things similarly.  But we all bring something unique to the mix.  Seriously, everyone reading this has some special angle on things that hasn’t occurred to others.

So the fact that one of the “magicians” of the Excel world is going to be living down the street from me this summer is Fan.  Freaking.  Tastic.  I’m giddy.

A Few Masterclass Openings Remain

I think there are still a few slots open for Chandoo’s Excel classes in Chicago, Columbus, Cleveland, and Washington DC, and the PowerPivot course he and I are jointly teaching in Columbus.  Check out the registration page here for details.

“Real” Post Still Coming

I still want to follow through on last Thursday’s post, so stay tuned.

13 Comments | Uncategorized | Permalink
Posted by powerpivotpro

Showing Only Months/Weeks/Etc. When at Least N Stores Showed a Certain Behavior

April 25, 2013

 
image

Nice Pivot, But I Only Want to See Months Where Eight
or More of My Stores Went Negative!

***Update:  Technique Extended, Workbook available

In a followup post I have added a slicer that lets the report user control the minimum number of stores required, rather than fixing it at 8 like this post does.  Also, the workbook is now available for download.

Find both in the followup post, located here.

Tales from Remote Consulting

Awhile back I left my job to start a new company.  I’m not yet ready to announce what that new company is about – I’m working hard on that and you folks will be the first to know.  Spoiler:  it’s about PowerPivot and Excel.

But in addition to hard work, there’s also a lot of waiting involved in all of that.  I’ve been filling the gaps with training and remote consulting to keep my head in the PowerPivot game.

Remote consulting in particular is a lot of fun – people send me a workbook, I spend 1-3 hours and build what they want, then send it back.  Gives me a good sampling of the problems that are “out there.”

One of those remote consulting jobs featured the problem pictured above (except that they had real data, and what I’m showing is 100% fake).

How Many Stores Fell Below Zero Each Month?

Read the rest of this entry »

7 Comments | Accounting/Financial Techniques, DAX, Quick Tips & Tricks | Permalink
Posted by powerpivotpro

“More Excel” is Often the Answer :)

April 24, 2013

 
image

Foreign + Banks + Wealthy = Not Quite Overwhelming
(But Still More than Half)

On yesterday’s post I got a comment indicating that most government debt is actually held by US workers in their 401k’s.  Clearly that was not my understanding, so I went digging.

And after I dug, I went to Excel.

And after I went to Excel, I went to PivotCharts.

The results are pictured above.

“Overwhelming” was Too Strong a Word.  “Majority” is Better.

Read the rest of this entry »

29 Comments | Uncategorized | Permalink
Posted by powerpivotpro

« Previous Entries