It is of the utmost importance know how to make a form of any type in Microsoft Excel. Forms are one of the best methods for enter data into a spreadsheet. They meet the goal of to ease the capture, organization and edition of information.
However, it is important to know that a custom form is not the same as a templatebecause the templates already have a default layout in which only replace the data or information fields according to your needs.
While a custom form contains boxes, controls, or dropdown lists that make it easier for its creators to specify and edit the . To know how to make a form in Excelcontinue reading.
Excel Tools to Make Forms What are the best out there?
The most interesting thing about the create forms in excelis that it allows the user create documents of this type in various waysregardless of whether they will be printed or online.
Let’s see several types:
- Printed forms: usually they contain labels, formats, whitespace, and instructions for writing data. With the help of Excel and from its templates, you will be able to elaborate printed forms.
- Online forms: have the same characteristics as a printed onebut with the difference that this online version contain controls. The Controls are objects that allow you to display data, modify it or perform various actions on them. In this way, they make the form be easier to use.
- Templates: The templates are pre-designed documents that simplify the process of creating a form. The templates are primarily available for printed forms. They can be obtained from template office portaland you just need to upload them to the spreadsheet.
Steps to create any type of form using Microsoft Excel
First of all, it should be mentioned that There are three types of forms what can you create in excel. Each one fulfills a specific purpose.
Therefore, we will explain the steps to create any type of form using Microsoft Excel:
Of data
This is the most common of all And allows write or display an entire row of data within a table or range and does not require a horizontal scroll. For this reason, they are easier to use and are recommended when you do not require custom features.
MS Excel has the ability to automatically generate a data formintegrated for a table or interval. Its visualization is very comfortable, because displays all column headers as labels within a single dialog.
Each label has a blank text box adjacent, where the user can transcribe the data corresponding to each columnup to a maximum of 32 columns.
As additional information, with this type of forms you can write new rows, search for them, scroll through them, update or delete them. In case one cell contains a formulathe result will be displayed in the Data Formbut it cannot be changed through said form.
A clear example in which this type of document is used is for create a contact form.
The way to do it is as follows:
- Open the program from Microsoft Excel.
- Create the labels in the header of the spreadsheet.
- add bordersdesigns or colors to customize the form.
- select all tags and the bottom row.
- go to “Insert”.
- click on “Table”.
- In the pop-up window, check the box “Table has headers”. Next, press “To accept”.
- Now it’s time to create the form of data. Enter to “Records”.
- Click on “Options”.
- In the popup window, select “Quick Access Toolbar”.
- Locate the dropdown menu “Commands available in:”.
- Choose the option of “Commands that are not available on the ribbon”.
- Normally, the forms command in excel is disabled by default. In the command listingsearch “Forms” and double-click, or click “Add”. Finally, press “To accept”.
- Go back to reselect all header tags in the spreadsheet.
- go to the Quick-access tool bar and press the command “Form”.
- A new pop-up window will appear on the screen. Its about form where you can enter the data, delete information, search criteriaamong other options.
Spreadsheets with ActiveX and form controls
They are forms that are used mostly to carry out surveys or exams through the Internet.. They are characterized by the use of buttons and commands that execute automated functions. You may visualize data in the spreadsheet grids. In addition, you have the possibility of use the cells such as text boxes, add a format, among other customization options.
The spreadsheets they behave like a simple data entry form. Nevertheless, you can give it more flexibility with the use of controls. These controls are linked to cellsso it is not necessary to use programming codes.
There are two types of controls in Excel:
- controls form
- controls ActiveX.
The form controls are those that are used to easily interact with the data entered in a cell. likewise, also run a macro by binding it to a form control.
Secondly, activex controls can be used with both VBA codes, or without them. Its purpose is to contribute greater flexibility than that offered by form controls. Also, you can personalize its appearance, behavior and source.
Once added, the user will be able to adjust and rearrange controls in multiple ways to simplify the form design.
The process for deploying form controls and ActiveX is as follows:
- Begin by displaying the tab “Programmer”. It is normally disabled by default.
- Right click on the ribbon. Then press “Customize the Ribbon”. A pop-up window will open.
- Similarly, you can also press “Records”oppress “options” and finally “Customize the Ribbon”.
- In the new window, go to the menu on the right side and check the box “Programmer”. To finish, press “To accept”.
- The tab of “Programmer” will appear on the ribbon, between the tabs “View” Y “Accessories”.
- Create the labels in the spreadsheet corresponding to the form.
- In section “Programmer”press “Insert” to add form or ActiveX controls.
- Select any control: Combo box, check box, drop down list, among others. Locate the controller on the spreadsheet.
- If necessary, press the button “Properties”which is located right next to “Insert” to adjust the parameters of the controls.
- Add shape to the form and integrates document security in the tab “Check”. By clicking on “Protect Sheet” to grant you a password against third-party editing.
Userform in VBA
The user formsor UserForm, are custom dialogs generated by Excelwith which you can execute one or more form or ActiveX controls to obtain the information written by the users.
When using UserFormsalso it is possible to opt for the advanced functions of the forms. This includes add multiple checkboxes to all the elements arranged in the worksheet or provide a different button to each character.
The tool VBA either Visual Basic for Applicationsis the most complex method for make forms in excel.
Start with:
- Open the VBA window. You can do it from the tab “Programmer”by clicking on the button “Visual Basic” or by pressing the keys ALT+F11.
- In the new window, click “Insert”. Then press “UserForm”.
- A screen will appear blank form and the toolbox with the controls.
- Use the toolbox to add labels, text boxes, buttons, dropdown menus, among others.
- when you have added controllerdouble click to enter programming codes that correspond to the buttons or text boxes.
- Make sure the routes are well linked. Upon completion, close the visual basic window. The spreadsheet will act as a database.
- You may add an ActiveX button on the sheet to quickly access the form. You just have to use the code: “UserForm1” (or the name of the form) followed by a period (.) and the term “Show” (Show), all together.
List of the best form templates that you can use in Microsoft Excel
Excel templates are freeand you can get them from the official Templates Office portal: “https://templates.office.com” where you will find a wide variety of themes, templates and layouts for both Word, Excel and PowerPoint.
If you need view and manage the form data in an organized way, a template is your best option.
Here is a list of the best form templates that you can use in Microsoft Excel.
weekly task scheduling
Manage your tasks with this simple and very practical template for MS Excel.
Shopping list
Take control over the purchases and household expenses.
sports record
lovers of sports need to keep track of their physical activity. Visualize your progress with this template.
semester calendar
Manage and keep track about your upcoming academic activities. specially designed for educational environments.
credit card tracking
Makes track your spending with the credit card to always be up to date with your finance.
warehouse inventory
Is a template designed for managers of markets or small stores. Enter the products you have sold or unsoldits costs, and so on.
weekly meal plan
Yes you are on a dietyou should do a meal plan for the week. In this way, you will know exactly what you have consumed and how you can vary the dishes each day.
Book Collection List
The book collectors need to have a document on hand about the books they own. This template is exactly what they are looking for.
House Contents Inventory List
Manage your household possessions like this nice template.
Organization phone list
Organize your customers’ phone numbersand other data of interest, with a pre-designed template for Microsoft Excelhe.
Facebook Comments
Disqus