I’ve said it many times and I’ll say this again: Excel if the ultimate SEO tool that can help you in any task (if you know how to use it right). I have already shared the huge collection of Excel tips and tricks for SEOs and web marketers – that post helped me to discover another awesome tool I was hugely impressed by.
ASAP Utilities (the tool has been shared with me by SEO Disco) is a powerful Excel add-in that fills the gaps in Excel and automates frequently used tasks. The addin can be paid but I was planning with its free edition.
Below I am sharing the most useful (for me) options I have found inside. But before that, I’d like to warn that you might need to tweak your Exel security settings before being able to use the tool. I was using the tool’s prompts to set my settings, so you are unlikely to have any problems. On top of all, to make sure the addin is going to work, navigate:
File -> Options -> Trust Center -> Trust Center Settings
- Make sure macros are enabled (in “Macro Settings”)
- Make sure the addins are NOT disabled (in “Add-ins”)
1. Find (and Remove) Duplicate Key Phrases: Clean up Your List
It often happens so that you have merged data from various keyword tools and now need to clean up your list (or just find the duplicated key phrases).
First select the range containing all your keywords (or just select the column with the keywords) in the Asap Utilities tab click “Range” menus and there select either:
- “Count and /or color duplicate in selection”
- OR “Clear duplicated values in the selection”
2. Play with Advanced Sorting: Group Your Keywords
The Advanced Sorting options found under Range menus lets you sort on many more properties than just the cell values.
- Number format
- Cell Color
- Cell Color Indexnumber
- Font Color
- Font Color Indexnumber.
- Font Size
- Font Name
- Text Length
- Random order (shuffle)
- Email address host (@…)
- Alphanumeric content based on the numeric portions
- Day of the week (start on Sunday)
- Day of the week (start on Monday)
For example, if you were using my previous tip on finding and coloring the key phrases based on the base word or modifier, you may end up with the column colored (or formatted) based on the keyword in it:
Now, with ASAP utilities you can sort the whole table based on your colors:
3. Round Numbers: Make your Keyword Performance Data Easier to Understand
One thing that kept bugging me about exported Google Analytics report is the huge numbers that are too hard to consume. Asap Utilities offer a three-click solution to the problem:
- Just select the column you want to work on;
- Navigate: Numbers -> Round Numbers
- Select the number of decimals to be left
Bonus! Other Tool Options I Was Happy to Discover:
1. Create a separate index sheet linking to all other sheets within a document (think how easily it is now to impress your client with multi-sheet Excel reports enhanced with an index page!)
2. Color each n-th (second, third, fourth) row or column in selection: for better readability and just to make your reports prettier!s
3. Change, extract, unlink hyperlinks:
- Easily extract all hyperlinks and send them in a a separate sheet (or in a separate column)
- Activate all hyperlinks in selection (i.e. make them clickable)
- Remove all hyperlinks;
- Change the hyperlink formula:
Of course, this just the tip of the iceberg. The addin has plenty more awesome features to cover any needs you may have. I was listing only those I was instantly impressed by and found particularly useful (for the tasks I come across). You are most likely to discover other features you will love. Good luck!