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:
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.
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 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 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.
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.
Browse By Category
The PPC Newsflash: Exclude Your Ads from Mobile Apps in the GDN
When we heard the news about the targeting changes, our analysts got to work...and we have a solution!
The Missing Ingredient to Your PPC Strategy: Non-Branded Campaigns
Learn how to employ a non-branded campaign strategy that will increase traffic, capture new customers, and beat the competition.
Is AI the Secret to Google Ads Success?
Stay in the running with your competitors on Google Ads, but don't stop there! Conquer them!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
High CTR? Low Conversion Volume? Where's The PPC Disconnect?
Struggling with PPC and converting clicks into customers and sales? Learn the importance of targeting those with a realistic intent to purchase.
Join the hunt: your first Golden Hero is now hidden!
It's here! Now is your chance to win 1 of 3 free conference passes to Hero Conf London with our annual Golden Ticket Giveaway.
4 Facebook Ad Time Saving Tools
Four tools you need to be using in Facebook advertising to save time and help focus your attention where it really matters.
Load More »