4 ASAP Utility functions that will make you love Excel even more…
As a senior production manager at PPC Associates, I spend a great deal of my day working in Microsoft Excel, whether that be manipulating data for reporting or prepping an upload sheet to load into AdWords Editor (Google AdWords’ offline editing program). When you’re dealing with thousands of rows of data that needs to be formatted for a client or uploaded into an account, accuracy and efficiency become vital.
In Excel, there are literally thousands of tools, formulas and shortcuts that can be used to your advantage when managing your PPC campaigns; some have already have been explained by my colleague, Laura Rodnitzky. However, within Excel, ASAP Utilities (which can be downloaded for free, here), is an add-in that works as a supplement to your preexisting tools and formulas and can enhance your Excel capabilities. Here are some of the ASAP Utilities’ most helpful tools for managing your AdWords data:
1. Advanced character remove or replace
This utility allows you to remove or replace advanced characters, some of which Excel does not allow you to change. In Excel, when using the Find/Replace function, the question mark is used to signify all data within a cell, which prohibits you from being able to isolate a question mark and deleting it. However, with ASAP Utilities, you can remove or replace a question mark.
a. In Excel, highlight the cells that contain the question marks.
b. In the “Text” section of ASAP Utilities, click “10. Advanced character remove or replace.”
c. Select the appropriate symbol – in this example, the question mark – then fill in the “Replace each selected character with:” box with a symbol that is not represented in the URL string. Replacing the question mark will allow us to then use the Find/Replace function in the next step. For this example, we chose the dollar sign. Then click “OK” and “Close.”
d. The question mark within the urls should now be a dollar sign. Use the Find/Replace function to find “$*” and replace with nothing. In Excel, * represents all data that is left in the cell (similar to the ?). This will allow you to delete all tracking, along with the dollar sign.
2. Performing Calculations on Selected Data
This utility allows you to perform a specific calculation on a selected range or data. It is most commonly used for calculating new bids. Instead of setting up a new formula juxtaposed with the original bid (Max CPC/CPM) to perform the calculation, this utility lets you update the original data. Note that this can also be done quickly in AdWords Editor, but if the keywords/ad groups can’t be isolated easily in AWE, this is the fastest way to update your bids.
a. In Excel, select the data to apply the calculation to.
b. In the “Formulas” section of ASAP Utilities, click “2. Apply formula/calculation to selected cells…”.
c. Create the formula, and then select “OK.”
3. Delete leading, trailing or excessive spaces
This utility allows you to delete any extra spaces before or after text, as well as extra spaces between tokens (excluding single spaces). The Excel Trim Function, which is located under Excel Tools and Functions, can also be used to eliminate unwanted spaces. However, depending on the format of the data, the Trim Function does not always delete these spaces, whereas the ASAP Utility will work on all types of data. This utility is most commonly used for deleting unwanted extra spaces in keywords, ad groups or ads before uploading into AdWords Editor.
a. In Excel, highlight the cells that contain extra spaces.
b. In the “Text” section, click “9. Delete leading, trailing and excessive spaces”.
4. Insert before and/or after each cell in your selection
This utility allows you to prepend or append data to a cell or range of data, which can be helpful when creating keywords with a broad match modified match type where a “+” must be prepended to each token in the keyword. The simple Find/Replace Excel function allows you to insert a “+” for all tokens by simply finding a space and replacing with a space and “+”. However, we can’t add a “+” at the beginning of a cell, in front of the first token, because Excel will then view the data as a formula, and will show an error. Similar to Excel’s Concatenate Function, we can prepend a “+” to the first token without receiving a formula error. However with ASAP Utilities you can prepend data to the cell itself, instead of creating a new cell to add in the “+”.
a. Select the cells that you want to prepend or append data to.
b. In the “Text” section, click “1. Insert before and/or after each cell in your selection” and fill in the “add before” and/or “add after”. In this example the “+” is added to “add before”. Make sure to check the “Example” section to verify the data looks accurate, and then push “OK”.
There are hundreds of other utilities within ASAP, all of which can be used to make your work in Excel a more efficient and user-friendly experience. I highly encourage anyone who uses Excel to manage their PPC campaigns to take advantage of this free utility and to start exploring all of its possibilities.
Guest Blogger:
Melissa Bregar is a Senior Production Manager for PPC Associates, a digital marketing firm with offices in the Bay Area and downtown Chicago.
