Excel is undoubtedly the most important program I use in conjunction with my PPC work. It’s the Swiss Army Knife of online tools where I build my keyword lists, write my ad copy, prep campaigns and ad groups for uploading, and the center of my analysis of historical data that my campaigns have produced. I have to assume that others use it as often as I do for these and other tasks, so I thought this would be a good time to go over what I consider the most useful tools to use.  If you’d like to see even more, check out Josh Dreller’s excellent series at Search Engine Land, called How to Excel at Excel

Concatenation When I first learned to use this tool several years ago I asked where had it been all my life! This allows you to combine the information in two cells together into one additional cell.

=CONCATENATE(A1,B1)

To automatically include a space between the words in each cell, you can include a space using quote marks between the two cell numbers, so the equation would look like =CONCATENATE(A1,” “,B1). This is best used to combine two groups of words for keyword generation

Character Counting If you’ve done anything in search you know that the character limits in search engine ads are brutally short. That’s why you have the LEN counter to help you:

=LEN(A1)

This will tell you how many characters are in the designated cell. I usually set my ads up in three rows, each with a len equation, so I can play with any of them as needed.

Trimming There have been numerous times where I’ve spent much more time than it should have taken trying to format the ideal piece of ad copy to fit within character guidelines only to later find out I had an extra space or useless character taking up room. Using the trim equation will remove all of them and leave only one space between words, and remove any unneeded spaces at the beginning or end of the cell.

=TRIM(A1)

If you duplicated my character counting example in Excel, you’ll notice that the second description line (I Love Writing PPC Ads. Don’t You?) is actually 34 characters, with an extra space at the end. If you put the trim equation in C3 for A3, the LEN count in B3 will drop to 34 characters instead of the listed 35. Try it!

Left/Right Trimming Instead of removing all floating spaces regardless of position, this formula removes the dictated number of characters from either the left or right side of the cell. This is handy when editing URLs, where part of the cell stays constant, but other parts need to be removed.

=RIGHT(A1,2)

=LEFT(A1,2)

The formula will remove the number of characters stated after the comma starting on either the right or left side of the cell, depending on which formula you use. For example, if you have a URL that looks like https://www.yoursite.com?pid=13, and you wanted to remove everything after “.com”, you would write the formula to look like RIGHT(A!,7). I count 7 characters, which includes the question mark (?) and the equals sign (=).

Word Counting Word counting is good as an analysis tool to see how many words within a keyword string perform best.

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1

This will return only a number. For example, if your keyword was “White car”, then this formula would return a value of “2”. If your keyword is “New Used Car”, then the formula would return a value of “3”.

VLOOKUPs Vlookups–short for vertical lookups–are probably one of the most powerful analysis tools, since they allow you to compare data for the same source cell to different entries within a table of data. In search, this is usually comparing a keyword’s performance over a given length of time.

=VLOOKUP(A2,$F$3:$J$247,3,)

There are some very important issues you need to be aware of when using these formulas:

• The first entry (A2) is your source cell, or most commonly your keyword. If you have the same keyword in multiple match types, you’ll need to concatenate the keyword and the match type so the two are in a single cell, otherwise you won’t get correct data. Be sure to concatenate your data the same way over all data periods.
• Do not forget to include the dollar signs ($) in the table field, which is the group of numbers between the first and second commas ($F$3:$J$247). If you forget the dollar sign, your data table will move down with each new Vlookup. The dollar signs keep the data table static.
• The number between the second and third commas is the column number in the data table that you want to retrieve the information from. This includes the column with your source data! Count by starting with the column your source data in as column 1, and count over the number of columns until you come to the data you want to see.
• Don’t forget the last comma after the column number. The help section states that this comma is used to lookup a range in the value, but in all honesty I have no clue what that means. All I know is that it works well with the comma and doesn’t work well without the comma.
• Be sure to use Excel’s formula help section to understand how to best use this formula for your specific data.

Proper Statements It’s always good to have proper punctuation! This formula will capitalize the first letter of each word in a cell, which I think is the most attractive formatting for ad copy text.

=PROPER(A1)

Using the formula will turn a sentence like “this is a ppc headline copy” into “This Is A Ppc Headline Copy”. After the cells have been PROPER-tized, copy it and right click on an empty cell. Select “Paste Special” and then “paste values” to turn the text from a formula result into editable text. Then you can make the minor adjustments like capitalizing all of “PPC” in the example, and maybe making the “A” lowercase for proper formatting and grammar.