Microsoft Forms: Validate User’s Data

I got this question from a friend about how to validate user’s data. I’m paraphrasing, but here’s the gist of it:

I have an Excel file that my subordinates use but they mess up the data all the time. I want to validate the data so that the reports are generated correctly.

This is quite a common problem. It’s not directly related to Microsoft Forms, but it’s something that it can solve beautifully and save you a lot of time.

If you’re not familiar with Microsoft forms, I have another article where I explain some of Microsoft’s Forms fundamentals so that you have a starting point.

The mindset

We think of Forms as something used to propagate to a huge number of users to collect information. For example, if you want to get the employees’ satisfaction, you create a form and propagate it in the organization. But Microsoft Forms can and should be used in much smaller environments. For example, within a department, to collect holiday information. Going back to the first example, one can create a Form with all the data that the user needs to fill in. With the internal validation capabilities of Forms, you can ensure that the data is submitted correctly. You can also take the extra-step and transform the data in Power Automate.

Finally, there’s an amazing advantage of having a Form. If you change the destination, for example, from an Excel to a SharePoint list, you need to change the Power Automate, and the users won’t even notice the change. It’s a lot better than having to say to everyone. “Forget about this Excel file, and now go, fill-in, the SharePoint.”

How to do it?

There’s a lot of data that you can validate, so let’s go over some quick examples.

Set of defined options

One of the biggest issues is that if you want to segment data. Segmentation requires that you have a small defined group of data. You can create it in Excel, but it’s much nicer to have something like this:

With this simple question, we’re doing a bunch of things at the same time:

1. Ensure that the department is always filled in the same way. Some people could write “HR,” “Human Resources,” or “HR Department.” People are creative, and this way, the information is always the same.
2. Have multiple answers. In Excel, you would need to ask people to separate the answers. This would exacerbate the previous issue 10x.
3. Make the information mandatory. People have to choose one. Again, in Excel, people will be tempted to “jump” certain sections if they don’t know the answer. Then the data would be incomplete.
4. Have a description of what the data is used for. This will help people to understand the context, increasing the probability of people inserting the correct data.

In one simple step, we have a huge number of advantages right out-of-the-box.

Dealing with dates

We all know that it’s a nightmare to ensure that the dates are consistent for us that deal with dates. If you have a multi-language and culture, it makes the problem even worse. Americans write month/day/year, while in Europe, we (correctly) write day/month/year or year-month-day. There are already 3 variations that can bring a lot of errors.

The above example solves this issue by guiding the person of the format. It’s even better because you have a way for the users to select the data and not worry about the format.

With this, you gain:

1. The clarity to the user. They know what date they are inserting and what’s the context.
2. Validation. The date is always correctly validated, and you’re sure it’s always correct.
3. Easy to use. It’s a lot better to pick a date from a calendar than writing it.
4. Integrity. The data will always have the same format. There are hundreds of formats, but you always get the same to parse.

Statistical data made easier.

Another point of confusion is when you ask people to rate something. Let’s take the following example:

If you ask this in an Excel file, people would think:

1. Is 10 better or worse?
2. Can I insert 11?
3. Can I put 5.3?

I know this may look extreme, but if you deal with statistical data, you know that these cases show up, and you have to think about them. With a Form, you don’t need to worry. It’s clear to the user, and the data is always validated correctly.

Final thoughts

There is a part missing in this article. When someone submits the information, how do I parse it? Luckily I already wrote an article showing you how to do this. Several in fact:

1. How to format Submitted Data.
2. Save multi-choice Microsoft Forms.
3. Fundamentals.

These articles will show you how to get the data and parse it using Power Automate and get the information in your destination storage.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Microsoft Forms-related articles here.

Photo by Aleks Dorohovich on Unsplash