Effective Link Building Service: In The Trenches: How To: Excel At Excel For SEM Applications, Part 4

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

**by Josh Dreller**

I hope the previous post of this series (Part 1
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-1-19840>
, Part 2
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-2-20453>
Part 3
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-3-21435>
) 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
documentsespecially 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/283 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 <http://www.techonthenet.com/excel/macros/delete_hl.php>
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
<http://www.cogniview.com/convert-pdf-to-excel/post/free-excel-cheat-sheet/>
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&

Opinions expressed in the article are those of the author, and not
necessarily Search Engine Land.

Josh Dreller <http://searchengineland.com/author/josh-dreller/>

is the Director of Media Technology for Fuor Digital
<http://www.fuor.net>
, an agency concentrated in the research, planning, buying and
stewardship of digital media marketing campaigns. Josh can be reached
at jdreller@fuor.net <mailto:jdreller@fuor.net>
.

<div><a href="http://www.superbhosting.net/"><img
src="http://www.searchmarketingnow.com/_images/superb.gif"
border="0"></a></div>
You are receiving this email because you subscribed to a Search Engine
Land, Search Marketing Now or Search Marketing Expo newsletter. To update
your subscriptions or unsubscribe, use your newsletter preferences page at
http://third.thirddoormedia.com/lists/?p=preferences&id=2&uid=f9779113b77d03598707df46ae032f9b.
Or send your written request to: 279 Newtown Tpke., Redding, CT
06896</font>

Effective Link Building Service

Friday, July 10, 2009

In The Trenches: How To: Excel At Excel For SEM Applications, Part 4

0 comments:

Post a Comment

Followers

Blog Archive

About Me