When it comes to using Excel for SEO, whether you’re modifying a large list of URLs or updating a few thousand Title Tags there are a few essential Excel shortcuts that can dramatically increase your workflow.
Mr. Miyagi once said, “You trust the quality of what you know, not quantity.”
I like to apply this approach to SEO. Starting out, you may share many of the same frustrations of Daniel-san as you begin to paint your Title Tags and polish your URLs but with persistence, it won’t be long until you’re kicking SEO butt! Although learning Excel’s more advanced keyboard shortcuts and functions takes time, with that comes a wealth of knowledge that is sure to double your efficiency as you accumulate valuable functions along the way.
1. Find And Replace
This is a very practical tool that makes updating Title Tags a breeze. For example, say you have a lengthy list of products where over 200 isolated instances of “Hat” needs to be renamed “Baseball Cap”. Simply use the keyboard shortcut CTRL+F, or click Edit from the menu bar, and select Find to open the Find and Replace tool. Where it says, “Find what:” type, “Hat” to select every instance of the word. Now click Replace and where it says, “Replace with:” type, “Baseball Cap” and click Replace All. Now you’ve updated over 200 products in only a few short clicks.
Now this,
Looks like
2. Excel Filtering
The Filter tool functions similarly to the Find and Replace tool; however filtering works better for more invasive editing. If you have a workbook with thousands of products and you want to edit every product that contains the word “Portable”, simply click on the Product Column, and select the Filter tool. If you’re using Excel 2011, you should see that your Product Column now has a drop down arrow. Click on the drop down arrow and the Filter tool appears. Locate the Filter Method box and select Contains. Now type “Portable” in the toolbox and HiYa! Every portable product in your inventory is displayed.
3. The Excel Proper Function
This simple little function capitalizes the first letter of each word in the desired cells, which is an important step in developing your Title Tags. Type “=PROPER” in the desired cell, specifying what cells to affect: “=PROPER(C2)”. So if Column C requires some capitalization, scoot over to D2 and type “=PROPER(C2)” and apply this function to the remainder of Column D.
4.The Excel Len Function
LEN is handy for a number of reasons, two of which being that LEN displays the number of characters in a cell, making data crunching much easier. This is particularly useful when working on Title Tags and Meta Descriptions. To display Column D’s character lengths in Column E, Key in “=LEN(D2)” in E2. Now column E displays the character length of all your Title Tags.
You want to shoot for less than 70 characters on your Title Tags and less than 156 on Meta otherwise the Black Belts over at Google will surely truncate you.
5. Total Concatenation
The Concatenate Function is the mother of all timesavers. Think of it as the Karate Kid’s “Crane Kick” that delivers the final blow to win the championship. In place of tediously keying in our Meta and Title Tags, we will use “=CONCATENATE” to aggregate all of our product information into one cell. So, if Columns A and C are product descriptions and Column D is your Title Tag column, key in “=CONCATENATE(A2,” : “,C2,” | YourCompany.com”). Depending on your preferences, you may want to modify this function to enhance your Title Tags’ visual aspect.
For such a complicated task it’s a pretty simple function that will save you lots of time.
Congratulations Grasshopper, you’ve earned your White Belt in SEO!