# Nth Select in Excel?

**URL:** <https://boards.straightdope.com/t/nth-select-in-excel/540826>\
**Category:** Miscellaneous and Personal Stuff I Must Share\
**Created:** [May 26, 2010, 3:44pm UTC](https://boards.straightdope.com/t/nth-select-in-excel/540826 "2010-05-26T15:44:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jonathan\_Chance](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jonathan_chance/32/701_2.png) [@Jonathan\_Chance](https://boards.straightdope.com/u/Jonathan_Chance)\
**Post date:** [May 26, 2010, 3:44pm UTC](https://boards.straightdope.com/t/nth-select-in-excel/540826/1 "2010-05-26T15:44:48Z")

</div>

Anyone know how to do this? Or even if it’s possible?

I’ve got a few worksheets that are larger than I need for some mailing. I need to Nth select it down to 150. Any help would be appreciated.

---

<div class="post-metadata">

**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\
**Post date:** [May 26, 2010, 5:35pm UTC](https://boards.straightdope.com/t/nth-select-in-excel/540826/2 "2010-05-26T17:35:13Z")

</div>

You mean, select every n-th record?

The way I’ve done that is to add another column and type “1” in the first cell of the column. Then type in the following formula to the second cell, assuming that your new column is column A:

=if(a1=N,1,a1+1)

where N is the number you’re looking for. Then paste that formula all the way down the column. Copy the column and paste-special, “Values” over top of itself. Now sort the whole spreadsheet by that column, and you’ll have all the “nth” records stacked on top of each other.

---

<div class="post-metadata">

**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\
**Post date:** [May 27, 2010, 1:07am UTC](https://boards.straightdope.com/t/nth-select-in-excel/540826/3 "2010-05-27T01:07:19Z")

</div>

That works.

Variants include:  
use row() rather than A1+1  
use =IF(MOD(ROW(),7)=0,1,0) to ID the Nth rows  
use filters, there is no need to copy & paste-special then sort

If you have Excel 2007 or above;  
set up conditional formating to highlight whichever Nth rows you want  
=MOD(ROW(),N)=0  
Then use filter by color

---

<div class="post-metadata">

**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\
**Post date:** [May 27, 2010, 1:23am UTC](https://boards.straightdope.com/t/nth-select-in-excel/540826/4 "2010-05-27T01:23:54Z")

</div>

Or you could do a =COUNTIF(a1:a…,“N”) on the column I added, or to go VBA and do a Rows.Offset(N,0), or… 😃