Forms Questions Pulling Data From Excel

Copper Contributor

Hi everyone,

 

I wonder if you can help me! I'm trying to setup an operational process between a requisitions form and a stock control excel spreadsheet.

 

The excel spreadsheet lists all of our stock, broken down via mac address for each device (IP phones, modems, networking equipment, etc.). This spreadsheet already automatically tracks stock levels, as well as a few other things.

 

I would like to setup a Form my team can fill in remotely where they can select specific devices for the warehousing team to then set aside for programing. This would automatically remove that device from the spreadsheet to more efficiently track stock levels. 

 

Is there a way to link a Microsoft From and Excel spreadsheet to accomplish this? If not, is there another way to accomplish the same goal? 

 

Thanks!

3 Replies

@BrendanFairTel 

Certainly! If you have questions related to creating forms that pull data from Excel, here's a brief guide:

Using Microsoft Forms with Excel:

1. Create a Form:

  • Go to Microsoft Forms (forms.office.com) and sign in with your Microsoft account.
  • Click on "New Form" to create a new form.

2. Add Questions:

  • Add questions to your form using various question types such as multiple-choice, text, or date.
  • Customize the form to suit your needs, including adding sections and branching logic.

3. Link to Excel:

  • To pull data from Excel, you can use the "Excel" tab in Microsoft Forms.
  • Click on the "Excel" tab, and you will have the option to create a new Excel workbook or select an existing one.

4. Select Excel Workbook:

  • Choose an existing Excel workbook where you want the form responses to be stored.

5. Map Questions to Excel Columns:

  • For each question in your form, you can map it to a specific column in your Excel workbook. This ensures that responses are organized correctly.

6. Share Your Form:

  • Once your form is set up, share it with your audience. You can share the form link or embed it in websites.

7. Responses in Excel:

  • As respondents fill out the form, their responses will be automatically populated in the linked Excel workbook.

Tips for Data Management:

1. Data Validation:

  • Utilize data validation in your Excel workbook to ensure accuracy and consistency in responses.

2. Pivot Tables and Charts:

  • Create pivot tables and charts in Excel to analyze and visualize the form responses easily.

3. Regular Backups:

  • Regularly backup your Excel workbook to prevent data loss.

Example Use Cases:

1. Surveys and Feedback:

  • Gather feedback from customers or employees and analyze it in Excel.

2. Event Registrations:

  • Allow people to register for events through a form, and have their details automatically recorded in Excel.

3. Quizzes and Assessments:

  • Conduct quizzes or assessments where responses are recorded in an Excel workbook for easy grading.

4. Order Forms:

  • Create order forms, and have the order details automatically recorded in Excel.

5. Employee Satisfaction Surveys:

  • Measure employee satisfaction with surveys, and analyze the responses in Excel.

By integrating Microsoft Forms with Excel, you can efficiently collect and manage data, making the process of gathering information streamlined and organized.

Hi @Tomsmith1122,

Thank you for your rapid response!

My only questions is where exactly do I locate the "Excel" tab? I cannot locate it in either the Forms home page or when creating a Form?

Thanks again!
Hi @BrendanFairTel,

there is an "Open in Excel"-Button if you have at least one answer, but its just a static Excel Export.

The only way i know is to open your Excel in Excel Online (Browser or Teams)->Tab Insert->Section Table->you have a Forms Button to create a new Form which is connected to your Excel. Unfortunately, you cannot use an existing form, but have to create a new one.

Kind regards