Three Marketing Examples Of The OFFSET Function [VIDEO]

the Offset function visualized for marketers

Continuing with our #FunctionFriday series, today we’re going to talk about the OFFSET function. And I demonstrate three uses of the OFFSET function at the very end of the post.

What Does It Do?

The OFFSET function allows you to scoop up data to use in other functions. Excel gives you the ability to tell it exactly where to find the data and how many rows and columns you need. It looks something like this:

Syntax

The syntax for OFFSET is:

OFFSET(reference, rows, cols, [height], [width])

reference: Your starting cell

rows, cols: How many rows you need to move (up or down)  and columns you need to you need to move (left or right) to get to the data range you want to capture

height, width: How many rows and columns you need to lasso. It’s the red box in the above illustration.

Download File

If you want to see examples of the OFFSET function with real marketing data, download the example file I used in the video.

Marketing Uses For OFFSET

Dynamic Named Ranges

If you’re creating a chart that needs to dynamically update when the number of rows in the data set changes, you can use the OFFSET function to do this. If the number of rows will only increase and never decrease, I wouldn’t go to the trouble of creating named ranges; I would build your chart off of a formatted table. If you haven’t worked with formatted tables, they’re fabulous. You can learn all about them in this post I wrote for Search Engine Land. One of the best benefits of tables is that charts built off of them automatically update.

However, if the number of rows in your data set can increase or decrease (such as charts inside reporting dashboards), you should use named ranges and build your table off of them.

OFFSET function for dynamic named ranges in Excel

Click for larger image

Dynamic Data Validation

Data validation drop-downs are a great way to restrict users’ input in a table. They can also be used much like a drop-down to update a chart (as I demonstrated in this video tutorial). One problem with them is they don’t update automatically if you add a new item to the list. But you can use the OFFSET function to make your data validation list update dynamically.

OFFSET function to create dynamic data validation for marketing data

Format Comparison Data From Google Analytics

Google Analytics gives you the ability to compare one date range to another. This is a great way to do month-over-month and year-over-year analysis on your website’s traffic. However, the export isn’t conducive to charting. The OFFSET function can be used to get your data into a format that can be easily charted.

OFFSET function in Excel to format Google Analytics data

Click for larger image

Pro Tip: If you’re comfortable using pivot tables and pivot charts, you could also use a pivot table to organize the data and then create a pivot chart from it (Mac: static chart from your pivot table since Excel for Mac doesn’t support pivot charts). I include a formatted pivot table and chart in the download file.

Video

I demonstrate all three examples in the video below.

Did I Miss Any?

Do you have examples of how you use the OFFSET function for marketing? If so, include it in the comments below, and I’ll add it to the post (if it’s clear enough for people to follow). If you would like a link, include that with your example.

Comments

  1. alorant says:

    Another terrific post & video. Thanks! Keep up the great work!

  2. I am open source lover. I am wondering will this work as well as on LibreOffice and I am going to give a shot.

  3. aaronfriedman says:

    Hey Annie,

    I don’t know if this is what you were talking about, but I <3 offsets (as I have told you many times :) ) and I included this one in my moz post. http://moz.com/blog/storytelling-through-data-a-new-inbound-marketing-seo-report-structure (if that helps)

Speak Your Mind

*