Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams (2023 Update)
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11 (2023 Update)
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Data Analytics
        • Introduction to Analytics and Artificial Intelligence
        • Data Analytics in Excel
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Randomize a List in Excel? 2 Different Methods

How to Randomize a List in Excel? 2 Different Methods

(Note: This guide on how to randomize a list in Excel is suitable for all Excel versions including Office 365)

Excel is used for various purposes that include storing, retrieving, and showcasing data in several different formats. In some lists or tables, there might be a need to shuffle the entries randomly.

In this article, I will tell you how to randomize a list in Excel in 2 easy ways.

You’ll Learn:

  • Why Randomize a List in Excel?
  • How to Randomize a List in Excel?
    • Using a Combination of Formulas
    • Using the RAND Function and Sorting 

Watch our video on how to randomize a list in Excel

How to Randomize a List in Excel - Worksheet
How to Randomize a List in Excel – Worksheet

Why Randomize a List in Excel?

Before we learn how to randomize a list in Excel, let us see some cases where we randomize the entries in lists and why we do that.

We all might be quite familiar with the concept of randomizing a value. When we have a list of entries at hand and have no criteria to sort them, organizing or arranging the entries in an unbiased way can be a bit complicated. Here is where randomizing the entries of the list or picking a random entry from the list comes to your aid.

We randomize the entries of a list or table while picking any random criteria in an unbiased way.

Excel has a variety of ways to sort your data either in ascending or descending format based on different columns. Excel even has a way to custom sort a list. But, Excel lacks a common functionality to sort data, i.e. the random sort. 

You can easily randomize a list in Excel with the help of certain functions and shuffle all the values.

Related Reads:

How to Wrap Text in Excel? With 6 Simple Methods

How to Convert XML to Excel? – 2 Easy Methods 

How to Count Cells with Text in Excel? 3 Different Use Cases

How to Randomize a List in Excel?

Though Excel does not provide an option of a random sort, there is a function called =RAND() which helps you to sort the data in the list randomly. However, when this function is used in the usual way, this returns a random numerical value. But this function can be used with other functions and with a series of steps to randomize a list. 

Let us see 2 ways to randomize a list in Excel with an example.

Consider an example where a group of 10 people arrives for a conference. The members consist of different majors and fields. The order of presentation should be unbiased and must be picked randomly and decided beforehand.

Example for How to Randomize a List in Excel
Example for How to Randomize a List in Excel

Using a Combination of Formulas

Functions and formulas help to solve a variety of problems in an efficient way. To randomly sort out data, you can use the SORTBY formula with a combination of RANDARRAY and COUNTA functions with a few criteria to randomize the list.

First, select any destination cell to create the random list. One advantage of using this method is that the new randomized data will be created onto a new cell/column and the original row will be left intact for any further reference.

Enter the formula =SORTBY(range,RANDARRAY(COUNTA(range))) in the destination cell and press Enter. Here, the range is the array of cells that houses the list to be randomized.

How to Randomize a List in Excel using SORTBY and RANDARRAY formula
How to Randomize a List in Excel using SORTBY and RANDARRAY formula

This creates a random list of entries from the selected range of cells onto the destination cell.

Looks so simple, right? It really is. Now, let me explain how the function works and how it returns a list of randomized values from the array. 

The COUNTA function counts the total number of values in the provided range of cells. In this case, this function counts the number of entries between cells B4 and B13. 

The RANDARRAY function generates random values and stores them in an array. Since the value returned by the COUNT function is 10, the RANDARRAY function creates 10 values and stores them in an array.

Now, these numbers are given to the SORTBY function and it sorts the contents of an array and returns the value in the same form. In this case, randomized values from the RANDARRAY function are sorted and populate the cell range B4 to B13.

You can see that only the first cell contains the function and the formula for the rest of the values is grayed out. That means that the SORTBY function returns the array of values starting from the first cell which cannot be independently modified. 

The rest of the array does not show the formula
The rest of the array does not show the formula

This combination of functions is volatile and changes with any update or iteration made to the sheet. If you are not satisfied with the current random values, you can either enter the edit mode of the first cell and press Enter or press the function key F9 and the values change automatically. 

After you create a random list in Excel, you can copy the cells and paste them as Values to prevent any involuntary fluctuation of the values.

Note: This function is only available in newer versions of Microsoft Office (O365 or Office 2021 and higher versions).

Suggested Reads:

How to Add a Secondary Axis in Excel? 2 Easy Ways

Short Date Format in Excel – 3 Different Methods

How to Graph a Function in Excel? 2 Easy Ways

Using the RAND Function and Sorting 

This is one of the oldest ways to create a random list in Excel. This method just employs the RAND function to create random values and randomly sort the data. However, it employs a couple of steps to arrive at the randomly sorted data. 

First, select a destination cell.

Enter the formula =RAND and press Enter.

When you press Enter, a random value populates in the cell. 

Use the drag handle to apply the formula to the remaining cells. 

How to Randomize a List in Excel using RAND function
How to Randomize a List in Excel using RAND function

As of now, it makes no sense and the values are not random yet.

Now, with the selected cells, navigate to Data. Under the Sort & Filter section, click on Sort. You can sort the random values either ascending or descending. 

Sort the data
Sort the data

Excel throws a warning suggesting you select the data adjacent to the random values. Select the Expand the selection and click Sort.

Expand the selection and click Sort
Expand the selection and click Sort

In case you click on Sort instead of clicking on the ascending or descending button directly, Excel opens a new Sort dialog box. Select the values to sort, the order of the sort, and click OK.

Set the criteria to sort
Set the criteria to sort

Once you click on Sort, the random values will be sorted out along with the values.

The data is sorted along with the values
The data is sorted along with the values

You can delete the random values and just keep the list that has been randomized.

Delete the Random Values
Delete the Random Values

Also Read:

How to Calculate Factorial in Excel? Along with 2 Easy Examples

How to Enable Excel Dark Mode? 2 Easy Methods

How to Create an Excel Slicer? 2 Easy Ways

Frequently Asked Questions

How to Randomize a Large List in Excel?

You can randomize a large list in Excel, it is better to use the SORTBY, RANDARRAY, and COUNTA functions as it randomizes the list in a single step.

How to Assign Random Numbers to a List in Excel?

To assign random numbers, sort the numbers in ascending or descending order. Now, use the random function and sort the cells together with the numbers. 

Are Duplicates Created When Randomizing a List?

The answer is No. When randomizing a list in Excel, only the existing values are randomized. No new values are created, duplicated, or deleted.

Closing Thoughts

In this article, we saw how to randomize a list in Excel in 2 easy ways. On one hand, using a combination of SORTBY, RANDARRAY, and COUNTA functions is easy and efficient but it does not work on lower versions. (Ambien) Using the RAND function and sorting the values works on every version but it takes a few extra steps. Choose the method that suits you the best.

Want more high-quality guides for Excel? Check out our free Excel resources centre. 

Click here to access in-depth Excel training courses and master in-demand advanced Excel skills. 

Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 130+ IT training courses by seasoned professionals.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Use Critical Paths in Microsoft Project 2016

The Most Used Microsoft Outlook Shortcuts – Download

Inserting Shapes in PowerPoint 2016

How to Use Layers in Adobe Photoshop Elements 2019 Tutorial – Part 1

How to Create a Step Chart in Excel?

How to Use the Chi Square Test Excel Function? 4 Easy Steps

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)