3 Excel Formulas to Master Before Getting a Job In Paid Media

Microsoft Excel – it starts as a somewhat foreign concept to nearly every college student, but its importance somehow ends up transcending any industry or job upon graduation and one’s entry into the proverbial “real working world”.

But why is that? Let’s think it through. It’s specifically used, as many know, for helping with statistical analyses and dicing up huge sets of data into more digestible and understandable segments.

As we move further and further into the information age, and more companies in different industries than ever before are making decisions based on informed data as opposed to standard business intuition or “gut instincts”, Excel has established itself as a pivotal centerpiece in any professional’s pursuit of career success.

Using Excel In Digital Marketing

Digital marketing is certainly no exception. Regardless of your niche in the digital world (especially at a data-driven agency like Power Digital), you’ll most likely find yourself in a situation where you simply need to use Excel to get things done. In paid media, specifically Google PPC advertising, we use Excel in some capacity for nearly every single analytical or reporting task that we intend to accomplish throughout the day.

But, Excel’s power is diminished if you aren’t using it efficiently to accomplish tasks – especially due to the high frequency with which you use it. People waste time every day by not using easy-to-conquer formulas to speed things up. Moreover, and arguably more importantly, people make easily avoidable mistakes by trying to “brute force” their way through processes, because they don’t take the time to learn any other way. I’ve been in this position before, and let me tell you now that there isn’t anything more frustrating than spending a lot of time on something in Excel, only to have done it incorrectly.

A saying that has become a mantra around the Paid Media department here at Power Digital, whose genesis can be attributed to my first manager and Paid Media Director Austin Randall is “if it feels like you can be doing something faster in Excel, you’re probably right”.

In the spirit of that, I’ve compiled a list of some of the most commonly used Excel formulas, that are tailor-made to help Paid Media professionals increase efficiency in their processes, while reducing their likelihood of error. Here are three Excel formulas to master to make your job easier (and save your sanity):

Concatenate

This is a function within Excel that allows a user to easily combine the contents of two individual cells into one cell. This becomes especially useful when uploading keywords to the AdWords user interface, where you’re required to include the proper formatting for different keyword match types, like broad match-modified (BMM), phrase, or exact. It saves you a lot of manual entry time in this regard, as well as lowers the probability of manual or “user” error.

For example, let’s say I wanted to take the Search Query Report below, and upload those terms as phrase match negatives into the AdWords Shared Negative Library. But, since there are a lot of search terms, I don’t want to waste the time manually denoting each with quotation marks, because that wastes time and opens the door for user error, which can affect the account’s query mapping. So, instead, I’ll use the CONCATENATE formula to save time, and apply that quotation to all the terms at once.

To use this formula, navigate to a blank cell and insert whatever character you wish to apply to all terms – in this case the quotation mark (again, to signify that I want these terms to appear as Phrase match when uploaded to the AdWords UI).

Once you have this all set up, the next step is to apply the formula in order to effectively combine the content of these two cells. In the cell to the right, enter the formula =CONCATENATE, and the following should appear:

In the parenthesis, first enter the contents that you want to have appear on the left-hand side. For this example, since we want the quotation marks to appear on each side of the search term, the first entry should be the contents in cell B2.

Next, separate your entries in the parenthesis with a comma, and enter the next piece. In this case, since we want the search term to be the SECOND entry featured, so the second entry in the formula should be cell A2, like this:

Lastly, now that we have the first quotation mark and the search term incorporated into the formula, we need to close it out with another quotation mark. So, just as we did before, enter a comma into the formula, and click on cell B2 to close it out, and press ENTER.

As you can see, the formula shows which cells are included in the formula, though the cell we had entered the formula in originally is now showing the “values” of the formula we entered, which for this example was a search term with quotations around it. From there, simply double click on the little blue box on the bottom right of the selected cell (in this case C2) to apply it to all search terms from the report.

 

 

Find And Replace

Find and replace is a function that nearly everyone knows how to use. However, many do not know how to properly leverage it to save time and increase their efficiency. It can be used to make bulk edits to ad copy, messaging, Max CPC’s, and many other aspects of paid media.

One that I have found particularly useful though, is a very simple one that saves a lot of time, and actually incorporates the CONCATENATE formula from above; that is, changing keywords from simply “broad” to “broad match modified”, which are denoted with a plus sign (+). For example, the standard broad version of my name would be Pat Kreidler, but the Broad Match Modified version would be +Pat +Kreidler. Make sense?

To accomplish this, let’s say we have a list of broad match keywords that we wish to edit. First, select the all the materials you would want to edit, in this case, from cell A2 all the way down to the bottom of the list:

From there, if you have a Mac, use the keystroke COMMAND + F to bring up the “Find and Replace” menu, and click the button labeled “Replace…” to bring up the following screen:

Now, we know that to denote a Broad Match Modified (BMM) keyword, there needs to be a plus-sign before each component of a keyword, so in the “Find what:” field, we simply enter a space, and in the “Replace with:” field, we enter a space immediately followed by a plus-sign. This is what it should look like:

Next, you will want to select “Replace All” in this menu, so that the change is applied to all keywords you have selected on your list. The result should appear like this:

You may notice that there isn’t a plus-sign at the front of these newly modified keywords yet. Are you thinking what I’m thinking? This would be a great opportunity for us to use the concatenate formula that we just went through to apply a “+” at the beginning of each of these to fully close out the process.

Here is what the final product should look like once you’ve done this. Now these keywords are broad match modified and ready for upload.

V-Lookup

This might be one of the more complicated formulas you’ll learn in Excel, but it’s hugely important for any paid media professional’s QA process. Specifically, I find it useful when attempting to upload new keywords to an existing campaign or account, and I want to make sure that those keywords don’t already exist in the account to avoid cannibalizing against my own previous efforts.

Moreover, the v-lookup function is used to compare one set of data to another, to discern if there are any differences or similarities between the two. This becomes especially useful when you’re dealing with huge sets of data or big lists that would otherwise eat up a good amount of your valuable time, and like mentioned earlier, allow for human error in a more prevalent way.

For this example, I’m trying to compare a list of keywords that I would like to upload to an account against a list of all of the account’s other keywords to make sure that I’m not repeating any.

The first step here is going to be making sure that we have our keywords that we wish to upload, and a complete list of existing keywords on separate tabs in Excel; this makes the process much easier.

Next, since we’re comparing our list of Keyword Ideas against the list of Existing Keywords, we’ll navigate to our “Keyword Ideas” tab, and in the cell next to the first keyword you wish to check, type in the formula =VLOOKUP. It should appear like this on your screen:

Then, select the cell of the first keyword you wish to test by clicking on it. In this case, the first keyword appears in cell A2, so click that, and then follow it by manually entering a comma like this:

The next step, is to select the “range” that we want to compare that keyword idea against. In this case, since the objective of the test is to make sure that our keyword ideas don’t already exist in our keyword library, we are going to navigate to the “Existing Keywords” tab, and select column A in it’s entirety.

The reason for this is because we want to see if that keyword exists anywhere in our current library. After navigating there and selecting the your “range” column, your screen should look like this, with the formula reading =VLOOKUP(A2,’Existing Keywords’!A:A):

Since we the value will appear somewhere in the FIRST column of our selected range, we denote that with a 1 in the formula, followed by a comma, then a zero to close it out. At this point this is what the formula should look like:

=VLOOKUP(A2,’Existing Keywords’!A:A,1,0)

Once you completed this and hit ENTER, you are returned to your original Keyword Ideas tab. In our case, the “#N/A” value in cell B2 signifies that the keyword in cell A2 that we tested does not appear in our existing library, and therefore IS eligible to upload into the account. From there, just as we did with the CONCATENATE formula, simply double-click on the little blue box in the bottom righthand corner of the selected cell to apply it to the rest of our Keyword Ideas list:

As you can see, once we applied the formula to the entirety of the list, the other cells in our “vlookup test” column populated with the same terms as our Keyword Ideas list. This means that those keywords DO exist in our current keyword library, or “Existing Keywords” list, and in this situation are therefore NOT eligible for upload. From there, we simply would upload the keyword in cell A2 only, since our test helped us determine that the other ideas are not appropriate for this account.

Wrapping Up

These three formulas, which are comprised of only a few keystrokes on your computer, can help save you time, energy, and frustration. I can safely say that on any given day, I can expect to use each of these formulas multiple times, so they are crucial to master if you want to produce high-quality work in a short amount of time. Most importantly, they help provide safeguards that prevent errors, which can’t be overvalued in an industry like digital marketing where you deal with such huge amounts of data and information at once.

My advice? Take the time and put forth the effort to learn these as well as you can, and find as many applications for them as possible through research and experimentation in your free time. And always remember, “if you think there’s a faster way to do something in Excel, you’re probably right.”