Is it just me or is sorting data and discovering all the little tricks that Excel has to offer like the smell of fresh-cut grass? No, it’s more, it’s like finally reaching the summit of that 14er you’ve been putting off for far too long. It makes you feel giddy inside and is the perfect addition to big data problems. Seriously, all I need to make me happy in life is good decent food and Excel (at work anyway).
So, when I’m asked for a quick way to get things done with data, of course I’m the first to raise my hand and offer assistance. This is one of those cases, and I thought it may be best to go ahead and write out the details so you all can join in on all this Excel fun! Don’t worry, the list may look long, but I did a step-by-step guide, and once you learn these little tricks, you’ll be just as fast as me (and quite possibly, just as geeky – that is a good thing).
What is this quick Excel lesson about?
For all of you Moz Pro account members, there’s a time where you’ll likely come to a point where you need to delete keywords or remove duplicate keywords from some of your campaigns to free up some space for new clients coming aboard. This list shows you how to export keywords from an account and quickly see if you have duplicate keywords or keywords that need to be removed.
Don’t use Moz?
That’s okay, this will be a quick run through to see duplicate issues and how to create a pivot table to see how many issues you may have in your data set.
The instructions to keyword slimming victory.
- Login to Moz Pro account.
- Go to the dashboard of the campaign you want to remove duplicate keywords from.
- From the left, sidebar navigation, click on Search > Keyword Rankings.
- Scroll down to the title that reads Tracked Keyword Overview. To the right of that title is a CSV download button (not the ‘Rankings History CSV,’ but the one right next to that). Click on that to export all of your keywords.
- When open the CSV file, you’ll have a lot of information that you don’t need for this specific topic, so we’ll need to clean things up. Start by deleting rows 1-5.
- Then delete columns C-X (columns A-B are the only ones we’re worried about at this point).
- Highlight A1 and B1, then from the HOME tab, click on Sort & Filter > Filter. This will add a filter to those 2 cells, making it easier to sort.
- Go ahead and highlight columns A and B, then double-click on the dividing line between the columns (at the top, between the A/B letters) – this will expand the columns so you can see all the content below.
- Now, just highlight column A. From the HOME tab, click on Conditional Formatting > Highlight Cell Rules > Duplicate Values. Click OK.
- From the top of the Keyword column (cell A1), filter the results in alphabetical order. Then sort by color > pink.
- Now, all of your duplicates are at the top, alphabetized and you can go through and see what duplicates you want to delete or don’t want to delete. Keeping in mind that there will be duplicates if you’re tracking national and local cities.
- Let’s take this one step forward and create a pivot table so we can quickly see where issues may be. From the INSERT tab, click on Pivot Table. The range should auto set to all of the data, but if it doesn’t just re-highlight the correct data set, and then click OK.
- From the right sidebar labeled PivotTable Fields, drag Keyword to the Rows box below, and then drag Location to the Values box below. What should be created in the spreadsheet is how many of each keyword exists. If I know I’m tracking keywords on a national level and for 2 cities, then I shouldn’t have more than 3 duplicates of the same keyword, right?
- Excel doesn’t let you filter ‘Count of Location,’ so highlight all of the data (click on the first cell, click CTRL + SHIFT + à, then without letting go of CTRL + SHIFT, click â too – this is an easy way to highlight all of the data). Once all of the data is highlighted, click CTRL + C (to copy the data).
- Create a new tab/sheet, and right-click on A1, and then choose Paste Values Only under Paste Options (it looks like a clipboard with numbers).
- Again, adjust the columns so you can clearly see the data and add filters to A1 and B1 (see #7 above).
- Click on the filter in B1. The options you see in the list are the number of times each keyword appears. The largest number is the total count of keywords, so ignore that, but if you see anything higher than a 3 (see what I said about 3 duplicates in #13), then those keywords would need to be pulled from the list.
From here, for the keywords you want to delete, there’s not a quick way to do it. You’ll have to go back to Moz in Add & Manage Keywords and individually click the box next to the keywords you want to delete, and then click Delete Keywords at the top of the list.