Teachers often like to create different versions of the same test or final. The goal obviously is to prevent cheating. By creating many different versions of the same test, the student who looks over at his shoulders at his friend's test will see questions in a totally different order. This will obviously hamper his ability to "share" his answers with his friend. But how do you do this in a fast and efficient way? Most of us just cut and paste in Word to create different tests. However, if you write your questions in different cells of an Excel spreadsheet, you can quickly create many different orders for your questions using the Random formula and sorting from smallest to largest. Below are step-by=step instructions to use this feature.
- Open Excel and name column A in your spreadsheet "Questions". Type each test question in a separate row in Column A.
- Next to list, add a heading called Random
- Select all of the cells next to this column of Test Questions. Type =RAND()
- Hit Ctrl+Enter to enter the cells in all of the cells in the selection.
- Click "Sort and Filter" then "Sort Smallest to Largest" in order to sort by column B.
- Make sure to click "Expand the Selection" and then "Sort" to sort all of the rows.
- You now have a Random Sequence for your test questions. You can copy and paste these questions into MS Word.
- To create a second Random Sequence (different from the first) just press F9 on your keyboard which will automatically recalculate the formula as seen below.
- Now once again Sort<Smallest to Largest< Expand Selection< Sort. You will now have a new random list of questions which is different from the first.
