5 Essential Tips & Tricks You Might Not Know


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

Of all of the web optimization instruments I exploit each day, few are extra highly effective or extra versatile than spreadsheets.

While Word, PowerPoint, and Access have their little idiosyncrasies and challenges in day by day work, Excel has stood the take a look at of time and continues to be simply as beneficial because it has ever been.

There is solely no simpler method to type and categorize knowledge rapidly with so little enter required.

I exploit Excel day by day to type lists:

  • Keyword lists alphabetically.
  • Revenue studies by most income earned.
  • Pages by highest bounce fee.
  • And each different factor you possibly can consider or type by.

Excel can be nice for de-duplicating lists. You can simply take an enormous listing of information, like inbound hyperlinks, and type it and take away all of the duplicates with a couple of clicks.

Pivot tables are phenomenal for categorizing and re-categorizing knowledge units with many values. Want to know the way many individuals clicked on a specific key phrase from a specific referral supply by the date they clicked? There’s a pivot desk for that.

And the VLOOKUP command is a poor man’s database, good for combining lists from a number of locations rapidly with out having to enter the entire knowledge right into a cumbersome instrument like Access.

Chances are, you already know all these methods.

So what follows are some stuff you won’t know you are able to do with Excel.

Note: While this chapter focuses on Excel, you possibly can carry out most of those duties in any spreadsheet program, together with Google Sheets.

1. WildCards

Excel helps the * command as a wildcard.

If you need to lower off a part of a knowledge set, it’s simple to do.

For instance, let’s say you will have a really giant listing of hyperlinks however you solely need one pattern URL from every area.

After you discover/change for http:// and https://, you possibly can cut back each single URL to solely its top-level area by discovering and changing for /* – the star is a wildcard that removes every little thing after the primary backslash.

If you need the backslash left on, change for /.

If you need the / gone, change for an empty area.

Then you should use that de-duplication operate to take away all however one instance of each URL.

Excel wildcards

2. LEN

This one counts the variety of characters within the string.

This is nice for checking the size of titles or meta descriptions. But it can be used to deliver the longest string to the highest.

Taking the earlier instance, let’s say you wished one pattern URL from every area that you simply deduplicated, however you wished an inside web page as an alternative of simply the highest degree area.

You’d take the identical steps as above, however as an alternative of modifying the URLs in place, you’d copy them to a brand new column (Source Domain) and edit these as an alternative.

Then, use LEN to search out the character size of the Source URL and type by Col A ascending and Col B descending.

That will deliver the longest URL for every area to the highest of the listing after which while you de-duplicate, it is going to save the primary occasion of that area – the longest URL.

Excel LEN

3. Text to Columns

Text to columns is one other sensible characteristic of Excel.

You’ve most likely used it to make a .csv file helpful, by delimiting the information by commas. But do you know you possibly can truly delimit any character you need?

I exploit this on a regular basis to delimit by the backslash.

This is a terrific method to take an output from Screaming Frog or one other crawler and rapidly construct out a visible structure map.

By placing every subfolder in its personal column, you should use a pivot desk to search out out precisely what number of pages are in every degree, then use the information from the pivot desk to rapidly construct a visible map in one thing like Visio.

Excel text to columns

4. Find

The Find operate is likely one of the most helpful capabilities in Excel.

You can use it to remove issues from lists of key phrases.

For instance, let’s say you will have an enormous listing of key phrases and also you need to discover issues that solely comprise one model of a phrase (helpful when categorizing).

You would begin a brand new column, then use this formulation the place “word” is the phrase you need to discover.

=FIND(“word”,B2,1)

This will present you the character depend at which that phrase begins, and an error if the phrase isn’t there in any respect… like this listing the place I appeared for something with “mixed”:

Excel find

Just type by the “Sort” column descending, and also you’ll deliver all of the key phrases that comprise “mixed” to the highest of the listing.

5. Concatenation

Concatenation is nice for placing collectively easy rows of information, nevertheless it’s even higher for automating the creation of a number of URLs.

Let’s say you wished to look a number of websites to see if a specific key phrase was in textual content on that web site… possibly you’re doing a hyperlink audit and it’s worthwhile to see in case your consumer’s area identify seems wherever on a listing of websites.

You may (theoretically) concatenate a easy listing of instructions that you would then run in a crawler like Screaming Frog to see if there are any outcomes.

So, hypothetically talking, you’d do that:

Take the command it’s worthwhile to add and put it right into a row in excel.

Let’s say the location is motorola.com, and also you’re in search of the phrase “motorola” on a number of web sites, one in all which is www.cnn.com.

Put the location you might be looking on in A1. You’ll use this command in Excel:

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

If every row in column A has a brand new worth in it, then in concept, you would have a full set of Google instructions to place in listing mode in Screaming Frog.

Excel Concatenation

Using the Custom configuration, you would search for any outcomes that had “No results found for” motorola.com within the code.

This is all theoretical, after all, as a result of scraping Google is a violation of their phrases of service.

Summary

Hopefully now you see many makes use of for Excel which are past the usual mathematical and accounting makes use of.

Excel is solely one of the best and quickest method to slice and cube small knowledge units, and a necessary web optimization instrument that belongs in your arsenal.

Essential web optimization Tools for Agencies

Ebook



Tags: , ,