Transform PPC Data into a Striking Pivot Table: AIMCLEAR‘s Deep in the Sheets (Episode 13)

Posted in Analytics & Reporting, Paid Search

Posted on March 4th, 2015

Tune in for the final installation of our five-part Excel series on converting raw data into a presentable format. This week’s episode of Deep in the Sheets provides formatting tips to create stunning representations of your data. Follow along as we add style, color and font to really make data pop in an Excel spreadsheet. Enjoy and more Deep in the Sheets episodes are coming soon!

Read on for the full transcript:
Hey everybody, Dion here. Today’s the last part in our series about taking raw data to presentation ready. [MUSIC PLAYS] Today we’ll be looking at making it presentation ready with some formatting tips. We have here our data from last time. So we’re gonna get this on another tab and clean it up to make it presentation ready. First thing we’re gonna do is remove the blanks from our document. So we’ll go ahead and drop those off. Now we have our lead quality by month over time. So we’re gonna go ahead and copy our chart that we made last week and put it in a new tab here. We’re also going to grab our data. And we’ll just go ahead and drop it right next to it. Alright, looks good. So now we’re gonna go ahead and make all areas around it white. So go ahead and do that… just to kind of get the focus right on the data. Go ahead and do that with these last squares down here. We’re gonna wanna put a box around our data. Get that back in there so it’s not hovering. So I’m gonna change the color, give it a nice light blue, and do an outside border. So, things are starting to look better already. Let’s go ahead and hide these rows to really focus it in. So there’s no more rows to the right—I’m sorry, no more columns to the right. And now there’s no more rows! Then we’re gonna put a title above this. Let’s call this, “Lead Quality Over—” “Lead Quality Over Time.” Then we’re gonna go ahead and… I like to use the— let’s see, font, cell styles— I like to use heading 1. Let’s do heading 2 because it’s a bit big for this. So, already things are looking a little—a little cleaner. We’re gonna go ahead and insert some rows up here. So we’re gonna go ahead and insert the AIMCLEAR logo here. Go into insert, pictures, grab the logo, I’m gonna resize it holding shift to keep the proportions. Leave that in the top. Shrink the distance between these a little bit. And now the last thing I’m gonna do is modify our table just a bit. So, the first thing I wanna do is make the colors to be more relative to what they actually are. So, the inquiry bar here, I’m gonna go ahead and make that grey. Because they’re not our worst leads, they’re not our best leads, they’re just kinda in the middle. Junk, which are these orange bars, I’m gonna go ahead and make those red. Gonna choose a little less strong red than our option there. And then our qualified leads, I’m gonna make those green just because those are what we’re really looking for. And once you get to that point you got your pivot table standing out—oh, I’m sorry, you have your pivot chart standing out, and everything is very legible. That’s it for our five-part series, over these you can see how to take your raw data and make it presentation ready. Stay tuned for more episodes of Deep in the Sheets coming soon.

Subscribe Today

We'll keep you updated on the latest Aimclear musings & appearances