Use Select Randomly 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:
The add-in offers you the following options:
- Your entire data range will be selected by default. Choose another range by highlighting it in Excel or entering it in this field.
- If you changed the range, highlight the entire table back by clicking the Expand selection icon.
- 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:
- 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.
- 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
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.
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.
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?
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.
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.
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.
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
Hello Larry,
Thank you for contacting us. I have just replied to you via email. Please check your Inbox. Thank you.
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.
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.
Ok, figured out that it highlights the randomly chosen rows, but how do you get them grouped together by themselves?
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.
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?