Google Sheets is great, but the main disadvantage it presents for writing question banks is that you will need an internet connection to use it. If you would like to write your questions offline, you will need to put a (QB)² sheet into Excel, and then once done, put it back into Google Sheets and export as normal.

Setup

Template

When starting a brand new question bank sheet, we recommend downloading the following template:

Other options include generating the excel file from a Google sheet:

  1. Either grab a blank template from here, or generate it on Google Sheets using the Add-ons menu:

    1. Add-ons > (QB)² > Question Bank Quick Builder > Set up (QB)² spreadsheet

  2. Download the template as an Excel readable file (.xlsx):

    1. File > Download > Microsoft Excel (.xlsx)

  3. Open it in Excel. It should look like this:

Writing New Questions

Writing questions in Excel is very similar to writing them in Google Sheets.

  1. Start off by writing the categories in for the questions in the Categories sheet. Here, we will make two quizzes, a midterm, and a final, but it can be anything.

  2. Choose a category and question type for your question on the Question Bank page. Here, we are doing a short answer for the midterm.

  3. Write in the question, answers, and corresponding point values. In this case, we asked “What unit (equivalent to 4 inches) is used to measure the height of horses? The answer is “Hands”, and any other answer will result in zero points awarded (though partial credit could be given by changing any particular answer’s value to something above zero).

    1. Repeat until you are done writing questions!

Important Differences From Google Sheets

There are a few differences between Google Sheets and this spreadsheet that you’ll need to know to ensure a smooth transition from here to your export.

True/False Questions

For True/False questions, the spreadsheet does NOT auto fill out the words “TRUE” and “FALSE” for the answers. These need to be put in by the user exactly in all capital letters.

Finishing Up

  1. Save the spreadsheet and go back to your Google Sheets.

    1. Open the spreadsheet by clicking: File > Open


      Then drag the file into the box in order to open it.

  2. Set up the spreadsheet again by going to the menu above the spreadsheet like we’ve done before (except now there will be data in the spreadsheet).

    1. Add-ons > (QB)² > Question Bank Quick Builder > Set up (QB)² spreadsheet

    2. You will receive a warning about resetting the spreadsheet. This is okay, since the only thing that will be overwritten are the headers in the spreadsheet. Click Ok.

    3. The reason for this is that there is currently a bug where the dropdown menus for the categories column won’t link up with the categories sheet. Resetting the spreadsheet quickly fixes this.

  3. You are done! Now that you have loaded your spreadsheet, you can export it as an XML the same was as you normally would with (QB)².