Excelling (Again) At Excel For Search Engine Marketers

On Thursday, March 13th, I will be presenting on the Extreme Excel Excellence panel at SMX West with John Gagnon and Brett Snyder (moderated by Chris Sherman). In the session, we will be delivering power-user tips for marketers using Excel, and Chris has raised the bar very high for us to deliver truly advanced-to expert-level hacks on what has become the most commonly used software platform in the digital marketing industry.

In my time here at Search Engine Land (this will be my 148th post), I’ve written about best practices, innovative technology and the business of paid search. However, the series of articles from which I have by far received the most reader feedback have been the pieces I wrote in 2009 regarding Excel for SEM Applications:

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
  • Part 6 – Recap of series, Excel user levels, The Online Marketer’s Excel Skills Test

Check out those previous columns for some great Excel tips, especially Part 5 on Pivot Tables which seemed to be the most popular post. Below are some more quick Excel tips for you:

Persistent Format Painter

Most people know the Format Painter tool. You can highlight any text, click that button and then “paint” the formatting of that text on to other text. By default, after you paint something, the button resets so you can continue working and not have to turn it off. But, what if you have more text to paint? It can be a pain to keep having to highlight the source text if you are painting multiple selections.

You may not know (and thanks to Sara Rennich for this one) that you can double click the Format Painter so it stays “pushed down” so that you can paint as much as you want without having to keep selecting and clicking. When you’re done, click the button once, and it will turn off.

image001

Get A List Of Pivot Table Formulas

Pivot Tables are one of the most important features of Excel for search engine marketers. You can slice and dice the data in order to find those great little nuggets of insight you need. Many times, you’ll need to create calculated fields in order to display ratio metrics such as cost-per-click (cost/clicks), click-through rate (Clicks/Impressions), etc.

However, sometimes you have so many calculated fields that you might forget (or want to check) what they are. On the Pivot Table -> Options ribbon, click Fields, Items, & Sets and then select List Formulas. Excel will automatically create a new sheet in your workbook and list each Calculated Field and its formula. (Thanks to Laura Ruszkowski for this one!)

image002

Improve Your Pivot Tables With Slicers

You now have the option to use slicers to filter pivot table data. These controls allow for quick filtering and have the added benefit of also showing what filters are currently active. This makes it easy to understand exactly what data is being displayed.

I find slicers are really valuable when sharing pivot tables with others so that they can easily take control of the data without worrying about accidentally changing anything that could mess up the report.

image004

When You Don’t Need A Big Chart, Use Sparklines

Sparklines are very small charts without axes, coordinates, or legends. They’re meant to provide a brief visualization of simple data when you don’t need a fully blown-out chart. I find that they’re very helpful at the end of rows or columns to quickly compare data over a range. For example, if you are looking at a month-over-month table, you can drop some Sparklines in to visually see the spikes and dips of that metric throughout the year.

Sparklines can be found in the Insert ribbon and you can choose from either Line, Chart, or Win/Loss types. You choose the range of data and then which cell or cells you want to embed the sparkline. Although tiny, they’re very powerful and Excel provides some pretty cool customizable options in the Sparkline Tools menu if you select one.

image005

Keep Your Eyes On The Prize With Watch Windows

Watch Windows let you select cells to add to a small window that will stay on top of your screen while you are in Excel, even if you switch between sheets in a workbook. This way you can monitor those cells from one spot without having to keep flipping between sheets while you work.

Watch Windows can be very helpful when you’re working in large workbooks with charts that are linked together with cells that change when new values are added to other cells. For example, you may be working on next year’s budget and want to keep an eye on total spend for your campaigns as you add and remove money from various aspects of your search program.

image006

Goal Seek

Found in the Data ribbon under the What if Analysis menu, Goal Seek can be a very helpful tool in situations where you know the result that you want from a formula, but you are not sure what input value the formula requires to get that result. That may sound a bit confusing, but let me illustrate it with an example.

Let’s say you want to reach $1,000,000 in revenue. You want to figure out how much that’s going to cost you. You know your average CPC is $0.43, your average conversion rate is 1.87%, and your average order value is $96.22. For purposes of this example, it may seem like a relatively easy calculation to do manually, but just imagine if this problem was much more complex with much bigger numbers and even more fields to consider.

After filling in my table with the known data and setting up the formulas properly, you get to this point:

image009

By using Goal Seek, you can specify that the million dollars in revenue is the goal with the number of clicks being the linch pin to figuring out how much it would cost to hit that revenue goal.

image010

Goal Seek figures out that you would need 555,767 clicks to hit that revenue with a spend of $238,979.97.

image011

You can do some really cool stuff with Goal Seek and it’s a function that many power Excel users have never even heard of.

Power Pivot

I’m sure many of you have experienced the dreaded “Excel freeze” when trying to manipulate big tables in Excel. As of 2012, Excel now offers Power Pivot to not only help solve this common problem, but also to provide deeper business intelligence (BI) capabilities that turn the application into a very powerful analytics tool.

Power Pivot also allows you to import millions of rows of data from multiple data sources into a single Excel workbook. The real advantage is the speed. Because the data tables live outside of Excel, filtering and pivoting big files feels like you’re working with a twenty-row table.

You do have to install Power Pivot but the implementation doesn’t take much time nor is it very complicated. Once you have it going, you will also benefit from Data Analysis Expressions (DAX), a new formula language that really shows off the power in Power Pivots. It enables more sophisticated and complex grouping, calculation, and analysis and the good thing is that it is very similar to that of Excel formulas.

If you really want to unlock the full processing power of Excel, you have to use Power Pivot.

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

Related Topics: Channel: Analytics | How To | How To: Analytics | Paid Search Column

Sponsored


About The Author: 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. You can follow him on Twitter at @mediatechguy.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.

Comments are closed.

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide