Did you know that you don’t have to spend big bucks for one of the best SEO tools out there?

In fact, it’s free?

And, unlike every other piece of software out there, it’s hosted by the company that every SEO professional is trying to become best buddies with?

Because of that (and it’s sweet analytics and search data), the Google Search Console comes highly recommended by this guy.

In this article, I’m going to show you a quick hack on how to take data from the Google Search Console and transform it in Microsoft Excel to correct those nagging 404 errors that kill your user experience and, consequently, your website’s search engine optimization.

This is a simple, visual, step-by-step guide.

Step 1: Review Your Website’s Crawl Errors

After logging in to the Search Console, go to Crawl > Crawl Errors on the left navigation. You will see a screen displaying a chart of your website’s crawl errors over time and a list of results below:

If you have a large site (or it’s been a while since you’ve looked at this page, you may have a very large number of errors displaying here (hundreds or thousands).

Don’t fear.

Whether you have 5 or 5,000, this hack will take roughly the same amount of time.

Now click the check-all box, and download the data as a CSV file. Be sure to show as many rows as possible so that every line will get saved on the same document.

Go ahead and open that CSV file that you saved. It should look something like this:

Step 2: Manipulating Our Crawl Error Data with Excel Formulas

We going to manipulate the data in column A of the CSV document.

First, we need to create 3 new columns to the left-hand side. Select and drag your mouse over the headers of columns A, B and C. Then right-click with your mouse on the header for column C and select “Insert”:

Now we need to enter the data that will be copied and pasted into our .htaccess file. So with the new columns that you created, enter in the information you see below – “Redirect 301”, skip the middle column, and “/” in the third column.

The “/” button on your keyboard will default in Microsoft Excel to a “Help” feature. To get past that, just double-click in the cell on column C to enter the “/” character.

Now, we need to enter in a formula for column B:

As you can see in the image above, the formula is

=RIGHT(D2,LEN(D2)-23)

After entering that formula into Excel, the domain name in column D will be removed and you will be left with the remainder of the URL path which is all that we want.

Now, what you will need to change is the “-23“. You will want to count up the length of characters for your domain name and enter that instead. For example, for my domain name:

h-t-t-p-:-/-/-t-y-l-e-r-e-w-i-l-l-i-s-.-c-o-m  equals 23 (I just recounted it so you wouldn’t be able to prove me wrong!). Now I’m left with “…/contact” or “…/website-pop-ups”.

Once you have that all set, you need to apply this formula to every cell row. Highlight both rows containing 6 total cells (the reason we do 2 rows is because Excel may think that you’re trying to count up. For example, “Redirect 301” becomes “Redirect 302” … and we don’t want that).

The columns A, B and C should now be completely filled in:

Step 3: Copy/Paste 404 Errors into the .htaccess File

Now, all that we need to do is copy the data in columns A, B and C and paste it into our .htaccess file.

If you are using WordPress, you can actually access this file through the YoastSeo plugin. Or (using WordPress or not) you can typically find this file in the public_HTML folder via FTP or your website’s cPanel (this gets a little technical, so feel free to holler if you’re stuck).

Now, march on over to the Google Search Console (same page as before), and click the big red “Mark As Fixed” button. This will remove the pages from being listed and basically check them off your list of things to do.

Over the coming days or weeks, go back to this page in the Search Console to verify that Google no longer sees these errors.

The post How to Bulk Fix 404 Crawl Errors from Google Search Console appeared first on TylerEWillis.