5 Excel Skills Every Marketer Should Have

I found parts of this article quite helpful (even though the title is poorly worded: you have skills; you don’t know them).

It covers these five areas:

  1. Table formatting
  2. Charting
  3. Pivot tables
  4. Functions
  5. Advanced filters

If you’re looking to improve your Excel skills (and you should be!), the article is worth your time.

Thoughts on Data and Reporting

Reports cannot answer questions for you, or rather data cannot. Reports exist to show data in a way that is useful. Developing the skills to understand data, what the data shows, and more importantly understanding what the data does not show is a skill that everyone should learn.

What I mean by this is that I want everyone to have a realistic view of what data and reporting is. It is information. It can either be presented in a useful way or not (and hopefully we can get our reports created in a useful way).

Many questions that we (as marketers) pose are about intent not behavior. Data can only describe what is (behavior), not why it is (intent). Careful, thoughtful, thorough analysis of data can produce theories of why (intent) that can then be tested for validity. This is a key important thing about data that needs to be understood. When you are looking for a report to tell you why, you are looking in the wrong place. So rather than asking “what makes a customer do X” ask “what are the traits of customers who are most likely to do X based on our current data”. The best way to get a why answered is to ask our customers (that is why surveys are so useful).

Further, data changes; which means the answer is always changing. What is true today may not be true tomorrow. However, tracking answers over time is useful as it can reveal useful things. This is called trending. When looking at data trends you want to try to spot disruptive changes, identify what caused those (data does not tell you this, analysis does), and use that information as a basis for decisions.

Finally, when it comes to identifying customer funnels, it is best to look at them as an ideal rather than an absolute. That is to say that the analysis of data should show the most likely pathway a customer takes, this is our ideal funnel (it is not ideal in the sense of preferred, but ideal in the sense of what takes place the most often). But there are always going to be exceptions to the funnel; and we may find that our most profitable (preferred) funnel is different than the current funnel. That should prompt change.

Rule the world with Excel: my favorite features & formulas

My favorite Excel features:

F4.  Use the F4 key to repeat the last thing you did (i.e. if you just made a cell green, use F4 to make another cell green. If you just deleted 3 lines, use F4 to delete 3 more lines). If F4 isn’t working on your PC, make sure F-lock is disabled. This feature is a no-go in a Mac.

Format Painter. I hate wasting time on formatting. But thankfully, the format painter button means I only have to format once and then I can easily “copy” that formatting to other cells. I use format painter so often that I like to add it to my “Quick Access Toolbar”

The Quick Access Toolbar in Excel

Customizing the Quick Access Toolbar (only on the PC version). For weeks after I converted to the new ribboned version of Excel, I struggled to find the functions I needed. Thankfully they added the ability to customize the Quick Access Toolbar. There are 2 ways to do this:

  1. Selecting the downward facing arrow on the top bar, selecting “more commands”, then look for the function you want. Here’s a step-by-step visual on how to do this.
  2. Right clicking on the function then selecting “Add to Quick Access Toolbar”

Filtering. I often use Excel to track large amounts of information and filtering allows me to find the information I need.

  • A tip I’ve learned the hard way: If you add a column after turning on filtering, double-check that the filter icon is showing up in the top cell. If it’s not showing up and you sort your spreadsheet, the information in that column won’t sort. To fix, just turn the filtering feature off and then back on.

Find/Replace. Find and replace is often a life-saver. Right now I’m putting together a marketing plan for 2013, but because it’s 2012, every date I enter defaults to 2012 unless I type in the year. By using find/replace I can quickly replace all 2012 dates with 2013. The speeds up my data entry. I like to use the short cut Command (or Control on PC) + F
  • Using an asterisk makes it even more handy. If you type a comma and then an asterisk (,*) in the FIND box and then leave the REPLACE box blank, Excel will delete everything to the right of, and including, the comma. If you type an asterisk and a dash (*-) Excel will delete everything to the left of, and including, the dash. If you want to keep the dash, just type a dash into the REPLACE box.

Freeze Panes. When working with a lot of data, this feature is also incredibly helpful. It allows me to see the top and left cells even if I scroll to the bottom or far right of the worksheet. Learn how to use the freeze pane feature.

Copy/Move sheet. If you want to copy an entire sheet to another workbook (that is currently open) you can move the entire sheet, formatting and all, in a few clicks. Learn how.

A few features I really like, but that I don’t use very often:

Otherfavs

  • Text to column. I use this when I have information in one column that I want to split into multiple columns.
  • Validation. This feature allows for more accurate data entry. I prefer to validate using a list that lives on a different worksheet.
  • Remove duplicates. This button makes identifying unique values in a long list quick and easy.
  • Group. This is great when I’m working with large groups of information that I want to condense or subtotal.

If you don’t mind “tech speak,” this blog has some tips and tricks that I love to use, plus a few that I was really excited to learn.

My Favorite Excel Formulas

In addition to the normal addition, subtraction, multiplication, division and sum formulas, these are the top 3 that I use that make my life easier.

FebDates

=WORKDAY(  I use the workday formula for planning purposes. It allows me to calculate when a project should start (if I have the due date) or when a project will be finished (if I know when I’m starting it) and it accounts for weekends and any holidays I want to exclude. Check out this post on how I use the workday formula to project plan.

=CONCATENATE(  I use concatenate when I want the values of multiple cells to be “added together” into one cell or when I have additional text I want to add to other cells. This blog has a good step-by-step instruction for using the concatenate function.

=VLOOKUP(  I use vlookup to compare information from multiple worksheets. Check out this step-by-step guide to using the vlookup formula in Excel.

On occasion I also like to use:

  • =MIN( =MAX( and =AVE( I use these formulas when I want to know the average, minimum and maximum values of a group of cells
  • =IF(  The if formula is more advanced than most of what I covered here. There are a lot of uses for it, but I primarily do a =IF(A2=””,””,FORMULA) calculation that says, if cell A2 (or whatever cell I’m calculating off of) is blank, return a blank value. If it’s not blank, then do this FORMULA. And the calculation I want to do is nested where it says “FORMULA”.
  • =PROPER( =LOWER( = UPPER( These 3 formulas will change the case of text in a cell to Proper Casing, lower casing or UPPER CASING.
  • =COUNT( will tell you how many cells in a range contain a value

Excel formulas can get gnarly really quickly. Just do a google search and you’ll see what I mean. I always suggest keeping it as simple as possible. And to do a sanity test on the results of your formulas.

Other Excel related blog posts: