How to Extend and Increment Formulas

When you have created formulas in Excel, you may want to extend and increment them down to other cells. In many cases, you can just drag down a cell that contains the formula and pull it down so that it automatically gets copied. However, there’s a problem if you need to increment a formula or number in an argument. Below, I’ll show you an example of this and how you can address it in multiple scenarios.

Let’s start with just pulling the largest value in a data set. Using the LARGE function, you can grab the largest value with a formula of =LARGE(A:A,1) — that will give you the largest value in column A. However, you’ll notice quickly that just copying this formula down won’t work as expected, it’ll keep copying the 1 in the second argument down; you will continue extracting the largest value. Sometimes Excel just isn’t good at detecting patterns. But there are ways you can adjust the formula to ensure your formula is incrementing correctly.

Option 1: Using an Additional Column

In a separate column, you could list the numbers 1 until 100 or whatever range you needed to and reference those. In the example below, I can pull the largest 10 items using the LARGE function and a referencing the cells to the left:

The problem with this approach is that it isn’t always practical within a spreadsheet, especially if you don’t have room to spare and don’t want to be creating extra columns for the sake of what should be a simple calculation. It may also be difficult to determine what the upper limit should be. That’s where the second option is going to be a better one.

Option 2: Using the ROW Function

There’s an easy way to adjust the above calculation so that you don’t need to reference the actual number, nor do you need to manually enter it. The trick is to use the ROW function. It returns the row value of a specified cell. If I entered =ROW(A20), it would return a value of 20 for the row value. You don’t need to enter an argument and using =ROW() will just return the row of the cell that contains the formula. The function is particularly useful when you want to increment values because as you drag it down, the results will increase by one each time.

In calculating the largest values, we’ll use the ROW function as the argument to determine which value we want, whether it’s the fifth largest or 20th largest number. Here’s how the initial formula would look like, assuming we wanted to start with the largest value:

=LARGE(D:D,ROW(A1))

In the above formula, the calculation will return the largest value since ROW(A1) will return a value of 1. Now, if the formula is copied down, here’s what it will look like:

By copying down the formula, ROW(A1) changed to reflect the next row. Excel does a better job of detecting patterns when they’re part of a function as opposed to just standalone numbers. Using the ROW function eliminates the need for the extra column. And as you’ll notice, the results are the same as in the earlier example.

This is just one example and there are many others where you can use the ROW() function to your advantage and increment values down. If you need to increment as you’re going to the right instead of down, then you’ll want to use the COLUMN function, which effectively does the same as the ROW function, only it’ll pull the column number that a cell is in.

If you liked this post on how to extend and increment formulas please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.