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:
- How can I clean a list of keywords exported with extra characters?
- How can I find duplicate searches?
- How can I see which keywords in my keyword research are already in Conductor Searchlight?
- 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.
How to do it:
- Open the dialogue box (Ctrl+R on a PC) or under Home>Find&Sort>Replace.
- Put in the character you wish to replace (in the image above, the Column labeled “Things” includes a few common ones we see).
- Leave “Replace with:” blank.
- 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 ].
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.
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.
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.”
The =COUNTIF function returns a binary 1 or 0 if a cell value in a certain range meets a criteria.
The full formula used in the following example is: =IF(COUNTIF(B:B,K8),VLOOKUP(K8,B:B,1,0),”A”)
The steps to replicate are:
More from my site
- Export the keywords currently in Searchlight under a domain by going to Settings>Domain>Batch (this forms the green table).
- Add the keywords you have found during your Keyword Research under column K.
- Place the formula above in Column L.
- Column M is optional and just shows that =COUNTIF responds with a binary output.
- Filter Column L to be the As.
- In this example, Keywords: pigeon, pig, and piglet would all be new Keywords to add into 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?
Excel Feature: PIVOTTABLE & PIVOTCHART
Create a PivotTable by clicking into the INSERT ribbon and then PivotTable (the first option):
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.
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:
The Bar Chart option gives us the best visualization. We can click on “Total” to customize to “Total Searches by Location.”