Editing forms for excel

Question

I have created a "form for excel". I would like to set up the excel sheet to auto-populate some data, based on data input via the form questions. However, when I've added columns to do so, the form no longer works when someone tries to submit answers. For example, I created a column that converts the number entered by the user to different units via the form using the formula =IF([@[Utility Type]]="Natural Gas",[@Use]/100), but then answers submitted by the user no longer showed up in the excel sheet. Is this possible to do?

Answer 1

Hello Mallory Geresy,

It is possible to auto-populate data in an Excel sheet based on data input via the form questions. However, adding columns to the sheet can sometimes cause issues with the form submission process. One possible solution to this problem is to use Microsoft Power Automate to create a flow that updates the Excel sheet with the calculated values, Here are the steps to create the flow:

1. Open Power Automate and click on Create.
2. Select Automated cloud flow.
3. Choose When a new response is submitted as the trigger and sign in to your Microsoft Forms account.
4. Add a Get response details action and select the form you want to use.
5. Add a Compose action and enter the formula you want to use to calculate the values.
6. Add an Update a row action and select the Excel sheet you want to update.
7. Map the fields in the Excel sheet to the corresponding values in the form response.
8. Save the flow and test it by submitting a form response.

Once the flow is set up, the Excel sheet will be updated with the calculated values whenever a new form response is submitted.

Hope this helps,

Fathia A