September 14th, 2010
Tips, Tools and Tutorials
There are so many great uses for a spreadsheet program like Microsoft Excel. Though it can be utilized for several industries, there are a few helpful functions in Excel that pertain to Search Engine Optimization and Internet Marketing. Here’s a look at just the basic 10 functions that you can use in Excel to improve your SEO workflow.
counts the number of characters in a text string
The biggest and perhaps most useful feature of the LEN function is that it counts the number of characters in a text string. So, let’s say I’m trying to figure out my blog post’s title tag and I don’t want it to exceed 70 characters. I can simply put a formula in the adjacent cell and viola! The character count appears. This way I can see if the character count will be sufficient. I can even add conditional formatting if I want it to stand out even more.
makes all letters (in a given cell or text) lowercase
Let’s say you’re trying to develop a list of keywords that you want to check with Google’s keyword tool and you want to make sure that all of your characters are lowercase. Here’s how you do it:
Make a formula that references the cell(s) that you want to research and then copy and paste the cells (that contain the values) into the keyword tool. How simple is that?!
capitalizes the first letter of each word (in a given cell or text)
Let’s say you want to do the reverse and create a search engine optimized headline out of the keywords that you have that are in all lowercase. You might want to capitalize the first letter of each word, but instead of having to do this manually, there is a quick and easy way to convert your text (regardless of it being all lowercase or not).
Create a formula much like you did with the LOWER function only this time type PROPER instead of lower. If you need to use the information again in another spreadsheet, you want to make sure that you don’t end up copying the formula itself so do a quick copy of your cells, then Paste Special (in the same location) and select the Values option. Now your formulas have been converted to their associated values.
capitalizes all letters of each word (in a given cell or text)
Though you may not want to use this unless trying to get attention for the keywords in your meta description, you can also use the UPPER function to turn all of the text into uppercase letters. If you’re only going to be using this on small amounts of text that you’re going to type out, you might as well as use the Caps Lock key instead.
removes all “extra” spaces from text except the spaces between words (if you have many keywords that might have a preceeding or trailing space that might interfere with keywords)
It can happen to the best of us. We make loads of spreadsheets with lots of text in each cell and somehow a lone space ends up in front of the cell’s values or at the end. The problem is that you can’t just use Find & Replace or you might risk removing all spaces (even the ones between your keywords). If you really need to trim the space off your cell’s values, use the TRIM function. Here’s how:
Just like with the other functions like PROPER and LOWER, you essentially choose another cell adjacent to the cell with the values and make a formula that refers to that cell with the following syntax:
Complicated? Not at all. So, here’s an example of the TRIM function in effect:
Text in A2: “This is an example of the TRIM function at work.“
(Note that there are several spaces in front of the word “This” and several spaces after the word “work.”)
allows you to substitute one text string with another (much like F&R, allows you to change a space with a dash for URLs)
If you’re using Excel to help you formulate the text for your page’s URL aliases you may already be using the LOWER function, but what about the SUBSTITUTE function to replace your spaces (or underscores if they’re present) with dashes ( – ) instead. This can help save you time having to alter the text of each alias one by one. Though you can try to do this with Find & Replace, you can create this formula so that you can apply it to any other cell by simply copying/pasting the formula (or dragging down the formula) rather than having to go through F&R to do this. Here’s how you’d do it:
If you want to replace the spaces in cell A1 with dashes ( – ), then in cell B1, just put in the following formula:
Keep in mind that you’ll want to follow the proper syntax for this formula in order to get it right. Using this feature is a good way to improve the URL structure of your webpages. If you want to know some good SEO practices for URL structure, check out the post written by Ann from Search Engine Journal.The great thing is that you can make a nested function using both LOWER and SUBSTITUTE functions like so:
allows you to join two or more text strings together (you can connect the domain name with additional text to create specific aliases)
But wait, there’s more! So, let’s say you want to join your domain name with the spiffy alias text that you just made with your nested LOWER and SUBSTITUTE formulas. You can either use the CONCATENATE function, or just make it simple and use the ampersand. Here’s what that would look like:
The concatenate function can still be helpful outside of this use because you can join all sorts of different text strings. You can join text strings within the formula (=”This text”&”goes with this text”) or text strings that refer to other cells (=A1&B1), or any combination of the two (=A1&”goes with this text”). The options are seemingly endless.
allows you to specify the display type of a cell’s values (use in other formulas where dates, times, text or numbers might get mixed)
So you’ve tried the concatenate and perhaps even tried to take a stab at using a date in your concatenation, but Excel spits out a completely different number. In fact, Excel might even give you the date’s serial number. As frustrating as this can be, there is still a fix. If you know that the cell you are referencing needs to be in a specific number format, you can specify that in the formula with the TEXT function. This might come in handy if you want to include dates in the aliases of your pages, like with dated blog posts. Here’s an example:
returns the first/last character(s) in a text string based on the number of characters you specify
So this really counts as two separate functions, but they work in much the same way, just as opposites. What the LEFT or RIGHT function might be useful for is if you’re trying to extract some data from a cell, but not the whole thing. An example would be if you are trying to provide a truncated URL. But you want to make sure to include not only the front(first) letters, but also the end(last) so that you can see what type of file extension you’re working with (.htm, .html, .xml, .asp, .php, etc.). If you’re dealing with a fixed column width in Excel, this might be helpful too. Here’s one way to utilize both the LEFT and RIGHT functions to create a truncated URL.
Let’s say you want the first 20 characters of a given URL (listed in cell A1) as well as the last 15 characters. You may be interested in doing this if you are trying to provide a truncated URL for reference in Excel. If you’re working with fixed column widths, making a truncated URL may help conserve space. In B1, type the following formula to get these results:
creates a shortcut or jump that opens a document stored on a network server, intranet or internet.
Last on this brief list is the HYPERLINK function. It might seem a bit similar in concept to how you would do hyperlinking in lightweight markup languages. The syntax is as follows:
Here’s an example of a formula: =HYPERLINK(A1,(LEFT(A1,20)&”…”&RIGHT(A1,15)))
Notice (below) that the anchor text has changed from being specified as "Managing Your Time & Work Flow" to become the truncated URL that was created using the LEFT and RIGHT functions.
The great thing about making these HYPERLINK functions is that you can use the [link location] as a cell reference so that you can create links to all of the URLs you have listed. This way, you can save time from having to copy and paste the URL into your browser because you can just click the link in your spreadsheet. You can also use this function to create a link to another cell or worksheet. I will elaborate upon this in another post, so keep an eye out for more Excel tips.
Are there more SEO related Excel Functions? You betcha! These are just a few of the BASIC functions. There are definitely more functions that you can use and I look forward to getting up another post to add to these. If you have any suggestions, please feel free to add to this list by posting a reply. Your contributions are greatly appreciated!
There are no comments yet.