How to Create a Lag Function in Excel

This guide will discuss how to create a lag function in Excel using the OFFSET function.

The rules for using the OFFSET function in Excel are the following:

  • The OFFSET function can be used in formulas that need a dynamic range since the function returns a dynamic range.
  • When the height and width arguments are left blank or empty, the function will default to the height and width of the reference. 
  • Since the function only returns a reference, no cells in the worksheet are moved.
  • If we want to reverse the directions of the offset columns and rows, we can input a negative value in both rows and cols argument. So negative cols will offset the column to the left while negative rows will offset above.
  • When the offset is outside the edge of the worksheet, the function will return a #REF! Error.
  • Since the OFFSET function is a volatile function, it will recalculate with every worksheet change. 

Excel has multiple built-in functions that we can use and manipulate to perform several tasks, such as organizing and analyzing data, calculations, and even manipulating a list of data.

Specifically, we can create a lag function in Excel using its built-in functions. So a lag function is used to access or get data from a previous row in the same result or data set. We use a lag function to get the previous value from the same row in a data set. 

And the OFFSET function is the perfect function to use in Excel to perform this specific task. Since we already have a built-in function to manipulate, we can easily create a lag function in Excel.

Let’s take a sample scenario wherein we must create a lag function in Excel.

Suppose you have a sales report for a specific store. And it shows the daily sales in the last week. So you want to calculate the lag sales to create an in-depth sales report. In this case, you want to input the sales from the previous day into the current day.

To get the lag sales, you created a lag function using the OFFSET function in Excel. 

Before we learn more about how to create a lag function in Excel, let’s first learn how to write the OFFSET function in Excel.

 

The Anatomy of the OFFSET Function

The syntax or the way we write the OFFSET function is as follows:

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

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • OFFSET() refers to our OFFSET function. And this function is used to return a reference to a range that is a given number of rows and columns from a selected or given reference.
  • reference is a required argument. So it refers to the reference from which we want to base the offset. Additionally, this can reference a cell or a range of adjacent cells.
  • rows is another required argument. And it refers to the number of rows, either up or down, that we want the upper-left cell of the result to refer to.
  • cols is also a required argument. So it refers to the number of columns, either to the left or right, that we want the upper-left cell of the results to refer to.
  • height is an optional argument. And this refers to the height meaning the number of rows that we want the results to be. When omitted, the function will use the same height as the reference.
  • width is another optional argument. So it refers to the width meaning the number of columns we want the result to be. When omitted, the function will use the same width as the reference. 

Great! Now let’s move on and dive into a real example of creating a lag function in Excel.

 

A Real Example of Creating a Lag Function in Excel

Let’s say we have a data set containing the number of sales from each day last week. So we have the sales number for 7 consecutive days. And our initial data set would look like this:

Initial dataset

 

Furthermore, we want to get the lag sales for this data set meaning we want to obtain the previous number of sales to input the next day. For instance, we want the number of sales from Day 3 to be placed as the lag sale for Day 4. 

In a large data set, this task can be taxing and time-consuming. So we can simply create a lag function using the OFFSET function to easily and quickly access the values from the previous rows in the same result or data set.

So the OFFSET function will return a reference of the previous sales having a lag of n-1. Using this formula, we can obtain the number of sales from the previous day and place it in the current day. 

Additionally, we can also create a lag function to calculate lag values by group using the same function. For example, we have a data set containing the sales from different stores. Similarly, we want to calculate the lag sales from the previous day into the current day. However, we will do it by group or by store. 

So we will add the IF function to our original formula to perform this. Firstly, the IF function will check if the store value in the current row is the same or matches the store value in the previous row. If it matches, the function will return the lagged sales. If it does not match, it will return a blank. 

So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can discuss the process of how to create a lag function in Excel using the OFFSET function.

 

How to Create a Lag Function in Excel

In this section, we will explain the step-by-step process of how to create a lag function in Excel with the help of the OFFSET function. To apply this method, simply follow the steps below.

1. Firstly, we will create a new column in the data set to input the results of the lag values. Then, we can simply type in the formula “=OFFSET(C3, -1, 0)”. Lastly, we will press the Enter key to return the results.

Create a Lag Function in Excel

 

2. Secondly, we will drag the Fill Handle tool down to copy the formula and apply it to the other cells.

Copy formula down

 

3. And tada! We have created a lag function in Excel.

Final output

 

4. Additionally, let’s try getting the lag values by groups. In this case, we have two different stores in the same data set. And we want to only get the lag sales for the same store.

To do this, we will add the IF function to our formula. So type in our formula “=IF(B12=B11, OFFSET(C12, -1, 0))”. Finally, we will press the Enter key to return the results.

Create a Lag Function in Excel

 

5. Next, we will drag down the Fill Handle tool to copy the formula to the rest of the columns.

Create a Lag Function in Excel

 

6. And tada! We have obtained the lag sales per store.

Create a Lag Function in Excel

 

And that’s pretty much it! We have successfully created a lag function in Excel using the OFFSET function. And we have explained how to use it in two different situations and purposes. Now you can apply this method to your work whenever you need to access previous data and input it in the current row. 

Are you interested in learning more about what Excel can do? You can now use the OFFSET function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like