Excel 2010: Data Input Forms

Excel 2010 provides Data Input Form that helps users fill cells more easily. It enables user to create a Form for entering data, which saves a lot of time by providing a simple interface in which you can enter the data without hassling of scrolling the window and search records for editing. The Data Input Form feature lets you fill all the cells’ values of a single record quickly. With this feature, you no longer need to move to the required cell in the spreadsheet manually and enter the data. Furthermore, it lets you move through the entered data using the next and previous buttons. You can easily edit the data, delete a record and create a new record from within the data input form dialog box.

For instance: If we want to add marks obtained by a great number of students, we need to enter a lot of data manually into the respective student record cells. To begin, launch Excel 2010 worksheet in which you want to enter data. For illustration, we will use a simple student record sheet that contains names of students and courses.

form datasheet

Now we want to populate the data sheet that will show the name and marks secured by each student in respective courses, we can do it in old-orthodox way, but if the datasheet grows bigger in size then scrolling down and across the window to find the records would be difficult to get by. For this problem, we need to make a form in which we will be entering marks.

Go to File menu, click Options, and in left pane click Customize Ribbon, In the right pane select All Commands from Choose commands from the drop-down options and look for Form command. Now from left pane, select Home and hit New Group, new group will be added to Home tab, change its name to Form, and then click Add >> button to add Form command to newly created group. Click OK to close Excel Options dialog.

form 2

You will see the Form command at the end of the Home ribbon.

form command 1

Now Select the cell beneath the first course name field.


name 1 enter

Click Form button to  open it’s dialog, now we will start entering the name and marks obtained in the respective subjects.

form dialog

Once Form is filled, you can enter marks for the other student, click Find Next

jack marks 1

Once done with entering marks for all the students, you can click New to add a record of a new student by specifying his/her name.

new 1

Records of all the students are populated in a quite simple way, as seen in the screenshot below.

student final record

Advertisement
  • Doug

    Have you ever loaded this page? It has beeb loadind for ever. I had to turn off the internet just too get to this comment box. I don’t know if this was my computer or your page. Hope this reaches you well.

  • elKhatib

    Thanks, that is very useful, especially when you notice that I have so many tools that are there and paid for and not used :)

  • Imjeffman

    Exactly what I was looking for.  I just didn’t know where to find it, or how to use it.  Thanks!!

  • Kjhfdgh

    this’s a very good example of my assignment topic.. this’s helping me a lot as a support of the idea am trynna to deliver thank you :D  

    • Anonymous

      Glad, you found it helpful!

      • http://www.facebook.com/people/José-F-Carvalho/1288453017 José F. Carvalho

        Yes, thank you. But the M$ help is pathetic. I presses the ? to find help on criteria. Instead of being taken to specific help was taken to top level M$ Excel help. No reference to Forms. Input Form in the search box and got all kinds of stuff, none related at all to the subject of forms. Pathetic!.

      • http://www.facebook.com/people/José-F-Carvalho/1288453017 José F. Carvalho

        Yes, thank you. But the M$ help is pathetic. I presses the ? to find help on criteria. Instead of being taken to specific help was taken to top level M$ Excel help. No reference to Forms. Input Form in the search box and got all kinds of stuff, none related at all to the subject of forms. Pathetic!.

  • trainer

    Can you lock the Form in place so that people can only enter data into the Form and Protect everything else?

    • Lucas

       I want to do the same thing and am having no luck. Great to know how to load the form function though, thanks!

    • Caraline

      I think if you look up, its says something like data validations restricting data, wonder if that would be helpful

  • Blue

    Is there any chance to have series of combination between characters and numeric count down automatically? For example series of AC245001, AC245002…?

  • http://twitter.com/takyar Dinesh Kumar Takyar

    Well explained. Step by step. Good screen shots. Thank you

  • Johnny

    Is it possible to copy the value from 1 entry to all of the fields

  • Caraline

    perfect easy instructions, definitely recommend to anyone trying to get a handle on the newer versions,

  • Chris

    How do you create forms on Excel Mac?

    • Chris

      anyone know if this works on Excel for Mac?

  • Nick SA

    Excellent, clear and well written.

  • markgrew@menziesgroup.com.au

    this is exactly what i need, but is there any way for Excel to “remember what i am entering in the form boxes, eg i normally type 5 or 6 different “words”..and I would love to start typing “va” and have the box automatically fill with “Vacuum” etc. Can this be done too???

    Thanks,

    • srs

      0300-6809638 sir give me ur phone number plz.
      reply on my number stated above.

  • http://darklorddungeon.blogspot.com/ Denis Achilles L. del Callar

    I have a question: When I stop entry and click close – say at the end of the day – you can’t continue the next day by clicking on Forms. How do you continue data entry if you stop?