- Roll On Through: Create an Iterative List in Excel Without Any Real Coding
- Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel
- Active-X Gonna Give it to Ya
- Your Invitation to the Excel Pivot Party
- Slicing Excel: The Way of The Knife
- Yes...If(And)): Excel’s Mighty “IF” Statement
- Getting Iffy With it: Conditional Statements in Excel
- Taking Out the Trash: Dirty Data in Excel
- Dealing with Dirty Data in Excel
- Doing the Excel Data Dance
- Adventures in Excel: Power to the Pivot
- The Art of Excel Pivot Tables
- The One Excel Formula to Rule Them All
- Excel Showdown: Index Match vs. "The Lookups"
- How I Stopped Worrying and Learned to Love the Formula
- Excel Data Manipulation: a Guide For Everyone
As previously discussed, the entry level data professional (coming from a background other than statistics, math, or computer science) consists of two keywords: The ability to lookup and the ability to pivot.
Dear reader, this is not an oversimplification or hyperbole, the hard truth is that if someone has a reasonable familiarity with a lookup function (remember, cool kids use Index-Match), and equal familiarity with how to use pivot tables, you can get most "day to day" work done on the job, and if you get really good, by utilizing both of these tools in creative ways you can essentially hack at any problem thrown at you.
So...what is a pivot table? Simply put, building a pivot table is the reason why Excel is still used at all (and as of about a month ago, Google Sheets has pivot tables too, so that's encouraging). It essentially offers a way to summarize your data and quickly draw conclusions from a bunch of random rows and columns. If that sounds very powerful, you're correct, and it would be easy enough to write an entire book just about pivot tables (and I'm sure these books exist), but right now, we're just going to learn to build one.
The good news is, pivoting doesn't even require a formula to exist, it only requires at least two columns with headings (this is the number one reason for pivot tables not working out) and the desire to succeed.
Here's how to do it:
- MAKE SURE ALL OF YOUR COLUMNS HAVE HEADINGS
- Highlight all of the data you want to pivot (there's the sucker way to do this: pressing control-A to highlight everything, and the more accurate way: highlight the first heading, and then hit control+shift+right to highlight all the headings, and then control+shift+down to highlight all the data related to those headings, this removes blanks from the pivot table). Ultimately though, highlight the data anyway that makes you happy, the important thing is to include all the data you want to pivot out.
- Click insert at the top of your screen (in between home and page layout...not the inset key), alternatively, you can hit Alt+D and then P to open the wizard, but the wizard actually makes things more difficult (in my opinion, of course). Then click "pivot table" (it's right underneath).
- This will launch another window, the table/range will already be populated if you've followed these steps as directed, so the only thing you need to concern yourself with is if you want the pivot table on a new sheet (probably), or if you want to place it on an existing sheet (maybe at some point, but not now). 4b. I purposely left out that checkbox regarding "adding the data to the data model", for our purposes, checking the check box will let you figure out the number of different values (this is called a distinct count) but it WON'T let you find the Maximum or Minimum value. To be honest, I have no idea why this is. For most purposes though, leave the box unchecked.
- Click OK...you now have a weird looking blank box that'll become your pivot table.
Now, click the weird looking box, and you'll see a menu on the right side of your screen, the top half of which will consist of the headings that you highlighted, the bottom consists of 4 distinct boxes that you can drag these headings into: filters,columns,rows, and values.
Now, take one of your headings, and drag it into the rows box (or just click it, Excel defaults to putting headings in the rows box) you'll now see a column of distinct values in the formally blank table. Honestly, the best way to learn is to just drag headings into each of the 4 boxes, their functions rapidly become self evident...and here's the best part: the pivot table doesn't make any changes to your existing data, so any craziness you engage in on the pivot table...stays in the pivot table, so don't worry about messing up the data that your boss gave you.
So, I'll leave you with that, your homework today is to try and plug each of your headings into each of the 4 options offered, remember, it's almost impossible to break a pivot table, it's easy to make it useless, but it's equally easy to reset it back to normal.
Today we've learned:
- That the combination of lookup skills and pivot skills makes up the entry level data professional's resume
- The most basic of overview of what a pivot table is
- The steps to set up a pivot table
- How to interact with a pivot table once it's been created.
Next time, we'll go over what each of the 4 pivot "buckets" actually do, and how to utilize each of them to get a different view of your dataset.