Excel is loved by most SEO specialists – and using many paid aficionados, too. Of all the search engine optimization tools I use on a day-by-day basis, few are more powerful or more versatile than spreadsheets. While Word, PowerPoint, and Access have their little idiosyncrasies and demanding situations in each day’s work, Excel has stood the test of time and is still just as precious as it has ever been. There is actually no less complicated manner to kind and categorize information fast with so little input required.
I use Excel each day to kind lists:
Keyword lists alphabetically. Revenue reviews using mthe most revenue earned. Pages by way of maximum leap fee. And every other element you can think about or kind of use. Excel is also high-quality for de-duplicating lists. You can, without problems, take a large list of statistics, like inbound hyperlinks, and type it, and cast off all the duplicates with a few clicks. Pivot tables are out of the ordinary for categorizing and re-categorizing fact units with many values. Want to realize what number of humans who clicked on a specific keyword from a specific referral supply with the aid of a specific date they clicked? There’s a pivot desk for that. And the VLOOKUP command is a negative guy’s database, best for combining lists from a couple of places speedy while not having to go into all the statistics into a cumbersome device like Access.
Chances are, you know most of these tricks.
So what follows are a few things you may not realize you can do with Excel.
Note: While this bankruptcy focuses on Excel, you may perform a maximum of those duties in any spreadsheet application, along with Google Sheets.
1. WildCards
Excel supports the * command as a wildcard. If you want to cut off a part of a data set, it’s easy to do. For example, let’s say you have a massive list of hyperlinks; however, you only want one sample URL from every domain. If you need the backslash left on, update for /. If you want the / gone, update for an empty subject. Then you could use that de-duplication characteristic to eliminate all; however, one example of each URL. Excel wildcards
2. LEN
This one counts the number of characters in the string. This is splendid for checking the period of titles or meta descriptions. But it can additionally be used to carry the longest string to the top. Taking the previous instance, let’s say you wanted one sample URL from each area, which you deduplicated. Still, you wanted an interior page in place of just the pinnacle level domain. You’d take the same steps as above, but in place of editing the URLs in the location, you’d copy them to a brand new column (Source Domain) and edit them ones rather. Then, use LEN to discover the person period of the Source URL and sort by way of Col A ascending and Col B descending. That will carry the longest URL for every domain to the pinnacle of the listing, after which, when you de-replica, it’s going to show the first instance of that domain – the longest URL.
Excel LEN
3. Text to Columns
Text to columns is another notable function of Excel. You’ve likely used it to make a CSV file beneficial by delimiting the information with the aid of commas. But did you recognize that you could truly limit any person you need? 3 Simple Ways to Build Awesome Links for Your Business [Webinar] Join our next subsidized ThinkTank webinar on Wednesday, November 14, at 2:00 PM Eastern and discover ways to quickly and efficaciously execute 3 demonstrated hyperlinks building techniques.
Register Now ADVERTISEMENT
I use this all of the time to delimit by using the backslash. This is becoming an excellent way of taking the output from Screaming Frog or another crawler and speedily constructing a visual architecture map. By setting every subfolder in its own column, you may use a pivot table to find out exactly how many pages are in each stage, then use the data from the pivot table to speedily build a visible map in something like Visio.
Excel textual content to columns
4. Find
The Find characteristic is one of the maximum useful functions in Excel. You can use it to remove matters from lists of key phrases. For example, let’s say you have a large listing of keywords and also you want to locate matters that most effectively incorporate one model of a word (useful when categorizing). You could begin a new column, then use this formula where “phrase” is the phrase you want to locate.
=FIND(“word”,B2,1)
This will display you the man or woman matter at which that phrase starts offevolved, and blunders if the phrase isn’t there in any respect… like this listing where I looked for whatever with “combined”:
Excel locate
Just kind on the “Sort” column descending, and you’ll deliver all the key phrases that incorporate “mixed” to the top of the list.
5. Concatenation
Concatenation is remarkable for placing together simple rows of data, but it’s even higher for automating the creation of many URLs. Let’s say you wanted to search a variety of sites to see if a particular keyword appears in the textual content on that site… maybe you’re doing a link audit. You want to see if your patron’s domain call appears anywhere on a listing of websites. You ought to (theoretically) concatenate an easy listing of instructions that you could then run in a crawler like Screaming Frog to see if there are any effects.
So, hypothetically speaking, you will try this:
Take the command you want to feature and position it right into a row in Excel. Let’s say the website is motorola.com, and you’re looking for the word “Motorola” on a whole lot of websites, one of which is www.cnn.com. Put the site you’re looking on in A1. You’ll use this command in Excel: If every row in column A has a brand new price in it, then, in theory, you could have a full set of Google instructions to install listing mode in Screaming Frog.
Excel Concatenation
Using the Custom configuration, you may search for any outcomes with “No consequences determined for” motorola.com in the code. This is all theoretical, in direction, because scraping Google is a violation of their terms of service.
Summary
Hopefully, now you see many uses for Excel, which might be past the standard mathematical and accounting uses. Excel is surely an excellent and quickest way to slice and cube small information units and an important search engine marketing tool that belongs in your arsenal.
