Happy New Year! I hope it was a great holiday filled with family, cookies, and lots of love. Hopefully Santa brought you everything you were hoping for. I thought I would start the year off by giving the gift of Pivot Tables! (Maybe not quite as great as the shiny, new Tesla wrapped with a gigantic, red bow that Santa left in your driveway -but I’m sure it was a close second on your wish list).
It’s been a little while since my first post when I promised to teach the three components of Excel (VLookups, Macros, and Pivot Tables) that would set you apart in an interview. (Sometime before those great food and cookie coma’s of December). So let’s finish off the trifecta and get the job.
What are Pivot Tables?
Excel’s definition: “PivotTables make it easy to arrange and summarize complicated data and drill down on details.”
In keeping with the Donut theme, let’s say you run a ginormous donut factory that supplies donuts to different customers. Below is a raw table of data which includes the following information: Customer Name, Username, Donut Type, and Quantity Sold. Let’s say this is a huge amount of data containing 10,000 lines.
Why a Pivot Table?
You want to know how many donuts each store bought last month, and which donut was the most popular. (This way you’ll be able to better predict demand and have enough supplies to meet it). Creating a Pivot Table will quickly and efficiently depict all this information in a neatly organized table. Powerful, right? So let’s create it.
The Step by Step:
Move over to Sheet2. Once there, navigate to the Insert tab and select Pivot Table. The following screen will pop up:
For the table/range you will want to navigate to the sheet that contains your data (Sheet1 in this example) and select the data. Then hit OK. Once that is done, we’ll want to select what data we want the Pivot Table to summarize. You’ll see the following screen to the right:
We’ll be selecting Customer Name as the rows, Donut Type as the columns, and Quantity as the values to summarize. Go ahead and check those off and put them in the appropriate boxes.
It looks like Dunkin Donuts is our winner in terms of total donuts bought. (Barely beating out Krispy Kreme! You east coast lucky people!) Our most popular donut was the glazed. (I guess we won’t be buying very much powdered sugar next month).
I hope you now have a better understanding of Pivot Tables and will be able to find some cool uses for them! Hopefully, as a side effect, this series hasn’t made you just really want donuts – I’m sorry if it has and that conflicts with your new years resolution. If you have any excel questions/ stuck on something and want a second opinion (or just want someone nerdy to talk about Excel with) let me know! I really love sharing when I learn cool new Excel tricks (my coworkers would tell you). My email: Katherine.firstname.lastname@example.org