Pivot tables made easy, a how-to guide
Pivot tables are a highly marketable skill for all analysts and remains an easy way to stick out in an applicant pool. The learning curve for pivot tables is steep, but also short! Once you get going it’s an easy and immensely powerful skill. In this post I will share how to make pivot tables work for you in the easiest way possible.
First things first, “What is a pivot table?” They allow you to narrow down large sets of data into bite sized pieces. Say you have sales records, with pivots you can easily answer questions such as “who’s the best salesmen”, “what product is hot right now”, “when was it hot previously”, ETC. The examples are endless but I think it’s obvious the level of intelligence that can be quickly gained from pivots is well worth your time to learn them. I can’t stress enough how useful PTables are; this is easily the most requested excel function my team and I use.
Step One: I named my columns Name, Quarter, Year, Region and Sales[USD] and am entering in data. This step shouldn't be necessary if you're working with an already existing data base.
Step Two: I finished making the dummy data by randomly entering data for q1 and using =RANDBETWEEN(100000, 15000000) for the remainder q’s.
Step Three: This is the most crucial part. I’ll walk you through it to be very easy. Select all data by clicking the little triangle in the top left, select insert then select pivot table. The pop-up asks you, are you sure this is the range? And where do you want it? Both defaults will work fine in most cases.
Step Four: In the new tab which you can select on the bottom left, you will see the blank box. Simply click and drag the info you're looking for on the right, in this case we put region, names and sales amounts in the column, row and values boxes, respectively. The default for values is count, something I change nearly every time. This literally counts the number of values. Say you have 1,000,000 and 1,500,000. Count would tell you, 2. Click on values, click values field settings and change it to sum, which would change the value from 2 to 2,500,000.
Step 6: To make things easier to read, if you click into the pivot table and select inset chart, the data in the chart will represent the pivot table! So here we can easily see that Nico had the best sales for q1 and was in the south region.
Step 7: Lets pretend our boss is more interested in cumulative sales, not person by person. So its very easy with pivots to switch up the info. Simply swap name and quarter and be sure to take the quarter filter off to show all 4 quarters. Now its clear that the North in q2 was our highest earner for the year!
Step 8:
Now our boss is wondering, quarter by quarter, who is the better salesperson. Christie or James?
Since we placed name in the report filter box on the right, we can select the drop down, unselect all, and select just Christie and James. This is showing us James is q over q a more lucrative salesperson! You may notice at first the selection is hard to read because the salespersons weren't in the legend, a simple fix! Just move names to legend and it will automatically assign a color. The select options will remain wherever the field is dragged to.
This was a first for me and had a good time making the tutorial. If you enjoyed this post be sure to leave a like! If you had trouble following the gifs and would prefer a video, let me know!
If you have specific questions I’ll do my best to answer them! Enjoy!
