3 Ways to Create Forms on Worksheets

2024 Author: Jason Gerald | [email protected]. Last modified: 2024-01-02 02:28

Information becomes easier to obtain, organize, and edit with the help of forms. Forms are especially useful if you need to enter a lot of data from a list or get survey results. Each form has several fields (boxes for entering data). Forms can have different field types, both simple and complex. Simple fields allow you to type in the desired data. Complex fields can be a “list box” that lets you choose from several options in a list, and a “spin button” that lets you choose the values you want to enter. This article helps you create forms using Excel or Google Forms, which are components of Google Docs (Google's online web-based word processing program).

Step

Method 1 of 3: Creating a Data Entry Form with Excel

Step 1. Determine if the data entry form is best suited for your needs

Data entry forms are used when you need simple text box forms that each have a title in the form of a label. The data entry form allows you to easily view and enter data without the need to switch fields, especially when not all of the fields can be displayed on the monitor screen. For example, maybe your list of contact information and customer orders exceeds 10 columns. It will be easier if you enter your name, address, phone number, email, order date, order type, and so on with the data entry form instead of entering each information into its own fields.

Step 2. Add the title used in the data entry form

Enter a title at the top of each column that you want to use in the data entry form. Excel uses these headings to create fields on data entry forms.

• For example, if you provide the headings “Name, Address, Telephone Number, Email, Order Date, and Order Type” in their respective columns starting from left to right on the worksheet, those titles will appear in the dialog box (entry form data) in order from top to bottom.
• Before you proceed to the next process, check if there is a Form button in the Data label. Otherwise, add a Form button to the Quick Access Toolbar. Click Customize Quick Access Toolbar, and select the More Commands option. Change Popular Commands to All Commands, scroll to the very bottom of the list, and click Form. After that, click the Add button followed by the OK button.

Step 3. Open the data entry form to start entering data

Click all the cells under the headings and go to the Data label. Find and click the Form button. Excel will display a dialog box showing each column heading that was previously entered in the worksheet in the form of a vertical field list. The dialog box acts as a data entry form.

• The Form button is on the Data or Quick Access Toolbar label.
• If you have named the worksheet you are working on, the dialog box (data entry form) will have a title. The text in the title will be the same as the name of the workpaper. If the worksheet does not have a name, the title of the dialog box (data entry form) will have the word ' Sheet ' followed by the number of the worksheet.

Step 4. Enter the data for the new row

Use the TAB key to move to the next field in the data entry form. Press the RETURN button after entering data into each field in the data entry form. The data entered will be added as the next line in the list on your worksheet.

Step 5. Click the Close button in the dialog box (data entry form) to end data entry

Excel will close the data entry form. You don't need to press the RETURN button for the last row.

Method 2 of 3: Creating a Form with Google Docs

Step 1. Visit docs.google.com/forms

Google Forms is very useful for planning events, creating surveys, or gathering information quickly and easily. Sign in to your Gmail account before continuing, if you haven't already.

You can share the form with others so they can fill out the form online and all responses can be collected in a worksheet

Step 2. Click the plus (+) symbol

Google Forms will open a new form. There are two main labels at the top of the form: Questions and Responses.

Step 3. Add questions to your form

You need to write down each question and state the type of answer you want. If this is your first time creating a form, take advantage of the Take Tour button to familiarize yourself with all the available options. You can enter 9 different types of questions on the form.

• use Short answer (short answer) if the desired answer consists of only one or two words
• use paragraph (paragraph) if the desired answer is one sentence or more. For example, your question might be “What are the benefits of exercising regularly?”
• use multiple choice (multiple choice) if you want one answer from several given options. For example, “What sport do you enjoy the most?” Provide a list of sports to choose from. Each option in the list will appear next to a small round button on the form
• use Checkboxes (checkbox) if you want multiple answers from the given list of options. For example, “What sport do you enjoy?” Provide a list of sports to choose from. Each option in the list will appear next to a small square button on the form.
• use Dropdown (drop down) if you want an answer as a choice from the menu. For example, “What season do you usually do most of your favorite sport?” provide a list of seasons. A list will appear on the form in the form of a drop-down box. The first answer will appear in this drop-down box.
• use Linear Scale (linear scale) if you want the answer in the form of a rating of the options in the form of a numerical scale. For example, “How interested are you in the Indonesian League?” The question scale will appear in the form in the form of numbers 1-5. You can increase or decrease the scale when creating a question. The maximum scale that can be used is 1 to 10.
• use Multiple Choice Grid (multiple choice grid) when wanting multiple answers to a question. For example, “In what month do you usually do the following sports?” Enter a list of months as rows and a list of sports as columns. The “multiple-choice grid” question will appear as a grid with row options listed vertically, and column options horizontally.
• use Date if the desired answer is a specific date.
• use Time if the desired answer is a certain time.

Step 4. Choose a data collection method from the form filler

Click the Responses label to display the available options. Choose Create a New Spreadsheet or Select existing spreadsheet.

• If you are creating a new worksheet, enter a name for the worksheet and click Create. Google will link the new worksheet to the form. When people fill out forms, Google will enter the answers to the questions in the form onto worksheets. Each answer will appear with a timestamp (timestamp).
• If you select existing spreadsheet, choose one of the worksheets you have created. Google will link the existing worksheet to the form. Please note, this method will make Google reformat existing worksheets. Questions in the form will appear as column headings.

Method 3 of 3: Creating Your Own Creative Form with Excel

Step 1. Determine if this form type best suits your needs

Use a custom format if you want a form with a custom layout and certain value limits that other people can fill out. For example, you might collect data related to a particular sport and want to know what are the exercises you can do alone, how often per month, percentage of free time spent exercising, and other details.

• Step 2. Enable the Developer label

  The Developer label is to the right of the View label. The Developer label allows you to use the various tools needed to create your own custom forms.

  • Click the Microsoft Office button followed by the Excel Options button.
  • Click on the checkbox labeled Show Developer within the Ribbon.
  • Click the Developer label to view available tools. Excel uses special terminology for complex fields, such as list boxes. This complex field is called “control”. You will need to use the Insert Controls icon which depicts a suitcase with a hammer and wrench.

  

  Step 3. Enter the main title that will be used on the form

  Type each title into its own cell. For example, if you collect data about people of different ages in different cities, enter the headings Name, Age, City.

  Specify the desired title layout on the form when entering the title. It's a good idea to leave one or more blank cells below or to the right of each heading. Thus, the distance between each title and the field for entering data is not too tight

  

  Step 4. Enter the data used into the complex (control) field on the form

  Type each data in the cell. For example, if you collect data about sports, enter Baseball, Football, Hockey, Tennis, Golf.

  

  Step 5. Use the INDEX function

  The INDEX function can return values from a specified range of data. Enter the INDEX function into the target cell to display the selection of a control. For example, if A1 will contain options and C1:C5 contain data, enter INDEX (C1:C5, A1, 0) into A1.

  

  Step 6. Add the list box you want to appear on the form

  Use a list box if you only want to give the form user certain choices. For example, if you collect sports-related data, limit the selection to Baseball, Football, Hockey, Tennis, and Golf. Anyone who fills out the form can only choose from a few of these sports. Set the location and value of each list box.

  • Click Insert in the Control section. Then, click the List Box (Form Control) under Form Control.
  • Click the location on the worksheet where you want the upper-left corner of the list box to appear, and then drag to where you want the lower-right corner of the list box to appear.
  • Click Properties in the Controls section. In the Format Object window, enter the range where the data is (in this example, C1:C5), and the cell that will return the result (in this example, A1).

  

  Step 7. Add all the combination boxes you want to appear on the form

  The combination box is almost the same as the list box. However, this box combines a text box with a list box. Use the combination box when you want to provide options for the user to choose from a limited list or type in alternative answers. For example, if you collect sports-related data, enter Baseball, Football, Hockey, Tennis, and Golf as options. However, for a user who may play Football and other sports, he can select Football and other sports in the list, or select Soccer and fill in the name of another sport in the text box that is part of the combination box. Set the location and value for each combination box.

  • Click Insert in the Controls section. Then, click Combo Box under Form Controls.
  • Click the location on the worksheet where you want the upper-left corner of the combination box to appear, and drag it to where you want the lower-right corner of the combination box to appear.
  • Right-click the combination box and then click Format Control. In the Format Control window, enter the range in which your data is located (in this example, C1:C5), the cell that will display the result (in this example, A1), and the number in the Drop Down Lines box. The number entered will determine the number of lines in the combination box before you have to scroll down to see the rest of the list.

  

  Step 8. Add all the play buttons you want to appear on the form

  The spin button will appear on the form as a symbol whose top half is an arrow pointing up and the bottom half is an arrow pointing down. Use the dial when you want the user to select a number from a range of numeric values, including date or time. For example, if you collect sports-related data, maybe you want to know how often users play a certain sport in a month, but you only want to enter results where the total answer is more than 20. In this case, you can set the maximum limit of the play button to 20. Who Anyone filling out the form can select a value from 1 to 20 using the up and down arrows on the play button. Set the location and value for each play button.

  • Click Insert in the Controls section. After that, click the Spin Button under Form Controls.
  • Click the location on the worksheet where the top left corner of the spin button will appear, and drag until where the bottom right corner of the spin button will appear.
  • Right click the play button and click Format Control. Enter the numbers in the Value and Change fields. Enter the cell that will display the result (in this example, A1) in the Cell link field.

  

  Step 9. Add a scroll bar to the form, if needed

  The scroll bar supports a range of values using scroll arrows or scroll boxes. Use the scroll bar if you want the user to select a value from a large range of values, such as a percentage. For example, if you're collecting sports-related data, you might want to know the percentage of a user's free time spent doing their chosen sport. In this case, you can use the scrollbar as a percentage range. Users filling out the form can move the scroll arrow or scroll box to select the percentage they want to enter. Set the place and value for each scrollbar.

  • Click Insert in the Controls section. Then, click the Scroll Bar under Form Controls.
  • Click the location on the worksheet where the top-left corner of the scrollbar will appear, and swipe all the way to where the bottom-right corner of the scrollbar will appear.
  • Right-click the scrollbar and then click Format Control. Enter a number in the Value and Replacement fields. Enter the cell that will display the result (in this example, A1) in the Cell link field.

  

  Step 10. Disable Developer label

  This option removes access to control equipment. Do this when you have finished adding all the desired controls to the form. This step needs to be done so that when you submit the form to users, they can only enter data into complex fields (controls) on the form. The user cannot change the range of data that can be entered into complex fields (controls).

  • Click the Microsoft Office button followed by the Excel Options button.
  • Click the checkbox labeled Show Developer on the Ribbon.

  

  Step 11. Save the form as a template

  Click the Microsoft Office button followed by the Save As option. Select Excel Workbook from the list that appears. Select Excel Template in the field that says Save as Type and click the Save button. Your custom form is ready to be sent to the crowd to fill out.