Three Marketing Examples Of The OFFSET Function [VIDEO]

UPDATE: I shared this post because I know marketers are quite fond of the OFFSET function. But I share a much more stable alternative to the OFFSET function. If you’re building out a dashboard or large Excel file, the INDEX function will take less of a toll on your workbook than its more volatile OFFSET counterpart. And there’s nothing you can do with OFFSET that you can’t do with INDEX, as I demonstrate in the video.

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.

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.

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.

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.