We’ve all been there: starting out as new PPCers, trying to navigate the waters and figure out this complex thing called digital advertising.
At first, it can be overwhelming, but with a few helpful Excel formulas, PPC doesn’t have to be as overwhelming as you might think.
The video below will walk you through some helpful formulas when working in PPC accounts. I’ve included start times of each section in case you are only looking for one specific formula. We’ve also included the formulas below, if you want to copy and paste them.
Please Note: This video does not have sound.
Get Character Counts for Ad Copy
Video Start Time: 3-second mark
Formula: =LEN(cellReference)
Example: =LEN(D2)
This formula is incredibly helpful for bulk updating your AdWords ads by allowing you to quickly see how many characters (with spaces) are in the Headline 1 for a given cell.
Not sure how to easily get your PPC data into Excel? Read our post about downloading your PPC account into an Excel file.
Append Text or Numbers to an Existing Cell
Video Start Time: 44-second mark
Formula: =cellReference&"Text to be appended"
Example: =C2&"; New Product Line"
If you need to quickly add new labels, this formula is your best friend. This can also be used for any situation where you need to append text to other areas (such as adding client branding to an ad headline).
Please Note: if you need a space after the initial cell reference, you MUST include a space manually in the quotes.
Group Things Together with SumIf
Video Start Time: 1:09 mark
Formula: =SUMIF(columnReference,"criteria to be met","columnReference)
Example: =SUMIF(D:D,*t-shirts*”,E:E)
Have common themes and looking for an easy way to collect data? SumIf is your best friend! This formula lets you add up data for cells that match a specified criteria. Just give the formula a column or range to be evaluated, a criteria to evaluate that range against, and then finally the column or range to sum when the corresponding row in the first column matches the criteria specified.
VLookups
Video Start Time: 1:43 mark
Formula: =VLOOKUP(valueToLookUp,arrayToEvaluate,columnToPullMatchedDataFrom,TRUE/FALSE)
Example: =SUMIF(D2,'Zip Codes'!,2,FALSE)
VLookups can be an intimidating function for Excel novices, but once you’ve done them a few times, you’re going to find uses for them EVERYWHERE. This function allows you to look up a value or range based on a certain value. For example, say you need to pair a list of zip codes with the city they’re in. You could manually pair up each zip code with it’s city… or you could run a VLookup to do it automatically.
Please Note: The criteria you’re looking for MUST be in the first column of the array you specify in the second parameter (arrayToEvaluate). You may need to move your columns around to get this to work properly.
Convert Dates for Easy Pivoting
Video Start Time: 2:18 mark
Formula: =TEXT(cellWithDate,"newFormatForDate")
Example: =TEXT(A3,"mm/dd/yyyy")
Ever have a bunch of data formatted with a date written in long form, but really want the date shorthand? This formula will let you convert a date-based value into a different format so you can more easily use it for pivot tables or other purposes.
You can also get more sophisticated and use this formula across multiple columns to break the date into day, month, and year for filtering, SumIf-ing, and other data manipulation:
- Insert 3 columns: Year, Month, Day
- =text(cell of date,”yyyy”)
- =text(cell of date,”mmmm”)
- =text(cell of date,”dd)
I’ve found these five tricks to be helpful when analyzing data or updating ad copy.
What are your favorite Excel formulas and hacks? Let us know in the comments below!
