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.
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?
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:
|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!”|
What if, however, the “Criteria” section is something that would typically result in an error? Time to use the =IFERROR( function
|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.
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.
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:
|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)|
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!
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.
|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)|