One question I get asked a lot is this: what Excel skills are most important for marketers to learn?

I have whittled that interminable list down to five skills that I believe are absolutely essential for marketers to know, presented below in order of importance.

Table Formatting

I believe that table formatting is the baseline skill that every marketer needs to know how to use, and I wrote a post here on Search Engine Land on how to use table formatting with some discussion on why it’s so helpful for marketers.

It seems so basic, yet I rarely see marketers taking advantage of this wonderful tool Excel provides. You can also check this resource on the Microsoft site for Excel 2010 for PC and 2011 for Mac.

Charting

Far too often, I see marketing data presented to key stakeholders in tables when they should be presented visually. Even with large datasets that don’t chart easily, it is possible to use conditional formatting to at least add some visual cues to tabular data. But, if you’re not dealing with an unwieldy dataset, you should look for every opportunity possible to chart out your data.

The Microsoft site offers an introductory course in how to create basic charts in Excel 2010 for PC and 2011 for Mac. Once you have those basics under your belt, check out my 10 tips for making charts sexier. I also high recommend any video you can find by Mike Girvin (AKA ExcelIsFun) or Bill Jelen (AKA MrExcel). Mike did a great overview video of all the different Excel chart types and how to create them.

Pivot Tables

Oh man, you don’t even want to engage me in a conversation about pivot tables. Regular tables are awesome — I don’t even touch a dataset without formatting it as a table. However, if you want to be able to evaluate large datasets (which we, as marketers, are inundated with daily), you absolutely must learn to master pivot tables.

If you’ve never created a pivot table or are scared to death of them, I recommend starting with the video walk-through I did on my blog. I basically take you by the hand and explain the basics for both Mac and PC in under 13 minutes.

If you want to download the Excel file I used in the video, you can find it on the post page.

Once you wrap your mind around that, I provide more details in a post I wrote here on pivot tables, which also includes an Excel download. Mike Girvin also provides a great video with 15 examples of pivot tables. His videos are always PC-only and swing much more financial than marketing, but his explanations are sublime and easy to translate into a marketing context.

Functions

Okay, deep breath….

I would say this is the skill that is, by far, the most difficult to truly master. And there are pretty slim pickins in terms of resources out there for marketers.

That said, to get things started, Distilled pulled together a great resource of essential functions for marketers. I would recommend that as a good jumping off point. Microsoft also has a resource that breaks down Excel functions by categories.

I wrote a post here on how to write complex Excel formulas. As someone who was acutely intimidated by compound formulas for years, this approach is the only thing that’s saved me. And, it makes me look way smarter than I actually am, just because you end up with these really impressive formulas while breaking them down into tiny, baby steps.

I seriously had fleeting reservations over writing that post, because it really strips the mystique out of these long formulas that take people’s breath away and make you seem legendary. It’s kind of like that moment in The Wizard of Oz where the curtain is pulled back and you see that the wizard isn’t quite the big deal you imagined throughout the movie (in spite of the really bad sound effects). But, it’s a sacrifice worth making to give marketers the skills they need to tackle tough data challenges.

Advanced Filters

One of my biggest complaints about Excel was its seeming oversight in not allowing for regular expressions (aka regex) in its filters. It especially frustrated me that I could use regex in Word (learn how here) but not Excel. Double guh err.

However, all of my angst was abated when I discovered the power of Excel’s advanced filters! I never thought I’d see the day when I said this, but they’re actually more powerful than regex. The reason for this is that you can actually filter your data using formulas in addition to text-based filters. And, they’re much easier to learn how to use than regex.

That said, there’s still some level of intimidation when you first start out, so I wrote a post here on how to use advanced filters with tons of practical and marketing-oriented examples. I still have to refer to my own blog post sometimes when I hit a brick wall — usually because of a silly mistake.

Advanced filters are a true godsend with large datasets. Before I discovered them, I used to create pivot tables using all the data from a dataset and just filter the pivot table. The problem with that is pivot tables are processor intensive, and if you have multiple pivot tables in a workbook, it can really slow Excel down to a sludge. Plus, pivot tables and formatted tables give you very limited filtering ability.

Now I spawn new, smaller datasets with my filters applied (easy to do with advanced filters) and create pivot tables from the already-filtered data. Doing this makes your pivot tables so much faster!

Although mastery of these skills will take some time, even just familiarizing yourself with these basics will go a long way toward helping you make sense of the data you have to analyze.

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

Related Topics: Channel: Analytics | How To: Analytics | Search & Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.

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.
  • http://www.asktimothynorton.com/ Timothy Norton

    Great article! Most people don’t know the powerful capabilities of Excel. It actually has the same capabilities as other software which sells for thousands of dollars. Thanks for sharing these powerful, practical tips.

  • Britt Mccrimmon

    Great post. This one is going in the Google Bookmark for later!

  • Dean Marsden

    Before I started working in Digital Marketing I was a complete Excel newbie and was in fact very scared of it. I’ve re shared this post to let others know the extent to which Excel can help with our work. I agree its very important for reporting and analysis. I have to admit though, I’m still a little bit scared of this beast.

  • http://twitter.com/sammmer Sam Mazaheri

    Good stuff! I started using table formatting after one of your posts last year, but do you find that it slows down your formulas? Each cell gets a crazy name instead of the simple A1 name.

  • Bill Bean

    Not developing expertise in Excel much earlier life is one of my main professional regrets. Annie, you might yet convince me to change rectify this.

  • adam 2290

    Good stuff! I’m always looking to expand my base of knowledge with Excel (not my favorite program).

  • http://www.annielytics.com/ Annie Cushing

    You’re so right. I think most marketers use only a tiny fraction of its capacity.

  • http://www.annielytics.com/ Annie Cushing

    Thanks!

  • http://www.annielytics.com/ Annie Cushing

    You and me both, Dean! Mastery is impossible but a goal I work toward regardless.

  • http://www.annielytics.com/ Annie Cushing

    It doesn’t slow them down at all, but yes, structured referencing takes some getting used to. If you stop and look at it, it’s actually pretty intuitive.

  • http://www.annielytics.com/ Annie Cushing

    You can do it, Bill! Every new skill you learn pays off in spades.

  • http://www.annielytics.com/ Annie Cushing

    You are not alone in that sentiment. I used to hate Excel, but it was because it made no sense to me. Now I adore it.

  • Colin Guidi

    Vlookups are my life bread.

  • http://www.annielytics.com/ Annie Cushing

    Right?!? I have to marry my data with them all the time.

  • http://twitter.com/sammmer Sam Mazaheri

    Good to hear, I’ll push through, thanks!

  • localsearchguy

    Great tips. This stuff should seriously be a prerequisite to graduating high school.

    For those interested in taking Excel to the brink (web scraping, web api requests) check out this stuff.

    For the beginner to intermediate.
    http://nielsbosma.se/projects/seotools/

    For intermediate to advanced.
    https://github.com/timhall/Excel-REST

    Too intimidated by this stuff… check out this blog to refine your skills.
    http://chandoo.org/wp/

  • andrekibbe

    Anyone know a good vlookup tutorial? The ones I’ve read and watched make my eyes glaze over.

  • http://www.workwithclintbutler.com/ Clint A. Butler

    I’ll be the first to admit that Excel has always intimidated me when it
    comes to using it to it full potentiall. But clearly that is something I
    need to change. There has got to be a good resource to learn the more basic and advanced features of the software.

  • http://www.facebook.com/profile.php?id=550571817 Wayne Stephenson

    Ironically I would use Excel every day in my past life as a mechanical engineer. I would use all sorts of formulas and formatting functions to report to the directors. Shame I can’t remember a thing about it and now have bigger data sets than ever. Anyone have a good SERP template they wouldn’t mind sharing ;)

  • andrekibbe

    Look very thorough. Thanks!

  • http://www.annielytics.com/ Annie Cushing

    I just use the Remove Duplicates utility under the Data tab to remove duplicates. Or the “Unique Values Only” option if I’m using advanced filters.

  • http://www.annielytics.com/ Annie Cushing

    Fantastic resources! I’d love to see a blog post on how to use Tim Hall’s REST client.

  • http://www.annielytics.com/ Annie Cushing

    By the time you get to the end of this video, you will have an advanced understanding of how vlookups work: https://www.youtube.com/watch?v=-hJxIMBbmZY. It’s off-the-charts awesome.

  • http://www.annielytics.com/ Annie Cushing

    I used to be so intimidated by Excel, and my lack of comfort with it almost caused me to fail an analytics certification course I was taking. (That near brush with failure ended up becoming the impetus for my obsession with the program.) But I started with watching lynda.com tutorials. I think I paid $25/mo for maybe three months and consumed a lot of videos in that time. From there, I read books that focused on the areas I wanted to learn specifically – like charting, pivot tables, formulas, etc.

  • http://www.annielytics.com/ Annie Cushing

    I use this gdoc for SERP scraping: http://bit.ly/1anrz8R. Make a copy to save off your own copy and scrape away. Not sure this is what you had in mind, but it’s my fave scraper.

  • andrekibbe

    It was. Thanks for the link, and thanks for the article!

  • http://www.annielytics.com/ Annie Cushing

    You’re welcome!

  • Johny Woller Skovdal

    Nice article, and great resources to go for further reading.
    One of the most useful features I discovered recently was Array Formulas. Really helped me out in crunching data a lot quicker than before. :) http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

  • http://www.annielytics.com/ Annie Cushing

    Oh? I only use them every once in a while. Can you give some examples of where you fit them into your workflow? You’ve piqued my curiosity.

  • SingleTrackGirl

    you’re my hero.

  • http://www.annielytics.com/ Annie Cushing

    That was easy. :)

  • SingleTrackGirl

    ;) now my work begins (learning). very excited. thank you.

  • http://www.annielytics.com/ Annie Cushing

    My pleasure! Run like the wind. :)

  • http://www.clinicaferrusbratos.com/ marconi88
 

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