Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Form in Excel (Table of Contents)
- What are Excel Forms?
- Steps for Adding the Form Feature in Excel
- How to Create Data Entry Form in Excel?
What are Excel Forms?
Enter data one by one for tables in excel is a long time procedure and little tedious task. Also if we want to check the row-wise data, sometimes it’s confusing too. For overcoming with this problem, Excel has a very interesting inbuilt feature which is “Excel Forms”.
The forms in Excel provide a facility to create a Data Entry Form in Excel. This is the best way to enter data in table format quickly. The best part of this feature is it’s predesigned and flexible.
Form in Excel is a hidden feature in Excel and it’s not available in Ribbon Tools. We need to add this feature by using Quick Access Toolbar option available under the FILE tab.
The Excel Forms allow you to view, add, edit and delete one record at a time in a horizontal orientation which makes data entry easier.
Before adding the Excel Forms facility in Excel, certain points need to be remembered or steps need to be done:
- You need a table to enter data or at least column headers of the data.
Steps for Adding the Form Feature in Excel
Let’s assume we have the following column headers or fields in which we need to enter the data.
- Product ID
- Product Name
Enter these headings into the excel column-wise.
- Convert these Column Names/ Headings into a Table and Go to Insert then, click on Table under Tables section. Or Press CTRL+T.
- A box will open for creating a table then, tick the checkbox “My table has headers” and click on OK.
- Now the table looks like the below screenshot.
- Go to File a menu bar will open in the left then, click on options tab available in the left pane.
- A dialog box will open for Excel Options then, Click on Customize Ribbon tab in the left pane.
- This will open a dialog box for adding the Ribbons/Commands which are not available at an initial stage then, click on Choose commands from Dropbox as shown below screenshot.
- This will show the following option then, Choose the Commands Not in the Ribbon.
- It will display a lot of options under this then, Scroll the bar in the above-shown box and click on Form Refer below screenshot.
- Then click on New Tab Refer below screenshot.
- After clicking on New Tab, it will add New Group under this heading as shown below screenshot denoted by a red box.
- Select New Group (Custom) as shown in the below screenshot and click on Add.
- It will add Forms functionality under this. Refer below screenshot.
- We can Rename the New Tab and New Group by using Rename It easy to understand from a future perspective. Like we replace this New word with Form. Refer below screenshot.
- While renaming the New Group, it also asks for icon image, select the icon image, if you want and click on OK.
- After renaming, it will look like the below screenshot.
- Exit from the Excel Options dialog box.
Now you can see there is a new tab added in the Ribbon “FormTab”. Refer below screenshot.
How to Create Data Entry Form in Excel?
Let’s understand how to create Data Entry form in excel with examples.
- Select your table and click on this Form Tab then, click on Form option under Form Group. Refer below screenshot.
- A new dialog box will open as shown below screenshot.
- In the above screenshot, as we can see in the left side there are labels which we created earlier as Column Headers of the table. Along these label’s, there are text boxes where we can insert the values/record/data one by one.
- Enter your data in each field. After filling all the fields click on the New button to save this record.
- Repeat this process for all the records which you want to enter. After this click on Close button. Now we can see the data in your excel table. See below.
- Now you can use this Excel Form facility for modifying the data into a table.
- We can switch between the records by using Find Prev, Find Next buttons. Refer below screenshot.
- Use the New or Delete button for modifying the records.
- This Form feature provides very important functionality which is Criteria. If you want to see only those records in the table which meets certain criteria/condition, Use this Criteria button. For Example, in the above data, we want to see only Coffee product details. For this criteria, follow the below steps:
Click on the Criteria Button. Enter the criteria. Here we want to see only coffee product details. Enter product “Coffee” into the relevant field and click on the form button
- Now if you click on Find Prev and Find Next, you will be able to see only those records who meet that particular criterion.
- You can edit these criteria anytime by again click on this Criteria button. click on Close button for an exit.
Things to Remember
- You can use only 32 input boxes or field under this Data Entry Form table.
- The width of the input boxes depends on the worksheet column width. So make sure that the column width should be sufficient to show values under the field.
This has been a guide to Data Entry Form in Excel. Here we discuss how to Create Data Entry Form in Excel along with practical examples and data entry form excel template. You may learn more about excel from the following articles –