Campaign Management: 3 Beginner Excel Tips

When I first started managing campaigns in bulk using Excel, these three tips were those I used most often.  I thought I would quickly summarize them here for anyone new to manipulating data in Excel.

Column Width Adjustments

While you could go through each column and manually drag the column dividing lines to adjust each of their widths, there's a much easier method that will adjust all columns at once in just a few clicks; each one will be as wide as the cell containing the most information in that column.

1.) In the upper left hand corner, click on the box above the 1 and to the left of the A, this will select all columns within the worksheet:

2.) Once selected, move your cursor to the alphabetized column headers and place it directly over any of the lines that delineate one column for the next.  This will turn your cursor into a dark black vertical line with arrows pointing in either direction.

3.) Once your cursor has the appearence described above, double click your mouse and all columns will snap to the width of the largest cell within that data set.

The Length Formula

This formula is particularly helpful when checking the character lengths for ad titles and copy.  As I've already covered how to implement this formula in a previous post, adCenter Editorial Disapproval Tips: Common issues and solutions, I'm going to cheat and simply paste the instructions from that post here.

1.) Open your ads file and click on the column to the right of whichever column you want to check the character length of. In the screen shot below, I'm going to check the length of the ad title in column E, so I've clicked on the column header for F:

2.)  Right click on F and select "Insert". a new column will appear, which now becomes F:

3.)  In the 2^nd row down, right next to the ad title, type in "=LEN(E2)". without the quotation marks:

4.)  Now tab out of that cell and you'll see that Excel has calculated the length of your ad title, which is housed in cell E2:

5.)  Our ad title character length is 29, which means we need to revise it down to 25 in order to meet the editorial guidelines. Revise your ad title, tab out of the cell and the formula will refresh to show your new character length.

If you have more than one ad running (and you should), you don't need to manually enter the formula for each cell adjacent to your ad title. Simply click on the cell with the formula applied to it to highlight it, in this example, it's cell F2. You'll notice there's a small black box in the lower right hand corner of the bold black outline of the cell. Hover your mouse over that box and it will become a plus sign. Once it does, click and hold the mouse and drag that black box down the column until you reach the last ad title you want to apply the formula to. Release the click and you'll find the formula has populated all the way down for all of your ad titles.

These same steps apply for checking any character lengths. all you need to change is the value for the length formula. In the above example, we were checking the length in cell E2. If you were checking ad description (70 characters max), you would insert your new column by clicking on column H, right clicking and selecting "Insert" and then using the length formula "=LEN(G2)" so that the formula would reference the text in cell G2.

NOTE: Be sure to remove these length columns once you've made your changes and are ready to re-upload these ads or else the system won't recognize the format of the upload sheet.

Proper Case Formula

It's a best practice to ensure that your ad titles appear as capitalized in adCenter.  While this is a no-brainer when you're writing them out, you may find that ads with dynamic titles show up in lower case.  This can be remedied in a few simple steps.  (The below information also appears in a previous post, Tips to make sure your dynamic ads appear as capitalized in adCenter). To start, you'll need an export of your ads and your keywords.

1.) In the ads file, click on any cell within the sheet and type the command Ctrl+H. This will bring up the Find/Replace dialog box. Enter in the text you want to replace, in this case, you want to remove "{keyword" and replace it with "{param2" (I purposefully left off the closing brackets here, they aren't typos):

2.) Hit "Replace All" to change all matching values in the document. Once all the "{keyword" values are replaced, save this updated ads file for upload.

3.) In the keywords file, click on the cell containing your first keyword on row 1 and then hit Shift+Ctrl+? (down arrow) to highlight all text within that column. Copy it and paste it into any empty column at the far right of the sheet. In this example, I'm using column P, starting with row 2:

4.) Highlight the cell to the right of your first keyword, as shown above. Type the equals sign (=) in it and then go to the drop down menu shown above where it says "Q2" and look for the option entitled "PROPER". If you don't see it, begin typing it in that field and it will appear.

5.) Once you have that selected, a dialog box will appear asking you for a value for "Text". Enter in the location of the first instance of the text you want capitalized. In this example, it's P2. column P, row 2:

6.) Hit "OK" and you'll find that in row Q, there is now a copy of the value of P2, only it's capitalized:

7.) In order to have this formula repeat for all values in column P, simply grab the tiny black box (your pointer will turn into a plus sign when you're hovering over it) that appears in the lower right hand corner of the highlighted cell of Q2.

. and drag it all the way down to the last cell that's adjacent to your last keyword:

8.) Now all you have to do is copy these capitalized keywords (Ctrl+C), select the first empty cell under the Param2 column of your keyword sheet and then use "Paste Values" from the "Paste" drop down menu:

Your capitalized keywords will appear in the Param2 column and should mirror all the terms in your keywords column exactly.

9.) Once this is done, delete the 2 columns you used for the PROPER capitalization formula as they're no longer necessary. You now have your updated keyword sheet ready for upload.

10.) Upload the updated copies of the ads and keywords sheets and now you'll find that when you search for your ad that all dynamic terms are showing as capitalized.