Microsoft Excel Tips for Marketers – Part 2

This is the second part of on ongoing series on excel tips and tricks for marketers [Click for Excel Tips Part 1.].  My focus as a data analyst is on being able to rapidly parse data in ways that are presentable as final products to clients.  As a Microsoft Excel 2008 user I lament my inability to use Visual Basic for more fine-tuned (and easier!) ways of accomplishing some Excel tasks, but at the same time welcome the challenge.

I use many of the things I describe on a day-to-day basis. I’ve started with some easier tips in the “General” and “Formulas” sections. If you have any additional tips and tricks you use regularly, please feel free to post them in the comments.  Moving forward, I’ll try to keep it down to only a few things per post, so each individual post is less overwhelming.

Here is a spreadsheet I put together that contains an example of each of the techniques I outline below:

Microsoft Excel 2008 for Mac – Tips for Marketers Part 2

GENERAL TIPS

Apostrophe Prefix

A handy little tip to know: if you put an apostrophe in front of whatever you are about to type into a cell, Microsoft Excel interprets it literally.  If you’ve ever wondered how to keep your in-cell-note to remind the client that a particular metric has doubled (“-this is important!”) from turning into “#NAME?”, this is for you.  Credit where credit is due: if you type in “–> this is important” Excel will tell you the formula contains an error and suggest the apostrophe (though it’s none-to-apparent to a panicking Excel user).

FUNCTIONS

SEARCH()

Filter works great when you want extract matches to the entire contents of a cell, or if you love repeatedly working through layers of menus (ugh).  What if you need to quickly, repeatedly scan a Search Query Report from your Search campaign?

The SEARCH() function is a great way to accomplish this.  The behavior of the SEARCH() function is quite direct: it looks inside a cell to see if it contains the phrase you’ve given it:

=SEARCH(“[your phrase]”, [the cell to look in])

Create a new column (next to the column you’re searching in is usually best), and paste this function into all rows with the data you are interested in searching.  Unfortunately, Excel 2008’s SEARCH() can be a little quirky sometimes, so if you find yourself unable to sort your SEARCH() column, copy said column with SEARCH() in it and paste values over your SEARCH() functions.  This will eliminate the functions themselves but keep the values (sort doesn’t really like to sort functions).

Shortcut for CONCATENATE()

CONCATENATE() is such a long word.  Instead, use the ampersand between cell references or text in quotes:

Example:

=CONCATENATE(“it is critically”, “ “, “important that you ”, A6)

is also

=”it is critically”&” “&”important that you ”&A6

Note: no efficiency gain for concatenations of more than 14 things!

MORE ADVANCED TIPS

Condense to Weeks

How to turn daily data from multiple sources into weekly, biweekly or monthly data when the online dashboards for your ad serving systems won’t let you?

Why force yourself into having to pull data from Google Analytics twice for the same report – once by day and once by week?  This is a formula I wrote to sum daily data to weeks, though it really could be used to condense daily data to any length period of time using a variety of aggregating functions (SUM(), AVERAGE(), etc).  Given the difficulty with explaining this formula in words, I think it’s worth just grabbing the demo / sample spreadsheet and working through the function itself.   The important base functions I use in my own function are: INDIRECT() and TEXT().   I use TEXT() to turn numbers into text, the text is cell references, which are interpreted by INDIRECT() (whose purpose is, not surprisingly, to turn text into cell references).

TEXT() may or may not be critical to what I’ve written, I’m just more comfortable when I explicitly state my data types.

Array Formulas

Time-series data such as impressions, clicks and conversions need not require hundreds or thousands of cells worth of calculations when you use Array formulas.

Array Formulas are covered quite well in a few other places on the internet (for instance: http://www.cpearson.com/Excel/ArrayFormulas.aspx).  I thought I would provide a mention to them here as well, as some examples in the demo / sample spreadsheet because they are quite powerful.  The simple premise is that they allow you to target a range of cells with a function that “normally” only takes a single cell at a time.  ROW() returns the number of which-ever row you give it.   {ROW()} (achieve the Array / curly brackets by pressing Cmd+Enter) returns all the numbers of all the rows you give it:

ROW(A1:A10) = 1 (What actually appears in the cell: 1)

{ROW(A1:A10)} = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 (What actually appears in the cell: 1)

Don’t be discouraged by the fact that {ROW(A1:A10)} just shows 1 in the cell when you hit Cmd+Enter!  Excel cannot display more than one value of an array in a single cell, so it shows the first – but the other 9 numbers are still there!

To make use of a lot of Array formulas you have to include some kind of aggregating function that wraps around the Array you are interested in.  Try:

SUM(ROW(A1:A10)) = 1 (What actually appears in the cell: 1)

{SUM(ROW(A1:A10))} = 55 (What actually appears in the cell: 55)

And that’s all for now.

Future topics:
• Building and parsing URLs
• Using dates in conditional statements
• Using TODAY() in reports
• Excel + Wordle to make advanced wordclouds
• Conditional formatting and dynamic cell references
• Copy / paste series to make large tables

[Click for Excel Tips Part 1.]

Kind Regards,
Nate


This is some text prior to the author information. You can change this text from the admin section of WP-Gravatar  To change this standard text, you have to enter some information about your self in the Dashboard -> Users -> Your Profile box. Read more from this author


RSS 2.0 feed. You can also leave a response, or trackback from your own site.


Leave a Reply


 

Contact

About Web Liquid

Web Liquid is a digital marketing agency with offices in London, New York and Lagos.

Search

Recent Comments