New! Local SEM Competitive Intelligence
Discover Your Competitor’s PPC Keywords & Ad Copy
Enter your #1 competitor's URL

Top Excel Tips for SEMs

As many SEMs know, Microsoft Excel is a top tool of the trade (or a necessary evil, depending on your level of comfort with it). Whether you’re looking to get into the field or simply sharpen your skills, here are a few of my favorite, necessary shortcuts and Excel tips for SEMs.

1) Concatenate

Let’s say you have information in two cells that needs to be combined into one. An instance in the PPC world that jumps out at me is if you have description lines 1 & 2 in a Google upload and you need to prep it for a Bing upload. If that might as well have been in a foreign language, let’s just imagine you have a first & last name in separate cells and need to combine them into one.

If you have a long list, this would clearly take forever. In order to deal with this issue at hand, you’ll have to concatenate.

(Quick tangent: “Concatenate” means link together or join in a chain. I had never heard this word before. It took a trip to dictionary.com to figure out its meaning. I never hear it outside the world of Microsoft Excel. My goal for the week is to work it into casual, non-computer-related conversation.)

Anyhow, back to business: The simple way is to go up to the function bar (fx) and select Concatenate, then select the first cell you’d like to join. In *most* cases, you will want text 2 to be a space (although the choice is yours). Then, a third box will magically pop up and you can select the next bit of text you want to join in the fun.

2) Proper

Often when I’m typing, I am more focused on content and less on proper capitalization, etc. However, sometimes that leads to needing to make some edits in the end. I used to go in and fix it manually. Annoying, and a total time suck. That is, until I learned ‘proper’.

Let’s say you have some ads you created and forgot to capitalize them. All you need to do is create a new column to the right of what you want capitalized. In this new column, 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).

3) Asterisk Find Replace

Find/replace, in and of itself, is a great tool. It can be found by simply hitting ‘Ctrl+H’. In the instance you need to make something disappear (in this example, the http:// at the beginning of an url), you can simply find the item you want and replace it with nothing.


In the instance you have some variables you want to strip off (such as tracking parameters), you can always use the asterisk to replace everything after something. See below.

Before

After

4) Format Painter

Let’s say you’re working with a lot of data that you need to make client-facing. When copy/pasting from different docs and downloading from AdWords, certain numbers might not end up in the ideal format. One can easily reformat from all the buttons at the top, but the format painter can be a nice little shortcut. Let’s say you simply want to copy the formatting of another cell nearby. Simply highlight the cell you want to copy the formatting of, select format painter, then highlight the cell you’d like to change.

5) Freeze Panes

This function is great if you have a large amount of data to work with, such as an AdWords report with a lot of rows and columns. Let’s say you want to scroll far down or far right to see a certain line of data, but you don’t want to lose the heading associated with it There’s a helpful, quick, easy to way to keep either the top row or side row (or both) still while scrolling through large amounts of data. You can go to the ‘view’ tab on the ribbon, then, around mid-screen, select ‘freeze panes.’ From there, you can choose which area to, well, freeze.

Now that you’ve frozen the top row, you can scroll down and keep the headers visible…note the row numbers below.

These are just a couple helpful tips to get an SEM through the workday. There’s plenty more where this came from. Leave a comment if you have favorite Excel tips for SEMs or specific questions!

Author

Guest Blogger Jaime Sikora
Jaime Sikora joined PPC Associates in July 2011. Jaime is certified in Google Search and Google Display advertising. She graduated from the University of Illinois at Urbana-Champaign with a degree in Advertising. Prior to joining PPC Associates, she worked in the newspaper industry at the Chicago Sun-Times. In her spare time, Jaime enjoys reading, cooking, traveling, and spending time at the Chicago lakefront.