A Few Excel Tips to Get You Through the Workday
Published: March 14, 2012
So as I established in an earlier post about curious queries, I am quasi-new to the world of SEM. I’ve had several jobs prior to PPC Associates, none of which were too technology-heavy. I knew I needed to learn more about Microsoft Excel, but I wasn’t sure where to begin. A couple of years ago, I bought a ‘For Dummies’ book. I began reading, got about 30 pages in, and realized I’d forgotten most of the stuff already, and I’d just need to look it up again when the time came around to use it. I decided it would be more logical to keep the book around for a reference; it continues to collect dust on my bookshelf.
About a year ago, the company I was working for at the time let a couple of its employees take a two-day-long Excel bootcamp class. I found myself in a giant ballroom at a local hotel. The course went over quite a few different Excel concepts, with around a 15-minute infomercial break every hour, promoting more courses and products offered by their program.
For 16 hours of my time, I left with a couple new skills but still had what I’ll call a ‘swiss cheese’ understanding of Excel – there some great bits, yet there still were some gaps and holes.
In short, there are two main things I really know about Excel, on the whole: 1) The only way to really get to know it is to work closely with it on a regular basis; and 2) It’s a big part of the business world, specifically online marketing, and even more specifically, paid search. There’s no quick fix for getting to know it comprehensively, and even self-proclaimed ‘experts’ can probably be shown a new trick or two. That being said, here are a couple of my favorite tricks. It is quite probable that you know them already…but if you don’t, I do accept flowers and retweets.
Let’s say, hypothetically speaking, you have a long list of items, and there might be some duplicate values in the list. In a past life, I would have gone through and looked over the list, item by item, for duplicate values. I might have sorted alphabetically to get really crafty. This would work, but there are much quicker ways to handle the situation.
If you, like me, would rather move on with your life than go blind from squinting for two hours, simply remove the duplicates as follows: highlight the area, go to the data tab, and, near the center, select ‘remove duplicates.’ It will then prompt me to confirm the area. After I say ‘Ok,’ it will remove the duplicates and kindly let me know how many there were and how many are left.
If you simply want to identify the duplicates, you can use conditional formatting, which is found on the slight right side of the home tab in the 2010 version of Excel. Simply highlight the area in which you want to identify duplicates, select conditional formatting, highlight cell rules, and duplicate values. It will then prompt you to select a color.
One more thing on the subject of duplicates (for now): if you want to see how many times a value appears in a list, use the countif function. It can be found in the function bar (usually hanging out with its good friends, concatenate and vlookup), but you can start to type in ‘countif’ in the search field if you’re having some trouble locating it. For the range, highlight the area in which you want to identify duplicates, then press F4. For the criteria, select one cell. You’ll then get the results, and you can then drag down to populate for the whole list.
Now, onto a new subject. If you are like me, you probably like to type quickly and not deal with capitalization, especially when dealing with ad customization. I’m sad to admit it, but I get a little lazy on this one at times. I used to go in and fix it manually. Annoying, and a total time suck.
Then, I discovered proper. One can create a new column and insert the proper function — from either the function bar, or by typing ‘=proper(‘. Then, select the cell that needs to be capitalized, and either type in a closed parenthesis or hit enter.
Ta-da! Said information is capitalized. You might want to remember to copy/paste- special-values of the newly capitalized data (so you can then delete the original, uncapitalized version).
Anyhow. As I said, these are just a couple of my favorite/most useful tricks. There are millions more out there – I’d love to hear some of your favorites as well. If there’s one thing we can all benefit from in the SEM world, it’s ways to save time.
– Jaime Sikora, Production Manager