We’ve gone through Excel tips and tricks numerous times here on PPC Hero. I have found that I am constantly learning new things about the program that make working with data easier and more user-friendly to whoever I’m sending it to. Excel is capable of doing so much more than what the majority of people use it for, so today we’ll go through a few features that are commonly overlooked.
Naming Cells / Fields
Have you ever opened up an Excel workbook that someone else created, only to discover a slew of complicated formulas that you don’t understand? Maybe there are errors in the calculations, but it’s quite difficult to piece together the formulas. Avoid confusion by naming cells!
Click on the cell you’d like to define, and then type the name in the upper left corner (this cell normally shows your location, ex: C14).
You can also choose to name a range of cells as shown below:
Now, when you create a formula, the names you’ve assigned will appear in the formula, rather than your typical cell names.
Once you’ve named a cell (or range of cells) you can just start typing in the name and Excel will auto-fill for you:
Tables
Excel Tables do more than just make your data pretty. It makes it easy to manipulate your data. Simply highlight your data and click the Table icon.
Once you select the “Total Row” option, you can select what you’d like to do for each column:
Another neat feature of Tables is that if you want to make a calculation, not only does it use the column names to make things clear, but it automatically fills the formula down throughout the column once you hit enter.
Paste Special
If you are unacquainted, let me introduce you to Paste Special:
Paste Values is most likely already in your Excel repertoire, but using an operation can also be useful. For example, say you’re doing a round of bid changes in Excel and want to increase all bids by 10%. Add a new column with the desired multiplier (ex: 1.1 for a 10% increase). Then copy your max CPC, and Paste Special over the Multipliers.
“Skip blanks” is also great if you’re copying and pasting a list that has lots of blanks in it, as those can be rather cumbersome to get rid of. Paste Transpose will paste a column into rows and vice versa.
Word Count
Word count is a great little formula to have in handy when you’re looking at search query (or keyword) data. The gist of the equation is that you want to count the number of characters (using the LEN function) and then subtract the length with spaces removed (plus one). Essentially you’re counting the number of spaces.
You can also use this formula to count the number of instances of a particular character in a text field (like + for example):
This tactic is particularly useful in an account with many modified broad keywords, with not all keywords having every word modified.
Note: this also works with counting the number of instances of any character in a text field. Just change the “+” to what you are counting. Once you’ve calculated the number of words and the number of “+” modifiers, you can compare the performance of keywords that are fully modified, or partially modified.
Standard Deviation
Standard deviation measures the amount of variation in a set of values. In the PPC world, standard deviation is a great way to measure the amount that a certain metrics fluctuates. Take this example, where we were running a test between an automated bidding platform vs. manual bidding via Excel:
To do this, we downloaded a report with conversion and revenue data segmented by day. Then we calculated the averages and standard deviations using the STDEV.S formula. As you can see, manual bidding has a lower standard deviation, meaning that the number of conversions and the amount of revenue fluctuates less. Automated bidding was changing bids very frequently, and caused a lot of fluctuation in performance.
Closing Thoughts
While you can certainly use Excel perfectly fine without using these features, knowing the program more in depth can make your daily usage that much more enjoyable.
