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.
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.
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.
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.
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.
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.