You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
This post is brought to you by Allie Rutherford, a Program Manager on the Analytics and Presentation team.
In many offices, there always seems to be at least one person who is referred to as an Excel expert/master/wizard. Their masterful status often originates from one fact: they can make a PivotTable. Are you jealous of this person? Have you ever wished you could have that glory for yourself instead? Do you secretly wonder what a PivotTable even is?
Excel 2013 can help you achieve this masterful status. You’ll be a legend at the water cooler. How? We made PivotTables easier to make than ever before (no, really!).
We’ve done something pretty special: you can create a nice summary of your data with just a few steps.
You can go from this:
Without doing any of this:
Let’s go over how this is possible. Before you begin, you gotta have some data. You can borrow mine . It’s sales data! Go ahead and download it, I’ll wait.
Note: If you’re doing this on another table that’s not mine (it’s OK my sales data will understand) and it’s not contiguous (i.e. there are blank lines, whatever), you’ll want to select the whole thing. If your data isn’t tabular (i.e. in column-ular form), then this feature won’t work for you.
Okay, first thing you do: click on the table. Get that cursor anywhere within the range of data you want to use. I chose this spot:
Take a minute to appreciate the awesomeness that is my dataset; it has sales records (i.e. table rows) that include who made the sale, what office they work in, and the sales amount. What kinds of things might you want to know if this was your data set and not mine?
Some examples might include:
If you thought of any of these (and probably even if you didn’t), you’re going to want a PivotTable! It can tell you all of those things. PivotTables are very handy for summarizing and categorizing your data, putting it into smaller chunks that you can really munch on. They work best when you have column names and category columns in your workbook as well as something you can count/add up/average/(insert other aggregations here).
Ok, ready for step two? On the insert tab, hit the “Recommended PivotTables” button. It’s the second from the left.
You’ll get a dialog that looks something like this:
Here we have a few things: a list of recommended choices we’ve generated for you on the left, a preview of the currently selected table on the right, and the ability to just create a blank PivotTable yourself on the bottom there if we were way off base.
It’s a lot easier to figure something out if you’ve got an example, right? This feature will get you started on PivotTables if you’ve never done one before and/or show you different ways to lay out your data.
Anyway, what you should do next is pick your favorite (choose wisely!) and hit OK. Most of you will probably pick the first one (and I do too), so you’ll probably get this on a new worksheet:
Congratulations! You made a PivotTable in three easy steps. We just answered Question #1: What office is bringing in the most sales? It’s Seattle!
What’s most awesome about this (and there’s a lot that’s pretty super awesome here) is that you can quickly get to something that answers a question without knowing much about PivotTables. If we had done this without a PivotTable, it might include SumIF and VLookup and things I can’t even imagine. It would be hard. This is easy.
PivotTable Recommendations will help you make awesome report-outs in no time that will make your coworkers jealous and your boss super happy. It can revolutionize the way you summarize your data and shave time off of your work week. Sounds pretty good, eh? Go get crackin’ on one of your own workbooks!
Here’s the consolidated steps:
Until next time,Allie
Nice post Allie!
Thanks! I've always wanted to know what a pivot table is.