5 More Excel Tips To Get You Through Your Workday
December 1, 2015
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:
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 Advertising Principles That Guide Facebook's Decisions
Facebook highlights the advertising principles that guide their decision making in response to recent events.
Smart Ways to Get More Marketing Budget and Better Tools
In this new live webinar, Kristin Vick from Hanapin Marketing and Jeff Sauer from Jeffalytics discuss how marketers can ensure they have the budget they need to be effective with online advertising and get the right tools to make that argument.
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
The Hero Conf Difference
Of the 50+ search and social conferences you can choose from, we're here to show you why Hero Conf is a can't miss event for busy digital marketers like you.
Improve Your Facebook Ads With Creative Hub and Split Testing
With the updates to Creative Hub and the Split Test feature, one can now institute a basic ad creation and testing process directly within Facebook.
Work Habits & Resources for the Stressed-Out PPC Manager
Feeling a bit stressed this holiday season? Get tips and resources that will help you become more efficient and alleviate stress!