• Disclosure
  • Free SEM Tools

The SEM Blog

Search Engine Marketing for Online Lead Generation and Conversion - by White Hat Ops

  • Web Tech
  • Marketing
  • How Tos
  • PPC Tools
  • Data
  • Free SEM Tools
You are here: Home / Excel Tips and Tools / ASAP Utilities: One Tool to Rule Them All

ASAP Utilities: One Tool to Rule Them All

June 7, 2012 by Melissa Bregar

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.

Advanced character remove or replace example

a.  In Excel, highlight the cells that contain the question marks.

ASAP Utilities in Excel

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.”

ASAP Utilities -Advanced character remove / replace

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.

Use the Find/Replace function to find “$*”

Advanced character remove or replace example

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.

Adjusting Bids example

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…”.

Apply formula/calculation to selected cells

c. Create the formula, and then select “OK.”

Performing Calculations on Selected Data example

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.

Delete leading, trailing or excessive spaces example 1

b. In the “Text” section, click “9. Delete leading, trailing and excessive spaces”.

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”.

Insert before and/or after each cell in your selection example

 

Ex Prepended "+"

Insert before and/or after each cell in your selection example 2

 

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 - Senior Production Manager for PPC Associates Melissa Bregar is a Senior Production Manager for PPC Associates, a digital marketing firm with offices in the Bay Area and downtown Chicago.

Filed Under: Excel Tips and Tools, Google AdWords, How Tos and FAQs, PPC Tools Tagged With: excel tips, excel tools

Categories

Recent Posts

  • Google Login Account Using Your Existing Email
  • Local Business – How to Add Posts to your Google Business Listing
  • Recap: Google Marketing Next 2017

Partners

  • White Hat Ops
  • Tendenci
  • SearchHOU Meetup

Tools

  • BigCommerce Shopping Cart Trial
  • Secure WP Hosting
  • Google Mail and Apps

Categories

Partners

  • White Hat Ops
  • Tendenci
  • SearchHOU Meetup

Tools

  • BigCommerce Shopping Cart Trial
  • Secure WP Hosting
  • Google Mail and Apps

Google Weather Report

Weather provided by MozCast

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in