Excel Tricks for Bulk Ad Group Formatting
Do you do bulk ad group and keyword buildouts in Excel, and are you looking to save time formatting your excel sheet for uploading? If so, you should continue reading! This trick is going to be the most beneficial if you are dealing with many ad groups and keyword sets.
Let’s take the AdWords Editor platform as an example. Of course you can do keyword set buildouts within the Editor platform itself. But if you’re dealing with campaigns that have a lot of ad groups with long keyword sets, it’s more efficient to do the buildouts within Excel then upload them to AdWords Editor.
Part A: Adding Multiple Keywords in AdWords Editor
Step 1) Log into AdWords Editor and select an account that you want to work on.
Step 2) While in the Editor Platform: Click Keyword Tab >>>> Select “Make Multiple Changes” >>>> Select “Add/ update multiple keywords”
Step 3) You should be taken to a window that looks like the window in the image below. Be sure to check off the box that says “My keyword information below includes columns for campaign and ad group names.”
Notice that the format of the upload field for the Keywords, along with their corresponding ad groups and campaigns, is organized in a vertical fashion. The excel macro & technique detailed in the following steps, will show you a quick and easy way of formatting your ad groups and keyword sets in a vertical fashion for upload to the AdWords Editor platform.
Part B: Excel Tricks to Create Bulk Keyword and Ad Group Buildouts
Step 1) In this image, this how your buildout would look with the ad groups with the corresponding keywords beneath them. When using this technique and doing your buildout, you want to be sure to start your buildout in Column B and leave Column A empty. Note: I’ve created ad groups with varying lengths of keyword lists, just to give you an idea of how beneficial this technique can be.
Step 2) Next, you want to create the Macro that is detailed below.
Sub CopyToA()
Do While activecell <> “”
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range(“a65535”).End(xlUp).Offset(1, 0)
Activecell.Offset(0,1).Select
Loop
End Sub
To create the Macro, you would do the following:
Select Developer >>> Select Macros >>>> Name the Macro (note, the name cannot contain spaces) >>>> Select Create
Step 3) Open the “adgroup macro” Document and Copy & Paste all of the Macro text (from above) into the Macro coding field, replacing any of the existing contents. Then, close out of the macro coding field box.
Step 4) Select Row 1 and color it yellow.
Step 5) While in Cell B1, perform the following:
Select Developer >>>> Select the Macro you created (Note: the macro will rename itself to “CopyToA”) >>>> Select Run.
Once the macro has run, the values should populate into Column A in a vertical fashion.
Step 6) Duplicate Column A values, and Copy & Paste the values into Column B.
Step 7) Leave the blank Row 1 where it is and do not delete it! Then, go to the Data tab and Filter Columns A & B by color.
Select Columns A & B >>>> Select Data Tab >>>> Select Filter >>>> Filter Column A by Cell Color (Yellow)
Step 8) Once the Columns are sorted by Cell Color (Yellow), you want to delete all of the values in Column B. This is because Column B will be for your keywords, while Column A will be for your ad groups.
Step 9) Then, Filter Column A once again by “No Fill” instead of by the Cell Color yellow.
Step 10) Once Column A is filtered by “No Cell Color”, you want to select all of the values BELOW the colored cell A1 and DELETE them. The values that you delete are simply copies of the keywords that are within Column B.
Step 11) Upon deletion of non-colored values from Column A and colored values from Column B, and after removing all filters, your data range should like this:
Step 12) Select all of Column A >>>> Click F5 >>>> Select Special
Step 13)
Select Blanks >>>> Select OK
Step 14) You should find that the blank cells become highlighted.
Step 14) Without deselecting the blanks, perform the following:
Hold down CTRL >>>> Click into Cell A3 >>>> Type “=a2” into Cell A3 >>>> while holding down CTRL click ENTER.
Step 15) The ad group names should populate into the blank cells. Then, delete “#REF!” from Cell A1. Additionally, scroll to the bottom of Column A and be sure that no excess ad group names (ad groups without corresponding keywords next to them in Column B) populated at the bottom of the list.
Note: Do you remember when I said that I intentionally created ad groups with different keyword list lengths? This is because, it can be very cumbersome when going through each ad group and manually dragging down the preceding values, especially when ad groups vary and don’t contain the same keyword list lengths. Therefore, no formula can be written to drop down the values.
Step 16) Be sure to select Columns A & B and Copy & Paste just the values into the same cells, so that you remove the formulas from Column A, that you entered in Step 14.
Step 17) You want to run a filter again to remove the extra, unnecessary cells (that should still be colored yellow). Perform the following steps:
Select Columns A & B >>>> Select the Data Tab >>>> Select Filter >>>> Filter Column A By Cell Color Yellow
Step 18) Select the All Colored Cells beneath Row 1 and Right Click, then Delete the rows.
Step 19) Finally, remove ALL filters, ad headers to your columns, and remove the coloring from row 1
Step 20) Once you have your ad group and keyword lists formatted in a vertical manner, you want to Copy & Paste the values into the AdWords Editor Bulk Keyword Window from Part A.
There you have it! You now know how to save time for bulk ad group & keyword formatting with Elite SEM’s Excel tricks! If you have any questions, please email [email protected].
You Might Be Interested In
