Free Excel Negative Keyword Tool

YouTube Preview Image

Negative Keyword Tool for ExcelThis is the new and improved version of my initial Excel negative keyword tool.  That tool was not very good because it could only identify strings, not whole words.

With the help of the genius Excel gurus over at ExcelForum, I was able to throw together a process for analyzing search query reports for the purposes of identifying negative keywords... but also to identify keyword opportunities.

I started out with the idea of

What if I could assign a value to each word within a given keyword phrase?

Then I thought,

Well, I'd need to know every single word within the keyword list.

That's where it started.

How to use the tool

First download your search query reports from Adwords and Bing Ads.

Next, remove all columns except for cost and conversions.

Next, you can clean your search query list using an excel formula called 'Substitute'.  I essentially use this formula as a find/replace where it's substituting nothing in place of the characters I don't want in my keyword list.

Next, pivot the data so all search queries are unique and have total cost and conversions summed.

Next, I added my my Excel keyword density tool to analyze the phrase density of the search query report.  This will output 1-word, 2-word and 3-word phrases and their frequency within your search query report.

Next, I input the one-word phrases under the "Lookup Value" column heading.  Then drop down the formulas.  Doing this assigns a weighted value to each word.

Lastly, now that we have assigned a weight to each word to ALL words in our search query report, we just need to sum those weights for each ACTUAL search query.  That's what the formula in the "Results" column does.

How can I add a different language?

Special thanks to Alexander for showing me this.  He wanted to make the tool evaluate Russian characters so he edited the code below and also allowed it to accept numbers!  I'm thinking this should work with other language alphabets but I'm not 100% sure.



The negative keyword tool won't analyze words greater than 7 words.

Download the Excel Negative Keyword Tool!

Build PPC Campaigns Faster With

PPC Campaign Generator

Build PPC campaigns in 5 simple steps!
Simple enough for beginners, powerful enough for advanced users
Training videos and support
FREE stand-alone version of my Keyword Grouper software with purchase (Limited-time!)

Tags: , ,

12 Responses to “Free Excel Negative Keyword Tool”

  1. Sam June 11, 2013 at 11:55 am #

    Awesome tool - very handy.

    I wanted to query one of your formulas though - on the tab "3. Evaluate" - column J "weight"...
    The weighting of a keyword is calculated (if conversions >0) as conversions/total conversions in column H. However, column H double counts conversions (each conversion will be counted multiple times - once for every keyword in the string) - wouldn't the weighting formula be more accurate if you used the total in column C?

    Either way - love it... and thank you so much for distributing it free.

    • Jarad Collier June 16, 2013 at 5:11 pm #

      I'm certain you're right about the double-counting issue. Using the total conversions in column C as a weighting is probably more accurate :) Thanks for offering your valuable suggestion! I'll have to dig into this in the future.

    • Victor Subia July 10, 2013 at 5:01 pm #

      I double checked the formula and the double counting doesn't matter because the ratios stay the same as in all other keywords get double counted as well so the formula works as is. I wanted to make sure it was accurate too = )

      • Victor Subia July 10, 2013 at 5:39 pm #

        Sorry I double checked again and Jarad may be right. I'll update you if I find a fix for this.

        • Jarad Collier July 14, 2013 at 11:44 pm #

          I just wanted to comment on the very interesting PDF you sent me Victor in case other people have more ideas. Victor's idea is to take the COST and CONVERSION values and divide them by the # of words within a keyphrase.

          For example, if the keyword is "where to buy ppc software", and its cost is $10 and its conversion is 1, you would divide the cost evenly amongst the words in this phrase so $10/5 = $2. This would be the cost attribution. For conversion, you take 1/5 = 0.2 conversion attribution.

          I think this approach is really interesting. I also can see that it is weighting each word equally though which, in my humble opinion, may not be the best idea because of less important words (to, of, as, i, a, and, etc.). However, I think using some method for weighting the important words may improve results. I'd say try Victor's approach out and see if you like the results!

          Thanks again Victor for sharing this great idea.

  2. tim July 15, 2013 at 7:37 pm #

    Do you know if this works with the latest version of excel on mac?


    • Jarad Collier July 16, 2013 at 2:59 pm #

      Good question Tim. I don't happen to have the latest version of Excel so I don't know. If you try it, let me know!

  3. Dave January 9, 2014 at 6:47 am #

    Hi - I've been enlightened as I step away from "testing the waters" to now walking into building my PPC accounts out CORRECTLY!

    Your training videos and software seem to be a great place to be for just that.

    I am posting here because I d/l'd the negative keyword Excel and I think it is great. Weighting each word based on having a low cost plus a high rate of conversion (or vice-versa or somewhere in between.) Make a lot of sense to me for discovering negatives.

    My question is - WHEN will your next update be coming out and will it have this Negative Keyword tool involved? (Also will it have the import upgrades you mentioned in your last training video? One button export/upload to all PPC advertisers AND negative keyword list export/upload as well?)

    Thanks again for the work you do here and the information you freely part!

    • Jarad Collier January 9, 2014 at 6:19 pm #

      Glad the tool helps. Currently, I am working on making an online campaign building tool that's much easier than PPC Campaign Generator - less clicking buttons and knowing nuances and even learning how to use the tool... just basic inputs and an advanced algorithm that is smart enough to build it from these basic inputs. That project is on hold though because I'm finishing up a project for an Excel add-in reporting tool called PPC Report Generator.

      The export as 1 file for all platforms is appealing but my attention is elsewhere right now in efforts to make better, easier solutions.

  4. Rodrigo Marbán April 19, 2014 at 5:32 am #

    Awesome tool! +1....

    I was already building my own but I was having a lot of trouble. What I had in mind was more or less the same, but my Excel knowledge is limited, so I am glad I found your tool. Thank you very much for sharing.


  1. Find Negative Keywords Using ExcelPPC Campaign Generator - December 18, 2012

    [...] UPDATE: I created a NEW, better Negative Keyword Tool.  Get it here! -> Excel Negative Keyword Tool [...]

  2. Build Successful PPC Campaigns | PPC Campaign GeneratorPPC Campaign Generator - January 2, 2013

    [...] resulting in clicks that you pay for.  To identify potentially negative keywords, I've created a negative keyword tool (warning! It's pretty complex) in Excel that can be used to identify keywords with low probability [...]

Leave a Reply