how to sort multiple columns in excel not independently

#How to sort multiple columns in excel not independently how to

The RANDARRAY function returns an array of random numbers. Using a base list, a randomized list can be created using the RANDARRAY, SORTBY, and ROWS Functions. Using RANDARRAY, SORTBY, & ROWS Functions to Randomize a List

Now that we have our random list ready, we can select the column with the RANDBETWEEN function and delete it.

The numbers will recalculate (RANDBETWEEN too is a volatile function) which is not an issue since you will be deleting the numbers soon.

This will rearrange the names to be randomly jumbled.

Select any cell from the range and navigate to the Home tab > Editing group > Sort & Filter button > Sort Smallest to Largest (any of the two options will work).

In this formula, we use the RANDBETWEEN function to return a random number from 1 to 10.

In the column next to the names, enter the following formula for the RANDBETWEEN function:.

Then, we will use the Sort & Filter tool to rearrange those random numbers, which will also rearrange our list of names into a randomized list. The RANDBETWEEN function returns a random number between the specified range.įirst, we will use the RANDBETWEEN function to return random numbers against our list of names. Not very different from the method above – we’re only switching things up here, swapping the RAND function for the RANDBETWEEN function. Using RANDBETWEEN Function & Sort Feature to Randomize a List

The upside of this method is that you keep the original list and randomize that instead of creating a new list from the original as a base. Even if there are columns on both sides of the RAND function column, they will all rearrange linearly together. The data in any columns aligned with the RAND function column will also rearrange linearly. This means you can keep scrambling the data (names in this case) using the Sort & Filter tool until the randomness is good to go. By that, we mean the RAND is a volatile function and for all volatile functions, Excel triggers recalculation on every worksheet change. We mentioned in the steps that recalculation of the numbers is a part of random functions.

Now that we have our random list ready, we can select the column with the RAND function and delete it.

The numbers will recalculate (which is a part of random functions) and not to worry, since you won’t be needing them much longer. We will go with the first one – Sort Smallest to Largest. From the drop-down, select either of the top two options. Navigate to the Home tab > Editing group, select the Sort & Filter option.

Select any cell containing the number.

The RAND function returns random values that are greater than 0 but less than 1.

This formula is simply the RAND function applied on its own with no arguments.

Let’s start exploring the options and see what we can do to randomize lists. While we’re at it, you’ll also learn to use functions to make a random pick from the list.įor our example to be used throughout this guide, we will randomize the following list of names that are arranged in an alphabetical order:

#How to sort multiple columns in excel not independently how to

This tutorial will teach you how to randomize a list in Excel using functions alone and also with the Excel Sort & Filter feature. Or maybe you’re just randomly aiming to be an Excel nerd. You might want to rearrange dates or scramble a list of names or products. just to prevent tipping the balance that comes from good old alphabetical sorting.

You may want to randomize a list of names of students, employees, contestants, etc. Although we do have functions to help us along with that and that’s what we will be looking up in a bit. Excel is quite proficient with sorting lists alphabetical order, chronological order, ascending and descending order but it’s yet to come up with straight-up randomization (nearly oxymoron-ish). That is true for life and Excel and we are here for the Excel side of matters. Organizing is good but sometimes a measure of randomness makes things better.