As the years fly by, there remains one constant while working as a SEO/PPC manager. That constant happens to be the frequency of Microsoft Excel use by SEOs and the importance of being proficient with functions or formulas that can help you save time. There's really no way around it; if you work as a campaign manager or project manager for a SEO/PPC management company you are going to be using Excel, A LOT!
With that being said, I wanted to share some of my favorite formulas, functions and tips for Excel I have used over the years. I hope to teach readers how to use these tricks and tips to their advantage so they can increase the efficiency of their workflow.
1. The "Proper" Function
This is a very basic formula that is in my opinion, grossly underutilized. Most people don’t even know it exists. I have been showing this function to people throughout my career and they almost always reply with a reaction similar to "Sergio, do you know how much time I would have saved had I've known about that one before?" In short, this formula will take any string of text and capitalize the first letter in each word.
One of the most common uses that I’ve found for this function includes formatting title tags or ad titles in bulk. Oftentimes our titles are created using simple concatenation and the keywords are taken from a keyword tool like SEMRush. However those results are often default exported in lower-case letters. This formula can be helpful if you are using a large list of titles or headers that need to be capitalized in order to adhere to editorial guidelines as shown below:
2. Using Simple Concatenation
This is a great tool that I use when planning local SEO or PPC campaigns. I wrote a very detailed post on how I use simple concatenation last month, which I encourage you to read.
Using simple Concatenation, you can quickly create text for ads, page titles, descriptions, keyword ideas and many other SEM key components very quickly. Here's an example: imagine you are creating a campaign with service-specific campaigns. In each campaign you have city-specific ad groups. If you are targeting two services and 20 cities you’re already looking at having to build 40 ads and keyword groups.
With simple concatenation, these combinations can be made simply and easily.
3. Data Validation - Character Limits For Bulk Ad Copy Creation
Chances are, most of you reading this post are managing campaigns. Sometimes, you need to let other people do the legwork and more mundane tasks so that you can focus on the headier stuff. When it comes to creating ad copy, that's one thing that I like someone else to do for me so that I can focus on more advanced strategy. I use data validation to create ad copy templates in Excel, which then eliminates the chance of someone sending me ad copy that exceeds character limitations.
Setting up character limits for your cells is a cinch. A great video demonstration can be shown here. To set up data validation, you will need to click on the “Data” tab in the Excel ribbon and then click on “Data Validation," the video below will demonstrate how to configure the settings correctly.
4. Concatrange - Consolidating Text From Multiple Cells Into One Cell
This is a great user-defined function that allows you to combine multiple cells into one, separating the combined contents of each cell with a comma. Imagine you have a large list of email addresses in column A that you want to compress into a single cell with each address separated by a comma. Concatrange will allow you to copy and paste the list right into your “email recipients” field and “voilà,” you’ve created a large email list without breaking a sweat.
You can also use this trick to create regex filters for keyword reports if you want to hyperfocus on a small data set of keywords related to a certain city or location.
The first thing you will need to do is access your developer tab, which, in most cases, you will need to use to manually add to your ribbon. Click on “file,” then “options" to access the ribbon settings menu as shown in the screenshot below. Once you click on “options,” click on “customize ribbon” which should be on the left, then click on the check box next to “developer” so that it’s added to your main menu (aka ribbon.)
Next you will need to create the function using a module in Visual Basic, which is accessible through the developer tab. I’ve created a video tutorial which demonstrates the entire process of creating the user defined function then using it to combine keywords into one cell separated by a line break so that you can use it as a regex filter in Google Analytics. The video also demonstrates how to change the line break to a comma so that you can use it to combine text strings separated by a comma. It’s also important to note that you can change the operator to anything you like. It doesn’t have to be a comma or a line break.
For your reference, the code used to create the function is pasted below:
Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & "|"
ConCatRange = Left(sBuf, Len(sBuf) - 1)
5. "Left" & "Right" Functions
These functions allow you to quickly pull data from the beginning or the end of URL strings with ease. Each function can be used to extract a specified amount of characters from the beginning (left) or end (right) of the data set in any cell. For example, if you have a set of URLs in Column A, you can insert the "Left" function in Column B to pull the domain or URL extension by using the "Right" function.
To conclude today’s blog post, I hope that these simple yet highly useful Excel for SEO/PPC tricks are useful. To get more tips and tricks like these, I encourage you to visit my blog where I have been creating an ever-expanding list of Excel for SEO tricks much like the ones mentioned in this post. Having the ability to use shortcuts and tricks like these to automate some of the more mundane processes you have to use every day will allow you to become a more efficient marketing manager who can focus on the big picture and not get as bogged down in minute details. Hopefully you have enjoyed this post and I would love to hear back from everyone with your questions or comments!