SEO

Excel Power User’s SEO Formula Power Pack

In our last Excel-focused piece, we showed you 10 fundamental Excel formulas most commonly used to answer SEO questions.

This post, catered to the more advanced SEO Power User, showcases nesting formulas together as well as a few other techniques to help clean up data. Make the jump by clicking a question below:

  1. How can I clean a list of keywords exported with extra characters?
  2. How can I find duplicate searches?
  3. How can I see which keywords in my keyword research are already in Conductor Searchlight?
  4. How can I see how many Searches I have per location in my Batch File? Can I make visualizations off of information around my Batch Upload file?

1. How can I clean a list of keywords exported with extra characters?

Excel Feature: FIND AND REPLACE

We showed you how to use the =SUBSTITUTE formula to swap out a piece of a URL in our previous article. An alternative to this is using the Find and Replace function in Excel.

find-and-replace-feature-excel

How to do it:

    1. Open the dialogue box (Ctrl+R on a PC) or under Home>Find&Sort>Replace.
    2. Put in the character you wish to replace (in the image above, the Column labeled “Things” includes a few common ones we see).
    3. Leave “Replace with:” blank.
    4. Click “Find All” if you want to see what is found first, otherwise click “Replace All.”

Repeat for each character you wish to remove (don’t forget to look for both opening and closing variations like <, >, [, or ].
find-and-select-excel

2. How can I find duplicate searches?

Excel Feature: CONDITIONAL FORMATTING

Conditional Formatting is a great way to quickly highlight duplicate values, but that’s only the tip of the iceberg.

The feature is found in the HOME view under the Conditional Formatting icon > Highlight Cell Rules > Duplicate Values.

duplicate-values-excel
duplicate-values-box-excel-formula
red-duplicate-values-excel-feature

Note that in this example, there are a number of duplicate values due to the nature of a Keyword Batch file; however, focusing in on the keywords, we see that there are two instances of “buffalo.” As their rows are completely the same otherwise, this is an error that will cause the batch upload to fail.

Deleting or editing one of the results will fix this (unless you meant “buffalos” or wanted to track the keyword on a different Device Type like “Tablet” or “Smartphone”).

If you have a list of searches or URLs you wish to remove duplicate values from, you can do so under DATA > Remove Duplicates, but as a best practice I like to highlight them first to see what possible reasons for duplications might be.

remove-duplicates-excel

3. How can I see which keywords in my keyword research are already in Conductor Searchlight?

Excel Formula: If, CountIf, VLookUp 

We showed you how to use the =VLOOKUP formula in the last post. Let’s add two more.

=IF and =COUNTIF helps you quickly find a list of keywords to add into Conductor Searchlight from extra keyword research.

The =IF function is pretty straight-forward and follows this logic: “if this happens do this, if not do this.”

if-function-excel-formula

The =COUNTIF function returns a binary 1 or 0 if a cell value in a certain range meets a criteria.

countif-function-excel-formula

The full formula used in the following example is: =IF(COUNTIF(B:B,K8),VLOOKUP(K8,B:B,1,0),”A”)

if-countif-excel

The steps to replicate are:

    1. Export the keywords currently in Searchlight under a domain by going to Settings>Domain>Batch (this forms the green table).
    2. Add the keywords you have found during your Keyword Research under column K.
    3. Place the formula above in Column L.
    4. Column M is optional and just shows that =COUNTIF responds with a binary output.
    5. Filter Column L to be the As.
    6. In this example, Keywords: pigeon, pig, and piglet would all be new Keywords to add into Searchlight.

Looking for a way to do Keyword Research to create Column K? Check out this keyword research article, and if you’re a Conductor customer, don’t forget you have Audience Intent Explorer:

4. How can I see how many Searches I have per location in my Batch File? Can I make visualizations off of information around my Batch Upload file?

Excel Feature: PIVOTTABLE & PIVOTCHART

Create a PivotTable by clicking into the INSERT ribbon and then PivotTable (the first option):

pivot-table-excel

Set the Rows as the Locations and the Values as Count of Location. Then, we can quickly see the number of Searches for each location.

batch-upload-excel

Create your PivotTable in another Tab (Sheet).

If we want to see this visually, we can add a PivotChart through INSERT and then PivotChart>PivotChart:

pivot-chart-excel

The Bar Chart option gives us the best visualization. We can click on “Total” to customize to “Total Searches by Location.”

total-searches-keywords

 

Did more Excel questions pop into your mind? Share with us your questions, feedback or errors in the comments below!

excel-for-seo-tricks

Excel for SEO Success: Top 10 Formulas Every SEO Needs to Know

Read More