• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Gather Data with Excel Forms

You are here: Home / Excel / Gather Data with Excel Forms
Gather Data with Excel Forms
May 13, 2020 by Mynda Treacy

Excel Forms are super useful if you collate data manually, for example via email or PDF forms. Or even if you’ve set up an Excel template for your users to enter data into, that you then spend time converting into a layout suitable for use with formulas and PivotTables.

Excel Forms don’t require any complex coding and those entering the data can access the Form from a web browser on any device without the need to even open Excel.

Excel Forms present a clean data entry front end based on questions and fields you set up:

gather data with Excel forms

And the data entered into the form is placed in an Excel file in the perfect tabular format ready for further analysis:

tabular format

Note: Forms for Excel is currently in Preview, which means the Excel team are still working on it and improvements are likely to be added over time.

Watch the Video

Subscribe YouTube

Creating Excel Forms

Option 1

For those without an Office 365 or Microsoft 365 account*, Forms can be created from the Microsoft Forms site:

my forms

*Sign in with your Microsoft 365 school credentials, Microsoft 365 work credentials, or Microsoft account (Hotmail, Live, or Outlook.com). Tip: you can get an Outlook.com email account for free 😊

Note: Live results in the associated Excel file are not possible when creating Forms from here. Instead use one of the following options to create your Form.

Option 2

Microsoft 365 and Office 365 users can create an Excel Form from Excel Online:

create an Excel form from Excel Online

Option 3

Or OneDrive for Business:

create an Excel form from OneDrive

Setting Up Excel Forms

After clicking the ‘New Form’ button start by giving your form a name (1) and an optional description (2). Then choose the type of question (3). My form is going to be used to capture daily sales data from 4 stores:

setting up Excel forms

Forms can also be used to create questionnaires or surveys, so the question types include everything from multiple choice options through to Net Promoter Scores.

Excel Form Question Types

There are 8 different question types to choose from, but there’s only 3 or 4 that you’d use when capturing data.

With each question you can choose whether it’s required or optional by switching the toggle at the bottom of the question.

Date Questions

The Date field will default to take the date format based on your regional settings*. You can see in the image below that mine are d/mm/yyyy:

date questions

*I’ve found this inconsistent. Depending on where you’re creating the form (Forms.Microsoft.com/OneDrive for Business/Excel Online) you can get different date formats irrespective of your regional settings.

Choice Questions

The Choice question is handy if you want people to choose from a list. Think of this as data validation where you can ensure the data is entered uniformly with consistent spelling and case. The Choice setting default are radio buttons, but you can choose a drop-down list from the ellipsis (see image below):

choice questions

Notice you can accept multiple answers by switching the toggle at the bottom of the question. It’s not relevant in my example but may be useful.

Text Questions

Use the Text field for capturing free form data entry like text or numbers. In my example I’m using a Text question to capture the sales figures:

text questions

Notice you can switch the toggle at the bottom to allow long answers, but because I’m collecting numbers, I want to set a restriction by clicking the ellipses:

restrictions

Tip: Notice above there is also an option for Branching, but that’s more commonly used in surveys.

Then choose “Number” from the drop-down list:

choose number from the drop-down

Notice you can choose from other restrictions like greater than, less than etc.

Add more questions by clicking the ‘Add New’ button. When you’re done, check the Preview in the top right:

preview

Formatting Excel Forms

And add a Theme if you like:

add a theme

Choose from the built-in themes shown above, or click the + sign to upload your own image or customise the colour:

customize theme

Sharing Excel Forms

When you’re ready to share your form, click the ‘Share’ button and choose from the drop down whether you want anyone to complete the Form or only those inside of your organisation, the latter requires Microsoft 365 or Office 365:

sharing Excel forms

Choose how you want to share your Form:

  1. Get a link
  2. QR code
  3. Embed the form in a web page or app
  4. Email

Notice in the image above you can also share the form as a template and collaborate with others inside your Microsoft 365 or Office 365 tenant.

Excel Forms Data

Once you’ve started collecting data, you’ll want to analyse it in Excel and the options will differ depending on the location you choose to set up your Excel form.

If you used Option 1 and set up your form via Forms.Office.com you get some high level analysis of the results on the ‘Responses’ tab, which is handy if you’ve created a survey, but if you’re using the form to collect data that you want to analyse, then you’ll want to open the Excel file. Clicking on the ‘Open in Excel’ button (see image below) will download the responses up to that point in time. The file is no longer linked to the Form, so any new responses that come in won’t be included. You’ll need to download the file again to get the updates.

open in Excel

However, if you have a Microsoft 365 or Office 365 account and created the Form via Excel Online (Option 2) or used OneDrive (Option 3), then in addition to the high-level analysis shown on the Responses tab, you can simply open the Excel file you created the Form in and your data will be there ready to analyse.

The important distinction is that options 2 and 3 maintain a live connection to the Form, so new data entered into the form is updated in the Excel file in real time (ok, maybe a second or two lag, but close enough).

standard data

The first 5 columns of the Table capture standard data:

ID – this is an automatically generated unique identifier for each record i.e. each form completed.

Start Time – the time the form was opened.

Completion Time – The time the form was submitted. You can use these fields to calculate how long it takes to complete the form.

Email and Name columns - If you’re collecting data from users in your Office 365 tenant and they’re logged in when they enter data in the form, their email address and name will be captured in the table in Excel. Otherwise, ‘anonymous’ is entered for those not logged into Office 365:

email and name columns

The remaining columns contain the responders’ answers to your questions.

IMPORTANT: Don’t mess with this table by adding/removing columns/rows, sorting etc. because it could corrupt the connection between the Excel file and the Form. If you want to modify the table for analysis you should open a new workbook and use Power Query to get the Form data and do your data cleaning etc. so it’s ready for analysis.

Limitations

Excel Forms are limited to collecting 2000 responses. If you require more, you can subscribe to Forms Pro.

Excel Forms Feedback

Forms is currently in preview, if you have any feedback for the Excel team, post it here on UserVoice.

Gather Data with Excel Forms

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category: Excel
Previous Post:charting real time data in excelCharting Real Time Data in Excel
Next Post:Show Report Filter Pages for Power Pivot PivotTablesshow report filter pages for power pivot pivottables

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.