How to Create Modern Excel Data Entry Form – EASY & NO VBA

In this step-by-step tutorial, learn how to create a modern Excel data entry form. With Microsoft Forms in Excel, you can connect a form or survey directly to your Excel sheet. You can create any number of questions, including multiple choice, free form text, drop downs, Likert scales, and more. You can make questions required, set validation criteria, limit responses to your organization, and much more. Once your survey or form is done, you can share using a link, QR code, or even embed the form into a web site. When respondents submit the form, the results go directly into your Excel spreadsheet and show up instantaneously. You can even build charts, pivot tables, and more from the results and they’ll update as soon as additional form submissions arrive. To use this functionality, you need either a work or school Microsoft 365 account.

• Requirements
  • Need a work or school account to use. Does not work with personal accounts.
• Steps
  1. Navigate to office.com. Click Sign in and login with your credentials
  2. Navigate to Excel on the Web
  3. Create new Blank workbook
  4. Click the Insert tab and choose the Forms button and choose New Form.
  5. This opens Microsoft Forms that can be used to create surveys and quizzes. This form is now connected to the Excel spreadsheet
  6. Choose form name.
  7. Click Add New to create a new question. It has some suggested questions you can use. Click the questions you want to include and select Add Selected.
  8. Build out the rest of the form using the options needed.
  9. Use Preview to view the form. It includes a preview of the desktop and mobile device experience and an estimated time to complete the form. You can also apply a theme to the form.
  10. Share the form to others using the Share button. It will provide a URL or QR for the form and an experience to allow you to email the form to others.
  11. In your worksheet a new tab has been added named Form1 with the survey questions as new columns that will be populated as users fill out the form.
• Additional resources
  • Office.com: https://www.office.com
  • Microsoft Forms Overview video: https://youtu.be/ouFKWHQMxtQ
• Timestamps
  • 0:00 Introduction
  • 1:01 Sign into Office.com
  • 1:29 Create a new Excel workbook
  • 1:50 Insert form into Excel
  • 2:11 Design form
  • 4:31 Preview form
  • 5:09 Set themes
  • 5:32 Settings
  • 6:11 Share form
  • 6:45 View form in Excel
  • 7:34 Edit form
  • 8:12 View form results in Excel desktop
  • 8:28 View results in Excel
  • 9:19 Get back to results through Forms
  • 9:53 Wrap up