Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, Dennis Taylor shares easy-to-use database commands and methods for maintaining an Excel database. The course covers sorting, adding subtotals, auto-filtering, and using the Excel Advanced Filter feature and specialized database functions.
You may have the occasional need to rearrange data randomly. That is, sort this list in a random order. Now, why might you want to do something like that? Maybe you want to review the accuracy of your record keeping and you'd like to extract from here, copy from here, 8 or 10 records, paste them somewhere else, and review the data at hand. And rather than just doing it in a freeform way, let's actually rearrange this data in a random order. I think you could also imagine this on a sales environment. You're trying to analyze orders, inventory, processing, shipments, those kinds of things.
Maybe you don't have a formal Quality Control department, but you just want to pick random records at times. Let's randomize this list. Now, we can either add a new column over to left of column A, or possibly off to the right here. Maybe we haven't put in the new salaries. If we did, we'd create a new set of data in column N, but we'll just use this right here. What we're about to do is to simply use a function that perhaps a lot of you have not used. It's a mathematical function, =rand, and its sole purpose-- and you need to only type =rand( and then Enter.
Its sole purpose is to put in a random number between 0 and 1. And to fill in this particular function throughout the depth of the data here, simply double-click the lower right-hand corner. So here are a series of random numbers and this function has an unusual characteristic about it too, is if we manipulate data elsewhere or make a change in other parts of the worksheet, all of these numbers get regenerated. But our purpose to put it in here is simply to use this data as a basis for sorting.
So we'll simply click on one of the cells in Column M and click AZ. Now, although we won't see what's actually about to happen here, we will rearrange the data based on these numbers, but in the process the numbers were all changed. So it's like destroying our paper trail. I am going to click AZ and scrolling left towards here, I think you can see this data is in no discernible order whatsoever, certainly not in order by Employee Name or Department or Building and or any other field that you look at. This has been randomized.
It's in no particular order. And meanwhile, those random numbers got regenerated, and changed. We don't need them anymore. We can either delete the whole column or if we want to leave the label there. Just click here. We could double- click to copy down the column again and simply press Delete. But the main idea is we've randomized the list and now perhaps we'll copy the first 10 or 15 elsewhere. Review the accuracy of our data. That was the whole purpose. So using the rand function temporarily in a list for sorting purposes is something you'll need occasionally and it's easy to set up as you just saw.
Find answers to the most frequently asked questions about Managing and Analyzing Data in Excel 2010.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.