Day to day working in Pay Per Click, a substantial amount of time is spent on analyzing data and creating reports to communicate to clients what PPC advertising is doing for their business. It is not enough to optimize your account and automate your reports out of Adwords, however. Each client has a different set of goals, different ways of processing information and different levels of interest in reading reports.
The more reporting I have prepared for clients the more tricks in Excel I have mastered, yet there are many I have yet to learn. This week I sifted through some articles on Excel tools for PPC and tried to add a few new tricks to keep up my sleeve for future reports.
Some of the Excel tricks I learned were simple shortcuts to memorize while others were a bit more complicated. Don’t overlook the more simple shortcuts, however, because these are the tricks I find myself most giddy about!
“Ctrl”+”Shift”+”5”: I know how easy and simple this is but I’m obsessed with it. This shortcut is used to quickly format cost per click, total cost, cost per conversion and other monetary figures. Simply select the row or column you want to format and hold down “Ctrl”+”Shift”+”5” to turn the numbers within the pink columns into the numbers within the blue columns without clicking through all the formatting options. Call me crazy, but this is my favorite.
“Alt”+ “=”: This shortcut will quickly add up all the cells above within the same column. Instead of having to write out “=SUM(A1:A50)” you can get the sum of clicks, impressions, conversions etc. easily and more quickly than have to type out the formula.
“Ctrl”+”Shift”+”!”: This shortcut will apply formatting for numbers. It will format with two decimal places, minus signs and thousand separators. This will make your impression columns much easier to digest without having to go through the Format Cells dialog box.
“=IFERROR:” The IFERROR (If Error) function is helpful if you have implemented a formula and you get an error message such as “#DIV/0!” In this case, like me, you tried to divide by zero and got an ugly, urgent-looking error message. If you use “=IFERROR”, however, you can tell excel exactly what phrase or value you would like to show up in place of the error message.
“=VLOOKUP”: This is a formula that I have long heard about but only today (while researching this blog post) took the time to learn. Thank goodness I did, too! “=VLOOKUP” can be used to compare data month-over-month, compare data from an old account structure to a new structure, etc to easily see what has changed in your account. In my first few attempts trying to learn this tool, I struggled with writing the formula. The formula is “=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])”. A quick tip is to make sure you create a Table that contains the data you are trying to compare against before you complete this formula rather than trying to select the data range. For example, I struggled because I tried entering =VLOOKUP(D3,January!D:I,6,FALSE) instead of creating a table an entering in =VLOOKUP(D3,Table1,6,FALSE) which gave me issues.
Pivot Tables: By far Pivot Tables are my favorite tool within Excel. They allow you to summarize any amount of data in order to see a “big picture” view of what is happening within your account. It also allows you to filter data so you can easily look at individual campaigns, adgroups, locations, devices, etc. I learned how to use Pivot Tables by watching YouTube.com tutorials and following along step by step. If you are an Excel newbie or aren’t familiar with Pivot Tables, I suggest you put this at the top of your “to-learn” list.
Don’t forget that Excel offers many different ways to create visuals too. Creating helpful charts, graphs and other visual elements to add to my reports for my clients is something I have personally put on my to-do list. What is an Excel tool you are trying to master? Or better yet, what do you recommend I learn next?