We’ve mentioned it before, but Excel can be a lifesaver when working in PPC. If you know the right tips and tricks, Excel can not only save time, but help you be more accurate. I always create an Excel file to document any changes, or new campaign structure, that I want to implement in the AdWords Editor.
Creating an Excel file with my changes helps me back-up updates I am rolling out. If something goes wrong in Editor, I still have a record of all the changes I want to implement. I also find that having an Excel file with the keywords and ads for the account helps with review and QA. It’s all there in one file and I can quickly evaluate and edit it. Spell check in Excel has come in handy to ensure I’m not misspelling a word in the ad copy, or in the keywords. I can also easily email the file out to a co-worker to review.
The biggest reason I prefer working in Excel, though, is there are so many time saving tips and tricks to make the work easier. As I mentioned, we covered some of the most useful formulas before. But as an avid Excel user, there are a few other instances that I use formulas to speed up my PPC work. Today, I want to focus on some tricks to help you when working with Modified Broad keywords in Excel. Some of these may be tricks you currently use, but hopefully there will be some new ones you haven’t thought of.
Painlessly Switch Keywords to Modified Broad
Broad match keywords are always something you want to watch to ensure they aren’t getting too out of hand. One thing I’ve found lately, especially in Bing, is that I want to switch over those keywords to Modified Broad. If there are quite a few keywords, it takes time to add in those ‘+’ symbols. Thankfully, there’s a quick and painless way to do that in Excel.
This trick is also handy if you are building out a new campaign and want to create Exact and Modified Broad match keywords. You can just copy the Exact match and follow this same method. While there’s other ways to add in the ‘+’ symbol in other tools, I prefer having it all in Excel.
All you need to do is use a mix of CONCATENATE and Find/Replace. Here are the steps:
- Add a new column with ‘+’ symbols next to the keywords you want to switch to Modified Broad
- In the next column, use the formula CONCATENATE(E2,C2) where E2 is the ‘+’ symbol, and C2 is the keyword
- Drag this down for all keywords you want to convert to Modified Broad
- Copy all the these newly created keywords from the concatenate formula
- Paste them overtop of the original keywords using ‘Paste Special’ and select ‘Values’
- Once those are pasted in, you have added the first ‘+’, now you can quickly add the others with a Find/Replace. For ‘Find what:’ use a single space, for ‘Replace with:’ use ‘+’.
Bypassing the ‘#NAME?’ Issue in Excel
Have you ever been working with Modified Broad match keywords in Excel and seen the ‘#NAME?’ error appear?
When you download keyword reports, the Modified Broad keywords have a space before the first ‘+’. If you try to delete this space out, or make any revisions to the keyword, you may see this error. The best way to avoid this is to fix the column text type when you open up the file. All you have to do is highlight the column and change the Number type (see pic below) from ‘General’ to ‘Text’. Now you can edit the cell without that appearing.
What about that space before the first ‘+’?
As I just mentioned, a space is added before the first ‘+’ when downloading a keyword report. Thankfully if you later upload the keywords into Editor, the space goes away. But, if you want to delete that due to comparing keywords in Excel, or for some other reason, there’s a quick fix for that as well.
I would start by sorting the keyword column in alphabetical order, this will put all the ones with ‘ +’ up at the top. Next add a column off to the right with any symbol, I used an ‘*’. Concatenate the symbol with the keyword. Copy the new keyword column and paste using ‘Paste Special’ (like we did before). Now you can do a Find ‘* +’ and Replace with ‘+’. This allows you to remove the unwanted first space, without deleting other spaces in the keyword.
Modified Broad keywords can be an asset to any account. Working with them in Excel, though, can be a little trickier. Hopefully these three tips will help save you some time!
