5 Super Basic Excel Tips for the SEM Beginner
Published: November 7, 2014
Author: Charlotte Haab
As a true beginner in the SEM world, the last four weeks have been a whirlwind of information. As an account associate I am responsible for many production tasks — many, if not all of which require the use of Excel. As an excel newbie I quickly found myself drowning in formulas and functions, struggling to perform tasks as remedial as highlighting an entire row. For those of you who are just starting out I would like to share these five super basic tips that I wish I had known on my first day.
Autofill is a quick way to apply cell settings to the rest of the cells in that column. To autofill you simply hover your cursor over the right bottom corner of the selected cell until you see a solid black cross symbol. Then simply double click, and your settings will fill the rest of the way down.
In this example all of my proposed negatives are phrase match. Instead of typing “Phrase” in each individual cell, I can just use Autofill:
-Select starting cell.
-Hover cursor over bottom right corner of cell.
-Wait for black cross to appear.
-When black cross appears, double click.
- AutoFit Width for Entire Sheet
Often when a report downloads row information or headers cannot be fully seen. This cramped obstructed view makes it frustrating to work, and difficult to view all of your information. It can be time consuming to go through auto-fitting each column. A fast way to do this is to simply highlight the entire sheet using the Select All button in the upper left hand corner of the sheet. Then move your cursor over to the right edge of the cell A column header. When you see a black cross symbol with arrows on the horizontal part, double click, and the whole sheet will auto fit width.
In this example my report downloaded many columns, producing a pretty tight view. To spread these columns out and actually be able to read, all I need to do is AutoFit Width:
-Find Select all button in top left corner of sheet.
-Click Select All button to highlight the entire sheet.
-Hover cursor right side of Column A header.
-Wait for the cursor to be a black cross with horizontal arrows.
-Once you see the appropriate cursor, double click.
Ctrl+Shift is a keyboard short cut that enables you to highlight specified regions of an excel spreadsheet en masse. This is particularly helpful for dealing with sheets that have thousands and thousands of rows. You can select downwards using the down arrow key, sideways, using the side arrow keys, or you can select you entire body of cells by selecting the end key.
In this example I want highlight just column H. Instead of dragging my cursor through 5000 rows, I can just use Ctrl+Shift down once I’ve selected the top cell.
-Select the first cell in the body that you want to highlight.
-Press and hold down the Ctrl key and the Shift key simultaneously.
-Then press either the up, down, left, or right arrow keys depending on desired selection.
-If you want to select the entire body press and hold Ctrl+Shift and press the End key.
- Find & Replace
Find & Replace serves a couple functions. One use for find and replace is for getting rid of unwanted items in a wide range of cells. By entering Ctrl+H on your keyboard you will bring up the Find&Replace window. Simply type in what you want to find, and leave the Replace box blank. Click “Replace All.”
In this example I want to get these negative keywords ready for upload. With the match type listed in the column next to the keywords, I really don’t need to have quotation marks around each term. Find & Replace is the quick and easy way to remove those.
The second use for Find & Replace, is to actually replace on item with another. This can be done my typing Ctrl+H on your keyboard. When the window appears simply type what you want to replace in Find box, followed by what you want to replace it with in the Replace box.
In this example I am performing the preliminary steps of a second tier sync. I want “Google” to be changed to “Bing” in all of the campaign names.
-Select the body of data in which you would like to make adjustments.
-Press and hold Ctrl+H.
-Fill in the appropriate values in the Find box and the Replace box.
-Click Replace All.
Turning your data set into a table can be helpful when adding filters to multiple columns. The keyboard shortcut Ctrl+T quickly ads filters to each column header in your set. Just make sure you remember to select “My columns have headers,” when prompted by the pop up box.
Another advantage to making your data into a table is that formulas with automatically fill throughout the body of the table. It’s a quick way to calculate metrics.
Additionally, by making your data into a table you are more easily able to manipulate the style of the cells, for a neat presentation.
In this example I start by highlighting my data and pressing Ctrl+T. I want to turn my data set into a table to easily apply a formula to my metrics, sort by highest CTR percentage, and style my data how I would like.
-Select Data Set
-Make according adjustments
Excel shortcuts make the daily lives of search engine marketers so much easier. These are the ones that I use most often, and that have helped me the most in this learning process. I’m sure there are hundreds more that I don’t know yet, but for now, I hope that these five can help make some new account associate’s first week go a little smoother.