Questionnaires are a great way to collect customer insight or gather key information from others. There are many online tools to build and distribute questionnaires. However, they typically work well if your questionnaire doesn’t have too many questions, or lacks business logic and calculations. What if you have a questionnaire with hundreds of questions, or a questionnaire that’s supposed to generate customized results based on selected answers. There are many applications for these types of questionnaires like collecting financial data, security details, medical data, government permit applications, or other similar information where preferences or facts need to be gathered for better decision making.
Unfortunately, most online tools that offer these types of services don’t come with the flexibility and ease of use Excel has to offer. That’s why most companies prefer creating and distributing these types of questionnaires in Excel, because it’s easier to build the interface, as well as the calculation logic. Excel’s other features like data validation and conditional formatting are also a plus as they guide the end users as to what they’re supposed to do with certain questions.
Although Excel is fairly easy to use and comes with many features, its Achilles heel is collecting and aggregating the data. Since workbooks are sent to the users via email or other means, you will probably end up with dozens, hundreds, or maybe even more spreadsheets sitting in a folder. Since the admin user’s desktop essentially is the data storage, most of the time it won’t be accessible by others. The other piece of this problem is that aggregating data from multiple workbooks is usually not an easy task. Excel can work very well across workbooks, but when working with hundreds of them, things can get ugly quickly.
Another challenge in Excel is the lack of workflow features. These complex questionnaires usually are a part of a larger system where evaluations must be made by different people or departments. Take government permit applications for example. These types of questionnaires typically require a multi-step approval mechanism. In other words, the information needs to go from one person to another for review and approval and this means more room for error when workbooks are passed on here and there.
Moving this process to a web-based application with centralized control can greatly reduce the email and workbook traffic. Let’s take a look at an example. Companies conduct research using questionnaires like the one below to check whether their vendors comply with their security standards. Here, each question has a certain weight and specific answers to some questions can raise a red flag, meaning that something is off.
This is a rather complex questionnaire consisting of 205 questions, spanning over 12 pages. Input layouts guide users and help them enter data faster and easier - Some questions require Yes/No type of radio buttons, some file attachments, and some allow selecting an answer from a dropdown list. Furthermore, a few of these questions are shown or made hidden depending on the user input in another cell, so it’s a fairly dynamic model.
Answers to each question bear a weight which will be aggregated and affect the final decision for each case. This calculation logic was created in Excel, using coefficients for answer intervals. For example, selecting between 6 and 20 into the question “How many individuals within the organization are assigned full-time to information security?” will give a coefficient of 0.75, while selecting 1 to 5 will be 0.50 by weight.
At the end of the questionnaire, a summary page that is only available to the admin users lists the completion rate, final scores on each category, and indicates whether there are any red flags. Some factors are simply too important and can make or break a deal. For instance, saying ‘No’ to the question “Does the vendor perform risk assessments on its third parties?” will raise a red flag and the system will display that something is wrong in the summary page. The company can decide on these parameters if they want to work with this firm.
The underlying logic consisting of weight distribution of each answer and the summary page data, like completion rate and red flags were created in Excel. The workbook was then transformed into a web application using SpreadsheetWEB. The spreadsheet provides all calculation data, while the software ensures that all information is transferred into a web interface. The end product is essentially a web application with an elegant design that can run complicated calculations, and has database integration for capturing inputs. A centralized platform helps store data in a scale-able way, while ensuring that all end users access the latest version of the questionnaire. Not to mention that workbook transfers and constant communication with the end users will be redundant at this point.
The summary page is reserved for the company that is conducting this survey. Therefore, it must be hidden from the end users. The web application allows for checking the user role in the system and only displaying this page when accessed by an admin user.
A questionnaire with hundreds of questions can be hard to finish at one sitting. Users will want to go back and forth, save their data and come back at a later time. Page transitioning can be done easily using the left-hand menu. This also provides a nice overview of the entire system. When it’s time to take a break, users can save their progress by pressing the save buttons placed on each page. Saved data can then be accessed from the SpreadsheetWEB control panel. Obviously, users shouldn’t see each other’s data, and this can be configured through SpreadsheetWEB’s group management module.
This questionnaire was created in about 3 hours from scratch. This includes creating the calculation logic in Excel, uploading the workbook into a SpreadsheetWEB server, and building the web interface using the designer. Building a complex questionnaire like this would have taken days if not weeks using traditional approaches.
Since data is collected in a single accessible location, it’s extremely easy to produce a real-time overview of the system for an approval/denial mechanism. As soon as a record is entered, admin users can review it and make decisions in real-time.
Questionnaires play a very important role in decision-making processes. Businesses prefer creating them using the tool that they’re most comfortable with: Excel. Although Excel is a great platform to build these types of forms, moving them to the web can alleviate most of the challenges that comes with using a desktop software for collecting and processing data. SpreadsheetWEB can help you tap into your Excel expertise and transform a complicated questionnaire into full-blown applications with centralized control in no time.
