Mail Merge Question Bank

Download the current version.

How to Use

Question Bank.xlsx represents the catalog of all available multiple choice questions. Every time the file is saved, the random ordering of both questions and answer options is updated by Excel.

Test Generator.docx uses the question bank to generate an actual, formatted test. If changes are made in the spreadsheet, it must be saved and the Test Generator must be closed and re-opened.

In Question Bank.xlsx

On the Question Bank worksheet...

Add a new question

In the next available row of the Question Bank, enter the question in the Question column, followed by the multiple choice options in each of the Option 1, Option 2, etc. columns. If there are fewer options than columns, fill the columns from left-to-right (leave higher-number options blank).

Indicate which answer is correct in the Correct column by entering the number of the correct option.

Add subject tags to in the Tags column. Subject tags should be all lower case, separated by commas.

If the gray text columns do not populate as you go, you may need to "fill down": select the last row of populated gray text, position the mouse at the bottom-right corner of the selection, and then drag the box down (a lot) more rows to fill in those rows with the formulas necessary.

Some more complicated formatting can be tricky in Excel. Some quick hints:

• Line breaks can be entered within a cell by pressing ShiftCommandReturn

• Tab characters can be entered by copying-and-pasting them from another app (e.g. Word). Disconcertingly, they will not be shown except when you are editing that cell, but they will be stored and show up in the Test Generator.

  For example, a question that looks like this:

  Which of the following is true?

  I. Space is a vaccuum.

  II. Vaccuums are in space.

  III. The Ort Cloud is a dust filter.

  ...might be typed into Excel thus (assuming that a Tab was already in the clipboard):

  Which of the following is true?ShiftCommandReturn

  CommandVI. Space is a vaccuum.ShiftCommandReturn

  CommandVII. Vaccuums are in space.ShiftCommandReturn

  CommandVIII. The Ort Cloud is a dust filter.

Manually select a question

You can manually select a question for inclusion in the next generated test by entering Yes in the Manual Select column.

Edit an existing question

Edit only the fields with black text: fields with gray text are automatically generated by the spreadsheet formulas.

Delete a question

You may delete the entire row (right-click on the row the number).

On the Parameters worksheet...

Select questions by tag

In the yellow zone of the Select Tags column, you can enter up to 20 subject tags that will be automatically selected for inclusion in the test, one tag per row.

Identify "All of the above" questions

One style of question -- multiple options, followed by the option "all of the above" -- is best randomized with the "all of the above" coming consistently at the end of the list of options.

You can maintain a list of "all of the above" equivalent options that will similarly be the consistently last option if they are given (last) in the list of options. Edit the yellow zone of the All of the above column to list all possible matches.

In Test Generator.docx

The Test Generator.docx file formats the selected questions for use in a test or answer key.

The first time you open Test Generator.docx, you may need to update the link to the Question Bank.xlsx file.

Click Find Data Source... and locate Question Bank.xlsx (probably in the same directory as Test Generator.docx).

When you open the Test Generator.docx, Word will give you a warning about opening the question bank Excel file:

Click Yes...

...and then OK in the next dialog which should default to opening the Question Bank.

When the file opens, do not play with the formatting (there are a lot of Word field codes hidden in there that you don't want to scramble). If you want to see all the field codes (useful if you do want to play with formatting), press Alt/OptionF9

Under the Mailings ribbon, you can...

Generate a Test

Click the Finish & Merge button in the ribbon, and choose Edit Individual Documents…. When asked if this is the test key, answer either yes or no and click OK. A new, generated document will open with the selected questions inserted and formatted.

You can save this document, edit it, append it to other questions, etc.

Filter Questions

If you click on the Filter Recipients button in the ribbon, you can choose different filter criteria to select questions. By default, the filters are set to work on the Manual Select and Tag Select fields of the question bank spreadsheet:

Sort Questions

If you click on the Filter Recipients button in the ribbon and choose the Sort Records tab at the top of the window, you can alter the sort order of the questions. By default, the sort order is based on the randomization set in the Random Order colun of the question bank.

Adding Existing Tests to Bank

Obviously, this can be a tricky conversion. Import Existing Test.xlsx operates on the assumption that your test is formatted something like Sample Test.docx. The basic gist of how to do this is that you:

1. Copy and paste the multiple choice questions into the Paste Test in Cell A1 worksheet of Import Existing Test (in cell A1 -- it will overflow down the worksheet and that is what should happen).
2. Flip over to the Copy-Paste as VALUES into Bank worksheet, select the now-organized questions and options and copy them to the clipboard.
3. In Question Bank.xlsx, position the cursor in the next available Question column, and then -- this is important -- choose Paste Values from the Paste drop-down in the ribbon.
4. Laboriously sift through the results in Question Bank and delete all of the 0 options that Excel has willfully added.