- Define your URLs and the parameters you want to include. The most important thing you can do is make sure that your worksheet is set up in an easy to use, and easy to understand fashion. My sheets usually look something like this when I get started:
You can see how I’ve defined my URL, all of the parameters I’m going to use, and I have a list of the keywords I will be including after the “utm_term” parameter at the end.
- Concatenate your parameters. So some of you might be thinking “concate-what???” But don’t worry! Even though it’s not the easiest thing to say, it’s a simple formula that helps you pull multiple fields together into one long string. Before you dive in, consider these few tips:
- Start with a question mark. Before you add any parameters to the end of your URL, you must add in a question mark. From then on, each parameter will need to be separated with an ampersand (&).
- Think of each cell as a thought in a sentence, and remember to separate them each with a comma in your string.
Example: If you are concatenating cells A1, B1, and C1 your formula will look like this: =concatenate(A1,B1,C1)
- Use quotation marks when adding text to your string. Remember that spaces count as text, so put quotes around your spaces, and you will be all set.
Example: If you want to add spaces between each of the cells you concatenated in the first tip it would then look like this: =concatenate(A1,” “,B1,” “,C1) Notice that the spaces are a “thought” in your string, so they are also separated with commas.
- Use a $ before each part of a cell that you want to remain constant. When you copy formulas across or down a worksheet, the cells will move with you unless you make them constant.
- Double-check your formula. Once you have included all of the cells, look at the end result of your URL to make sure everything is there. In order to create the URL for the first keyword in the screen shot below, I used the following formula in cell B11:
And it returned this URL:
http://www.hanapinmarketing.com?utm_source=Google&utm_medium=CPC&utm_campaign=New SEO Boy Blog Launch&utm_term=hanapin
- Eliminate spaces. When you are creating a URL it’s important to remember that spaces are not allowed, and it is a best practice to replace them with hyphens. There are two ways that you can make sure spaces are eliminated from your URLs:
- Find and replace spaces with hyphens after you have created all of your URLs.
- Include a nested substitution formula as you build your URLs.
Nesting formulas is just the act of combining more than one formula. I wanted to replace the spaces in my campaign name with a hyphen, so I replaced “B5” in my original formula with a substitution formula, and the end result looks like this:
- Set your constants. Because we have a list of five keywords in the screen shot above, we need to make sure that all of the other cells in our formula don’t change. To do this I added a $ before each letter and number for the cells I want to stay the same. Note: I did not make the keyword cell constant, because I want that to change as I move down the list.
- Apply the formula to the rest of your sheet. Once you have verified that your constants are set you can pull the formula down the rest of the sheet, which in this case would be for the other four keywords.
- Test! Test a handful of the URLs you have created to make sure they go to the correct page, and they contain the correct information.
For those of you who didn’t give up through this – CONGRATS! Working in excel can certainly seem daunting, but remember that you can always hit escape if something goes wrong, and the best way to learn is to keep trying. Taking the time to learn helpful functions like this will only shave time off of your account work later, so always keep your eyes out for ways to make excel (or any program for that matter) work for you.