When I first started at Point It last year, I had no PPC experience at all. I minored in marketing at the University of Houston, had some email marketing background, and had plenty of account management and customer service experience, but I was still overwhelmed by all the things I had to learn to get up and running in the search engine marketing world. One of the first things I did learn, however, was that Excel would quickly become my best friend. With that in mind, here are some tips that’ll help you along the path of becoming an Excel whiz!
(Note: The following instructions are from Excel 2013.)
Text formulas are pretty critical in PPC as you handle reporting, copy writing, and many other things. Here are a few that I’ve found particularly helpful:
I’m pretty sure you’ll never hear this word in the non-Excel/technical world, but I promise you it’s incredibly useful. Here’s the formula:
=CONCATENATE(cell reference, cell reference…)
The concatenate formula will combine whatever cells or text references you list in the formula together (I’m not sure why Microsoft didn’t decide to call this formula “JOIN”, “FUSE”, or “MEGAZORD”, but that’s beside the point). You’ll need a minimum of two references, but you can do many more! You can also join your own text with cell references as long as you surround the text with quotation marks. Here’s an example:
Would result in: www.pointit.com/blog
The length formula (LEN) lets you know how many characters are contained in a specific cell reference. This is especially helpful when writing ad copy since you need to follow the search engines’ character limits. Here’s an example:
Would result in: 15
The proper formula is great for copy writing, especially if you’re of the “capitalize every word in ad copy” camp. All you have to do is type the formula and direct it to the cell where your ad copy is to get it to work. Here’s an example:
A1 = thanks for visiting our blog!
Would result in: Thanks For Visiting Our Blog!
The exact formula is useful when you’re checking for URL changes or trying to find differences in long lists or strings. The formula will return “TRUE” if your two cell references match or “FALSE” if they do not. Here’s an example:
A1 = www.pointit.com/abc123
B1 = www.pointit.com/abc122
Would result in: FALSE
The Importance of Paste Values
Have you ever copied a large chunk of data, pasted it into a report, and lost all the formatting you (or someone else) worked so hard to put together? Well, the magic solution to your woes is to use Paste Values instead. While copy/paste is great in most situations, Paste Values is critical in Excel reporting as it doesn’t change the formatting of the destination cells and also does not paste formulas if you used any in what you originally copied.
There are several different ways to get to the Paste Values command, but the one I’ve found easiest is right-clicking after you’ve copied something and clicking on the 123 clipboard under “Paste Options:”.
Bonus Tip: If you use Paste Values often, you can also create your own custom keyboard shortcut by recording a macro, but that’s a bit of a lengthy explanation. This site might help you get started: http://exceltactics.com/make-custom-keyboard-shortcuts-using-macros/
The Almighty Fill Handle
The fill handle is a life saver when you’re typing data in a series or want to quickly copy a formula down a column or across a row. If you hover over a cell, you might notice a small box at the bottom-right corner of it (the fill handle), which looks like this:
To use it, you just need to click and drag it down or across the cells that you want to fill in. For example, you could type in a date into a cell and use the fill handle to insert a series of progressively higher dates into the cells you select with it. You could also use it to copy a formula into a series of cells, but note that the cell references in the formula will change in the series since they are relative references by default. This is what would happen if you used the fill handle to copy down a length formula from a cell:
Bonus Tip 1:
You can double click on the fill handle to automatically fill all cells that have data in the adjacent columns to the left or right of the original cell.
Bonus Tip 2:
If you want to have the fill handle maintain the same value throughout the series (for example, filling the same date repeatedly instead of filling a series of dates), you will need to highlight two cells that have the same value and fill normally from there.
There are a lot of other use cases for the fill handle, but that may take some experimentation. Don’t be afraid to give it a try! You can almost always undo your work in Excel with “ctrl + z”.
That’s all for now, everybody. I hope this was helpful for PPC newbies and seasoned veterans alike. If you have any other tips to share or need some further help, feel free to leave a comment and I’ll do my best to point you in the right direction!