How to Select Every nth Row in Excel?
If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..
Sometimes it may be needed to select the data from alternate rows or data from certain rows which are in multiple of certain number. During that time ever wondered how to select every other row, 3rd, 4th, 5th or say nth row in Excel? Its simple with a small trick. All that you have to do is write a formula which will allow us to filter these rows.
So how to select every other, 3rd, 4th, 5th or nth row in Excel?
In a nearby column and in the first row of the data that you are considering, write the formula =MOD(ROW(A1),3)
Note:This is the example formula to select every 3rd row. If you want every 5th row change 3 to 5 in that formula. Similarly change it to corresponding number you want.
Using the autofill feature, left click on the lower right corner of that cell and drag till the last row you are considering.
Now go to the Data tab and click Filter to setup the filter. Click the filter drop down button and check only 0 and uncheck the other.Click Ok.
The remaining filtered rows will be every other nth row. This tip works in all Excel 2007 or older versions.
How and why this trick works?
Row(A1) returns 1 and Row(A2) returns 2 and so on. Now we have a sequence of numbers 1,2,3,… and Mod of these numbers with our choice of row number returns 0 on those rows. By filtering the rows for 0, we get to select every nth row.
Click here to read more Excel Tips like this.
