Randomly select data in your Excel file

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

On the Ablebits Tools tab, in the Utilities group, select Randomize > Select Randomly:
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. You can choose another range by selecting it on the sheet or typing a range reference in this box.
  2. If you changed the range, you can select 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 1 header row, 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 in the fields.

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

Responses

Jaison Antony says:
July 27, 2024 at 11:42 am

I have 500 rows of data having the age groups having data 18-20, 20-30, 30-40, 40 & above. I'm trying to assign each group a random value between 1 to 4. Value 1 should be populated to 48% of the 500 rows, value 2 should be assigned to 45%, value 3 to be populated only for 1% and value 4 to be populated for 6% of the 500 records. How to do it in excel?

Yacov says:
October 24, 2021 at 7:17 am

Hi
Is it possible to select cells according to the number of letters in each cell? I have a very long document and I would like to select all the cells that contain less than 20 letters, or alternatively cells that are too large that contain more than 100 letters?
thanks a lot.

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.

Ekaterina Pechyonkina (Ablebits Team) 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.

Sandi C says:
December 15, 2021 at 8:40 pm

I use this feature regularly - once the random cells are selected, use the cell "Fill color" option; this will fill all of the randomly selected cells with the selected color. You can then sort by color and cut/paste them to another worksheet. There may be an add-in that does this easier but I find this works for my needs.

Kira says:
March 2, 2020 at 4:56 pm

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?

Katerina Bespalaya (Ablebits Team) 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.

Paige says:
February 26, 2020 at 2:03 pm

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.

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

Ekaterina Pechyonkina (Ablebits Team) 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.

Ashlea says:
December 12, 2018 at 7:00 pm

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.

Doug Stewart says:
October 31, 2018 at 10:29 pm

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

Irina Goroshko (Ablebits Team) says:
November 2, 2018 at 9:05 am

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.

Doug Stewart says:
October 31, 2018 at 10:20 pm

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?

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

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.