How to Shuffle Cells in Excel

Recently, someone asked an intriguing question: how can one randomize a grid of cells in Excel? This question piqued my curiosity and I wanted to see if I could provide some useful insights. After doing some research, I am now ready to share my findings with you in this blog post. Here, I will discuss how to effectively randomize a list in Excel and provide tips on how to make the process easier. So, if you’re looking for a way to shuffle your data in Excel, then this post is for you! With my help, you’ll be able to quickly and easily randomize your lists in no time. So, don’t wait - read on and discover the secrets of randomizing a list in Excel!

Setup

Say we have a grid of 12 by 12 cells like this one.

Our goal here is to shuffle all of the cells throughout the range they live in. Something in A1 could end up all the way in L12, for example.

So how would you do it?

Does this article help you? If so, please consider supporting me with a coffee ☕️

Option 1: Shuffle Using the Rand() Function

If you haven’t read my other post about randomizing a list in Excel, please have a quick look. This post will make more sense if you do. In that post, we went over how to use the =RAND() function to shuffle a column of data in Excel. Let’s try to do something similar for this task.

First, insert a column before A and fill each cell with =RAND(). This will generate a random number between 0 and 1 in each of the new cells.

Now, follow these steps:

• Select all of the cells that we want to shuffle (including the new cells we added)
• Click on Home -> Custom Sort…
• Uncheck “My Data/List has Headers”
• Sort by: Column A
• Click OK

Now, each column’s rows are sorted in a random order.

Psst! If you are about to yell at me in the comments, good on you for catching the fallacy in this method, but also please read on, I’ll get to the shortcomings of this method soon.

Ok, let’s shuffle the columns now. First, insert a new row above the data and add =RAND() in the new cells above the columns we want to shuffle.

We’re going to apply the same idea by sorting the data from left to right by row 1’s data (the =RAND() numbers).

• Select the new cells along with the data below
• Click on Home -> Custom Sort…
• Click Options -> Sort Left to Right -> OK
• Uncheck “My Data/List has Headers”
• Select Row 1 for the Sort By
• Click OK

Let’s call this the “good enough” shuffled grid.

If you’re happy with this, then you can remove the column and row that we added the =RAND() formula to and you have your shuffled grid.

BUT, there is a problem with this method. Read on to find out how to really shuffle a grid in Excel.

So, what’s the problem with this method?

If you had a grid of names and you applied this method, it might be good enough for your needs. At first glance, it looks ok and it looks shuffled and it kind of is, but it’s not really shuffled.

To illustrate, I decided to add a color to each column of the data and apply the same sorting method. Check it out.

Here is the data as it was before the sorting (though the cells with =RAND() have different values now).

Now I’ll apply the two sorts like we did before and we end up with this.

Clearly, something’s up. When you look at the text, it might not be so obvious, but once you add color to the columns, it becomes clear.

When the columns are shuffled by row 1, you can see that they are moved as groups and the same letters in each column are brought along for the ride. Likewise, when we shuffle the rows by column A, all of the letters in each row are moved as groups as well.

The same thing would happen if I were to color the rows instead of the columns. Each row of colors would be moved around as a group.

In other words, when you sort all of the rows by one column (the column with =RAND() in it, for example), then each column still has the same items in it, just in different rows. Then, when you shuffle the columns, each row still has the same info in it, just in a different column.

This can give you the feeling that it’s “shuffled enough” and maybe it works well-enough for specific senarios. However, if we want to do a better job, we have more options we can explore. Some of these can be tedious, others not so much. In order not to bore you, let’s just jump to the solution I feel gives you a pretty good shuffling of the grid.

Option 2: Use VBA to Shuffle the Cells

After some trial-and-error, here’s what I ended up with:

Option Explicit

Public Sub ShuffleGrid()
  Dim rngSelected As Range
  Set rngSelected = selection

  Dim horizontalLength As Long
  Dim verticalLength As Long

  horizontalLength = rngSelected.Columns.Count
  verticalLength = rngSelected.Rows.Count

  Dim i As Long
  Dim j As Long

  For i = verticalLength To 1 Step -1
    For j = horizontalLength To 1 Step -1
      Dim rndRow As Long
      Dim rndColumn As Long
      rndRow = Application.WorksheetFunction.RoundDown(verticalLength * Rnd + 1, 0)
      rndColumn = Application.WorksheetFunction.RoundDown(horizontalLength * Rnd + 1, 0)

      Dim temp As String
      Dim tempColor As Long
      Dim tempFontColor As Long

      temp = rngSelected.Cells(i, j).Formula
      tempColor = rngSelected.Cells(i, j).Interior.Color
      tempFontColor = rngSelected.Cells(i, j).Font.Color

      rngSelected.Cells(i, j).Formula = rngSelected.Cells(rndRow, rndColumn).Formula
      rngSelected.Cells(i, j).Interior.Color = rngSelected.Cells(rndRow, rndColumn).Interior.Color
      rngSelected.Cells(i, j).Font.Color = rngSelected.Cells(rndRow, rndColumn).Font.Color

      rngSelected.Cells(rndRow, rndColumn).Formula = temp
      rngSelected.Cells(rndRow, rndColumn).Interior.Color = tempColor
      rngSelected.Cells(rndRow, rndColumn).Font.Color = tempFontColor
    Next j
  Next i

End Sub

What this code does

This code takes the currently selected cells and shuffles around the formulas, font colors, and background colors they contain. It does so by traversing through each cell in the selection, then randomly selecting another cell in that selection, and finally swaps each cell’s formula, font color, and background color.

How to use this code

NOTE: before using, I would recommend saving your workbook (or better yet, make a backup of your workbook) and copy/pasting your data to shuffle into another worksheet because you cannot undo anything VBA does to your workbook. Also, this might not be the most performant solution, so if you have a huge grid to shuffle, this might take a while. However, for relatively small grids (like this 12 by 12 example), this is a pretty good solution.

Here’s how to use this code:

• Add this code to a module in your workbook
• Select the range you would like to shuffle
• Run this code

Here’s what it looks like after running this code on the colored example I showed earlier:

Before:

After:

Much better.

I do need to note, however, that even though this does a pretty good job, it’s not without its faults.

What this VBA solution doesn’t do

• Copy conditional formatting
• Copy other formats (font weight, borders, border colors, etc.)
• Update any formula references relying on specific cells (meaning, if some formula was using A1 on the shuffled data, after the shuffling, the formula will still refer to A1…although this might be something you actually want to happen…)
• I’m sure there are other things I’m missing that it doesn’t do

If you wanted a more robust solution than this, the next thing I would recommend is to either find an Add-In that has a shuffle function (Ablebits seems to have a pretty good option (I have no affiliation with them at the time of this writing, btw)) or you can write a macro that will do basically a copy/paste from your original range onto a new worksheet (this will help with copying formats and conditional formatting, but may not work with formula references).

Either way, I hope this will help you solve your problem or at least give you some ideas to create your own solution. Thanks for spending the time to read this post.