How To Use Seo Tools For Excel

This post will introduce you to the many ways of how to use SEO tools for Excel to make your life easier. We’ve got a few different tools available, some of which are free and some of which need a paid subscription, but all of them are very useful.

How To Use Seo Tools For Excel

Excel is beloved by most SEO professionals – and by many paid aficionados, too.

Of all the SEO tools I use on a daily basis, few are more powerful or more versatile than spreadsheets.

While Word, PowerPoint, and Access have their little idiosyncrasies and challenges in daily work, Excel has stood the test of time and continues to be just as valuable as it has ever been.

There is simply no easier way to sort and categorize data quickly with so little input required.

I use Excel daily to sort lists:

• Keyword lists alphabetically.
• Revenue reports by most revenue earned.
• Pages by highest bounce rate.
• And every other thing you can think of or sort by.

Excel is also great for de-duplicating lists. You can easily take a huge list of data, like inbound links, and sort it and remove all the duplicates with a few clicks.

Pivot tables are phenomenal for categorizing and re-categorizing data sets with many values. Want to know how many people clicked on a particular keyword from a particular referral source by the date they clicked? There’s a pivot table for that.

And the VLOOKUP command is a poor man’s database, perfect for combining lists from multiple places quickly without having to enter all of the data into a cumbersome tool like Access.

Chances are, you already know all these tricks.

So what follows are some things you might not know you can do with Excel.

Note: While this chapter focuses on Excel, you can perform most of these tasks in any spreadsheet program, including Google Sheets.

1. WildCards

Excel supports the * command as a wildcard.

If you want to cut off part of a data set, it’s easy to do.

For example, let’s say you have a very large list of links but you only want one sample URL from each domain.

After you find/replace for http:// and https://, you can reduce every single URL to only its top-level domain by finding and replacing for /* – the star is a wildcard that removes everything after the first backslash.

If you want the backslash left on, replace for /.

If you want the / gone, replace for an empty field.

Then you can use that de-duplication function to remove all but one example of every URL.

2. LEN

This one counts the number of characters in the string.

This is great for checking the length of titles or meta descriptions. But it can also be used to bring the longest string to the top.

Taking the previous example, let’s say you wanted one sample URL from each domain that you deduplicated, but you wanted an interior page instead of just the top level domain.

Grow Your Business With Vetted Freelancers Today
Fiverr Business gives your team the tools to collaborate and delegate with the world’s largest selection of talented freelancers for any need.Get StartedADVERTISEMENT

You’d take the same steps as above, but instead of editing the URLs in place, you’d copy them to a new column (Source Domain) and edit those instead.

Then, use LEN to find the character length of the Source URL and sort by Col A ascending and Col B descending.

That will bring the longest URL for each domain to the top of the list and then when you de-duplicate, it will save the first instance of that domain – the longest URL.

3. Text to Columns

Text to columns is another brilliant feature of Excel.

You’ve probably used it to make a .csv file useful, by delimiting the data by commas. But did you know you can actually delimit any character you want?

I use this all the time to delimit by the backslash.

This is a terrific way to take an output from Screaming Frog or another crawler and quickly build out a visual architecture map.

By putting each subfolder in its own column, you can use a pivot table to find out exactly how many pages are in each level, then use the data from the pivot table to quickly build a visual map in something like Visio.

4. Find

The Find function is one of the most useful functions in Excel.

You can use it to eliminate things from lists of keywords.

For example, let’s say you have a huge list of keywords and you want to find things that only contain one version of a word (useful when categorizing).

You would start a new column, then use this formula where “word” is the word you want to find.

=FIND(“word”,B2,1)

This will show you the character count at which that word starts, and an error if the word isn’t there at all… like this list where I looked for anything with “mixed”:

Just sort by the “Sort” column descending, and you’ll bring all the keywords that contain “mixed” to the top of the list.

5. Concatenation

Concatenation is great for putting together simple rows of data, but it’s even better for automating the creation of a lot of URLs.

Let’s say you wanted to search a lot of sites to see if a particular keyword was in text on that site… maybe you’re doing a link audit and you need to see if your client’s domain name appears anywhere on a list of sites.

You could (theoretically) concatenate a simple list of commands that you could then run in a crawler like Screaming Frog to see if there are any results.

So, hypothetically speaking, you would do this:

Take the command you need to add and put it into a row in excel.

Let’s say the site is motorola.com, and you’re looking for the word “motorola” on a lot of websites, one of which is www.cnn.com.

Put the site you are searching on in A1. You’ll use this command in Excel:

=”https://www.google.com/search?q=site%3A”&A1&”%20motorola”

If each row in column A has a new value in it, then in theory, you could have a full set of Google commands to put in list mode in Screaming Frog.

Using the Custom configuration, you could look for any results that had “No results found for” motorola.com in the code.

This is all theoretical, of course, because scraping Google is a violation of their terms of service

seo premium tools

SEO is a lot more complex than most people give it credit for. Many regard it as something akin to voodoo or alchemy. But SEO can be incredibly powerful and lucrative if you have the right tools for the job. That doesn’t mean you have to splurge on every piece of software you come across. Having prowled through the interwebs, trial and premium tested a number of tools out there, I have come up with this selection of MUST HAVE Paid SEO tools you should grab your wallets to purchase when the need arises.

1 – SEM Rush Pro

for advanced keyword research – from $79.95/month website: www.semrush.com

The heart of Search Engine Optimisation is and always has been keyword research. There are free tools available online such as Google’s External Keyword Tool, but those programs aren’t always entirely accurate and are often lacking in features. In SEO, every possible advantage counts and can add up big time in the long run. You want the best information available when it comes to keywords, even if you have to pay for it. SEM Rush is the best way to locate lucrative niches and the keywords that will give you control over them.

SEM Rush provides query information for 40 million keywords, delivering results from at least 20 million domain names. You can easily rank specific keywords and phrases based on how high the competition is for any give term, or by any number of different criteria. Employing sophisticated algorithms to deliver up-to-date information on any keyword, SEM Rush is arguably the best keyword tool on the market today. While they offer a stripped-down free version, the paid version is well worth the extra cost. Recent improvements include a new database with suggestions for users in the UK, focused specifically on UK English.

2 – SEOMoz Pro

for on-page SEO analysis & SEO campaign management – from $99/month website: www.seomoz.org

In the “everything but the kitchen sink” department, SEOMoz Pro is the ultimate all-inclusive website performance analysis machine. SEOMoz Pro is an on-page Optimisation powerhouse, giving users data on practically every aspect of their chosen websites. One great feature is Crawl Diagnostics, which serves up automatic reports on server errors, client errors, and redirect errors. It also delivers traffic statistics, relevant bounce and click-through rates, and link analysis. SEOMoz is one of the best ways to gather information on relative and absolute website health and performance without having to rely on a grab bag of separate tools.

Once you’ve gotten used to using SEOMoz Pro’s My PRO Dashboard, which can be a bit intimidating, you’ll be able to compile performance reports quicker than you thought. The extensive graphical presentation capabilities of SEOMoz Pro allow you to fire up charts and visual representations of information on the fly for quick comparisons. SEOMoz Pro has more features than you can shake a stick at, or fully describe in a paragraph or two. Fortunately, they offer a 30 day free trial so that you can experience everything it has to offer before deciding whether or not it’s overkill for your needs.

3 – Majestic SEO

for advanced link development & competitor link research – from £29.99/month
website: www.majesticseo.com

Alongside keyword research, link building is another one of the obvious cornerstones of proper SEO. Majestic SEO brands itself as a “link intelligence tool”, otherwise known as an analytics program. But it would be a mistake to label Majestic as just another web analytics software tool. Majestic has a number of handy features that make it a worthy addition to your marketing war chest. Capable of tracking referrals, analysing the click-through rates of differing anchor text, and organising data on backlink effectiveness, Majestic puts you on the right track towards maximising your conversion rate.

Majestic is particularly well suited to tracking referrals from specific websites, giving you more pertinent information on who is giving you link karma. Once you’ve added a page to track to their servers, Majestic will crawl it every few hours to give you the most up-to-date information on how well-connected your website is at the moment. In addition to its many backlink analysis capabilities, Majestic offers a number of convenient ways to export your data for further study. One such feature is a handy export-to-CSV function that makes creating spreadsheets and charts a snap. When it comes to tracking backlinks, Majestic SEO one of the best options available.

4 – Advanced Web Rankings (enterprise edition)

for ranking monitoring & reporting – purchase price $399 
website: www.advancedwebranking.com

Like it or not, the Search Engine Results Pages still play a huge part in how successful your SEO efforts are. Much as Facebook would love to supplant Google with its own “social search”, most people still hit up search engines for information rather than relying on the likes and dislikes of strangers. Advanced Web Rankings uses proxy servers to gauge the relative strength of your websites, delivering results in real time. More specifically, AWR allows you to see how well your sites rank for hundreds if not thousands of specific keywords and search terms.

Tracking the everyday changes of your standings in the results pages is a lot easier with Advanced Web Rankings, thanks to its intuitive user interface and manageable functionality. It features automatic alerts or “triggers”, decent graphing capabilities to make sense of your data, and a clean and simple layout. In other words, the learning curve with AWR is shallow enough for you to be up and running in under an hour. If you manage many websites, either for yourself or as an agency, it’s just the ticket and scales up to handle an almost unlimited volume of keywords.

5 – Raven SEO Tools

for end to end SEO campaign management and reporting – from $99/month
website: raventools.com

Any SEO consultant worth his or her salt uses a master management program to oversee their various campaigns. Juggling all of your promotional efforts without a dedicated SEO management suite is difficult at best. That’s why it makes sense to automate some of the more tedious tasks with something like Raven SEO Tools. Raven makes it easy to organise your campaigns and plan ahead, thanks to a bevy of helpful analytics and tracking tools. If there’s an equal to Majestic SEO in terms of overall power and bang for your buck, Raven SEO Tools might just be it.

Raven SEO Tools stands apart from the crowd with its Quality Analyser. The Quality Analyser allows you to enter a domain name and get a torrent of information relating to its overall performance based on a number of different metrics. It spits out Google PageRank, Alexa statistics, DMOZ standings, how many pages are indexed in Google and Yahoo, and more. In addition, Raven integrates Google’s Keyword Tool backend for keyword research and analysis. With its built-in Link Manager, Content Manager, and comprehensive analytics tools, Raven is the complete package in terms of SEO management.

6 – UserTesting.com

for usability testing & web design – from $29.99 per user
website: www.usertesting.com

Split testing, also known as A/B testing or multivariate testing, is a surefire way to determine which of your web designs are working out. Even better than split testing is direct feedback from real people. Numerous studies have shown that usability is just as important as page load times when it comes to reducing bounce rates and increasing click-through rates – two rankings factors introduced with the Google Panda update. First impressions are important, so it helps to try out a few different landing page layouts to see which ones your visitors react to most favourably. That’s whereUserTesting.comcomes into the equation.

It’s a helpful online usability service that pays testers to interact with your website, rank it based on various usability parameters, and give feedback as to how you can improve your design.UserTesting.comis an effective way to field test various web layouts before you put them into production mode.

You can log into your account and see people accessing your site in real time, read their reviews, and use their feedback to rejigger your designs to improve conversions. While their own user interface could use a little work, it’s one of the best ways to get constructive criticism on your web pages when you need the information most.

7 – Hootsuite

for Social Media Management – $5.99/month
website: hootsuite.com

Whether you like is or not, social media is now part of SEO. Even if social media platforms may have become a bit over-hyped in today’s 24 hour news cycle, they’re still highly effective ways to increase your audience and raise your profile. Managing your numerous social media profiles can become a seriously taxing endeavour.

Compounding the confusion is the proliferation of third-party management tools for Twitter, Facebook, now Google+ and many other social media platforms. It makes more sense to have one unified tool to manage tweets, wall posts, and other forms of social media communication. Hootsuite can manage your Facebook, Twitter, LinkedIn, Foursquare, Myspace, and WordPress accounts from one location.

Hootsuite is a full-featured command centre that ingrates with Ping.fm, Google Analytics, Facebook Insights, and other services to streamline your social media interaction. You can pull in RSS feeds, post to your blog, and chat with others using a wide variety of services in real time. Better yet, Hootsuite has impressive tracking and analytics tools to keep abreast of the response your posts receive.

While it offers a limited free version, their paid version is only $5.99 a month and allows you to add unlimited accounts and removes all sponsored advertisements. If social media is a big part of your SEO efforts, and it should be, Hootsuite is a wise investment.

It may sound cliché, but you often need to spend money to make money. There’s a difference between non-productive spending and investments, however. The above SEO tools are an investment in your future SEO success. If you want to make SEO work for you and boost your revenue, these programs produce immediate results

Conclusion

Let us know your thoughts in the comment section below.

Check out other publications to gain access to more digital resources if you are just starting out with Flux Resource.
Also contact us today to optimize your business(s)/Brand(s) for Search Engines