Create a Form in a Spreadsheet

A form makes it easier to capture, organize, and edit information. It’s a great idea to use a form if you need to enter a lot of data for a list or capture results for a survey. Every form contains fields. (A field is a box where you enter data.) A form can consist of different types of fields – simple or complex. Simple fields allow you to type the data you want. Complex fields include “list boxes” that allow you to select from a specific list of items and “spin buttons” that allow you to choose a value. This article helps you create forms using either Excel or Google Forms, a component of Google Docs, Google’s free web-based office suite.

Steps

Creating a Data Entry Form in Excel

  1. Decide if a data entry form works best. Use a data entry form when you have a simple form of text boxes that list the column headings as labels. A data entry form allows you to view and enter data more easily than moving from column to column, especially when you have multiple columns of data that do not fit on your screen horizontally. For example, you may have a customer listing with contact and order information that spans 10 columns. It is much easier to enter name, address, phone number, email, order date, order type, etc. with a data entry form than entering the details directly into each cell in each column.
  2. Add the titles to be used in the data entry form. Enter a title at the top of each column you want to use in the data entry form. Excel uses these titles to create fields on the data entry form.
    • For example, if you have “Name, Address, Phone number, Email, Order Date, and Order Type” as your column titles from left to right in the worksheet, you will see those same titles in the dialog box (data entry form), but listed one under the other.
    • Before you take the next step in the process, check that you have the Form button available on the Data tab. If you do not, add the Form button to the Quick Access Toolbar. Click Customize Quick Access Toolbar, select the More Commands option, change Popular Commands to All Commands, scroll down the list and click on Form. Then click on the Add button, and click OK.
  3. Open the data entry form to start entering data. Click in any cell below the titles and go to the Data tab. Find the Form button and click on it. Excel displays a dialog box, showing each column title that you entered earlier in the worksheet as a vertical list of fields. The dialog box acts as the data entry form.
    • The Form button is either on the Data tab or the Quick Access Toolbar.
    • If you have named the worksheet you are working in, the dialog box (data entry form) will have a title. The title will be the same as the name of the worksheet. If your worksheet does not have a name, the title of the dialog box (data entry form) will be the word ‘Sheet’ followed by the worksheet number.
  4. Enter data for a new row. Use the TAB key to move to the next field in the data entry form. Press the RETURN key after entering data in each field in the data entry form. The data entered will be added as the next row in the list on the current worksheet.
  5. Click the Close button in the dialog box (data entry form) to finish adding data. Excel then closes the data entry form. You do not need to press the RETURN key for the last row.

Creating a Form in Google Docs

  1. Go to docs.google.com/forms. Google forms are useful for planning events, making surveys, or collecting information in a streamlined manner. [1] If you are not currently logged on with a Gmail account, you will need to log on before you can continue.
    • You can share the forms with other people, allow people to complete a form online and collect all the responses in a spreadsheet.
  2. Click the plus (+) sign. Google Forms opens a new form. There are two main tabs at the top of the form – Questions and Responses.
  3. Add questions to your form. [2] For each question, you need to write the question and state what type of answer you expect. If this is the first time you are creating a form, you can use the Take Tour button to familiarize yourself with the options available. You can include 9 different types of questions in your form.[3]
    • Use Short answer when you want the answer to be one or two words.
    • Use Paragraph when you want the answer to be one or more sentences. For example, your question might be ‘What are the benefits of exercising regularly?’.
    • Use Multiple choice when you want the answer to be one of a list of several options. For example, ‘Which sport do you enjoy most?’ Provide a list of sports to choose from. Each item in the list appears in the form next to a small round button.
    • Use Checkboxes when you want multiple answers from a list of several options. For example, ‘Which sports do you enjoy?’ Provide a list of sports to choose from. Each item in the list appears in the form next to a small square.
    • Use Dropdown when you want the answer to be a selection from a menu. For example, ‘Which season is most popular for your favorite sport?’ Provide a list seasons. The list appears in the form as a drop down box with the first season visible.
    • Use Linear Scale when you want the answer to rate the options on a numerical scale. For example, ‘How interested are you in the NFL?’ A scale question appears in the form showing a scale of 1 to 5. You can increase or decrease the scale when you create the question. The maximum scale is 1 to 10.
    • Use Multiple Choice Grid when you want several answers to a question. For example, ‘Which months do you play which sports?’ Provide a list of months as the rows and a list of sports as the columns. A multiple-choice grid question appears in the form as a grid with the row items listed vertically and the column items listed horizontally.
    • Use Date when you want the answer to be a specific date.
    • Use Time when you want the answer to be a specific time.
  4. Choose how to collect data from people filling out the form. Click on the Responses tab to display the available choices. Choose either Create a New Spreadsheet or Select existing spreadsheet.
    • If you create a new spreadsheet, give it a name and click Create. Google links the new spreadsheet to the form. When people fill out the form, Google puts the answers to the questions in the spreadsheet. Each answer appears with a timestamp.
    • If you select an existing spreadsheet, you can choose any spreadsheet you have already created. Google links the existing spreadsheet to the form. Note that if you do this, Google will reformat the existing spreadsheet. The questions in the form appear as column titles.

Creating a Custom Form in Excel

  1. Decide if a custom form works best. Use a custom form when you want to create a form that other people will fill in and you want to have a specific layout and impose limits on some of the values that people can enter. For example, you might be collecting data about specific sports and want to know which sport an individual plays, how often each month, what percentage of free time is spent on the sport and other details.
    • Although you can use a data entry form to collect this data, creating a custom form with complex fields such as “list boxes”,” combo boxes”, “spin buttons”, and “scroll bars” allows you to capture this information efficiently because you can limit what each person enters.
    • You can also position the complex fields anywhere you want on the form, not just in columns as with a data entry form. Each list box, etc. is a complex field on the form. You need to create each complex field separately.
  2. Enable the Developer tab. The Developer tab appears to the right of the View tab. The Developer tab gives you access to the tools you require to create a custom form.
    • Click the Microsoft Office button followed by the Excel Options button.
    • Click on the check box named Show Developer tab in the Ribbon.
    • Click the Developer tab to display the available tools. Excel uses special terminology for complex fields such as list boxes. They are called “controls”. You will need to use the Insert Controls icon that looks like a briefcase with a hammer and a wrench.
  3. Enter the main titles to be used on the form. Type each title in its own cell. For example, if you are collecting data about people of different ages in different cities, you can enter Name, Age, City.
    • Think about the layout you want in the form when entering titles. You want to leave one or more empty cells either below or to the right of each title so there is space to enter data next to each title.
  4. Enter the data to be used in any complex fields (controls) on the form. Type each item of data in its own cell. For example, if you are collecting data about sports, you can enter Baseball, Football, Hockey, Tennis, Golf.
  5. Use the INDEX function. The INDEX function returns a value from a specific range of data. Enter the INDEX function in the cell destined to display the chosen selection from a control. For example, if A1 is going to contain the selection and the range C1:C5 contains the data, enter INDEX(C1:C5,A1,0) into A1.
  6. Add any list boxes you want to appear on the form. Use a list box when you want a user to choose from a list of finite items. For example, if you are collecting data about sports, you can limit the sports to Baseball, Football, Hockey, Tennis and Golf. Anyone filling out the form can only choose from those sports. Set the location and values for each list box. [4]
    • Click Insert in the Controls section. Then click List Box (Form Control) under Form Controls.
    • Click the worksheet location where you want the upper left corner of the list box to appear, and then drag the list box to where you want the lower right corner of the list box to be.
    • Click Properties in the Controls section. In the Format Object window, enter the range where the data is located (C1:C5 in our example) and the cell where the result is to be displayed (A1 in our example).
  7. Add any combo boxes you want to appear on the form. A combo box is almost identical to a list box. However, it combines a text box with a list box. Use a combo box when you want to give a user the option to choose from a list of finite items or to type an alternative. For example, if you are collecting data about sports, you can list Baseball, Football, Hockey, Tennis and Golf as choices, but a user might play soccer or some other sport. Anyone filling out the form can either choose from the sports listed or type soccer or another sport in the text box part of the combo box. Set the location and values for each combo box. [4]
    • Click Insert in the Controls section. Then click Combo Box under Form Controls.
    • Click the worksheet location where you want the upper left corner of the combo box to appear, and then drag the combo box to where you want the lower right corner of the combo box to be.
    • Right click the combo box and then click Format Control. In the Format Control window, enter the range where the data is located (C1:C5 in our example), the cell where the result is to be displayed (A1 in our example), and a number in the Drop Down Lines box. The number you enter determines how many lines are displayed in the combo box before it is necessary to scroll down to see other items in the list.
  8. Add any spin buttons you want to appear on the form. A spin button appears on the form as a button with an upward pointing arrow on the top half and a downward pointing arrow on the bottom half. Use a spin button when you want a user to select a number from a range of numeric values, including dates or times. For example, if collecting data about sports, you might want to know how many times a month a user plays a chosen sport, but you are going to put any results where the answer is more than 20 all together. In this case, you can set a maximum limit for the spin button to 20. Anyone filling out the form can choose a value from 1 to 20 by using the down or up arrows on the spin button. Set the location and values for each spin button. [4]
    • Click Insert in the Controls section. Then click Spin Button under Form Controls.
    • Click the worksheet location where you want the upper left corner of the spin button to appear, and then drag the spin button to where you want the lower right corner of the spin button to be.
    • Right click the spin button and then click Format Control. Enter numbers in the value and change fields. Enter the cell where the result is to be displayed (A1 in our example) in the Cell link field.
  9. Add any scroll bars you want to appear on the form. A scroll bar supports a range of values using scroll arrows or the scroll box. Use a scroll bar when you want a user to select a value from a large range of values such as percentages. For example, if collecting data about sports, you might want to know what percentage of a user’s free time is taken up playing a chosen sport each week. In this case, you can use a scroll bar for the range of percentages, setting 1 as the minimum value and 100 as the maximum value. Anyone filling out the form can move the scroll arrows or scroll box to choose the percentage of time spent. Set the location and values for each scroll bar. [4]
    • Click Insert in the Controls section. Then click Scroll Bar under Form Controls.
    • Click the worksheet location where you want the upper left corner of the scroll bar to appear, and then drag the scroll bar to where you want the lower right corner of the scroll bar to be.
    • Right click the scroll bar and then click Format Control. Enter numbers in the value and change fields. Enter the cell where the result is to be displayed (A1 in our example) in the Cell link field.
  10. Disable the Developer tab. This removes access to the control tools. Take this action when you have finished adding all the controls you want on the form. You need to take this step so that when you send the form to users they can only enter data in the complex fields (controls) in the form. They will not be able to alter the range of data that can be entered in any complex field (control).
    • Click the Microsoft Office button followed by the Excel Options button.
    • Click on the check box named Show Developer tab in the Ribbon.
  11. Save the form as a template. Click the Microsoft Office button followed by the Save As option. Choose Excel Workbook from the list that appears. Choose Excel Template as the value for the Save as Type field and click on the Save button. The custom form is now ready to send to other people to complete.

Related Articles

Sources and Citations

  1. ↑ https://support.google.com/docs/answer/87809?hl=en
  2. ↑ https://support.google.com/docs/answer/2839737
  3. ↑ https://apps.google.com/learning-center/products/forms/get-started/#section-1
  4. ↑ 4.0 4.1 4.2 4.3 https://support.microsoft.com/en-us/kb/291073
Retrieved from "https://kipkis.com/index.php?title=Create_a_Form_in_a_Spreadsheet&oldid=286862"