Excel serves as a powerful tool for digital marketers, who can use the application to fuel analytical thinking. In my early days of digital marketing, figuring out formulas, pivot tables and all the exciting things that Excel has to offer took time; time which, previously, could’ve been saved had someone given me some direction and purpose around all the unfamiliar capabilities of Excel. In this article, I’ll be discussing some of the foundations that go into setting up data to then be used in a pivot table, including some common formulas and shortcuts to help us get there.
Step 1 – Group and Join Data
Let’s say you already have raw data pulled out of your favorite platform/search engine, like a campaign report out of AdWords. You’ve already segmented the data by day, and you’ve included fields like “campaign name” and “labels.” The first step is to make sure you’ve grouped the data into segments that you’re trying to look at (such as brand, non-brand, competitors, etc.), which should be defined already by your labels. If any segment labels haven’t already been created, or you need to make new segments, no need to panic! We will use VLOOKUPS to configure the missing spaces (if you aren’t sure what a VLOOKUP is, it’s a formula that looks at a cell for a value within another range to return a value in a specified adjacent column).
Let’s say your data is organized as such:
Labels are already being used, but we also want to roll up brand against non-brand, which isn’t already included. Let’s add in a new column on the left and call it “Search Type”:
The VLOOKUP formula will be looking for a cell with a value that matches another value in a separate array, which it will then return that matched value’s definition. At this point, it’s a good idea to create a second tab and call it “lookups.” This will be used to store your lookup tables (I say tables in the event you want to make more lookups later).
In this example let’s base our definitions off the “Campaign” column, which can be accomplished using this formula:
To give clarity on what each field does in the formula above:
- [D2] = refers to the cell we are looking up; in this case, it’s the campaign name
- [Lookups!A:B] = the name of the lookup tab and the range to look up from
- *NOTE* a VLOOKUP always looks up a cell in the left-most column of an array, then pulls values going to the right
-  = the lookup array has two columns (A through B), and this number specifies the column within the array that we want to pull a value from
- [FALSE] = For our purposes we pretty much always use “false,” since we want exact matching fields returned
Now that we have the VLOOKUP formula created, let’s drag the formula down to span all the rows in the dataset.
*Pro Shortcut* Select the entire blank range from the bottom-most cell and up to the VLOOKUP cell, then use “CTRL+ D” to quickly “drag” the formula all the way down!
Anytime data is added to this file, you’ll want to drag down the formulas to make sure all rows are defined. For this reason, I typically “bold” every column in my datasets that contain formulas, so it’s easy to spot which columns need to be applied down.
Step 2 – Specify Definitions
Now that you have a ton of #N/As, let’s edit some definitions so that these cells actually mean something. Copy the whole column called “Campaign” and paste them as values on the second tab we created earlier (you did make a lookup tab, right?) over column A.
*Pro Shortcut* When pasting the campaign column into the lookup tab, right click header of column A and press “S” and then “V” to quickly paste the data in!
Let’s call column B “Search Type” and then de-duplicate the campaign names in column A. A VLOOKUP will only pull the first matching value of a table, so if you have a campaign name listed multiple times, only the top campaign will be used by Excel.
*Pro Shortcut* Click on the column A header, then press “ALT,” followed by “A,” followed by “M” to quickly open up the de-duplicate menu!
After removing the duplicate values, apply the labels to each campaign. In my example we only have two unique values — “NonBrand” and “Brand”:
Now that this table has been filled out, we can return to our dataset to see that all rows now have the definitions applied to them, making our dataset pivot table ready!
NOTE: Using a VLOOKUP is helpful when you need data to appear differently! For example, Google Analytics exports date formats that are in the format “YYYYMMDD”. These dates can all be assigned a new value that is easier to read, such as “MM/DD/YY.”
Step 3 – Create the Pivot and Add Basic Views
Now we’re finally ready to create our pivot table. Select the whole set of data (column titles and all) and under the “Insert” tab at the top of Excel, click on “PivotTable”:
*Pro Shortcut* Select the entire area you want to make into a pivot table and then “ALT,” followed by “N,” followed by “V” – then press ENTER! A new PivotTable will be quickly created under a new tab!
When the dialogue window pops up, just press ENTER to create the table under a new tab. Right now, it doesn’t look like much; we need to add in fields from the “field list,” which appears on the right side of Excel. The field list is visible by default if you click anywhere inside the pivot table area. Hint — it looks like this:
Notice how our custom VLOOKUP column’s title appears in the list (it’s called “Search Type”). Let’s say we want to see how our brand impressions have changed over time. Drag “Search Type” under the columns area, and then drag “Date” under “Rows.” Once you have done both, drag “Impressions” under the “Summation Values” area. Your pivot table should look something like this:
Right-clicking either of the months will give an option to “Ungroup,” which will show the dates by day instead of by month. At this point we have some decisions to make regarding what we’re wanting to look for. Something handy that I wasn’t taught early on in my career was the function to show values of a pivot table as something else. Right-click any of the impression values and you’ll find a selection called “Show Values As” hiding in the contextual menu. Hovering over that will open up a plethora of choices, which I find very exciting. For example, select “% Row Total” to change the table from this…
This function changes the values in the table to a predefined calculation. This view gives us the ability to easily spot the percentage of our impressions being delivered by day between the two segments we’ve built. This idea can be expanded as much as your imagination is willing to explore!
NOTE: You can add data into a pivot table after it has been created. Simply add new data at the end of the raw data tab, and then, under the pivot table settings, make sure to change the data source to select the new range. Then simply refresh the pivot table to update (shortcut: “ALT” > “A” > “R” > “A”).
Step 4 – Add Formula Columns
The last step is to add formulas to the pivot table. This is particularly helpful when adding more data into this file — for example, if this file is going to be used as a daily pacing file and new data will be added daily.
Click inside the pivot table area, and at the top there’s a tab called “Analyze.” Click there, then click on “Fields, Items, & Sets,” and then finally on “Calculated Field…”:
Like using formulas as you would normally in Excel, you can predetermine formulas that are built into the pivot table which will automatically update all related values depending on how you shift the fields around in the field list and filters. This is critical to making sure your figures are updating depending on how you aggregate, drill down or adjust views. For example, “click through rate” or “cost per click” cannot simply be added into a pivot table if the raw data is pulled at the keyword level but the table is setup to view data aggregated by day. A pivot table will roll these values up as a sum/count/average/etc., which cannot be done. Well, it can, but it’s wrong.
Let’s name this formula “CPC Steve” for simplicity, then write out the formula like so and click add:
Close this window and “CPC Steve” should be automatically added into the table. Let’s remove impressions and change the view from “% Row Total” back to “No Calculation.” The view will now adjust to show you costs-per-click by day, or by month if you group them back up again (go ahead, try it). In my dataset, CPCs have decreased over time and it’s easy to see the split between both “Brand” and “NonBrand.”
Keep in mind that certain metrics such as “Average Position” must be calculated through a custom formula, since no matter how you pull average position from a data source, it’ll already be delivered as an average (unless you pull impression-level data, which is absurd to try to push through Excel, anyways).
But we’ll have to wait for my next blog post for more about that!
We’ve barely scratched the surface of the capabilities that’re offered through Excel, but this tutorial should serve as a solid foundation to get started. Try to get creative and look for ways to incorporate some of these functions into your daily life. These are some of the fundamental tools that a digital marketer will use day-in and day-out to search for those needle-moving insights that make agency life so exciting.