Realizing Efficiency in Data Analysis in Microsoft Excel 2013: A Pivot Table Primer

I often debate with other Microsoft Excel Wizards about the merit of Pivot Tables over formulas.  I’m struck by how vehemently some people oppose their use, preferring Nested If fun that can take hours to get right over a few clicks to arrive at the same answer.

I’m not a Pivot Table purist by any means.  I enjoy the mental challenge that comes with deciphering Microsoft Excel’s sometimes maddening parenthesis patterns and “coding” my spreadsheet every now and then.  And I am proud of the fact that I don’t have to go running to the office next to mine for help with a particularly nasty Nested If statement anymore. 

But if you don’t know about and use Pivot Tables, you really really should.  They save time, and, unlike formulas, are malleable to allow you to slice and dice your data.  Let’s look at a quick example of formulas vs.  a Pivot Table.

One quick disclaimer.  Microsoft Excel is a very robust tool.    You can even go behind the curtains and write your own VB if you really want to.  There are keyboard shortcuts, many many different ways to Insert cells, write formulas, and parse data.  What I am about to show is not the only way to create this analysis; indeed, it may not even be the best way to do it.  I don't however think that it can be argued that what I am about to demonstrate are common methods of working within Excel. I ask the Excel Wizards to accept that for the sake of this discussion.  Lastly, I use Microsoft Excel 2013, and all of my commands, menus, etc. reflect the use of that version.

Here's a simple spreadsheet tracking the hours of four people over a period of time.  In Excel, terms, this is about as simple as a multi-column spreadsheet gets, yet it contains some very pertinent information if you use it as the starting point for some data analysis:

OK, so let's say I want to roll-up my hours for each person and get a grand total for the entire group.  I can use formulas to do this, such as:  SUMIF(A:A,"bob",C:C); writing the formula or copy/pasting it three more times and substituting "bob:" with the names of the other guys in the list.    To get my sum total for the group, I can use another formula, like:  =SUM(F2:F5).  Toss in some minor formatting as well as data headers and the end product looks like this:

Not too bad, right?  Start to finish, this probably took me  something like two minutes to create. I have my data and I have my analysis.  Let's go have a beer to celebrate!

One moment please.  Let's try a Pivot Table.  To start, we go Insert->Pivot Table.  A new window pops up asking a couple of questions.  First, we specify our range and then  tell Excel where to put the Pivot Table:

When I click OK, I get this:

I have a place for my table and I have Options to the right of what to include in it.  To get my total by person and sum overall, first, on the right side, I click Name and I drag it to Row, then I click Hours Billed and I drag it to values:

Not quite right, though it is.  That's simply because the Pivot Table by default uses the COUNT function.,  To change it to Sum, we just have to right click on the header and click Sum:

A couple of clicks later and here's the result.  At this point I'll also want to set my sort choice, I do

this by clicking on Row Labels and choosing my sort option.  Note that I could also sort on Hours Billed if I wanted to:

Start to finish, the Pivot Table takes about a minute to create, or half the time that the formulas did.  Bear in mind that this is a simple example,  Imagine dozens of people and hundreds of lines to calculate.  The beauty of the Pivot Table is that were it to take you to create formulas for those dozens of people and it took 15 minutes, your Pivot Table will take...

About a minute still.

Let's look at a couple of other simple features of Pivot Tables.  Say that my team of four becomes a team of five.  I want to add Daryl to my analysis, and I want it to look nice- keep the guys in alphabetical order and such.  

 With my formula I need to:

1. Insert a row between Chris and Frank
2. Type Daryl
3. Insert my SumIf formula 

With my Pivot Table, I need to:

1. Go to Data->Refresh All

The Pivot Table will add Daryl in to its analysis, alphabetically:

. 

Neat, huh?

One more example for the day.  Let's say that I want my analysis to go week over week- list out the hours by person for each week, with a sum total at the end.

Given the way that the data is laid out right now, there's no easy way to change it with my formula example.  Off the top of my head, I could:

1. Create headers across the top with each week.
2. Either manually retype the values in for each person or each week OR do a referential formula to grab the values for you in a little less time.
3. Create a Sum field for each week.

I get it, with this example, its not too much work.  5 minutes on the outside.  Let's compare that level of effort to what a Pivot Table requires.

To get the same result with a Pivot Table I:

1. Click on the Pivot Table to bring up the Fields Menu on the right of the screen.
2. Click on Week Of and drag it to the Columns Area

5 seconds on the outside.  Here's what the end result looks like:

Come on, even the most Wizardly of Excel Wizards has to admit that's damn cool.

Hopefully you've seen the value of Pivot Tables within Microsoft Excel.  Give one a try next time you're wanting to do some analysis.  With a little bit of practice you will have an awesome new tool in your arsenal- and bear in mind that I have barely scratched the surface of what Pivot Tables can do.  

Epilogue

I do feel like I need to show some street cred here when it comes to formulas lest I be called a Pivot Table Poser.  Here's some Nested If hell that I whipped up to stretch my muscles a few months ago.  Truth be told though as you'll see even here I used a Pivot Table.  Think about that for a second and imagine what could happen if you took the chocolate of your formulas and combined it with the peanut butter of my Pivot Tables...

=IF(O1="week 1",1/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/40,IF(O1="week 2",2/14*$O$3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/80,IF(O1="week 3",3/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/120,IF(O1="week 4",4/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/160,IF(O1="week 5",5/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/200,IF(O1="week 6",6/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/240,IF(O1="week 7",7/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/280,IF(O1="week 8",8/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/320,IF(O1="week 9",9/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/360,IF(O1="week 10",10/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/400,IF(O1="week 11",11/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/440,IF(O1="week 12",12/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/480,IF(O1="week 13",13/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/520,IF(O1="week 14",14/14*N3*GETPIVOTDATA("Hours Billed",$F$2,"Name","Bob")/560))))))))))))))

Putting the Microsoft Surface Pro 3 Through The Meatgrinder

You've seen the snappy commercials and the product comparisons. You've read the reviews.  Here’s one guys unpaid and admittedly unsolicited feedback on the Surface Pro 3.  I don’t pretend to be a Surface expert; as such there may be errors in what follows.  More likely, there will be features called out that aren't unique to the Pro 3.  Let this stand as my disclaimer that I’m reviewing an experience not necessarily just a product.  Please keep this in mind and be gentle dear reader. 

The Surface Pro 3 is billed as “the tablet that can replace your laptop”.  As a guy that loads up every day with a Lenovo laptop and an iPad and uses both regularly throughout the day, it was hard not to be intrigued by that bold statement.  With an i5 Processor, 8GB of RAM, and 256GB of SSD space, it certainly seems feasible; the RAM was actually an increase over my Lenovo, and the SSD was obviously a big improvement over my traditional hard drive, even if I sacrificed 256GB in actual space in the process.  I considered the i7 but in the end decided that it wasn't worth the extra 300 bucks for what I was going to use it for. 

This isn’t a cheap device.  At $1299 USD out of the box, this is a pricy bit of technology; add in a keyboard, which is a must have if you’re really going to replace a laptop, and a mouse and you’re in for about 1500 bucks right out of the gate.  Interestingly, it feels like a quality device.  While long, slim, and light, the Surface Pro 3 also feels like it is well made.  The metal casing seems as though it will stand the test of time and the glass display feels tough and durable.  The kickstand feels as though it’s not going to loosen up on me over time either. 

The jury is still out on the keyboard.  The form factor is of course designed to complement the Surface Pro 3 and the result is a keyboard that feels like it is just an inch or two too short.  You do get used to this fairly quickly, but from time to time I do find myself wishing for just a little more room for my hands.  The keys are responsive and I am having some trouble getting used to them- I tend to mash them harder than they need to be pressed, which makes for some loud typing from time to time when I really get going, like I am right now.  I also find that I prefer to have the keyboard lay flat on a surface as opposed to at an angle- it just doesn't feel sturdy enough to withstand my key mashing when it is not fully supported.  I’m sure its fine, but there’s just a bounce in response to keystrokes that I’m not fond of when it is angled. 

When angled there’s a bounce to using the keyboard that I find off-putting

The Surface mouse is just really really cool.  With a flexible design, it contours really well to the palm of your hand and it’s just so lightweight and comfortable to use that I don’t know that I could ever go back to another mouse full time.  It then lays flat for storage- which is again for lack of a better adjective, cool.  Even if you don’t have a Surface, I strongly recommend this mouse for any road warrior laptop user. 

Just look at that contour!

Flattened out, the mouse is ready to get tossed in your laptop case and head home for the day

I haven’t used the pen very much as for right now I prefer using my keyboard for OneNote.  One truly baffling thing about the Surface Pro 3 is that the pen is attached to the keyboard.  OK, so let’s say that I want to go Tablet Mode for a meeting.  I detach the keyboard and guess what I’m also leaving behind?  Yep, the pen.  It’s just perplexing that they couldn't think of a better place to put it.

Why in the world would you attach the pen here?

Setting up the Surface Pro 3 is relatively simple.  There are the usual annoyances of getting Windows 8.1 to work the way you want it to work- booting to the desktop, for example, or the constant playing around with the tiles that seems to happen for the first couple of weeks.  Installing Office 2013, Visio and Project 2013 were simple exercises, which is of course as it should be.  The display is clear and bright- watching videos is actually better on the Surface Pro 3 than it is on my iPad with Retina display.  The speakers are clear and fairly loud; they’re free of distortion and I’m guessing music will sound pretty good on them as well. 

I haven’t been able to kill the Surface Pro 3 yet, even with multiple Excel workbooks and Project files open simultaneously.  The Surface is quick and responsive- the SSD is a marked improvement over my first experience with SSDs a couple of years ago.  The 8GB of RAM seems sufficient and the i5 holds its own.  The Surface Pro 3 does have a fan in it interestingly enough.  You’ll hear it kick in when you really get a few processes going at once or even install multiple updates.  The fan is rather loud and takes some getting used to although I will say I haven’t heard it kick in since the first couple of days that I was putting the Surface through its paces so either I’m used to it or its holding up better now that it has had a chance to break in a little bit. 

Split screen isn't a feature of the Surface of course, but I will say that I haven’t been able to use it quite like I can now.  Picture opening a Project plan in one pane and a PPT in another.  You need to see what is in the PPT and transcribe it to your Project file.  Because you’re viewing the PPT in one half of a 12” display, it’s a little tough to see what you’re doing.  No problem.  Simply do a finger swipe on the PPT screen and presto!  The text is enlarged on that side of the screen only.  I can’t tell you how handy this one feature is, and it isn't something I've been able to do with other devices. 

Notice how small the slide is?  Check this out:

Now I can see the task names and resources to plug in.  And my Project side stay the same size

I haven’t found a single thing after a month of use that I can’t do on the Surface Pro 3 that I could do on my laptop.  And I’m doing it with a significantly smaller form factor.  And I’m also doing it on a really cool looking device.  I can’t say the same thing when making the comparison with my iPad.  What’s interesting to me is that it is billed as a tablet.  In my opinion, it isn’t really a tablet at all, except for sometimes.  Want to rent a movie from iTunes, download it and watch it on a plane.  Sorry, you can’t.  Apple sees that Windows OS and decides that you’re on a computer.  Like Clash of the Clans or for those of you reading this in the future The-Latest-And-Greatest-Game-For-Mobile-Devices-Of-The-Month-That-You’re-Reading_This?  That’s great.  You can’t play on your Surface (Yes, yes, OK, I know that you can install an Android simulator and do it that way. But you can’t go to the Windows Store and install the App).  The reason is the same- those games aren't designed to run on a Windows OS.  This is an annoyance really and nothing more, but I do wish that it were different so I could get rid of the iPad. 

With that said, there are a ton of “apps” available via the store.   And some of them will make your Surface Pro 3 work like a tablet, even if you really wish that it wouldn't.  Take the Citrix Receiver app as an example.  Even if you download the proper files to run Citrix from a website, the second that you install the app everything routes through it and if you've used the Citrix Receiver app on an iPad you’ll understand that that just isn’t the ideal way to use Citrix.  What I don’t get is that there is a Facebook app, but not one for LinkedIn.  There’s one for ESPN Fantasy Football, but not for Yahoo Sports Fantasy Football.  It’s just hit or miss and like I said a little weird. 

I need to keep playing with it to see if I can make it replace my iPad entirely eventually.

In the end, after a month of steadily increasing use, I have replaced my Lenovo with the Surface Pro 3 for day to day work use.  My iPad does still make appearances from time to time, but mostly for the games.  The small form factor, performance, and Cool factor make the Surface Pro 3 worthy of your consideration.  The flexibility of using it as a tablet is an added bonus.  I recommend the Surface Pro 3 for professionals that carry their office with them wherever they go.  I’d be hard pressed to recommend it for personal use as there are other options out there that are cheaper that can do many of the same things, just not as well. 

Arbitrary rating system score:  The Surface Pro 3 gets 4 Footballs out of 5 on the Fowler scale.  


12042014:  Update:  I've created a second post with some additional SP3 thoughts,  Click here to read it.

On Working From Home, Minnesota Winters, and the Relative Sanity of The Author

Today we find our hero (your favorite author, me, thanks for being momentarily confused) having an argument with his inner monologue over a topic that has haunted leaders for decades. 

C:  Whelp, it’s that time of year again.

IM:  What, where you start swearing every time you hear Christmas Carols?  When you kinda give up on your Fantasy Teams?  When you start thinking about Thanksgiving stuffing?

C: Well, yes, but that’s not what’s bugging me today.  It’s Sunday, but I still got an email today from someone asking me if they can work from home tomorrow because the roads might be crazy.  I checked the forecast earlier today, and they are calling for anywhere from 2-10 inches of snow between late tonight and Tuesday morning.

IM:  So what’s the problem?

C:  First of all, I thought I was past all of this now.  I don’t technically manage anyone anymore.  I’m the Project Manager, so I oversee their day to day activities, but I’m not their manager.

IM: Say, didn’t you write a great entry about the different types of reporting relationships  that Project Managers work with?

C: Yep, I sure did.  It’s called Entering the Matrix, you can find it on this site.

IM: Golly, I really like that article.

C: Me too.  But we’re digressing now.  Anyway, so one of the guys on my team emailed me asking me for my thoughts on him working from home tomorrow if the roads are crazy.  My first reaction?  Of course the roads are going to be crazy.  This is Minnesota. In the summer, the roads are terrible because of all the construction.  In the winter, they are terrible because, well, I guess I’m not sure why.  It seems as though everyone just forgets how to drive here when it snows.  We get a small break in the fall and right after the snow melts where things are relatively normal, and as of tomorrow it seems as though that will be gone.  So if his criteria for working from home is crazy roads, I guess I will see him again in May.

IM: Yeah, but you hate traffic too.  That’s why you would wait out rush hour at night and leave early in the morning- to avoid the crazy roads last winter.

C: Sure, but I still went in.

IM:  Not all the time though.   There were at least two times last year where you didn’t go in.  And I know for a fact that one time was because you didn’t want to deal with the traffic.

C: It’s not that I disapprove of working from home.  It’s just that I think people abuse the privilege. If your position is a telecommute position, fantastic.  If it isn’t then you should be in the office more often than not. 

IM: But you telecommuted for years, right?  You understand better than most that you can sometimes get more done in 4 hours at home than you can in 2 days in the office.  There are fewer distractions, less interruptions, a more comfortable environment, and a stocked fridge.  All in all, more conducive to being productive, no?

C: Yeah, but I had an office.  I was all set up to work from home.  And remember, most of the time I was on the road at least 2 days every week.  It’s not like I’d take conference calls from my back yard so I could smoke or anything.

IM:  Chris…

C: OK fine, I did do that. 

IM:  So how do you know when someone is abusing the privilege of working from home?

C:  I don’t know.  You just do.   I guess when you see a guy who is out every single time it snows, that’s obvious abuse.  Obviously, there are lesser degrees of abuse than that. You just have to handle each case individually.

IM:  But if someone can do their job effectively remotely, what’s the problem?

C:  Here’s the deal.  We live in Minnesota.  It snows here.  A lot.  Starting tomorrow, we will enter a period of permafrost that won’t go away entirely until May of next year.  It also gets kinda cold.  And while I get that when it hits -25, you really can’t tell the difference between -26 and -50, it still hits that level of cold here.  As a Minnesota resident, you accept that weather as part of the package.  Why you accept it is a whole ‘nother article.  You drive the vehicle you drive, you work where you work, and you know that you’re gonna have to deal with the weather for 6 months out of each and every year.  That means you’re gonna have to go out in it.  You’re going to have to get the snow blower working.  You’re going to have to drive on Minneapolis side streets when they’re basically impassible and Minneapolis alleys when the city stops plowing them.  You’re going to have to chip the compacted snow and ice off of your driveway a few times.  And you’re going to have to go to work.

IM: Hey, why exactly do we live here again?

C: Quiet you.

IM: We’ll talk about that later.  Does 3AM work for you for that?  We can work it into our you’re 40 now and where are you going with your life conversation we’ve been having.  Anyway, if I am hearing you right, your basic objection to people working from home during the winter is that they should know that it snows in Minnesota and deal with it.  Do I have that right?

C:  Well, no see, its deeper than that really.  I mean…um….there’s company policy of course, and…see…um.  I JUST DON’T LIKE IT, ALLRIGHT?  Jeesh, get off my back.

IM:  So, you just don’t like something that you can do yourself, potentially saving aggravation and cold extremities that no one says you can’t do, and that you do in fact do from time to time, but only when it makes sense to you to do it?

C:  Right.

IM:  So what are you going to tell the guy then?

C:  I think I’ll go with a Minnesota Nice/Catholic Guilt hybrid response.   Just enough ambiguity to not give him my blessing but also enough specificity to convey how I feel about it without coming out and saying no while simultaneously pointing out that I’ll be in the office.

IM:  We really gotta get us out of this state.