Randomly select data in your Excel file

Use the tool to get a random selection from a set of your Excel data range. It's also possible to specify the percentage and number of cells, rows, or columns you want to get from the entire selection.

How to randomly select values in a range

Pick the option to Select Randomly from the Randomize tool, Utilities group on the Ablebits Tools tab in Excel:
Click on the Select Randomly icon to run the tool.

The add-in offers you the following options:
Pick the options you need on the add-ins' pane.

  1. Your entire data range will be selected by default. Choose another range by highlighting it in Excel or entering it in this field.
  2. If you changed the range, highlight the entire table back by clicking the Expand selection icon.
  3. You can exclude header rows from the selection if there are any by checking My table has 1 header row.
    Tip. If there are more headers in your table, click on the "1 header row" phrase, enter the number of lines with labels, and they won't be selected:
    Let the add-in know how many header rows your table has.
  4. Expand the group with the option that suits you best:
    • You can select some number or percentage of random rows in your Excel table.
    • Pick random columns in your worksheet.
    • Or select some random cells in your range.
  5. Specify the number or percentage of rows, columns, or cells that you want to select. You can enter the necessary value into the percentage or number field respectively. Or set these values by clicking up and down arrows next to the fields.

Once options are chosen, click the Select button and get your datasets highlighted.

Responses

I follow these steps and nothing seems to happen except under "select randomly" the rows selected show up but they aren't filtered on the spreadsheet, not does a new tab show up with the selected rows. what am i missing?

Reply

Ok, figured out that it highlights the randomly chosen rows, but how do you get them grouped together by themselves?

Reply

Hello, Doug,

Thank you for using our tool and for your question.
Select Randomly neither filters the sheet by the selected rows, nor groups the randomly selected rows/columns/cells.
In fact, it simply selects random rows, columns, or cells (depending on the options you choose) so you could work with them as you need. If you need to get the randomly selected entries on a separate sheet, you will have to copy and paste them into a new Excel sheet manually.

Thank you.

Reply

This would be great if the cells stayed highlighted. The range changes and highlights disappear when you click back onto the spreadsheet. This makes it very difficult to filter or do anything with the randomly selected cells.

Reply

Hi, Ashlea,
Thank you for using the tool and for your feedback! Since this is quite a common request, I will pass it to our developers and hopefully, we will implement this feature in the coming releases.
Thank you.

Reply
Larry R Nelson says:
January 29, 2020 at 2:04 am

I have an Excel worksheet with test data for over 4,000 students. It contains a single header row followed by 4,000 rows of data.

I'd like to randomly select a sample of 10% of the student records (which I can do with your "Select random rows" option) and then have them copied to a new worksheet.

I have to copy them manually? Is that right? That is not too convenient -- have I misunderstood something (hope so!).

Thanks for your help and regards from Western Australia, Larry

Reply
Ekaterina Pechyonkina says:
January 29, 2020 at 10:22 am

Hello Larry,

Thank you for contacting us. I have just replied to you via email. Please check your Inbox. Thank you.

Reply

In the "Select Randomly" function, is there a tracking or reference number that can be recorded per selection for documentation purposes? We use this function for random drug test selection and need to be able to document the selection.

Reply

Hello Paige,

Thank you for your question. I'm sorry but we don't quite understand your task. Can you please clarify? If you send us a sample of your workbook so that we could see your source data and the result you'd like to get to support@ablebits.com, it'll help a lot. Thank you.

Reply

My text box where i can manually enter my data range is not letting me type any longer. It will let me drag and select criteria, but no longer manually type in the box. is there an easy fix?

Reply
Katerina Bespalaya says:
March 2, 2020 at 6:19 pm

Hello Kira,

Please download and install the latest update of Ultimate Suite for Excel from this page and check if the issue occurs with the new version. If you need any further assistance, please reach us out by email, support@ablebits.com.

Reply
Karl Cutajar says:
March 15, 2020 at 9:40 am

Hi,

Is there any way to select rows randomly and then split the randomly selected that from the rest to get two separate data sets in separate sheets yet?

Thank you.

Reply
Ekaterina Pechyonkina says:
March 15, 2020 at 12:55 pm

Hi Karl,

Thank you for contacting us. Sure, this tool will help you to select random rows but to split them you need to use another add-in. Could you please send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com? We will have a look and suggest a solution. Thank you.

Reply

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard