Excel is beloved with the aid of most search engine marketing professionals – and by many paid aficionados, too.
Of all the search engine optimization equipment I use on a each day basis, few are greater effective or greater versatile than spreadsheets.
While Word, PowerPoint, and Access have their little idiosyncrasies and demanding situations in daily work, Excel has stood the take a look at of time and keeps to be simply as treasured as it has ever been, Online SEO Tools
There is certainly no easier way to type and categorize statistics quickly with so little input required.
I use Excel every day to kind lists:
Keyword lists alphabetically.
Revenue reviews with the aid of most sales earned.
Pages by means of maximum soar rate.
And each different thing you could consider or type by using.
Excel is additionally terrific for de-duplicating lists. You can without difficulty take a big list of information, like inbound links, and sort it and cast off all the duplicates with a few clicks.
Pivot tables are extra special for categorizing and re-categorizing data units with many values. Want to know how many human beings clicked on a selected key-word from a particular referral supply by means of the date they clicked? There’s a pivot table for that.
And the VLOOKUP command is a bad man’s database, perfect for combining lists from a couple of places fast while not having to go into all of the data right into a cumbersome device like Access.
Chances are, you know these kinds of tricks.
So what follows are a few things you won't know you may do with Excel.
Note: While this chapter focuses on Excel, you can carry out most of these duties in any spreadsheet program, including Google Sheets.
1. WildCards
Excel supports the * command as a wildcard.
If you want to reduce off a part of a records set, it’s clean to do.
For example, let’s say you have got a very big listing of links but you most effective want one pattern URL from each area.
After you find/replace for http:// and https://, you can lessen each unmarried URL to most effective its pinnacle-level area with the aid of finding and changing for /* – the celebrity is a wildcard that removes the whole lot 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 could use that de-duplication function to remove all but one example of every URL.
Excel wildcards
2. LEN
This one counts the number of characters in the string.
This is notable for checking the length of titles or meta descriptions. But it could additionally be used to carry the longest string to the pinnacle.
Taking the preceding example, let’s say you desired one sample URL from every area which you deduplicated, however you wanted an indoors page as opposed to simply the top level domain.
You’d take the identical steps as above, but instead of modifying the URLs in place, you’d replica them to a new column (Source Domain) and edit those instead.
Then, use LEN to locate the person period of the Source URL and sort by way of Col A ascending and Col B descending.
That will bring the longest URL for every area to the top of the listing and then while you de-duplicate, it will store the primary instance of that area – the longest URL.
Excel LEN
3. Text to Columns
Text to columns is every other tremendous function of Excel.
You’ve probable used it to make a .Csv record beneficial, by using delimiting the facts through commas. But did you know you could certainly delimit any man or woman you want?
Semantic optimization
How to rank 70% of your pages better in five weeks. Trusted by over 30,000 webmasters, enterprise owners, and search engine optimization specialists.
Test Your Website
ADVERTISEMENT
I use this all of the time to delimit by using the backslash.
This is a terrific way to take an output from Screaming Frog or some other crawler and quick build out a visual architecture map.
By putting each subfolder in its very own column, you could use a pivot table to locate out precisely what number of pages are in each level, then use the records from the pivot table to fast build a visible map in something like Visio.
Excel textual content to columns
4. Find
The Find characteristic is one in all the maximum useful capabilities in Excel.
You can use it to cast off matters from lists of keywords.
For example, let’s say you've got a large listing of keywords and you need to find matters that only include one model of a phrase (beneficial whilst categorizing).
You would begin a new column, then use this components in which “phrase” is the phrase you want to find.
=FIND(“word”,B2,1)
This will display you the person count at which that word starts, and an blunders if the phrase isn’t there at all… like this listing wherein I searched for whatever with “mixed”:
Excel locate
Just sort with the aid of the “Sort” column descending, and you’ll bring all the key phrases that incorporate “mixed” to the top of the listing.
5. Concatenation
Concatenation is fantastic for placing together simple rows of facts, however it’s even higher for automating the creation of a whole lot of URLs.
Let’s say you desired to search quite a few web sites to look if a particular key-word turned into in textual content on that website… perhaps you’re doing a hyperlink audit and you want to see in case your client’s area call appears everywhere on a listing of websites.
You could (theoretically) concatenate a easy list of commands that you could then run in a crawler like Screaming Frog to see if there are any outcomes.
So, hypothetically speaking, you'll do this:
Take the command you need to add and placed it right into a row in excel.
Let’s say the web site is motorola.Com, and you’re seeking out the phrase “motorola” on a lot of websites, one in every of that's www.Cnn.Com.
Put the web page you are searching on in A1. You’ll use this command in Excel:
=”https://www.Google.Com/search?Q=web page:”&A1&” motorola”
If every row in column A has a brand new value in it, then in theory, you could have a complete set of Google commands to place in listing mode in Screaming Frog.
Excel Concatenation
Using the Custom configuration, you can search for any outcomes that had “No outcomes found for” motorola.Com within the code.
This is all theoretical, of course, due to the fact scraping Google is a violation of their phrases of service.
Summary
Hopefully now you see many makes use of for Excel that are beyond the usual mathematical and accounting makes use of.
Excel is certainly the excellent and fastest manner to slice and dice small records units, and an crucial SEO device that belongs to your arsenal.