Advanced PPC Excel Tips: How To Do Pivot Tables and VLOOKUPS

Are you ready to save time and become more efficient with your Excel spreadsheets? If you are analyzing data and creating reports regularly, Pivot Tables and VLOOKUPs will make your life much easier.

Pivot Tables:

Pivot Tables are a powerful feature that allows you to easily summarize your data from a table or list of data quickly and accurately.

With PivotTables, PPC  specialists can:

• Summarize data in easy to manage formats
• Quickly compare subsets of data
• Reveal patterns and relations in the data
• Allow for fast analysis and create actionable insights

Let’s say I want to see impressions, clicks and cost segmented out by search engine. I can easily create a pivot table to show the total metrics by search engine.

Here are the steps:

1. Select the Data you want to summarize.

2. Click Insert a Pivot Table

3. Select the Pivot Table target location. In this case, I chose to show the pivot table in a new worksheet.

Now that Excel knows what the data is, you now have the opportunity to slice and dice the data any way you choose.

4. Select the unique identifier the pivot table will organize your data by, and drag it into the “Row Labels” section. In this example, we want to see the data segmented by publisher.

5. Drag the performance metrics you wish to see to the “Values” section. In this case, I chose Impressions, Clicks and Cost.

6. You can then select whether you want to see the count, sum, average etc of the data by clicking on the upside down triangle and click “Value Field Settings”.

And that is a basic Pivot Table! We now see impressions, clicks and cost by each publisher in an easy to manage view. From here, you can edit the formatting and take action on your findings.

VLOOKUPs:

VLOOKUPs are another very useful, timesaving excel function. They are great for searching through hundreds of rows of data or looking up data in a different location in your workbook.

Let’s say we want to compare conversions month over month for your keywords. Once we pull the two keyword reports for each month, we can easily use a VLOOKUP to find out which keywords saw the biggest increase/decrease.

There are four questions that need to be answered when doing a VLOOKUP:

• What are you looking for? (aka the lookup_value)
• Where are you looking for it? (aka the table_array)
• Which column exactly are you looking for? (aka col_index_num)
• True or False? (Keep reading to learn more about the True or False parameter in detail)

Here are the steps:

1. Enter the following formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) by filling in the following parameters:
   • lookup_value: The value you want to find in the lookup value column (A4)
   • table_array: The table range containing columns for both the lookup and return values (A4:E49)
   • col_index_num: The column number that contains the return value (5 since conversions is the 5th column)
   • [range_lookup]: optional parameter – Enter True if the data is sorted alphabetically or false if the data is not sorted

2. Hit Enter to get the return value.

Pro Tip: If you are looking to drag the formula down and apply it to the rest of a table, you must use the $ sign. If the formula is dragged down without the $ sign, the values could be wrong.

And that’s how you do a VLOOKUP! From here, you can add an additional column for percent change to see which keywords saw the biggest increase/decrease in conversions.

Both pivot tables and VLOOKUPs are essential when dealing with large amounts of data. The most important benefit is the fact that you can make fast actionable insights from large data sets. They will make your life easier and save you time overall!