Our next venture in Deep in the Sheets will feature a series of videos that transform raw data from your spreadsheet to beautiful, presentation-ready style. In this episode, we’ll walk you through how to prepare your data ahead of time to maximize the effectiveness of pivot tables through column grouping for pivots. Follow along as we share the step-by-step process that will help make your data relevant, informative and easy-to-digest.
Read on for the full transcript:
Hi everybody, Dion here. This is the first in the series of Deep in the Sheets videos where where we’re gonna look at taking raw data from your basic spreadsheet to presentation ready. First up, we’re gonna get the most out of our pivot tables by prepping our data ahead of time. [MUSIC PLAYS] What you see here is raw data exported from our lead quality source, as well as our just Adwords interface. So, you can see on here that there’s a number of columns that could be simplified. The simpler they are, the easier it’s gonna work in a pivot table. So, the first one here you can see, “Lead Status,” there’s a bunch of different entries here. A lot of them are very similar, so I’m gonna roll them together so we can aggregate the numbers as a whole. The other is the “Created At” date. This is exactly as it comes out of the interface, and it’s just not real convenient for us when we’re looking at a pivot table. So the first thing we’re gonna do is create a table. See? I got this handy button at the top there. You definitely wanna get one of those for yourself. Then I’m going to un-select and just keep “Junk” for now. But first I need another column for this new entry. This is gonna be a separate column I can use for my pivot table. I’m gonna call it “Lead Quality.” So, we’ll take this junk status and bring it down there, then we’ll move to “Inquiry.” Go ahead and copy one of those, bring it into our new column. And the last one, this is the one that really helps us out here. I’m gonna take out “Inquiry” and “Junk,” and you’ll see that all these statuses are quite similar. So for our case, we just wanna look at them as just one whole; consider them all good leads. So we wanna see data for all of them together. So I’m gonna un-select “Inquiry,” “Junk,” and “Blanks,” and I’m gonna go ahead and call these just “Qualified.” Go ahead and copy that down. And this column is ready to go in our pivot table. You’ll see for this left one, “Lead Status,” there’s a whole bunch of entries in here. Lead Quality is just as clean, just the three, as well as blanks. The next thing we’re gonna look at is the “Created At” date. This, as it is right now, includes a full form date, as well as time, and it’s not really helpful to us. So, the first thing we’re going to do is find the week of the year. So I’m gonna make a column, call it “Week,” and I’m gonna go ahead and do the formula WEEKNUM. That’s gonna give us the number of the week, this year. So, fifth week, fourth week. You can see the number here is what day of the week it starts on. I like to start on Sunday, so I’m just gonna type a “1” in there. I’m gonna go ahead and copy that as values, so we don’t have formulas in our pivot table. Next thing I’m gonna do is create a “Month” column. This is a handy formula to get rid of the date format Excel sees. I want my date as just text, so I’m gonna go ahead and use the text formula, I’m gonna point at my data, and I’m gonna give it the name—this is code in Excel for full-form month. So if you wanted to do an abbreviated month. You just put two Ms, etc. You can also do day with “DDD”; this is how I like to do mine. So you go ahead and take that, copy that all the way down, and we’re gonna go ahead and paste values there. So, this for—spreadsheet is very close to ready for a pivot table. You’ll see next week when we throw it in, it will be much easier to look over time, using month or week, and it will be easy to see “Inquiry,” “Junk,” “Qualified” compared to each other, rolled altogether. That’s it for this week, we’ll see you next time on Deep in the Sheets.
