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.

 

Such joy in little numbers.

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.

That's a right proper time-saver, all right.

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

6 Comments

  1. Kristi March 14th, 2012

    I am such an excel idiot. Thank you for this post! If only I can remember it when I need it next time… :)

  2. Matt March 14th, 2012

    Using the non-commercial version of Microsoft Excel 2010 for work? (Says so in the 1st screenshot). Oh dear.

  3. Stuart Draper March 15th, 2012

    Helpful tips. I wish I had paid more attention in my college Excel class, but thankfully there are bloggers like you!

    Matt, you should sick Bill Gates lawyers on PPC Associates! You’ll get REAL far. LOL. People use their personal computers to do their work from home using the “non-commercial” version all the time. A judge would laugh you and ol’ Bill right out of the court room.

  4. admin March 14th, 2012

    Thanks Kristi! I know the feeling. Keep checking back – more excel tips to come!

  5. Terry D. Whalen March 15th, 2012

    Speaking of Excel and ad copy, today I used the '=LEN(input reference cell here)' formula for measuring the number of characters in a line of text. This is especially useful if you're collaborating with a client on ad text so that they don't go over the limits. If you combine this with conditional formatting so that a green check mark appears when character counts are within limits and a red X appears when the character count is over the limit, you have a nifty little tool.

    For the conditional formatting part of things, put your cursor in the cell containing the LEN formula, click conditional formatting ==> new rule ==> format all cells based on their values ==> format style: icon sets ==> click to reverse the icon order (red is now first, green last) ==> first icon drop-down select the X inside the red circle; second and third icon drop-downs select the check mark inside the green circle ==> put '26' in the first value box and '1' in the second value box ==> select the 'number' option under 'type' drop-down for both values ==> go back and check that the 26 and 1 are still there – Excel may try to change them on ya ==> of course the '26' and '1' are for the ad headline, and for lines 1 and 2 of ad text you'd change the '26' to a '36'.

  6. Terry Whalen March 15th, 2012

    Speaking of Excel and ad copy, today I used the ‘=LEN(input reference cell here)’ formula for measuring the number of characters in a line of text. This is especially useful if you’re collaborating with a client on ad text so that they don’t go over the limits. If you combine this with conditional formatting so that a green check mark appears when character counts are within limits and a red X appears when the character count is over the limit, you have a nifty little tool.

    For the conditional formatting part of things, put your cursor in the cell containing the LEN formula, click conditional formatting ==> new rule ==> format all cells based on their values ==> format style: icon sets ==> click to reverse the icon order (red is now first, green last) ==> first icon drop-down select the X inside the red circle; second and third icon drop-downs select the check mark inside the green circle ==> put ’26’ in the first value box and ‘1’ in the second value box ==> select the ‘number’ option under ‘type’ drop-down for both values ==> go back and check that the 26 and 1 are still there – Excel may try to change them on ya ==> of course the ’26’ and ‘1’ are for the ad headline, and for lines 1 and 2 of ad text you’d change the ’26’ to a ’36’.

Leave a Comment