Updated: Jun 05, 2019
One of the arguably most exciting aspects of technical SEO is researching URLs that return 404 errors and writing 301 redirects for as many of these rogue broken links as possible. Some 404s cannot and shouldn’t be redirected. These are items in your store that have sold out and are no longer on your site, or you just finished a redesign and all of your content has been re-siloed due to new navigation. However, for those that you can find an equivalent destination for the link, organizing them and writing redirect can be, at times, taxing.
Good news for those of you whose sites run off of a CMS. Most CMS’s have a redirect plugin that allows you to simply add the old URL and new URL, and the plugin does the rest. If you were saving a sigh of relief, now would be an excellent time to let it go.
If you have access to your site’s .htaccess file, and you tire of manually writing 301 redirects, we have a time saving tip to allow you to move on to your next task quickly. This does require using a spreadsheet program such as Excel or Google Sheets. Our goal is to quickly write a large number of 301 redirects with one formula, and an unprecedented amount of clicking and dragging. To reset, when you are writing redirects for your .htaccess file, they should appear similar to the following:
Redirect 301 http://www.oldurl.com http://www.newurl.com
How to Set Up your Spreadsheet
First, you should organize your spreadsheet so that your old URLs are in one column, and the new URLs are in a new column. The next step is to have a column blank, with the text ‘Redirect 301’ in a cell on its own. Below the ‘Redirect 301’ cell, in a blank cell, hit your spacebar once. Just once.
Based on this screenshot, go to cell D2 and enter the following formula:
=CONCATENATE($A$2,$A$3,B2,$A$3,C2)
What does Concatenate Mean?
To explain the formula, concatenate will combine everything that is in between parentheses. The definition compiled from the first page of search results is “to link together, unite in a series or chain”.
$A$2 and $A$3 are absolute cell references. There may be times, not unlike this one, where you don’t want a cell reference to change when filling cells. Absolute references do not change when copied or filled. Absolute references are used in a formula by the addition of the dollar sign ($). In our formula, because the $ is before the A and the 2, both the column (A) and row (2) will not change when copied. If the $ was only in front of the 2, the row wouldn’t change, and if it were only in front of the A, the column wouldn’t change.
B2 and C2 are relative cell references. By default, all cell references are relative references. If you were copying data across multiple cells, they will change based on the relative position of rows and columns. For instance, if you had a formula of =A2+B2 from row 2 to row 3, the formula becomes =A3+B3.
Here’s what your redirect looks like with the Concatenation in action:
Note that the cell is highlighted to show the formula that is in place in cell D2 and the result. Here are is the same sheet, with multiple rows:
Updating the .htaccess File
Without the space between ‘redirect 301’ and the space between the old URL and new URL, the redirect will not work. Once you’ve finished, you should be able to copy and paste column D into your .htaccess file. Of course, you should test your redirects, whether you use a plug-in or this method, to ensure that they are working properly and that there are no redirect loops. It is very important that redirects are formatted correctly in the .htaccess file. If there is something not written properly, the site could crash or not load correctly.
If you thought that this tip saved some time, let us know in the comments below. We will be posting more tips like this in the coming weeks to help you, ahem, excel at technical SEO.
