Skip to main content

How to select random cells from a list in Google sheets?

This article, I will introduce some ways to select random cells from a list in Google sheets and Excel worksheet.

Select random cells from a list in Google sheets with formula

Select random cells from a list in Excel worksheet with Kutools for Excel


Select random cells from a list in Google sheets with formula

Applying the below formula to extract some cells randomly in Google sheets, please do as this:

1. Enter this formula: =ArrayFormula(VLOOKUP(QUERY(UNIQUE(RANDBETWEEN(ROW(INDIRECT("A1:A"&COUNTA(A:A)*10))^0,COUNTA(A:A))),"limit 4"),{ROW(INDIRECT("A1:A"&COUNTA(A:A))),FILTER(A:A,A:A<>"")},2,0)) into a blank cell where you want to get the result, and then press Enter key to get one any name.

Note: In the above code, A:A is the name column that you want to extract names from, limit 4 means 4 names will be extracted randomly, you can change them as you need.

2. Then drag the fill handle down to cells to extract any four names, see screenshot:

doc google sheets sort randomly 1

Note: You can press Ctrl + R to pick up new names randomly each time.


Select random cells from a list in Excel worksheet with Kutools for Excel

If you need to select cells randomly in Excel worksheet, Kutools for Excel’s Sort Range Randomly feature can help you to deal with this job with ease.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. 

After installing Kutools for Excel, please do as follows:

1. Select the data list that you want to select random cells from, and then click Kutools > Range > Sort Range Randomly, see screenshot:

doc google sheets sort randomly 2

2. In the Sort Range Randomly dialog box, under Select tab, enter the number that you want to select the cells in the No. of cells to select text box, and then choose Select random cells in the Select Type list box, see screenshot:

doc google sheets sort randomly 3

3. Then click Ok button, and the specific number of cells have been selected randomly at once, see screenshot:

doc google sheets sort randomly 4

Click to Download Kutools for Excel and free trial Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Спасибо большое автору. Около года искал решение и наконец-то, вот оно. Идеальное решение :)
This comment was minimized by the moderator on the site
Hello,
You are welcome. Glad it helps. Any questions, please feel free to contact us. Have a great day.
Sincerely,
Mandy
This comment was minimized by the moderator on the site
On Google Sheets, it is returning the row number instead of the names. Any clues on how I can solve it? Thanks a lot!
This comment was minimized by the moderator on the site
On Google Sheets, ir is returning the row number instead of the names. Any clues on how I can solve it? Thanks a lot!
This comment was minimized by the moderator on the site
This has been super helpful thank you! I was wondering when "Selecting random cells from a list in Google sheets with formula' - is there a way to randomly extract 2 columns of data together? For example column A has names and column B has dates - if I wanted to randomly extract the name and matching date together (not a random one from each column)? I've tried changing A:A to A:B throughout the formula but so far had little sucess.
This comment was minimized by the moderator on the site
Jay - rather than trying to extract two columns, extract one randomly, and then do a VLookup against the two columns to get the 2nd column.
This comment was minimized by the moderator on the site
Thanks a lot for your help!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations