How to shuffle rows/columns/a range of cells randomly in Excel?
When working with data in Microsoft Excel, you may encounter situations where you need to randomize the order of rows, columns, or even a specific range of cells. Shuffling data can be highly advantageous for tasks such as generating random samples for statistical analysis, randomizing quiz or survey questions, creating randomized participant lists, or simply ensuring a diverse distribution for testing and validation purposes. This article will walk you through several practical methods for efficiently shuffling data in Excel, exploring solutions that cater to different levels of expertise and Excel editions. Each approach offers distinct advantages, so you can select the method most suitable for your particular data management scenario.
Shuffle rows/a column list of data with a helper column
In many straightforward scenarios, you may want to shuffle the rows or a column list quickly without relying on add-ins or complex solutions. This method uses a helper column combined with Excel’s built-in sorting features to achieve randomness. The approach is simple and works in nearly all Excel versions, making it suitable for users who need a quick solution for small-to-medium datasets.
1. Select a list of cells next to your data range – for example, if your data is in A1:B8, select D1:D8, and then type this formula =RAND() in the first cell. This formula will generate a random number for each row, acting as a shuffle key. See screenshot:
2. Press Ctrl + Enter to fill the formula down the selected range. You’ll see a list of random values appear, corresponding to each row.
3. Go to the Data tab and select either Sort smallest to largest or Sort largest to smallest. This will reorder your data according to the random key, shuffling the rows. Be sure the selection includes all columns associated with your data to avoid misalignment. See screenshot:
4. When the sort dialog pops up, select Expand the selection to ensure the shuffle applies to entire rows and maintains data integrity. Click Sort to confirm.
Once these steps are complete, your data range will be shuffled by rows, displaying a new randomized order.
You can then delete or clear the helper column after finalizing the sort to tidy up your worksheet.
- The Excel RAND function is volatile, generating new random numbers each time the worksheet recalculates. This means your data can resort itself accidentally if you trigger recalculation, so be careful after initial shuffling.
- If you wish to preserve the randomized order, copy the cells containing the RAND formula and use Paste Special > Values to convert formulas to static numbers before sorting and removing the helper column.
- This method is best suited for shuffling entire rows. For more complex scenarios, such as randomizing columns or individual cells, consider alternative approaches described below.
Shuffle rows or columns using the RANDARRAY function (Excel 365/2021)
For users with Excel 365 or Excel 2021, the RANDARRAY function provides a dynamic way to generate unique random keys for each row or column, making it especially useful for shuffling large datasets with guaranteed randomness and no duplicate keys. It is ideal when you want to avoid the possible repetition of random numbers and need a formula-driven solution for modern Excel versions. This method is also very flexible for reshuffling based on any criteria.
1. Suppose your data resides in cells A1:B8. In cell D1, enter the following formula to generate a list of unique random numbers corresponding to each row:
=RANDARRAY(8,1)Explanation: 8 represents the number of rows; 1 specifies a single column. Adjust these numbers according to your range size. If you have more rows, replace 8 with the number of rows in your dataset.
2. To shuffle your rows, select your main data range along with the random numbers generated by RANDARRAY, then go to the Data tab and choose Sort based on the random column, expanding selection to include all relevant columns.
3. Once sorted, you can delete the helper column to clean up your worksheet.
Tips:
- If you want to shuffle columns instead, generate the random array and sort by columns instead of rows.
- You can combine
RANDARRAYwith other dynamic array formulas for more advanced shuffling tasks, such as reordering only a subset of rows or excluding certain entries.
Limitations: The RANDARRAY function is available only in Excel 365 and Excel 2021. For earlier versions, use the RAND() method described above.
Shuffle rows/columns/a range of cells randomly with Kutools for Excel
For more advanced data shuffling requirements, such as randomizing entire columns or shuffling individual cells within a range, Kutools for Excel offers the Sort Range Randomly feature. This utility provides a comprehensive set of options to shuffle rows, columns, or any selection with ease, accommodating sophisticated needs without resorting to manual formulas or complex workarounds. It is especially useful for users managing large datasets, creating experimental samples, or preparing randomized training and test sheets.
After installing Kutools for Excel, please do as below:
1. Select the range cells you want to shuffle randomly, and click Kutools > Range > Sort / Select Range Randomly. See screenshot:
2. In the Sort /Select Range Randomly dialog, under the Sort tab, choose the applicable option based on what you wish to shuffle, such as by entire rows, columns, or each cell in a range.
3. Click Ok or Apply to execute your chosen randomization. Kutools will instantly shuffle your selection according to the specified criteria.
Sort by entire rows
Sort by entire columns
Sort cells in the range
Sort cells in each row
Sort cells in each column
Additionally, Kutools for Excel’s Sort Range Randomly utility allows you to select cells randomly from within a range, which can be advantageous when you need a random sample or wish to pick specific cells without shuffling the entire worksheet.
Compared to manual methods, Kutools enables a wider range of shuffling operations and automates what might otherwise be a multi-step or error-prone process, providing increased efficiency and flexibility for repeated or large-scale use cases.
This article covers a variety of solutions for randomizing rows, columns, and ranges in Microsoft Excel, with methods suited to immediate needs or more advanced settings. Should you need to explore further Excel data manipulation techniques, our website features thousands of dedicated tutorials. Please click here to access them.
Shuffle cells by VBA macro
The macro below shuffles cells in a range.
Preparation: For safety, save your workbook before running macros, and ensure macros are enabled. VBA solutions are best used when you need repeatable, programmable shuffling across multiple files or larger datasets.
1. Click Developer Tools > Visual Basic, then in the Microsoft Visual Basic for Applications window, click Insert > Module.
2. Copy and paste one of the following codes into the Module area.
Shuffle cells in a range by direct swapping (full randomization)
Sub FullyShuffleRange()
Dim rng As Range
Dim arr
Dim r As Long, c As Long, i As Long, totalCells As Long
Dim r1 As Long, c1 As Long, r2 As Long, c2 As Long, tmp
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select a range to fully shuffle", xTitleId, rng.Address, Type:=8)
arr = rng.Value
r = UBound(arr, 1)
c = UBound(arr, 2)
Randomize
totalCells = r * c
For i = 1 To totalCells
r1 = Int(Rnd * r) + 1
c1 = Int(Rnd * c) + 1
r2 = Int(Rnd * r) + 1
c2 = Int(Rnd * c) + 1
tmp = arr(r1, c1)
arr(r1, c1) = arr(r2, c2)
arr(r2, c2) = tmp
Next i
rng.Value = arr
End SubClick Run to launch the macro, then select the target range to shuffle (for example, A1:D10). This code swaps cell values randomly and repeatedly, resulting in a thoroughly shuffled range. It is highly effective for shuffling all cells uniformly, such as for randomized assignment in games or quizzes.
- Macros may overwrite formulas in the original range with shuffled values—consider applying to static data only.
- If you encounter an error (such as wrong range selection), re-run and select the correct area.
- Always save your file before executing macros for the first time, and back up important worksheets, as macros cannot be undone automatically.
Demo: Select Or Sort Cells Randomly
Best Office Productivity Tools
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...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in