We recently shared our favorite PPC tools for campaign management and reporting. Aside from those tools that we use frequently, we are also constantly utilizing Microsoft Excel (like every other PPC account manager)! We thought we would share our favorite Excel functions and features. Hopefully you’ll learn something new!
My favorite Excel feature is the pivot table. In a lot of data downloads from our ad accounts, there are so many rows of data to sort through. With pivot tables, I can quickly create a condensed table to easily analyze data and turn it into visuals. The concept of a pivot table has also become available within ad platforms, like Google ads, and I’m looking forward to more excel-type features being available right within the interfaces.
We have written about Excel pivot tables before. If you’re interested in learning some specific reports you can compile using pivot tables, check out this post.
My answer is based on the function I use the most, which is actually the =LEN one. I write a lot of ad copy and this function provides character counts, allowing me to instantly see if my drafted headlines, descriptions, etc are within the proper limits. I also apply conditional formatting to cells above the character limit to ensure I notice copy that needs to be adjusted.
Using conditional formatting to turn your dataset into a heat map is super helpful, especially when reviewing dayparting data. If you’re unfamiliar with conditional formatting, this is a feature that allows you to change the format of your cells based on certain criteria that you specify. You can choose from Excel’s preset rules or create a new one, then choose Color Scales under Conditional Formatting to create your heatmap view:
The =VLOOKUP formula allows you to look up a value from a table and a return a corresponding value from another column. This feature has a number of potential uses and can be particularly helpful when building spreadsheets for reporting. For instance, when wanting to add together multiple conversion types, you can look up the conversion ID and report the number of conversions for each.
Macros are one of my favorite functions in Excel. If you have tasks in Excel that you carry out on a regular basis, you can create an action or set of actions that carries out your task with the push of a button. I have written about a use for macros when it comes to UTM term tagging.
Macros saved me many headaches when it came to a client that had very specific tagging needs. I also have a spreadsheet that I use to quickly build campaigns off of a keyword list for quick import into Bing Ads Editor and Google Ads Editor. Other uses for macros include formatting, filtering, organizing and sorting data, adding or creating labels, or calculating specific metrics like ROAS/CPA/ITC.
If you’re interested in additional ways to make the most of your efficiency with Excel, check out this post with shortcuts, links and more.