PPC Excel Tips For Every Level: Part 2, Faster Campaign Analysis For Intermediates

excel logoToday we have more handy Excel tricks from Bing Ads Evangelist John Gagnon. This second installment of PPC Excel tips focuses on intermediate level techniques for speeding up campaign analysis. Paid search managers will benefit, but really anyone using Excel for data analysis will find good information or a helpful refresher here.

Intermediate Tip: Get more out of Pivot Tables with calculated fields and by fixing #DIV/0 errors.

Pivot tables are truly a must-have Excel skill for marketers. If you haven’t used pivot tables yet, don’t be timid. You can’t damage your data set when working with pivot tables because they simply aggregate the data you are working with in a separate area, typically in a separate sheet. For more on getting into pivot tables read this earlier post from Annie Cushing.

Calculated Fields: For those with pivot table experience, you know that once data such as impressions, clicks, spend are aggregated, you still need to calculate metrics like CPA and CTR as you do with regular campaign exports. In other words, you can’t Sum cost/conversion or click-through data, you have to calculate them from the aggregated cost, click and impression data. This is what Calculated Fields do for you with just a little set up work. I have to admit, for years I calculated these metrics in cells outside of pivot tables not knowing about the magic of calculated fields.

A great thing about calculated fields is the data columns you want to use in your formula do not have to be included in your current pivot table. For example, in the demo below, John shows how to calculate CPA to find the total cost per conversion, but only spend is included in his pivot table, not cost/conversion.

One note, your calculated fields can’t use duplicate names from fields already in your data set. Thus the “CPA” field name in this example.

The calculated field will append a new column to the right in your pivot table. You can then sort and filter based on this new column.

Fixing #DIV/0 Errors: Whether you’re using calculated fields or not, if you divide a number by zero in Excel it returns the dreaded #DIV/0! error. This can obviously skew your analysis if you’re looking at CPA data.

“If you ignore the error, you’re missing out on valuable information — a keyword can spend thousands and still not convert a single time,” says John.

The solution: Use the IFERROR() formula in the calculated field to return “spend” or “cost” when there are 0 conversions. Open the Calculated Fields dialog again, go to the CPA field in the Name dropdown, and change the formula to: =IFERROR(Spend/Conversions,Spend)

Excel Tips IFERROR Calculated Field To Fix #DIV/0 Errors

John is using the 2013 version of Excel. If you’re using Excel 2010, you’ll find calculated fields under the Options tab in PivotTable Tools.

Qxcel tips pivot table calculated fieldsIFERROR can be used outside of calculated fields as well. It’s a good function for marketers to know, particularly for cost analysis. The logic of the formula basically says, if the calculation returns an error (#DIV/0! in this case) then return X, and X can be your total spend, or it can be a specific number or even text. Essentially, you can customize what the formula returns by putting what you want after that comma in the formula.

If you have any pivot table questions or tips you’d like to share, please do so in the comments below. Stay tuned tomorrow for the last segment in this series when John shares some advanced level Excel tips for PPC marketers. And be sure to check out PPC Excel Tips For Every Level: Part 1, Huge Time Savers For Beginners (And Beyond) if you missed it last week.

Related Topics: Channel: SEM | Google: AdWords | Microsoft: Bing Ads | SEM Tools | SEM Tools: PPC


About The Author: writes about paid online marketing topics including paid search, paid social, display and retargeting. Beyond Search Engine Land, Ginny provides search marketing and demand generation advice for ecommerce companies. She can be found on Twitter as @ginnymarvin.

Connect with the author via: Email | Twitter


Get all the top search stories emailed daily!  


Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Glenn Vinckx

    Neat trick, thanks for the advice Ginny!

  • Chad Summerhill

    Great post! Here’s another very useful PPC calculated field: http://www.chadsummerhill.com/calculate-weighted-average-position-excel-tableau/ that keeps us from mistakenly using the average of Average Position in our pivot tables.

  • Ginny Marvin

    Thanks, Chad, and great addition to share here.

  • John Gagnon

    Great post Chad! I definitely use that one too! The whole weighted avg. vs. simple average is a big problem if you get it wrong.

  • http://www.annielytics.com/ Annie Cushing

    Thanks for the mention, lady! Way to spread that pivot table love!

  • Damian Gonzalez

    Regarding the IFERROR, why would you return spend as CPA? What you’re basically saying with that if there were no conversions (denominator), make the denominator 1. Except it’s not 1, its 0. There’s no telling how much more spend is required to make a conversion.

  • Han Gao

    Pretty useful tips!

  • John Gagnon

    Good point Damian. When the denominator (conversions in this case) is 0, and your spend is $1000 — #DIV/0 is returned. It can valuable to have a $1000 CPA – so you can spot these issues. Especially when you sort highest to lowest CPA.

    But as you’re inferring, though $1000 CPA can be quite bad — using IFERROR means you can have a $1.00 CPA with ZERO conversions when you sort lowest to highest. And $1.00 could be excellent.

    Ultimately, it’s preference – many have found this tip to be very helpful, especially when spotting bad CPA.

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest


Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States


Australia & China

Learn more about: SMX | MarTech

Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!



Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide