Excel Guides

Referencing the Last Six Items in a Formula in Excel

There are a few different ways that you can reference the last six items in a formula in Excel. One way is to use the OFFSET function. The OFFSET function allows you to reference a certain number of cells from a given starting point. So, if you wanted to reference the last six items in a column, you could use the following formula:

=OFFSET(A1,COUNTA(A:A)-6,0)

This formula would start at cell A1 and then count the number of cells in column A (COUNTA(A:A)). It would then subtract 6 from that number and use that as the offset. So, if there were 10 cells in column A, it would start at cell A4. If there were 100 cells in column A, it would start at cell A94.

Another way to reference the last six items in a column is to use the INDEX function. The INDEX function allows you to reference a cell at a given index. So, if you wanted to reference the last six items in a column, you could use the following formula:

=INDEX(A:A,COUNTA(A:A)-5)

This formula would start at the first cell in column A and then count the number of cells in column A (COUNTA(A:A)). It would then subtract 5 from that number and use that as the index. So, if there were 10 cells in column A, it would reference cell A6. If there were 100 cells in column A, it would reference cell A96.

You can also use the ROW function to reference the last six items in a column. The ROW function returns the row number of a given cell. So, if you wanted to reference the last six items in a column, you could use the following formula:

=ROW(INDEX(A:A,COUNTA(A:A)-5))

This formula would start at the first cell in column A and then count the number of cells in column A (COUNTA(A:A)). It would then subtract 5 from that number and use that as the index for the ROW function. So, if there were 10 cells in column A, it would return row 6. If there were 100 cells in column A, it would return row 96.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.