Word form data to Excel

| February 8, 2012 | 0 Comments

Learn how to create a form in Word and how to import its saved data into Excel.

Creating a form in a Word document is one half of a data collection process. Once the form is completed, you will need to extract the data so you can do something with it. Here I will show you how to use the legacy form controls to create a form in Word and how to get the data into an Excel file.

Get the Developer tab

To get started, you’ll need to create a form in a new Word file. I’m using Word 2010 but the process is the same in any version of Word. In earlier versions the forms tools can be found by choosing View > Toolbars > Forms. In Word 2007/2010 the form tools are on the Developer tab – in Word 2007 choose Office Button > Word Options > Show Developer tab in the Ribbon.

show developer tap in ribbon

Create a form

My form includes fields for the name, email address, check boxes for the operating system, a combo box for the application they’re having problems with, and a small box for comments. This is the kind of form that could be used for a computer support desk for example, but your form can gather any kind of data.

The form is created using Content Controls from the Developer tab in Word 2007/2010. You need to use the Legacy Forms controls as the newer ones don’t work for this process. Each textbox is created using a Text Form Field and the combo box is created using a Drop-Down Form Field. The checkboxes are created using a Check Box Form Field.

word legacy controls

Add all the objects you need to the document and right click each control in turn to access the Properties for that control. For example, for the Drop-Down Form Field Options box you’ll need to add items for the list.

drop down list control panel

Protect the form

Once you’ve done this, protect the form by choosing Restrict Editing (Protect Document), enable the ‘Allow only this type of editing in the document’ checkbox and choose Filling in Forms from the list. Click Yes, Start Enforcing Protection.

restrict editing

Save the form template

Save the file as a template by choosing File > Save As. From the Save As Type dropdown list, select Word Template (*.dotx) and then give the template a name. From the Favorite Links at the top left of the dialog, select Templates, to locate your Word templates folder. Once you’ve saved the file you can close it.

save as a template

Use the new form

To complete the form choose File > New > My Templates and then select and open the template.

selectig the template file

Complete the form and then choose File (Office Button) > Options > Advanced and from the Save category of options, select ‘Save Form Data as Delimited Text File’ and click Ok. When you save the file it will save as a plain text file containing the data only.

save form data as a delimited file

Import form data into Excel

To import the data into Excel start by opening Excel and choose Data > From Text and locate the text file containing the saved data. Select Delimited, click Next, select Comma and deselect Tab and anything else that is selected, click Next and then Finish. Select the cell for the data (A1) and click Ok.

step 1

To add data from another file you will repeat the previous step but, to do so you must leave a blank row between the data that you previously entered and this new row. If you are positioned in the row immediately below the data the Data > From Text option will be greyed out.

step 2

Once you’ve finished importing all the data, you can delete the blank rows that you had to leave. Insert a new first row into the worksheet and add the column headings for your data. Save the file and you can now analyse the data or open it later on to add more data to it.

step 3

 

 

Tags: , , , , , , , , , , , , , , ,

Category: Word

About the Author ()

Helen Bradley is a lifestyle journalist specializing in Photoshop, Lightroom, photo editing, web design, Visual Basic and Office software. She writes how to articles, tips and projects and produces how to videos for computer magazines, newspapers and online services in the USA, Australia, Canada and the UK. She writes for PC World, SmallBusinessComputing.com, Practical Photoshop and Digital-Photography-School.com.

Leave a Reply