Home » Blog » How to Setup a Blank XLSForm Template

How to create xlsform template

How to Setup a Blank XLSForm Template

When you first start to design a digital questionnaire in XLSForm – which works for ODK, ONA, SurveyCTO, and many other mobile data collection apps – then you probably want to have a blank XLSForm template setup and ready to go.

Setting up a blank XLSForm template means that when you need to create a new survey for your team, you can get started right away.  It’s so easy, saves time, and makes your work flow so much faster!  You’ll look very professional in front of your colleagues (and your boss!).

Before we start – you can download our free template so you don’t have to create one from scratch!!Download Now XLSForm Navy

1.  Set up your XLSform tabs

Step 1 set up your xlsform tabs

  • Open a new Excel spreadsheet
  • Save it as “Blank XLSForm Template”
  • Create 4 tabs in your workbook and name them as follows
    • survey
    • choices
    • settings
    • external_choices

2. Set up your “survey” sheet.

Step 2 set up your survey sheet on xlsform template

  • In your survey tab, add all the possible column names across the first row of your sheet. Name the columns as follows:
    • type
    • name
    • label
    • required
    • hint
    • constraint
    • constraint_message
    • relevant
    • required_message
    • calculation
    • repeat_count
    • appearance
    • choice_filter
    • default
    • read_only
    • media::image
    • media::audio
    • media::video
    • body::accuracyThreshold

Download Now XLSForm Green

  • ODK keeps adding new column names to their software so that it has enhanced capabilities. Just in case they’ve added new columns recently, go check out the most up-to-date list of XLSForm column names in their documentation.
  • Set up your XLSForm template for multiple languages. In your survey tab, these are columns that can be translated into multiple languages:
    • label
    • hint
    • constraint_message
    • required_message
    • media::image
    • media::audio
    • media::video
  • If you frequently create surveys in more than one language, then amend those column names to show your primary language, and create additional column names with your language variations.
Column names (multiple languages: showing English, Arabic, and French)
Change column names with your primary language: Add new column names with your second language: Add new column names with your third language:
label::English label::عربى label::français
hint::English hint::عربى hint::français
constraint_message::English constraint_message::عربى constraint_message::français
required_message::English required_message::عربى required_message:: français
media::image::English media::image::عربى media::image:: français
media::audio::English media::audio::عربى media::audio:: français
media::video::English media::video::عربى media::video:: français
  • Now set up your “metadata” questions in your survey tab. These questions should be included on every new survey you create. That way you won’t forget to capture all the data possible about when each form was filled out and which device filled it out.
    • Under the “type” column and the “name” column, put in the following questions:
type name
start start
end end
today today
deviceid deviceid
subscriberid subscriberid
simserial simserial
phonenumber phonenumber
username username
email Email
audit audit
  • A new feature that ODK has recently added to their software is the ability to add an “audit” feature to surveys, which captures details about how a data collector moves through a form and how long each question takes, for example. In order for this piece of metadata to work, you need to be using ODK Aggregate 1.5.0+ as your server.

3. Set up your “choices” sheet.

Step 3 Set up choices tab in XLSform Template

  • In your choices tab, add all the possible column names across the first row of your sheet. Name the columns as follows:
    • list name
    • name
    • label
  • If you are using multiple languages in your XLSForm template, your “label” column can also be multi-lingual. Similarly to the “survey” tab, you can amend your “label” column name to be “label::English” (or whatever your primary language is), and then add additional columns with your additional languages.  You must spell these language names EXACTLY THE SAME as the languages you added in your “survey” tab.
Change column names with your primary language: Add new column names with your second language: Add new column names with your third language:
label::English label::عربى label::français

4. Set up your “settings” sheet.

Step 4 Set up settings tab in XLSform Template

  • In your settings tab, add all the possible column names across the first row of your sheet. Name the columns as follows:
    • form_title
    • form_id
    • public_key
    • submission_url
    • default_language
    • version
    • instance_name
    • style
  • Set up encryption for your XLSForm template next. Although many people may not use encrypted forms, my recommendation if you are collecting beneficiary data for humanitarian or development projects is that you MUST protect their personal and sensitive data, so I would recommend you always use encryption on your forms.
    • Once you’ve created your private and public encryption keys for your team/project, and have stored your private key somewhere safe (don’t share this with anyone!), then copy and paste the public key into your settings tab under the “public_key” column.
  • Enter in the URL of your server into the “submission_url” column.
  • If you’re using multiple languages in most of your surveys, then add in your default language under the column “default_language”. If your data collectors collect the data all in a local language, then put the local language name here, so they don’t have to switch languages every time they fill out a new survey form.
  • You can have a naming convention for each new survey form that your data collectors submit. Enter the naming format into the column “instance_name”
    • For example, concat(${today}, ‘-‘, ${deviceid}, ‘-‘, uuid())
  • In the XLSForm template, leave “form_title” and “form_id” blank, because if they are blank, they will automatically show the name of your saved survey form. However, you can fill in an example form title and form id in your XLSForm template to remind you what to fill in when you’re creating a new survey.
    • For example, under form_title column, type in “Title in Collect App – Bilingual – عنوان ثنائي اللغة”
    • For example, under form_id column, type in “title_in_xml_english_version”

Download Now XLSForm Turquoise

5. Set up your “external_choices” sheet.

Step 5 Set up external_choices tab in XLSform Template

  • This tab is set up exactly like your “choices” tab.
  • You only need to use “external_choices” in some surveys when you have very big lists of selection choices. My recommendation is to set it up for your blank XLSForm template, but then when you’re creating a new survey, to delete this tab if it’s not needed.
  • In your external_choices tab, add all the possible column names across the first row of your sheet. Name the columns as follows:
    • list name
    • name
    • label
  • If you are using multiple languages in your blank XLSForm template, your “label” column can also be multi-lingual. Similarly to the “survey” tab, you can amend your “label” column name to be “label::English” (or whatever your primary language is), and then add additional columns with your additional languages.  You must spell these language names EXACTLY THE SAME as the languages you added in your “survey” tab.
Change column names with your primary language: Add new column names with your second language: Add new column names with your third language:
label::English label::عربى label::français

 

6. Finalize your Blank XLSForm Template and save it!

Step 6 Finalize and save the XLSform Template

  • You’ve now completed setting up your blank XLSForm template!
  • Press “save” and get going on creating new surveys faster!

Download Now XLSForm Navy