Excel is loved via maximum SEO specialists – and using many paid aficionados, too. Of all the search engine optimization tools I use on a day-by-day foundation, few are extra powerful or extra 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 most revenue earned. Pages by way of maximum leap fee. And every other element you can think about or kind using. 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 facts units with many values. Want to realize what number of humans clicked on a specific keyword from a specific referral supply with the aid of the 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 belongings 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 location, you’d copy them to a brand new column (Source Domain) and edit the 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 while you de-replica, it’s going to shop the first instance of that domain – the longest URL.
Excel LEN
3. Text to Columns
Text to columns is every other notable function of Excel. You’ve likely used it to make an a.CSV file beneficial by delimiting the information with the aid of commas. But did you recognize you could truly delimit 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 manner of taking the output from Screaming Frog or another crawler and speedy construct out a visual architecture map. By setting every subfolder in its personal column, you may use a pivot desk to find out exactly what number of pages are in each stage, then use the facts from the pivot table to speedy 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, allow’s say you have a large listing of keywords and also you want to locate matters that most effective 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 by 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 advent of many URLs. Let’s say you wanted to search a variety of sites to look if a particular keyword becomes in the textual content on that site… maybe you’re doing a link audit. You want to peer 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 positioned 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, considered 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, of 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 manner to slice and cube small information units and an important search engine marketing device that belongs to your arsenal.
