Sometimes we may need to get value from alternating rows. Copying value from every nth row manually can be time consuming when your raw data is hundreds of rows deep.
If you have the following questions, make sure you read until the end.
- How to get value from every nth row?
- How to copy every nth value from a list?
- How to return value every other row in Excel?
In this article, I will show you how to get value from every nth row using a formula. With this formula, retrieving alternating values is no longer a draining task.
You might also be interested in How To Remove Digits After Decimal In Excel?
Example
Here I would like to extract the total sales of the month from column C.
I will need the value of cell C6, cell 12, cell 18, cell 24, cell 30, cell 36, cell 42, cell 48, cell 54, cell 60, cell 66 and cell 72.
The result is in column E and column F.
You might also be interested in How to highlight current row and column in Excel?
Get value from every nth row with formula
Formula in cell F2
=OFFSET(C2,(ROW(A1)*5)-1,0)
For the remaining cells, just copy the formula down.
How to use the formula
=OFFSET(First_cell_of_source_list,(ROW(A1)*number_of_rows_in_between)-the_row_difference_between_the_cell_and_the_last_cell_in_the_set,0)
In the sample sheet, the first cell in the source list is cell C2.
“Why is it not cell C1?”
This is because we only count the value in the list but not the column header.
For number of rows in between, just count how many rows there are between the first cell and the second cell.
the_row_difference_between_the_cell_and_the_last_cell_in_the_set
The last cell in the first set is cell C7. The cell we would like to retrieve is cell C6, which is one row upper than cell C7. So, the row difference here is 1.
You might also be interested in How to Compute IRR in Excel (Basic to Advanced)
You might also be interested in How to Merge Cells Across Multiple Rows/Columns.
How does this formula work
OFFSET function allows us to create a reference offset from a starting cell.
Here we are using OFFSET to locate another cell based on the first cell in source list.
(ROW(A1)*number_of_rows_in_between)-1
This part helps us to tell Excel how we would like our cell to move.
ROW function return the row number of the current cell so ROW(A1) returns 1.
The number of rows between the first set and the second set is 5 (as shown above).
(ROW(A1)*number_of_rows_in_between)-1 = 4
This gives Excel an instruction -> locate cell that is 4 rows lower than cell C2.
This is why cell F2 shows the value in cell C6.
You might also be interested in How to Merge Cells Across Multiple Rows/Columns.
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!
