How To: Excel At Excel For SEM Applications, Part 6

Ah, what a summer! The initial concept of this In the Trenches column was to provide the day-to-day search engine marketer with tips, tricks, and news to make them better at their jobs. I have been wanting to do this Microsoft Excel series for over a year, so I thank again my editors here at Search Engine Land for letting me get this done. Excel is such a huge component to all of our workdays that I thought it deserved this level of attention. Once again, my goal was not an Excel 101 for everyone, but rather highlight some advanced techniques for online marketers to be more effective and efficient when using the tool. Based on the great feedback I’ve received, I think I’ve been able to hit the mark.

But, if you’re going to social bookmark any of these posts, this final post is the one to tag. There’s a mini-guide to the previous five posts, a ranking scale of Excel ability, a twenty question skills test, as well as some final tips and tricks that I think you’re going to like. Let’s get started.

Excel for SEM Applications Mini-Guide

• Part 1 – Conditional formatting, Concatenation, Length Formula
• Part 2 – Select only visible cells, Macros, Create a Table of Contents, Set Print Area
• Part 3 – More Excel resources, Paste Special window, Protecting sheets, Custom Lists, VLookup
• Part 4 – General Excel efficiency tips, Named ranges, Secondary Chart Axis, Tab colors, Go to the next line in a cell, Print Headers on Every Page, Count days between days, Turn off hyperlinks, Excel shortcuts Cheat Sheet download
• Part 5 – Master Pivot Tables

Whew! I still have some cards up my sleeve but those posts should take any basic SEM Excel user to the next level.

Because you can engage with Excel for very simple tasks, I think that many people don’t have a good sense of just how much can be done with the tool. I can’t tell you how many times I’ve had candidates interviewing for positions at my firm that rate their Excel skills at “above average” or “excellent” but cannot put together a simple pivot table. I don’t think they’re being dishonest with me, I really think that they think they’re an eight out of ten when in fact, they are an eight – just on a scale of thirty. In previous jobs, they’ve been able to use Excel very efficiently and perform all of the tasks required of them. However, that doesn’t mean they know Excel.

I’ve come up with a scale of what I think is a good set of Microsoft Excel skills for the digital marketing world. Obviously, for Accountants or Computer Programmers, this would be a different scale. However, for online marketers, here’s mine:

• Rookie – can navigate through Excel, save and print documents, do basic calculations, able to use the SUM tool.
• Can Get Tasks Done – knows how to build basic charts, use a few common formulas, uses the “left hand” keyboard shortcuts (copy, paste, select all, etc), can format a spreadsheet to match previous ones if given enough time (i.e. borders, cell shading, etc).
• Advanced User – easily flies through all common Excel tasks, can import data from various formats with ease, utilizes time-savers (such as keyboard shortcuts, conditional formatting and names ranges), can follow the wizard steps for most formulas, builds nice charts/graphs, Pivot Tables are a snap.
• Expert – is the “go-to guy/girl” for team Excel questions, builds Macros to automate repetitive tasks, tweaks “hidden features” to provide more functionality, knows basic integration between Excel and other Office software, has dabbled in the Virtual Basic environment.
• Excel Master – rarely is unable to perform even the most advanced/expert tasks, builds 3rd party applications using the Excel environment, IT level understanding of how Excel can integrate with other systems, generates custom solutions for important tasks, feels Excel is a lightweight program for non-database administrators (similar to how a NASCAR mechanic might view a ten-speed bike).

The Online Marketer’s Excel Skills Test

I have been wanting to put together a twenty question test for awhile to have on hand to check the Excel levels of new hire candidates at my company. But putting this together was harder than I ever thought. I wanted to make sure I had a good mix of easy and hard questions to get a good gauge of the candidate’s skill set, but because just about every Excel operation can be done in several ways, it took me several tries to get the questions worded correctly.

This test should take someone no longer than five minutes  — any longer than that, and they just don’t know the answers. My scale would be: 0-7 correct – needs more training, 8-12 they know the basics, 12-15 they’re pretty good, 15 to 20 they can handle themselves pretty well in Excel.

I’ve made a clean copy of this test here if you’d like to print it out. The answer key is at the end of this post.

1. What is the keyboard shortcut for Paste?
2. In what mode of Excel allows you to look at how a document is going to look when printed before actually printing.
3. Which tool allows you to copy the formatting of one cell to another?
4. Which formula is being described in the following Excel definition? “The _________ function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items.”
5. What are two ways in which you can select all of the cells in a spreadsheet?”
6. True or False? You can set Excel to print the gridlines of the cells without having to create borders around each cell.
7. How do you copy and paste just the value of one cell which is a formula into another cell?
8. To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by using which Excel feature?
9. Which formula is being described in the following Excel definition: “You can use the ________ 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.
10. Right click any cell and choose which feature to change the cell’s shading, borders, font, etc?
11. True or False? It is possible to insert an image into a spreadsheet?
12. What is the shortcut for entering the current date into a cell?
13. How do you quickly change a column width to fit the contents?
14. True or False? By default, when the source data table of a pivot table is altered, those changes automatically are updated in the pivot table.
15. In the formula, =SUM(F5+G8), what do you need to do to the formula so when you drag it down, the first value to be summed is still F5?
16. What feature in Excel allows helps you autoformat cells based on rules? For example, “change the shading of all cells to red with values greater than 50″.
17. True or False? A macro allows you to look very close into a cell.
18. Which feature in Excel allows you to displays only the rows that meet criteria that you specify and hides rows that you do not want displayed?
19. Which feature allows you to reset the chosen range in alphabetical order?
20. True or False? If you use a password to protect a worksheet, you have to enter it and then re-enter it for verification.

A couple of final tricks before the Answer Key…

Sliders

You can use sliders to control values in cells. This is one of my favorite ways to trick out my spreadsheets because it allows me to quickly play with the numbers and see how other formulas are affected. Check out the example below:

Now I slide up the budget to $71,698…

Because I have the slider connected to the Total Budget cell, I’m able to see how my budget allocation changes as the budget goes up or down. There are pretty much unlimited uses for sliders in a spreadsheet. It’s very helpful in client presentations to show them, for example, how many more clicks they’ll get if they add more budget or how many conversions they’ll lose by not dropping their Average CPCs.

How To:

Basically, you’re just going to create a slider, connect it to a cell, and choose a Min and Max range for the control. First, you have to have the Developer ribbon visible, which is not a default Excel feature. If you don’t see Developer at the top of Excel, click the Microsoft icon –> Excel Options and make sure the box is checked next to Show Developer Tag in the Ribbon. Go to the Developer ribbon and click Insert. A mini-window will pop up with various controls you can add. Choose Scroll Bar under the ActiveX menu (it’s the last one in the third row). Your mouse will change to a T shape, which you can then draw your scroll bar. Don’t worry how it looks right now, because you can  change the shape later by selecting it and dragging any of the corners. The next part is connecting it to a cell. Click on the slider to select, and then click Properties at the top. The only properties you need to deal with are Linked Cell (which cell the slider is controlling), and the Min and Max Values. Min Value would be what the cell would show if the slider is at the far left, Max would be at the far right.

Creating Groups in Excel

On occasion, you may want to be able to quickly hide or unhide rows of data. For example, if you have all of your stats listed by account, campaign, ad group, etc, you can use Groupings to just look at the campaign level or just look at the ad group level. I often use groups when I’m outlining my project plans with each phase at it’s own group. That way I can look at each one separately without the clutter of the rest of the information displayed.

How To:

It’s very simple. Just choose the rows you want to group and click Group on the Data ribbon. You can Ungroup them at anytime. Here’s a visualization from the Excel help files on how this works:

Create a drop down menu of choices for a cell

To make data entry easier, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

How To:

Create a list (best to use a separate tab which you can hide later) and name that list by selecting it, then Right Click -> Name a Range. Then, select the cell(s) you want to create this drop down list, go to the Data ribbon, in the Data Tools group, click Data Validation. Then, Settings -> Allow -> List and enter the equals sign and the name that you defined for your list in the Source box. Make sure that the In-cell dropdown check box is selected. (Note – you can also choose how Excel will handle situations when users try to input a value that isn’t in the drop down box.

The SEM Excel Skills Test – Answer Key

1. Ctrl + V 2. Print Preview 3. Paintbrush tool 4. Concatenate 5. Ctrl + A, Click the top left button of the sheet 6. True 7. Paste Values 8. Freeze panes (also acceptable, splitting panes) 9. Vlookup 10. Format Cells 11. True 12. Ctrl + ; (also acceptable “=today()”) 13. Double click the line between two column headers 14. False (you have to set that option) 15. Change F5 to $F$5 (also acceptable, name F5 something and use that in place of “F5″ in the formula) 16. Conditional Formatting 17. False 18. Filter 19. Sort 20. True

Thanks for reading. No more on Excel for now…