In last week’s episode, we learned that you can use the Excel spreadsheet you get as a result from your Form, to create a Microsoft List. In this case I needed the easy data entry in Forms, but wanted to move the data (using a workflow) into a corresponding SharePoint list in order to facilitate a process.
This time I checked what happens if I use the other answer types (Date, Ranking, Likert, File Upload and Net Promotor Score) as a basis for a new List. I set up a Form with those fields, entered one response, downloaded the Excel and imported that into Microsoft Lists.
Let me share the full “Translation” here, so you have everything in one place.
|Field type in Forms||Suggested Column in Lists|
|ID||Do not import|
|Choice||Single line of text|
|Text short||Single line of text|
|Text long||Single line of text|
|Ranking||Single line of text|
|Likert||Single line of text (one per statement)|
|File Upload||Single line of text|
|Net Promotor Score||Number|
Please note that the “Number” columns have more options to select from than the columns identified as “Single line of text”.
- By default, the Date answer from Forms is translated into a Number column in the List. If you want a proper date in your List, make sure you change these during import, as you can not change into a Date and Time column after import.
- A Likert scale answer will provide you with one column for every statement (=row). I have never liked these question types, as they are a lot of work, but they also provide a ton of clutter in your list 😁
- The NPS gives you just a number, not the calculation of course.
- The File Upload option in Forms gives you an ugly URL. Sadly there is no option to change this column into a Hyperlink column.
I will repeat my suggestions from last time, and have added some new ones, so you have them in one place.
Forms design suggestions:
- Collect requesters’ email addresses (and names) by default in the Form. Those will be captured in the Excel automatically and can be pushed to the List, saving your users time in entering this info manually.
- Try to think of a unique identifier in your Form that you can use to fill the Title field in the List.
- When you enter your first item to create the Excel, use short dummy text to avoid scrolling when importing the Excel. (How do I know that, you ask? 😉)
- Select “Do not import” for the Excel columns “ID”, “Start Time” and “Completion Time” unless you really need those. (see next item)
- Make sure you map the Title column first when you create your List, or Lists will keep making suggestions until that field is mapped.
- If you have a Date column in your Form, other than the Start and Completion time, change that into a Date and Time column during import, as you will be unable to change it later.
- The Net Promotor Score will only return the number of each response, so think carefully if you really want to import this column into the list. The complete calculation, and the graphic, is nicely done in Forms and it may be easier to check that.
- Is the Start Time of the Form entry important, e.g. if these are requests and you need to sort those in order of entry, or calculate a response time? Use the default “Created” date/time of the item in the List. The workflow may have a few seconds delay, but it is usually the date that is important, not the exact time. This allows you to skip the date columns from the Forms/Excel during import.
- If you have Choice fields in your Form, it makes sense to configure the corresponding columns in the List as Choice fields and add the values. This will allow you to make use of List column formatting, such as displaying each value as a “coloured choice pill” for easy recognition. You can do this after import.
Yes, it is certainly possible to use the Excel spreadsheet that is produced from your Form, as a basis for a Microsoft List. However, the import is pretty basic (Numbers and Single Line of Text fields by default), so you will need to think carefully about how to import each answer, because you can not change all of them afterwards.
If your Form is very long it can certainly help, but if your Form only has a few questions, I think you can just as quickly make a list from scratch and make sure that all columns are correct from the start. But of course one wonders if a scenario like this was in scope when developing all this functionality.
Do you have any experience with this kind of set-up, and if yes, do you have any tips or tricks to share?