Keyboard shortcuts are the best way to navigate cells or enter formulas more quickly. We’ve listed our favorites below. Control-Down/Up Arrow = Moves to the top or bottom cell of the current column Control-Left/Right Arrow = Moves to the cell furthest left or right in the current row Control-Shift-Down/Up Arrow = Selects all the cells above or below the current cell Shift-F11 = Creates a new blank worksheet within your workbook F2 = opens the cell for editing in the formula bar Control-Home = Navigates to cell A1 Control-End = Navigates to the last cell that contains data Alt-= = Autosums the cells above the current cell
Excel is arguably one of the best programs ever made, and it has
remained the gold standard for nearly all businesses worldwide. But
whether you’re a newbie or a power user, there's always something left
to learn. Or do you think you've seen it all and done it all? Let us
know what we've missed in the comments.
There are two kinds of Microsoft Excel
users in the world: Those who make neat little tables, and those who
amaze their colleagues with sophisticated charts, data analysis, and
seemingly magical formula and macro tricks. You, obviously, are one of
the latter—or are you? Check our list of 11 essential Excel skills to
prove it—or discreetly pick up any you might have missed.
Vlookup
Vlookup is the power tool every Excel user should know. It helps you
herd data that's scattered across different sheets and workbooks and
bring those sheets into a central location to create reports and
summaries.
Vlookup helps you find information in large data tables such as inventory lists.
Say you work with products in a retail store. Each product typically
has a unique inventory number. You can use that as your reference point
for Vlookups. The Vlookup formula matches that ID to the corresponding
ID in another sheet, so you can pull information like an item
description, price, inventory levels, and other data points into your
current workbook.
Summon the Vlookup formula in the formula menu and enter the cell
that contains your reference number. Then enter the range of cells in
the sheet or workbook from which you need to pull data, the column
number for the data point you’re looking for, and either “True” (if you
want the closest reference match) or “False” (if you require an exact
match).
Creating charts
To create a chart, enter data into Excel with column headers, then select Insert > Chart > Chart Type. Excel 2013 even includes a Recommended Charts
section with layouts based on the type of data you’re working with.
Once the generic version of that chart is created, go to the Chart Tools menus to customize it. Don't be afraid to play around in here—there are a surprising number of options.
Excel 2013 includes Recommended Charts with layouts based on the type of data you're working with.
IF formulas
IF and IFERROR are the two most useful IF formulas in Excel. The IF
formula lets you use conditional formulas that calculate one way when a
certain thing is true, and another way when false. For example, you can
identify students who scored 80 points or higher by having the cell
report “Pass” if the score in column C is above 80, and “Fail” if it’s
79 or below.
IF formulas let you pull in just the data you need.
IFERROR is a variant of the IF Formula. It lets you return a certain
value (or a blank value) if the formula you’re trying to use returns an
error. If you’re doing a Vlookup to another sheet or table, for example,
the IFERROR formula can render the field blank if the reference is not
found.
PivotTables
PivotTables are essentially summary tables that let you count,
average, sum, and perform other calculations according to the reference
points you enter. Excel 2013 added Recommended PivotTables, making it even easier to create a table that displays the data you need.
To create a PivotTable manually, ensure your data is titled appropriately, then go to Insert > PivotTable
and select your data range. The top half of the right-hand-side bar
that appears has all your available fields, and the bottom half is the
area you use to generate the table.
PivotTables are a summarization tool that let you perform calculations according to the reference points you enter.
For example, to count the number of passes and fails, put your Pass/Fail column into the Row Labels tab, then again into the Values
section of your PivotTable. It will usually default to the correct
summary type (count, in this case), but you can choose among many other
functions in the Values dropdown box. You can also create subtables that summarize data by category—for example, Pass/Fail numbers by gender.
PivotChart
Part PivotTable, part traditional Excel chart, a PivotChart lets you
quickly and easily look at complex data sets in an easy-to-digest way.
PivotCharts have many of the same functions as traditional charts, with
data series, categories, and the like, but they add interactive filters
so you can browse through data subsets.
PivotCharts help you easily digest complex data.
Excel 2013 added Recommended PivotCharts, which can be found under the Recommended Charts icon in the Charts area of the Insert
tab. You can preview a chart by hovering your mouse over that option.
You can also manually create a PivotChart by selecting the PivotChart icon on the Insert tab..
Flash Fill
Easily the best new feature
in Excel 2013, Flash Fill solves one of the most frustrating problems
of Excel: pulling needed pieces of information from a concatenated cell.
When you’re working in a column with names in “Last, First” format, for
example, you historically had to either type everything out manually or
create an often-complicated workaround.
Flash Fill can automtically add data formatted the way you want without using formulas.
In Excel 2013, you can now just type the first name of the first
person in a field immediately next to the one you’re working on, and
click Home > Fill > Flash Fill, and Excel will automagically extract the first name from the remaining people in your table.
Quick Analysis
Excel 2013’s new Quick Analysis tool minimizes the time needed to
create charts based on simple data sets. Once you have your data
selected, an icon appears in the bottom right hand corner that, when
clicked, brings up the Quick Analysis menu.
Quick Analysis speeds the process of working with simple data sets.
This menu provides tools like Formatting, Charts, Totals, Tables, and Sparklines. Hovering your mouse over each one generates a live preview.
Power View
Power View is an interactive data exploration and visualization tool
that can pull and analyze large quantities of data from external data
files. Go to Insert > Reports in Excel 2013.
Power View creates interactive, presentation-ready reports.
Reports created with Power View are presentation-ready with reading
and full-screen presentation modes. You can even export an interactive
version into PowerPoint. Several tutorials on Microsoft’s site will help you become an expert in no time.
Conditional Formatting
For most tables, Excel’s extensive conditional formatting
functionality lets you easily identify data points of interest. Find
this feature on the Home tab in the taskbar. Select the range of cells you want to format, then click the Conditional Formatting dropdown. The features you’ll use most often are in the Highlight Cells Rules submenu.
Conditional Formatting lets you easily highlight data points of interest.
For example, say you’re scoring tests for your students and want to
highlight in red those whose scores dropped significantly. Using the Less Than conditional format, you can format cells that are less than -20 (a 20-point drop) with the Red Text or Light Red Fill with Dark Red Text
function. You can create many different kinds of rules, with unlimited
formats available via the custom format function within each item.
Transposing columns into rows (and vice versa)
Sometimes you’ll be working with data formatted in columns and you
really need it to be in rows (or the other way around). Simply copy the
row or column you’d like to transpose, right click on the destination
cell and select Paste Special. A checkbox on the bottom of the resulting popup window is labeled Transpose. Check the box and click OK. Excel will do the rest.
The Paste Special feature transposes columns and rows.
As far back as I can remember, I've been in sales. My dad had our
family working flea markets selling stuff when I was probably 5 years
old.
While we were selling all kinds of stuff, it seemed that my dad
always had a story about everything. He knew something about everything
we sold. It is that knowledge that separates most "salespeople" from
successful sales people.
In SEO, it's no different. In order to be successful, you must be
able to consultatively sell. You must be able to wrap your head around
the really important "pain points" and goals of a prospect, and speak
toward that.
On the flip side, it's very challenging to sell to someone who doesn't have a clue about what SEO really is.
Here are a few tips that you might use to help cut through the techno-babble, relate to a prospect, and close some business.
Tip 1: Listen, With Very Big Ears
I
was told once that "the person who speaks the most, loses". If you're
one of those guys who tries to out-talk the next guy, you probably won't
find a lot of success.
You must get a prospect to share with you their experiences:
What's important in their business (how do they make money)?
How are they currently staffed?
What marketing efforts currently exist?
Do they measure their marketing efforts to an effective cost-per-lead/cost-per-sale?
Then, you can start to ask some SEO questions such as:
When did they last redesign their site?
Are they happy with current conversion rates?
Have they ever hired an SEO firm before and, if so, what did they do and why aren't they still working with them?
Often times, there is so much information that needs to be gathered
that I will ask if the prospect has developed an RFP (request for
proposal) for the effort, so that the scope can be defined.
Tip 2: Understand Their Business Deeply
Recently, I met with a prospect that spends $19 million per year on
marketing, but not a penny on SEO. Then they proceeded to tell me that
organic search is their second most important lead generating source,
behind direct traffic to the website.
After gaining access to their Google Analytics, I was able to see
that they had lost significant traffic, year over year. Part of this was
due to the fact that they redesigned their website (wasn't responsive)
and had to address this "quickly", so no thought was put into SEO for
the newly designed website.
I can clearly see why/where they lost traffic, and I "know" we can
get it back. I also know that their conversion rate for organic traffic
typically is 3 percent. In our meeting, I was able to get a value that
they placed on new leads ($92), and so now I am armed to help to project
what I believe the output of an SEO effort can be.
Do some math:
I believe that we're going to be able to recover about 6,500 visitors
per month from the "recovery plan" mentioned earlier. I can also see
some fundamental elements preventing a presence across a good number of
other relevant/highly searched for terms. Put it all together and I have
at least an 80 percent confidence level that we can achieve 8,000
quality visits per month for this particular prospect.
8,000 x 3% conversion rate = 240 leads
240 x $92 CPA goal = $22,080 per month in value.
Tip 3: Speak in Terms They Understand
When you can tell a CEO (or a CMO for that matter) that you feel very
confident in churning out ROI, and you can share with them the numbers
as to why you feel that way, you're making the complicated process of
SEO selection much simpler.
Most clients don't understand "robots.txt" or "404s" or any of that
stuff. They know "money in, money out". They want to speak in terms of
ROI.
You might pull keywords that you know are relevant for their
business, and the search volume, and the client's current ranking, so
that you can "show them" why you feel justified in asking them to invest
$15,000 per month in SEO. Of course, you have to be able to back that
up and actually have a project plan in mind that you feel will equate to this success.
Don't be afraid to ask them if they're getting the same type of ROI
from television, radio, print, direct mail or other channels. This will
get the CEO to thinking, rather than painting SEO as the "black box".
Today's SEO is a very involved effort, requiring hours and hours of
research, analysis, and "real work." I would estimate that this prospect
is spending at least $8 million on television, and I don't even know if
that includes production costs. Yet, for some reason, this isn't a "top
lead generator"?
Starting a new SEO project, or want to check ongoing projects? Use
the following approach, which includes a mix of specific keyword
research, testing important assumptions, and growing traffic and
rankings with link building and copywriting.
Step 1: A Specific Way to do Keyword Research
Brainstorm
Good keyword research starts with good market knowledge. Keyword research can even serve as additional market research.
I always start keyword research with a brainstorm session together
with my customer. During this session we create a map with every topic
and subtopic related to their services. We list a couple of keyword
examples for each subtopic.
It's important to get client-facing people involved in this process,
because companies tend to think in industry specific terminology that
their potential clients would never use as keywords.
Search Volumes (Average Monthly Volume)
Reliable keyword data is scarce and Google only gives you rough
estimates from the AdWords Keyword Planner. Search volume estimates and
their historic trending provide a good enough starting point for
strategic SEO choices. You can always get additional keyword ideas from
paid tools (like SEMrush), but Google is the only real data source for
search volumes and others can only guess.
Start out by creating a huge list of potentially interesting keywords
based on your map. The AdWords Keyword Planner has a "Search for new
keyword and ad group ideas" function in which you can enter groups of
keywords to receive additional related keyword combinations.
Once added to so-called AdWords plans (even if you don't intend to
use them for AdWords), you can get search volume estimates for [exact
match] combinations. Various paid tools have similar functionality, but
the end result should always be the same: A huge list of >500 keyword
combinations with their historic monthly search volumes for the
localities your client services. You can use Excel for further
processing.
Conversion Rates (Conversion Probability)
Adding estimates for conversion rate and value makes the search
volumes a lot more usable for strategic choices. If there is no existing
data to base your assumptions on, a guess on a 1 to 5 scale on "how
relevant is the keyword to our services" is a good start.
You can always tweak this data once you've had more experience with
similar keyword combinations. Having grouped or tagged your keywords
during this first stage makes that a lot easier.
This is the basic first question that a search engine has to deal
with when looking at any web page. What's it about? Search engines want
to know this because it helps them respond to user search queries with
pages that are relevant to that query.
There are many signals that can be used to determine the relevance of a given page. Some of these include:
The title tag of the web page. As you would expect, the title for any document should be a leading indicator its contents.
Semantic analysis of the content. This is not tied
as much as it used to be to specific keyword phrases, but the general
relevance of a page is analyzed based on the words and phrases used.
Anchor text used in links to the page. The text you click on in a link also acts as a label for what you would expect to find once you get to that page.
Topic matter of third party web pages containing links to the page. If lots of pages about automobile topics link to your page selling a used Ford Mustang, that's a good thing.
Topic matter of the site on which the page resides. Your page about selling a used Ford Mustang will do better on a site about used cars then it will on a site about water parks.
How users respond to the content in the search engine results pages (SERPs). If everyone that clicks on a link immediately jumps back to the SERPs and clicks on something else, that could be a bad sign.
There are other potential signals that you can think up without too much trouble, but by now, you should get the idea. Impact of Relevance
You simply can't rank on given search terms if you don't have a page relevant to that search query.
Learn what types of search queries are used by people who are looking
for products or services like yours. Make sure you have pages for each
major variant of these, but don't over do it. You can read more about
how to think about that in 3 Golden Rules of Title Tags.
In addition, make sure the content on those pages in consistent with the title tag and create strong content to support it.
I
was told once that "the person who speaks the most, loses". If you're
one of those guys who tries to out-talk the next guy, you probably won't
find a lot of success.
