How To: Excel At Excel For SEM Applications, Part 3
In this continuing series (part 1, part 2), I’ve shared some tips and tricks specifically for the search marketing professional using Microsoft Excel. The feedback has been great (thank you!), so I’m going to expand these posts to not just include tasks associated with SEM, but into a mini-Excel training course for search marketers using the software.
By the end of this series of posts, I hope that it will become a reference point providing a good overview of efficiencies, tips/tricks, shortcuts, etc. for any search marketer in the trenches. I won’t be covering everything you can do in Excel, but rather the core skill set that I know every SEM pro needs to have, in order to move relatively quickly in the tool. There will be a post with just keyboard shortcuts and even a basic Excel for SEM Proficiency Test as the last post which you can use to evaluate the skill level of your team, new hires, or applicants.
Before I get into this week’s tips and tricks, I’d like to share some great resources on the web where you can take your Excel expertise to the next level.
- About.com: Learn Microsoft Excel is a great beginner’s course you can refer the new user. Includes all of the basic things you would imagine such as and intro to formulas, using cells, etc.
- Microsoft Office Official Excel Page Can’t go wrong going straight to the source, right? You can access the basic Excel help from here and look through some of the featured functionality.
- Mr. Excel Daily Podcast His name is Mr. Excel…doesn’t that say it all? Bill Jelen’s daily video podcast will keep your training going even when you’ve mastered the basics.
- Excel 2007 Tips.net There are a lot of tips sites out there but I think this one, especially for Excel 07 is the best one. Their (semi)daily email is really great.
- Lynda.com Not just for Excel training but for literally dozens of software titles. Lynda is a very low-cost on-demand video training that is really great. I have recommended their service many times.
And now for this week’s Excel tips/tricks:
Paste Special MS Excel definition – copy complex items from a Microsoft Office Excel worksheet and paste them into the same worksheet or another Excel worksheet using only specific attributes of the copied data, or a mathematical operation that you want to apply to the copied data.
Right on the HOME ribbon, the first option is PASTE. Of course, most people know what pasting is, but they may not know just how many options they have at their disposal.
Upon clicking: PASTE SPECIAL from the drop down menu, you get the following dialogue box:
By choosing the right option, you can chose how and what you paste inside Excel. For example, by choosing Paste Link, you paste a formula of the source cell(s) into the destination space. So, if you change those values in the source cells, the linked cells change too. This is very helpful when considering budgets. You can have all of your campaigns listed with the percentage of the budget you want them to spend and then, if the budget changes, you just change the source cells and everything calculates accordingly.
Here are your PASTE SPECIAL options explained:
- All – Pastes all cell contents and formatting of the copied data.
- Formulas – Pastes only the formulas of the copied data as entered in the formula bar.
- Values – Pastes only the values of the copied data as displayed in the cells.
- Formats – Pastes only cell formatting of the copied data.
- Comments – Pastes only comments attached to the copied cell.
- Validation – Pastes data validation rules for the copied cells to the paste area.
- All using Source theme – Pastes all cell contents in the document theme formatting that is applied to the copied data.
- All except borders – Pastes all cell contents and formatting applied to the copied cell except borders.
- Column widths – Pastes the width of one copied column or range of columns to another column or range of columns.
- Formulas and number formats – Pastes only formulas and all number formatting options from the copied cells.
- Values and number formats – Pastes only values and all number formatting options from the copied cells.
- Skip blanks – Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.
- Paste Link – Links the pasted data on the active worksheet to the copied data.
- Transpose – Changes columns of copied data to rows, and vice-versa when you select this check box.(see what it looks like below)
NOTE: The mathematical operations refer to what formula Excel will perform when you paste a number value into a cell that already contains a number value.
Protecting Sheets/Workbooks MS Excel definition – To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet or workbook elements, with or without a password. You can remove the protection from a worksheet as needed.
Have you ever received back your workbook from someone only to find they accidentally messed things up and saved over your original? Aaaarrgh! You should have protected your work. Protecting your workbooks and worksheets will also ensure your numbers remain correct as they get passed from person to person.
HOW TO: It’s not hard, you just need to remember there are two steps. The first is choosing which cells you want locked. It’s okay if you want every cell on a sheet, just click CTRL + A to select all of the cells on the sheet. Once you’ve chosen the cells you want protected, right click and choose FORMAT CELLS. Click the PROTECTION TAB on the dialogue menu that pops up and check the LOCKED option. By default, the locked option is checked. (see below)
The second step is to go to the REVIEW ribbon and click PROTECT SHEET. You have many options here but choosing SELECT LOCKED CELLS will ensure future users won’t be able to change your important data. You will have to enter a password to continue. (see below)
Now you won’t be able to select any of the cells you chose to lock. To unlock, click UNPROTECT SHEET and input your password.
Custom Lists MS Excel definition – sort or fill in a user-defined order.
Every Excel user has had to type in the same list over and over at some point. Whether it’s the days of the week, campaigns of a client, a core group of keywords, names of your colleagues, etc, this is a common thing when working with spreadsheets. Excel offers you the opportunity to create your own list of values which you can access quickly at any time.
All you have to do is type the one of the list’s values in and then you can simply drag the HANDLE (found when you hover over the bottom right corner of any cell) in any direction. You can go up, down, left, or right. The list will autofill in order–so if you start with a value in the middle of the list (for example, JUNE), the next value autofilled will be the one after it in the custom list (example, JULY).
Excel provides the following built-in, day-of-the-week, and month-of-the year built-in lists.
- Sun, Mon, Tue, Wed, Thu, Fri, Sat
- Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
- Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
- January, February, March, April, May, June, July, August, September, October, November, December
HOW TO: To create your own custom list, first create the list and select it. Then, Click the Microsoft Office Button , click EXCEL OPTIONS, click the POPULAR category, and then under TOP OPTIONS FOR WORKING WITH EXCEL, click EDIT CUSTOM LISTS.
In the Custom Lists dialog box, verify that the cell reference of the list of items that you selected is displayed in the Import list from cells box, and then click IMPORT. The items in the list that you selected are added to the Custom Lists box. Click OK twice.
Now you can access your custom list anytime by typing in any value of the list and dragging the handle.
NOTE: Once you create a custom list, it is added to your computer’s registry, so it is available for use in other workbooks. If you use a custom list when sorting data, it is also saved with the workbook, so that it can be used on other computers, including servers where your workbook might be published to Excel Services.
THE [DREADED] VLOOKUP MS Excel definition – You can use this function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.
In regular words, the VLOOKUP (Vertical Lookup) is a way to compare two lists as long as there’s a shared group of values in both. A common use for an SEM pro in the trenches would be to compare to lists of keywords to see if there are any duplicates. You can do a simple de-dupe easily in Excel 2007 (by selecting a list and clicking REMOVE DUPLICATES on the DATA ribbon), but VLOOKUP lets you return a value which is an important distinction.
I mentioned the VLOOKUP in a recent column but it makes sense to revisit it in this series and provide step by step instructions (which I didn’t do before).
HOW TO: In this example, let’s compare the two keyword lists, A and B. If done properly, the VLOOKUP formula will compare List B with List A and then return the value next to any paired value (we’re using the keyword campaign the term is located in).
Until you get formulas down solid, the best way to approach them is using the wizards through the FORMULA ribbon. Choose, LOOKUP & REFERENCE -> VLOOKUP and you’ll get the following dialogue box:
- lookup_value – The value to search in the first column of the table or range. In our example, I chose Cell E7. (I copied the final formula to the rest of the list).
- table_array – The range of cells that contains the data. I chose cells B2:C10. Thus, the formula will look to see if the term “automobile” is in List A.
- col_index_num – The column number in the table_array argument from which the matching value must be returned. I put “2″ because the campaign column is the second column of the table I’m comparing List B to. So, if one of the keywords in List B matches List A, the campaign name will be filled in where the formula is. In the example above, only the term “used car” was shared by both lists so it returns the campaign “Car Age Terms”; the terms not found in List A get “#N/A”.
- range_lookup – This is an option field. I always use FALSE when comparing text lists.
Obviously, for two lists that are only 5-10 terms each, you don’t need a formula. But what if you had 100,000 keywords? It would be a huge task to try to do this manually.
NOTE: Make sure to you absolute values of the lookup table if you’re going to drag the formula down the list as I have. So, if you look in the formula box, I use the dollar signs $B$2:$C$10…if I used just B2:C10, when I drag the formula down, the next cell would look at the cell range B3:C11 and so forth. By adding the dollar signs, I “lock” the range in place. You can also simply name the range and type in that named range instead of inputting cells.
Class dismissed for today. Be sure to do your homework, and practice some of these tips and tricks before our next installment.
Some opinions expressed in this article may be those of a guest author and not necessarily Search Engine Land. Staff authors are listed here.
(Some images used under license from Shutterstock.com.)
Sign up for weekly recaps of the ever-changing search marketing landscape.