From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Build basic pivot tables

From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Build basic pivot tables

“

- [Instructor] When using a pivot tool on data, it's important to remember the pivot you build is going to use the data you provide. It will not change the underlying dataset. And speaking of datasets, pivot tools expect a clean dataset, similar to what you see in our pivot data file in our exercise folder. So you need to do those cleaning steps before you actually pivot. Pivot tools use column headings to create your available fields, and because you'll be calculating values, you want to make sure your fields have the correct data type. For example, dates are dates and numbers are numbers, not text. If you need a refresher on cleaning data and making meaningful headers, check out the previous course, "Learning Data Analytics, Part One" in the library. Now let's insert our first pivot. I'll go to insert, tables, pivot table. It automatically detects my range and I do want it to be on a new worksheet. I'll go ahead and click OK. One of the first things I want to show you is that on the left-hand side is where the pivot table will actually show the display, and on the right-hand side is where you actually build the pivot table. Screen space is important. I'm going to go ahead and minimize my ribbon so I can see more of my pivot table field. I'll just double-click one of my ribbon tabs. Okay, great. The pivot tool in Excel has four main areas: Filters, columns, rows and values. Remember, the columns and rows intersect to show a value and, well, filters do just that; they filter. The value options in Excel are different aggregate functions like sum and average and counts. The first hurdle for most people is just dragging things in and exploring what the pivot might look like. I've found over the years that people are hesitant to drag and drop fields into the pivot. They're afraid they're going to be wrong or they're going to break something. Or they may just be unsure what to put where. Remember, pivots really tell a numbers story of our data. And each pivot can actually tell a different story about the data you're working with. Let's build a pivot to gauge our invoice counts by each customer for each month and year. Since we want to look month over month, we're going to use the invoice date field. Let's drag it into columns. Depending on your version of Excel, it might automatically group by month. However, earlier versions required us to have that extra step of grouping. Remember, only date data types can do this. It will not group if you use text. So you want to make sure that your actual date is a date data type, and remember, because pivots don't adjust the underlying data, you'll need to return to the dataset to make this change. Now let's go ahead and add our invoice ID to the values. I want you to note that, by default, it summed the invoice ID versus counted. I've been asked this over the years. Why does it default to sum? That's because our invoice ID is actually set to a number data type. I actually need it to count. So what I can do is adjust the sum value to a count value. I'll go to my sum of invoice ID, I'll go to my value field settings and I'll change it to count. Had it been a text data type, it would have counted by default. I'll go ahead and click OK. Now we see, for each year and each quarter and each month, how many invoices we had per customer. Let's go ahead and change this up a little bit. I'm going to move quarters out of the mix. I'll just drag and drop quarters out. Now I just see the year and the month. Now let's change our pivot to look at our products ordered each month. We'll want to change our row heading to the description for our field product names, and we'll want to change the value to the quantity field, because that field actually tells us how much of each product's been ordered. It's really easy to pivot this information. I'll go ahead and drag out my customer name. I'll bring my description into my rows. I'll go ahead and remove my count of invoice ID and I'll scroll and choose my quantity. Okay, I'll do a little bit of resizing here to take a look at it. Now I can see each product and how much of each product was ordered for each month, each quarter and each year in my dataset. Now we've seen two different insights on the data: We've seen how many invoices were created by month, and how many of each product were ordered based on the quantity for each month and each year. Remember, pivots give us critical insight into our data and they're easy to change. You just have to begin to become comfortable moving things around inside those columns and rows and values until you figure out exactly which story you're trying to tell.

Contents