Convert your data: Grouping

- [Instructor] A lot of times you get data that's really sort of transactional and it looks like this. I have a row of data for every sort of data point along the way. I'm back to my minimum wage data. I have minimum wage at the category. I have the amount, so three dollars and ten cents and the year, 1980. This was the minimum wage in this year. Here is the minimum wage in 1981, et cetera, et cetera, et cetera. Now, I have the same data for the price of bread and each one in a row and the price of gasoline, et cetera. This data is the same data that we've been looking at in some of the other videos in this course, but long story short each row is a transaction it just makes it harder to do certain things.

It's not impossible, but it makes it a little bit harder for instance, for me to generate line charts for every single on of these elements because unlike before where I could use keyboard shortcuts and jump around and generate charts. Now if I want it to do a line chart I have to sort of click and drag and make sure I stop at the right place and manually do it. It's just harder to do and there's certain things that are almost impossible to do. It's also really hard for me to get a sense of the average values or the sum of values with things like this, but by thinking in terms of aggregation and also in sort of regrouping the data, it makes it much easier to perform tasks like this.

One of the great things built into Excel is something called a pivot table. I can go into Excel and if I wanted to create a pivot table I can literally just click anywhere in this range of values. I don't even have to select the table. I can go up to insert and say pivot table. It now knows what data I want to use and I can say put it in the same worksheet or in a new worksheet. I'm just going to put it in a new worksheet for now. I get this thing. This is where my pivot table is going to live and I get these controls over here which essentially are what I want to do with my data.

This is not a course in pivot tables. There are courses on pivot tables in Excel all throughout the LinkedIn Learning Library so check those out. At a minimum, the thing I always remember which I think I learned from one of the LinkedIn learning courses was that you should always think of your value first. Essentially, what I'm going to be doing is I'm going to be dragging fields into these sort of buckets in order to generate my pivot table automatically. The first thing I think about is my value meaning the number I'm thinking about and I'm worried about. In all of the cases, I'm thinking about the amount.

How much is minimum wage? How much does it cost to buy a gallon of gas, et cetera? If I think of my value first and I drag that into my values field, what happens is my pivot table actually starts to create. I'm just going to zoom in so you can see it. By default, it thinks I want a sum of all of those values. Well, that's kind of weird. That's not really what I want. What if I wanted a row for every year? A single row for every single year of my data set. If I now click and drag and put year down in the rows, now I'm saying give me the year in the rows, a year for every row.

That's exactly what I see. For 1980, the sum of the amount for that entire year for whatever it is I'm looking at which is still not a very useful number, but I can see the sums for every year. If I wanted to then say by category, give me columns per category, now what I can see is that for bread in 1980, the sum of the values is .501. For gas it's this, for minimum wage it's that. So long story short, it makes it a very quick and simple way to essentially take my transactional data and turn it into an organized, aggregated view of my number.

By the way, you don't have to only use sums. You can do things in here and say show me the average values, show me the max values, show me the data as percentages or difference from a row total. There's all kinds of ways you can perform calculations while you're reorganizing and aggregating your data.