Add3 – Digital Marketing Agency with offices in Seattle & Portland

theformulas

Missed Part 1 of the series? You can find it here.

Ready for a fact?: Tools make jobs easier. Give any group of people enough time to do some thing and they will (eventually) figure out how to use a tool to do it more efficiently. As you may already know, Excel can be a super-tool for Digital Marketers and PPC professionals, but it can also be a bottleneck if you don’t know some of the tools within this tool.

One of the most powerful tools available in Excel are called “Formulas”. Formulas in Excel are extremely intuitive 50% of the time and can be a nightmare to learn the other 50% of the time.

In Part II of our Digital Marketing & Excel series, I will be highlighting the 7 most important formulas for you to learn to enhance your workday as a marketer.

Concatenate: The Concatenate function is designed to join several text strings into one text string. This can be a time-saver for doing something like repetitive keyword development. For example, you have ManufacturerA and ManufacturerB, and both of them make i) Shirts ii) Pants and iii) Trampolines. =Concatenate( can help create keyword lists with ease by joining these different cells.

Concatenate

Sum/SumIF: The =Sum( function helps massively when it comes to totals of anything. Need to add a whole column of data, even if it changes?

Sum

SumIF

If/IfError/IfNA: The =IF( formula can be a confusing one to understand, but the learning-curve will absolutely be worth it. Think of this as a conditional formula. It checks on a criteria you set up, and, based on whether your criteria is met or not, has two different outputs it will calculate. The formula structure is broken into three parts:

Excel: =if( Logical Test [value_if_true] [value_if_false]
What it means: Formula itself Criteria What should happen if your Criteria is “True” What should happen if your Criteria is “False”
Example: Cell 1 = Cell 2 (as in A1=B1) “That is good, they are equal!” “That is bad, they aren’t equal!”

IF

What if, however, the “Criteria” section is something that would typically result in an error? Time to use the =IFERROR( function

Excel: =IFERROR( Value [Value_if_Error]
What it means: Formula itself Either a cell or a “logical test” What you want to display instead of an Error Message
Example: Cell 1 = Cell 2Example:100 = Word “Looks like something isn’t correct”

For this example, what if Cell 1 was a number and Cell 2 was a written word. Cell 1 does not equal Cell 2, but even worse, that formula produces and error! Well instead of having a spreadsheet full of error messages, you can utilize the IFERROR formula to replace the error message with a message of your choice (Or with no message at all if you include back-to-back quotation marks (“”) in the part of the formula that asks what it should display if there is an error). IfNA functions the same way as IFERROR, but would be what you would use if you have spreadsheets that are showing lots of #N/A cells.

IFERROR

Count/CountIf: The count function get help you to get a snapshot of just how much data you actually have. It’s purpose is to return the number of cells that have a number value in the range you designate.

Count

CountIF

VLookUp: The =vlookup( function can be a game changer. Pouring through long lists of data, trying to remember where items are, or what you are even looking for are over. This will look at a specific cell that you choose to see what data is written there (number or text), then match that value to a range (or table) of cells that you select, and return a value in a set number of columns (vertical, hence “v” lookup) away from where it finds that information. An interpretation of the formula is:

Excel: =vlookup( Lookup_value Table_array Col_Index_Num True/False
What it means: Formula itself Which cell has the data that you want to match with in your table? Highlight your table How many columns to the right of your matched data from the vlookup cell and table are you trying to return True= Close matchesFalse= Exact matches
Example: Cell A9 (where I am going to type the title of the item I want to know the price of) C3:E7(the table that has all of that info) 3(How many columns away from the left most column is the data I want) False(exact matches to cell A9 only)

 

VLookup USE

Proper/Lower: Both of these functions are designed to help speed up your formatting. This can be a great tool for helping to format keywords or ad copy. =Proper( will change the first letter of each word in a string into an uppercase. =Lower( does (you guessed it!) the opposite. Every letter that this formula can find will be lower-cased without the need of individual editing; a major time-saver!

Proper

Lower

Substitute: The =Substitute( function can speed up big “editing” tasks in a clean and precise way. This is similar in functionality to a “Find & Replace”, but can be powerful in your formula arsenal for quick tasks of removing, adjusting, or adding terminology to a string of text.

Excel =substitute Text Old_Text New_Text
What it means: Formula itself Which cell is it looking at What should it look for in this cell to change (in quotes) What should it change it to (also, in quotes)
Example: C4 “UMBRELLA” “”

 

Substitute

 

[hs_action id=”10874″]

 

 

 

 

Justin Wade

About Justin Wade

Justin is an Account Coordinator at Add3. If there is one thing worth knowing about Justin, it is that he loves to play. Whether it be with his dog, a game of basketball, a PPC experiment, a new Excel formula, the drums, an xbox, you name it- he is playing. If all else fails, he'll be at a concert trying to sneak a better peak at the drummer.  

Comments & Replies