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”

Adding multiple keywords in Adwords Editor

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.

Adding multiple keywords in Adwords Editor setting Adding multiple keywords in Adwords Editor window

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.

Creating keyword buildouts in excel

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

Creating keyword buildouts in excel

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.

Excel Macro for keyword buildouts

Step 4) Select Row 1 and color it yellow.

Keyword buildout formatting in excel

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.

 

Using ad group macro on keyword buildout in excel

Step 6) Duplicate Column A values, and Copy & Paste the values into Column B.

Duplicating Keyword Buildout column in excel

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)

Sorting ad groups in excel

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.

Removing duplicate ad group values in excel

Step 9) Then, Filter Column A once again by “No Fill” instead of by the Cell Color yellow.

Sorting keywords in excel

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.

Removing duplicate keyword values in excel

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:

Setting ad groups for keywords in excel

Step 12) Select all of Column A >>>> Click F5 >>>> Select Special

Selecting blanks from excel keyword buildouts

Step 13)

Select Blanks >>>>  Select OK

Finding blanks from excel keyword buildouts

Step 14)  You should find that the blank cells become highlighted.

Highlighting blanks from excel keyword buildouts

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.

Inserting text into blanks in excel

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.

Matching ad groups to keywords in excel

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.

Formatting keyword buildouts in excel

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

Filtering ad groups by cell color in excel

Step 18) Select the All Colored Cells beneath Row 1 and Right Click, then Delete the rows.

Removing extra ad groups in keyword buildout

Step 19) Finally, remove ALL filters, ad headers to your columns, and remove the coloring from row 1

Formatting ad groups and keyword buildouts in excel

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 alan@elitesem.com.

Blog Categories
Shopping & FeedConversion Rate OptimizationPaid SocialPaid SearchPerformance DisplaySearch Engine OptimizationCompany UpdatesIndustry NewsConferencePressEventsUncategorized
Blog Archives
2016 2015 2014 2013 2012 2011