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

In part 1 of this series on Excel for search engine marketers, I started off by applauding the tool and pointing out how important it is to our industry. This week, before I get into the tips and tricks, I’d like to talk a little about training. Excel, like any sophisticated tool, can be dangerous […]

Chat with SearchBot

In part 1 of this series on Excel for search engine marketers, I started off by applauding the tool and pointing out how important it is to our industry. This week, before I get into the tips and tricks, I’d like to talk a little about training.

Excel, like any sophisticated tool, can be dangerous in the hands of an inexperienced user. As proficient as I am today, I remember how intimidating it can be to the novice or even advanced user. In this industry, we often have the more junior level members pulling reports and scrubbing data. As they have the least amount of Excel training, their mistakes can be compounded when analysts try to optimize or report using faulty data. One good rule of thumb is to never assume a new team member’s excel proficiency. Run them through some testing to make sure they can handle the workload properly.

Sometimes I think that Excel is one piece of software that gets the least amount of real training in relation to just how often it is used. We put on Excel trainings internally every six months or so, and we’re always passing each other new little nuggets of treasure we discover about the software. I suggest at every search marketing firm or department that there is at least one team member who has expert level usage and can provide continuous in-house support and lead the charge for training.

I’ve met a lot of people in my life, and I have yet to meet someone I feel has completely mastered Excel. There’s always something new to learn or feature to explore. I hope some of these tips serve you well.

Select only visible cells

This is one of my favorite excel tricks to pass along as it seems that few Excel users, let alone SEM Excel users, seem to know this one. Have you ever filtered a table and then tried to copy and paste it only to find out that it has copied the hidden the cells as well? By default, Excel copies hidden or filtered cells in addition to visible cells. The key is to select just the visible cells before you click Copy.

HOW TO: Select the cells. Go to the HOME ribbon, FIND & SELECT -> GO TO SPECIAL -> VISIBLE CELLS ONLY. Then copy and paste as you would normally.

HOW TO #2: Another way to have a “Select Visible” button in Excel is to click the Microsoft logo and choose EXCEL OPTIONS down at the bottom right of the dialogue box. Then (see below), chose CUSTOMIZE -> ALL COMMANDS (top drop down) -> Select Visible Cells and then ADD>> to the right side. Click OK and now you have that “black flag with white cross” on it symbol. This way whenever you select cells and then click that button, you’ll only copy the visible cells.
select visible cells

Macros MS Excel definition – An action or a set of actions that you can use to automate tasks.

For those of you who haven’t dabbled into the marvelous world of macros, you’re about to realize you’ve spent hours and hours wasting your time when you could have programmed excel to do it for you. The beautiful part here is that you don’t have to know any programming to build basic macros. You can literally press record, do one of your tasks as normal, and then assign that task to a keyboard shortcut. You can even have your own “group” of macros that you can use whenever you open Excel.

It’s hard to tell you what to use macros for because they are so versatile. Basically, any repetitive task you have that requires multiple actions to perform is a perfect candidate to create a macro. I remember in once instance, I had three very long formulas I was pasting into cells. I started by keeping them in a notepad file and copying and pasting whenever they were needed. However, I realized quickly that I should just record them and assign them to shortcuts. A keyboard shortcut takes less than a second. However, selecting the doc, highlighting the formula, copying, moving to excel, finding the right cell, and clicking paste takes ten seconds. Imagine if you had a thousand to do? You would save hours…

HOW TO: You’ll need to access the DEVELOPER ribbon in Excel. If the Developer tab is not available, do the following to display it:
Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

From the Developer ribbon, in the Code group, click Record Macro.
In the Macro name box, enter a name for the macro (it can’t have spaces or funky characters). Follow the directions for shortcuts and saving. When you’re ready, click OK to start recording.
Perform the actions that you want to record and click the square STOP button on the mini floating toolbar (see below)
excel macro

NOTE – Macros can only be used on “macro enabled workbooks.” It’s not a big deal, you just will have to save it in that format. Click SAVE and choose the option from the drop down menu under the file name.

NOTE #2 – Macros can be run from a keyboard shortcut or the DEVELOPER ribbon, MACRO option. You can set any shape or image to run a macro when clicked. Just right click the shape/image and choose ASSIGN MACRO.

NOTE #3 – Some email systems won’t accept macro enabled workbooks as they’re incorrectly identified in virus scans. Just know that may happen if you try to email them. It happens infrequently, but it happens.

Create a Table of Contents – Got a huge workbook? Wouldn’t it be nice to have a worksheet with links to each tab? I found a script online a few years ago that does this vary thing. You create the macro, click RUN, and it generates a Table of Contents with the name of each tab and hyperlinked to it. It’s a great little, free script that I’ve shared many times.

The code is here which was submitted by Zack Barresse. Thanks, Zack, that’s come in handy.

HOW TO: Not all macros are recorded. Some are built inside Excel’s Visual Basic Editor. I’m not a programmer, but I’ve learned to go in, open a editing box, drop in some code I’ve found around the net, and then click save. It’s not too difficult. Go to the link above and copy the script.

On the Developer tab, in the Code group, click Visual Basic.

If needed, in the Visual Basic Editor, on the Insert menu, click Module. In the code window of the module, paste the macro code that you want to use. In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel when you finish writing the macro. When you want to run it, choose MACROS from the DEVELOPER ribbon. Highlight the macro and click RUN.

Set Print Area

MS Excel definition – One or more ranges of cells that you designate to print when you don’t want to print the entire worksheet. If a worksheet includes a print area, only the print area is printed.)

This is one of those simple and quick things that can be done in Excel but I often find never utilized. If you have a lot of data, charts, graphs, shapes, etc on a worksheet and you just need to print a single portion, there’s no need to print the entire thing. Just set the print area on that worksheet and it will only print that portion until you reset it (which you can do from the same ribbon item).

HOW TO: On the worksheet, select the cells that you want to define as the print area.
On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.

select print area

NOTE – The print area that you set is saved when you save the workbook.

Next week, it’s all about Print Special, one of the most useful sub-menus in Excel, and the dreaded vlookup function.


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Josh Dreller
Contributor
Josh Dreller has been a search marketer since 2003 with a focus on SEM technology. As a media technologist fluent in the use of leading industry systems, Josh stays abreast of cutting edge digital marketing and measurement tools to maximize the effect of digital media on business goals. He has a deep passion to monitor the constantly evolving intersection between marketing and technology. Josh is currently the Director of Content Marketing at Kenshoo.

Get the must-read newsletter for search marketers.