2

Is it possible to convert MCQs (Multiple choice question) on excel into Word?

I have an excel file which has many MCQs with the options and the correct answer (each one of them is in separate column) and I would like to get them into Word arranged like in a real exam on paper (question and then each one of the answers is in a different row)

I have to say that I don't know how to use Excel (except to open and read obviously) while I know very well Word, PowerPoint and Outlook.

In this file we see (from left to right)

  • A:something not important.
  • B: the question
  • C, D, E, F, G, are the options for the correct answer.
  • H: is the correct answer.
  • E:the year of exam which it was taken from it.

Basically, columns A and E are not important and I can give up on them.

enter image description here

2 Answers 2

4

Since you have a well-formed data source already set up, you can also use a mail merge for this.

Create a Directory style merge, link it to your Excel workbook, insert merge fields, and format the paragraphs as you like. For example, the column names in my Excel workbook are "Question," "Option A," "Option B," "Option C," and "Option D," and I want questions to be numbered and in bold, and the options to be lettered. So my merge document looked like this:

Merge fields

When I ran the merge, I got this result for my meager bank of three lame questions:

Merge result

You could also generate an answer key, since you've created a separate column for the correct answer. There are several ways to do this. For example, I generated a separate copy of the exam where an asterisk appears at the end of the correct answer. At the end of the paragraph for option A, I clicked Rules > If...Then...Else..., and specified that if the "Answer" column in my Excel workbook holds the value "A", Word should insert "*". I repeated this step for options B–D, replacing "A" with the appropriate letter. For example, here's the rule for option D:

If...Then...Else... rule

And here's the result of the merge:

enter image description here

Alternatively, I could have had Word add a paragraph after option D that said "Correct answer:" followed by the value from the correct answer column. Or I could have just generated a numbered list of all the values from the correct answer column.

0
1

You can rearrange it with a pivot table:

  • insert a header row in your data
  • go to insert - pivot table
  • add column with question's number
  • add column with question's number and all relevant columns as row headers
  • select question count in pivot table (this column is necessary to maintain original order instead of ordering questions alphabetically)
    • right click and select "field settings"
    • in "layout and print" tab uncheck "display labels from the next field ..."
  • you'll have increasing indent in the pivot table, but I think you can manage it in Word

enter image description here

You can also add other columns as filters (e.g. year)

1
  • Thank you 1+. I don't have an idea what it is but I will try it out. May 26, 2017 at 7:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .