Excel is the most important and powerful search marketing tool. In PPC Management, Excel is a must-have tool in addition to AdWords (adCenter) Editors. Building keyword lists, writing ad copy, analyzing data and preparing reports – all these tasks mostly done using Excel. Most of our optimization time we spend cranking Excel spreadsheets and log in AdWords or adCenter web-UI only to get status updates or make some minor changes. Don’t get me wrong: same tasks can be performed in the web-UI, but Excel allows us to streamline the process and get a better use of our time. So, here are some tips to help you work with your PPC data more efficiently.
Both, AdWords and adCenter, allow you to download different kinds of reports in spreadsheets format. But in order to get them, you have to login in the UI and go through several steps to download your report. I found to be more efficient to simply copy or export data from AdWords Editor or adCenter Desktop tools. It is quick, and I can paste back changes in the desktop tool and upload them in the account right away.
Working in Excel involves a lot of copy-pasting, cleaning, reformatting and calculating. You can do it all manually, or, you can utilize powerful Excel’s functionality to get everything done more efficiently.
For those, who are using Microsoft Office versions 2007 and 2010, you can customize a Quick Access Toolbar with most commonly used commands and functions. This will help to quickly access your favorite commands without searching them in the ribbon tabs.
Most commonly used formulas
1. VLOOKUP. This is really a blessing for a PPC’er. This incredible function is great for comparing and connecting different sets of data. Basically, this formula looks if a given value from one table exists in the first column of another table, and then returns a value from the second table. It can be used, for example, to compare if new keywords you are planning to add, already exist in an account; or, you can analyze two different time frames.
Tip: do not forget to lock the range of the second table – a common error when copying this formula down.
2. CONCATINATE or &. Very useful while creating different keyword lists or adding tracking parameters to URLs:
Tip: I personally prefer using “&” symbol, because it is shorter than typing a full “concatenate” formula, but does an exactly same thing.
3. LEN – is great when writing an ad copy. Simply put this formula next to your headline and description cells; it will then tell you when there is more space available, or when it’s over the character limit.
4. LOWER, UPPER and PROPER –change to lowercase, uppercase and capitalize every first letter of each word in the cell. Helpful with keyword lists and ad copy creation.
Data Tools
You can find these tools on the Data tab of the ribbon. These are extremely helpful with making changes, analyzing data or preparing reports, especially while working on large data sets.
1. Subtotal. It will quickly summarize the table rows for a specific column. It is not limited only to a SUM function, but could also calculate average, min, max, count, deviation etc. Really great for a data analysis, but also with reporting, since it allows to collapse and expand rows in a table.
Subtotal Expanded view:
Subtotal Collapsed view:
Tip: first make sure to sort the column you’d like to summarize, and then use subtotal.
2. Text to Columns. I find it very useful while working with destination URL lists. Let’s say, you’ve got an URL report and you’d like to analyze the performance per unique URL. However, every destination URL has a unique parameter attached. In this case subtotal function will not work since it sees every URL as an unique value.
By using the text to column function with a delimiter “?”, we can separate the tracking parameters from the base URL, and then summarize the data using the subtotal function:
Tip: add a few empty columns after the one you are planning to break down, so it won’t replace data in other columns.
Other efficiency tools
1. Macros – great when you have to repeatedly perform same actions over and over again. It will record the set of your actions and then repeat them with just one click of a button. A saw many folks underutilize this feature, but it comes very handy in preparing client reports.
More VBA/Macro related resources
2. Pivot Table – another powerful tool to manipulate, aggregate and present data in different views. Beside tables you can also build pivot charts based on your raw data.
More Pivot Table related resources
3. Select visible cell (Alt+) – this one comes handy when after hiding some rows or columns you need to copy and paste only visible cells.
4. Paste Specials and transpose – are used to copy-paste formulas, values, formats, or a combinations of those. The only annoying thing is that these options are several clicks away. My efficiency advice will be to put these buttons on your custom toolbar, so when you are doing a lot of copying they are available in a single click. Transpose function will switch rows to columns and vice versa:
Transposing into:
And now the last but not the least. Look at your mouse. If it is not a very old one, it probably has a few extra buttons that you’ve never used. Speaking from efficiency standpoint, it would be nice to program those buttons to execute some commands that we use most often. Let me guess… that would be copy-and-paste, right? Well, on mine I have set the left scroll click for copying, right scroll for pasting, and wheel click to cut. If you are still using a good old cntrl-c/cntrl-v combo, you are not going to believe how fast I can move data around using only my mouse. That’s saves me a lot of time. Try it, and after a while you’d never go back to cntrl c and v combo.
That’s just a few tips on how to become a power Excel user. Don’t forget about numerous keyboard shortcuts and powerful functions that help to speed up your work and leverage your analytics.
Also Read:
- 10 Helpful Excel PivotTable Tips for Quick and Efficient Data Analysis
- 10 Simple Tips To Make Your Excel Charts Sexier
Click here for more Data Analysis Tips
Courtesy: pointit.com