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

I hope the previous post of this series (Part 1, Part 2, Part 3) have served you well. My goal was never to create an Excel manual for everyone, but rather to highlight specific tips and tricks to help out the search engine marketer who is toiling away in the tool hours every day.

Even though most SEM pros use Excel, just about everyone uses it differently. I’m sure accountants use it much differently than I do. Either from keyboard shortcuts, ribbon icons, or the right click menu – there’s probably two or three ways to perform any task, but it’s the right combination that can turn an average Exceler into a power user.

Most of the time, I find my Excel knowledge grows out of need. I’ll be in the middle of a task and think to myself, “There has to be a better way to do this.” I remember the first time I learned how to use the Paintbrush tool to copy formatting from one cell to another. That feature alone has probably saved me days of work throughout my career.

Here are some general efficiency suggestions when working with Excel:

• Use keyboard shortcuts. They are a huge time saver. At the end of this post is a printable cheat sheet of the best ones.
• Constantly save your work. This is a good idea for working with any software, but Excel can sometimes freeze up with larger documents–especially if you’re using an older computer or working on many spreadsheets at the same time.
• Freeze panes. Use the Freeze Panes feature to keep important column or row headers on the screen when you scroll down or right.
• Hide anything you don’t need. If you’re just working on a few columns, go head and hide the rest of the data. It keeps the clutter down and you can work more streamlined this way.
• Format your document at the end. Don’t waste time stopping in the middle of a spreadsheet to get your fonts and colors se. You may end up changing them later and then you have double the work.
• Use templates when possible. If you are constantly creating the same kind of report on a reoccurring basis, try building a “blank version” of it and saving it as one of your custom templates. That way, instead of starting from scratch, you already have the format ready to go.

This week’s tips:

Named Ranges – MS Excel definition – A name that represents a cell, range of cells, formula, or constant value.

This is really one of the most valuable tips when using Excel. When you name a cell or group of cells, you can then easily use them anywhere in the workbook interchangeably with the actual cell reference(s). So, for example, say you’re working with your February SEM Budget and you’ll be referring to that cell often in the document in manys table. You can name that value FebBudg and make formulas such as “=FebBudg/28” to get the daily amount. Or if you get word that your budget might be going up by 25%, you can simply use “=FebBudg+(FebBudg*.25)” to see what it would be.

It might seem like a hassle in these simple examples, but once you start naming ranges, you’ll see how efficient you can be. Imagine: “=YearBudget-(FebBudg+MarchBudg)” versus having to go and figure out what cells those values are in. In this case, the formula will make more sense. Imagine looking at the same formula but as “=K12-(C3+B4)”…you wouldn’t have any idea from a glance what it is.

Another great feature as you start typing in the named range, Excel will autosuggest the range as you type. In the example above, simply typing “Fe” would bring up “FebBudg” in a drop down menu which you can quickly choose.

HOW TO: Right above columns A & B is the Name Box on the formula bar. All you have to do is select your cell(s) and type in whatever you want into that name box. Just remember it can’t contain spaces or weird characters.

Secondary Axis on Charts

Have you ever seen charts with two data sets and it’s confusing because the verticle axis only shows one set? You can implement a secondary axis on the cart for the second data set.

See the example above. On the left chart, it’s hard to really know the values of the blue line (Dollar). On the right chart, now that the secondary axis has been enabled, the values are easy to see.

HOW TO: Select the data on the chart you want to plot and you’ll now see the Chart Tools tabs at the top of Excel. On the Format tab, in the Current Selection group, click Format Selection. Then, on the Series Options tab, under Plot Series On, click Secondary Axis and then click Close.

Here are some more quickie tips:

Tab color

You can change the color of tabs by simply right clicking the tab and then choosing TAB COLOR. This is helpful when you have a lot of tabs and want to organize them.

Go to the next line in the same cell

One of my favorite shortcuts that I find many people don’t know is Alt+Enter inside a cell. This lets you “go to the next line” inside a cell. It’s very useful when making text notes in an Excel doc so that you don’t have to use up many rows for one paragraph.

Print Column/Row Headers on every page

Ever print a multipage Excel doc and you don’t know what Column F is on page 4? You can make sure those row or header columns are reprinted on every sheet by choosing PRINT under HEADINGS on the PAGE LAYOUT ribbon.

Countdays between dates

This is such a common task for search engine marketers. Whether you need to calculate Daily Budget or Clicks per Day, Excel has an easy formula to count the days between two dates. Just put in the date (including year in case of leap days) and in a separate cell just simple subtract the later date from the earlier date.

Turn off annoying hyperlinks

When you enter a Web address into an Excel cell, it automatically converts to a hyperlink when you leave the cell. This can be extremely annoying as its very common to accidently click these links which immediately pops open your browser and interrupts your work.

To turn off auto-hyperlinking, click the MS Logo at the top left, Click EXCEL OPTIONS and go to PROOFING. Uncheck the Internet and Network Paths With Hyperlinks check box.

Also, to remove all hyperlinks already on a worksheet, see these instructions to create a simple macro.

Free Excel cheat sheet: keyboard shortcuts

Keyboard Shortcuts are absolutely crucial to being proficient and efficient with Microsoft Excel. I really can’t urge all of you strong enough to master as many shortcuts as you can. From flipping through worksheets to inserting columns or even spell checking, the seconds gained from using shortcuts not only adds up to hours over the course of the year, but also doesn’t wear you out on things you can be doing in half the time.

When using any Microsoft Office product such as Excel, you should have one hand on the keyboard’s left side and one hand on the mouse (see above). The reason why the left side is so important is that by using your pinky on CTRL, your other fingers can reach about 75% of the most important keyboard shortcuts you will use all of the time. For example, CTRL + Z is undo, CTRL + X is Cut, CTRL + B is Bold, CTRL + A is Select All, etc. This is not only important for Excel, but for Word, PowerPoint, Outlook, etc. Master the left keyboard zone and you will be flying through every Office product like a pro.

I was going to put together an Excel keyboard shortcut cheat sheet, but quick search on Google found several great ones including this one from Cogniview that is ready to be printed and cut out for your cubicle.

It has all of the basics from Opening a New File, Saivng a File, etc, but here are some ones I think are extra-special that I’d like to share:

• Next Sheet – Ctrl+PageDown
• Previous Sheet – Ctrl+PageUp
• Goto – Ctrl+G
• Delete Column or Row – Ctrl+K
• Insert Today’s Date – Ctrl+SemiColon
• Insert a New Worksheet – SHIFT+F11
• Extend Selection to the Next Blank Cell– Shift+Ctrl+Arrow(any direction). You want to select all the cells of a table? Go the to top left cell and use this shortcut right and then down.
• Quick AutoSum– in the cell below a group values use Alt+Equals Sign
• Edit Data in a Cell – You an use the formula bar, but F2 is faster…

Next time, Pivot Tables for Search Engine Marketers…