Top 5 Excel Features for PPC

Posted by on Feb 25, 2015 in Advertising | 4 Comments
Top 5 Excel Features for PPC

There’s probably no tool more dear to a digital marketer’s heart than Microsoft Excel. It’s the must-have data analysis program that helps turn raw numbers into actionable insights. Here are five of my favorite Excel features that I’ve learned to love over the years.

5. Table Formatting

Difficulty: Beginner

Table Formatting lets you apply a beautiful table style to your raw PPC data quickly.

Excel Table Formatting

For example, you can use it to convert your plain AdWords campaign performance report to a presentation-ready table.

How to set it up:

  1. Remove any extra title and total rows
  2. In the Table ribbon, select a predefined table style
  3. Your table will now feature clear formatting and filterable headers

Learn more about table formatting for PPC.

4. Macros

Difficulty: Intermediate

Macros let you automate repetitive tasks in Excel by recording your manual actions so you can replay them in future worksheets later with a single keystroke.

record excel macro

For example, you can create a macro that removes title and total rows from your AdWords spreadsheets for easy analysis with pivot tables.

How to set it up:

  1. Download and open an AdWords campaign performance report in Excel
  2. From the Developer ribbon, click Record
    1. Assign a keyboard shortcut (e.g., Opt+Cmd+S)
    2. Store the macro in your Personal Macro Workbook
  3. Begin performing the manual tasks you’d like to repeat automatically in the future
    1. Delete the first row in your table (the AdWords report title)
    2. Delete the last 4 rows in your table (the AdWords total rows)
  4. Click Stop to conclude recording
  5. You can now strip the title and total rows quickly by running this macro

Learn more about Macros for PPC.

3. Text to Columns

Difficulty: Intermediate

Text to Columns lets you parse through the data from one column and distribute it into multiple columns.

excel text to columns

For example, you could use Text to Columns to remove UTM tracking parameters from your destination URLs. This would make it easy for you to analyze landing page performance in a Pivot Table.

How to set it up:

  1. Download and open an AdWords ad performance report in Excel
  2. Select your Destination URL column, and launch Text to Columns (Data > Text to Columns)
    1. Leave your data set to Delimited, and click Next
    2. In the Delimiters section, type “?” into the Other field, and click Next
    3. In the Data preview section, select the new column (with your tracking parameters)
    4. In the Column data format section, select “Do not import column (Skip)” and click Finish
  3. Your Destination URLs no longer contain any of your tracking parameters after the “?” and are ready to be analyzed with a Pivot Table.

Learn more about Text to Columns for PPC.

2. Pivot Tables

Difficulty: Intermediate

Pivot Tables make it easy to summarize, analyze, explore, and present large sets of data. It’s a great way to reformat your raw data into interactive tables.

excel pivot table

For example, you can use a Pivot Table with the Ad Performance Report from AdWords to quickly discover the best-performing headline of your ads.

How to set it up:

  1. Download and open your AdWords ad performance report in Excel
  2. Prepare the table by deleting first row (the report title) and the last 4 rows (the totals)
  3. Highlight the table, and insert the Pivot Table (Data > Pivot Table)
  4. Use the Pivot Table builder to dynamically structure the new table
    • Segment performance by campaign and headline by dragging “Campaign” and “Ad” into the “Row labels” field
    • View relevant performance metrics by dragging “Clicks,” “Impressions,” and “Converted Clicks” into the “Values” field

Learn more about Pivot Tables for PPC.

1. Formulas

Difficulty: Beginner

Finally, we get to the most beloved Excel feature of all: Formulas. Formulas allow you to perform calculations and take actions on data in your worksheet. There’s a myriad of formulas that you can combine to make incredibly powerful spreadsheets.

excel formulas

For example, you can use the simple LEN formula to count the number of characters in a cell. This formula is especially useful when writing ad headlines and descriptions.

How to set it up:

  1. In a cell, type =LEN(
  2. Then type the reference of the cell you’d like to count (e.g., your ad headline in A2)
  3. Now, you’ll have a real-time count of the length of your cell

Learn more easy Excel formulas for PPC.

Share Your Favorites

Did I miss your favorite Excel feature? Share yours in the comments below!

Sam Mazaheri

Sam is the Director of Online Marketing at InVision and former Director of Marketing at AdStage. Prior to AdStage, he was part of the AdWords product team at Google, serving as the in-house AdWords expert and advisor to product management, engineering, and UX. Prior to that, he personally managed and grew in-house digital marketing programs with over $300,000 in monthly ad spend.



View Sam Mazaheri's profile on LinkedIn

  • Pingback: Pinterest Multi-Image Ads, Google Play Ads, Excel for PPC...()

  • Mark H

    It’s real hard it seems to create a multiple-series chart stemming off a pivot table. Especially on Mac. That’s one thing that should be made easy.

    For example, in your pivot table screenshot, it would be great if I could have:

    Display – US – Awareness
    Display – US – Retargeting
    Search – Brand
    Search – Competitors

    As series in a chat, plotted against dates (that would probably be under them when clicked instead of the ad copy you got in there now), and then run “Sum of Impressions” to visualize the growth of impressions of each of these campaigns over time.

    Doing this on a Mac, easily? God bless you.

  • Pingback: 5 Blog Posts to Expand Your Excel Expertise | Clix Marketing PPC Blog()

  • Pingback: Excel For Analysis | Pearltrees()