Excel is an absolutely essential tool for pay-per-click pros. I use it (along with other tools) at every stage of a pay-per-click advertising campaign: planning, researching, organizing, building, executing, measuring and reporting results. I’ve stubbed my toes more than a few times in trying to get excel to do what I want it to do, and with that in mind, I thought I would pass along some of the most helpful (basic) formulas to you. If there are formulas or other tricks that you use that you would like to share with me, I’d love to hear from you and add to this list.
- Take ALL keywords in an excel spreadsheet, and convert them to modified broad match:
- =SUBSTITUTE(“+”&A2,” “,” +”)
- Add a keyword (where a “comma” is the keyword) to any cell in Excel:
- =A1&”,”
- Sort fields in excel by word count (great for keyword research, when you are working with enormous lists of keywords that need to be prioritized):
- =IF(LEN(A2)=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))+1)
- Search for matching keywords:
- =IF(ISNUMBER(FIND(“social media”,A2)),”true”,”false”)
There are plenty of other things that I do with Excel (sorting, filtering, and even using macros to do more complex tasks), but I’ve found those four formulas above to be very helpful on more than one occasion, so I thought I’d share them with you, and I hope you find them useful too.
Here’s another formula that I wanted to add to this: =TEXT(A3, “ddd”) which tells Excel to reveal the day of the week for a particular date. This is a fantastic formula for Google Analytics or AdWords pros, because as you know, Google loves to give you massive Excel spreadsheets organized by dates, and if you want to see the average metrics for only Tuesdays, or only Sundays, for example, this would be very difficult to do. With this formula, however, it’s easy.
And another formula I like is this: =”X”&A1 which let’s you append any text to the beginning of cells in Excel. This is very useful, because whenever you download reports in Google Analytics, it never includes the URL root, so most clients don’t understand what they are looking at. For that matter, it also helps people who DO know what they are looking at, because using this formula can make the links clickable, so you can more easily navigate to the page, if you need to.
…And another formula is =” +”&C26 which let’s ou add a symbol before a keyword in excel. Good for adding a broad match modifier.
When I search for matching keywords, I use my own VBA script. I write the word(s) in a dialog box and click ok. The VBA will go through all the selected cells and extract the keywords to another column. You can find the Excel add-in here, it is a keywords grouping tool.
I also automated the process of modified broad match using another Excel add-in, you can download it here too, keyword wrapper. It can even search for particular word or words in a long list of keywords and add the modified broad match pluses.
Hope you like the Add-Ins.
Thanks! I will checkout the Add-Ins!
Thanks – I will check out the Add-ins!