Exporting responses - Working with .xlsx files

When you download responses from an Online Survey in Citizen Space, there will be in an .xlsx file, a plain text data format which commonly opens in Microsoft Office Excel or in Numbers on a Mac. With the .xlsx format you can then format and analyse responses within Excel, making the most of its powerful functionality, or import the data into other databases or systems for analysis. It's recommended you 'Save As' an Excel Workbook first.

It is undeniably the best tool for the job, however we do receive a couple of common questions about the format, in relation to how it displays response data from Citizen Space in Microsoft Office Excel:

Date formatting

In the export generated by Citizen Space, there are 3 cells per response which contain an automatically generated date and time:

Created Date
Last Modified
Submitted Date

Excel automatically recognises certain number formats, such as currency, date or time, but it struggles to recognise date and time together as a standard format. The result is that these 3 columns may display an arbitrary number within the cell (such as 23:18.5) - although the date and time will display correctly in the formula bar, and Excel will sort them correctly in chronological order.

However, if you find this frustrating, here's what you can do:

Select the relevant columns.
Go to Format Cells.
Select the Number tab.
Select Custom from the list on the left (it's the last one).
Select dd/mm/yyyy hh:mm (or whichever is best for your purpose).

These instructions might vary slightly depending on which version of Excel you are using.

Character Limits

When you open an .xlsx file in Excel, a field (or cell) has a maximum limit of 32,767 characters. So occasionally, a respondent may exceed this limit in a free text, qualitative answer. When this happens, the following cells get knocked out of sync, so any responses that follow won't be in the correct column and there will be more rows than you have responses.

In this scenario, we recommend identifying the response(s) which has caused this (it will be quite easy to spot - everything before it will look as it should) and extracting the answer which has caused the error (leaving the rest of the response in place, and an identifying note or tag in the affected cell that the answer). Alternatively, you can email support@delib.net and we can look at it for you (although please don't email the .xlsx export — just tell us the title of your activity).