While working with Microsoft Excel, we may need to store data and perform some useful calculations for our daily work or professional work. If entering data is a regular component of your job, Data Entry Forms is a very handy tool. We may use it to speed up the data-entering process and help you prevent errors. Also, concentrate on one record at a time with its aid. In this article, we will demonstrate some effective ways to create a printable form in Excel.
What Is Excel Form?
A Microsoft Excel form is a tool for data collecting. Large volumes of data may be swiftly entered into a table or set of fields using a worksheet form. In order to construct tables and put data into categories, forms are more user-friendly than creating queries.
How to Create a Printable Form in Excel: 2 Effective Ways
We may easily enter vast quantities of data into a table or set of cells using a printable Excel form. It is a handy and quick way to input records in Excel. Let’s see different approaches to creating excel forms.
1. Utilize Excel Ribbon to Generate Excel Form
A ribbon is a command bar that is located at the top of a window and groups the functionality of software into a number of tabs. Utilizing a ribbon simplifies the process of allowing users to find functionalities, speeds up their actual implementation, and gives them a greater sense of control over the core curriculum. Let’s follow the instructions to generate a printable form in Excel.
- Firstly, go to the Page Layout tab from the ribbon.
- Secondly, click on the Size drop-down menu under the Page Setup group and select Letter.
- Thus, you can see a dotted line which ensures that you can not cross the line as you want to create a printable form.
- We need to generate some drop-down lists for the form.
- So, go to another sheet to make a table of Customers; select the whole data and press CTRL+T.
- Consequently, a table will be created automatically.
- Now, go back to the main form sheet and insert some necessary information there.
- In our case, we will name the form Order Form.
- Then, we will add an Order Number and Order Date.
- Further, we will place a box where we will input the Ship to details.
- Here, we will generate a drop-down list.
- Select cell B8 and go to the Data tab from the ribbon.
- Click on the Data Validation drop-down list under the Data Tools category.
- Then, select Data Validation.
- Thus, the Data Validation dialog box will appear.
- Select List from Allow drop-down menu.
- In the Source field, go to the customer details table and select the whole data.
- Further click OK to finish the procedures.
- Now, you can see the drop-down list is created in the cell.
- At this point, suppose we want to put the Street, City, and Post Code automatically while selecting any customer from the drop-down list.
- For this, we will use the VLOOKUP function.
- Go to the Formulas tab and click on the Lookup & Reference drop-down menu, under Function Library.
- Then, scroll down and click on VLOOKUP.
- Function Arguments for the VLOOKUP function will appear.
- Here, the Lookup_value is our drop-down list cell. Table-array is the customer details table, Col-index-num is 2 as we want to put the street name and the street name is in the 2nd column of the table.
- Further, set all those things properly and click OK. And the final formula will be.
- By using the formula we put the city and the postcode. All we need to do is just change the column number in the formula.
- Suppose, we want a drop-down list for Item.
- Likewise the Customer we do the same and make another drop-down list.
- We will require some formulae for this. The cell where we wish to place those formulae will be picked, and we will then insert the formula into that cell.
- First, we’ll choose cell E16 and enter the straightforward formula there.
- After that, hit Enter.
- Second, we will employ the SUM function to determine the subtotal. We choose cell E18 and enter the formula there as a result.
- To set the formula there once more, press the Enter key on your keyboard.
- In the third step, we’ll figure out the tax using a straightforward multiplication method.
- Hit Enter after adding the other formulae.
- Finally, we will once more utilize the SUM function to determine the total.
- Press Enter.
- Finally, this is our form.
- Lastly, press CTRL+P to print the form.
Read More: How to Make a Fillable Form in Excel
2. Apply VBA Macros to Create a Printable Form in Excel
With Excel VBA, users can easily use the code which acts as an excel menu from the ribbon. We are going to create an order form. To use the VBA code to generate a printable form in Excel, let’s follow the procedure.
- Firstly, we will put some information which we will need. So, in a separate sheet, we record the customer’s details.
- Secondly, we will place all the details into another sheet. We put all that data into a table. If you don’t know how to create a table, just follow this;
- Select the whole data.
- Go to the Insert tab from the ribbon.
- Under Tables group, click on Table.
- Further, we will need some buttons to control the macros.
- Go to the Developer tab from the ribbon.
- Then. select Button (Form Control).
- We edit the name of the button form. As you can see in the previous screenshot, we have two different buttons. One is View Form the other is Print Orders.
- Furthermore, now we will create the form we want to print.
- For this, we will need some formulas there. We will select the cell where we want to put those formulas and insert the formula into that selected cell.
- First, we will choose cell E16 and put the simple formula there.
- Then, press Enter.
- Secondly, to calculate the subtotal we will use the SUM function. So, we select cell E18 and input the formula there.
- Again, press the Enter key from the keyboard to set the formula there.
- Thirdly, we will compute the tax, for this we will use a simple multiplication formula.
- Likewise placing the other formulas, hit Enter.
- Finally, to calculate the total we will again use the SUM function.
- Hit Enter.
- That’s it, our form is ready now.
- At this point, we need to write our VBA Macros. To update the data we are going to use the VBA code down.
VBA Macro Code:
Option Explicit Sub Update() Dim htryWs As Worksheet Dim inptWs As Worksheet Dim nxtRw As Long Dim oCol As Long Dim myR As Range Dim myC As String Dim myCll As Range myC = "D5,D7,D9,D11,D13" Set inptWs = Worksheets("Data") Set htryWs = Worksheets("CopyData") With htryWs nxtRw = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inptWs Set myR = .Range(myC) If Application.CountA(myR) <> myR.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With htryWs With .Cells(nxtRw, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nxtRw, "B").Value = Application.UserName oCol = 3 For Each myCell In myR.Cells historyWks.Cells(nxtRw, oCol).Value = myCll.Value oCol = oCol + 1 Next myCll End With With inptWs On Error Resume Next With .Range(myC).Cells.SpecialCells(xlCellTypeConstants) .ClearContents Application.GoTo .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub
- Click on the Save button to save the macro or press CTRL+C.
- We will need another code to represent the cell that is active.
Option Explicit Sub GoOrders() On Error Resume Next Worksheets("Orders").Activate End Sub Sub GoForm() On Error Resume Next Worksheets("Order Form").Activate End Sub
- Again, save the code by pressing CTRL+C.
- Finally, to print the data use the following code.
Option Explicit Option Base 0 Sub Print_Data() Dim FrmWs As Worksheet Dim DWs As Worksheet Dim myR As Range Dim myCll As Range Dim lC As Long Dim myAdd As Variant Dim lOrds As Long Set FrmWs = Worksheets("Orders Forms") Set DWs = Worksheets("Orders") myAdd = Array("D5", "D6", "C10", "D25", "C16", "E16", "E16") With DWs Set myR = .Range("C3", .Cells(.Rows.Count, "C").End(xlUp)) End With For Each myCll In myR.Cells With myCll If IsEmpty(.Offset(0, -1)) Then Else .Offset(0, -1).ClearContents For lC = LBound(myAdd) To UBound(myAdd) FrmWs.Range(myAdd(LC)).Value = myCll.Offset(0, LC).Value Next LC Application.Calculate FrmWs.PrintOut Preview:=True lOrders = lOrds + 1 End If End With Next myCll MsgBox lOrds & "." End Sub
- To save the macro, either click the Save button or use CTRL+C.
NOTE: The locations you’ve supplied on this line are put into the appropriate places on the order form, starting with Column B and going right. Modify these references to correspond with your order form.
myAdd = Array("D5", "D6", "C10", "D25", "C16", "E16", "E16")
Change the worksheet names towards the beginning of the code to reflect the worksheet names:
Set FrmWs = Worksheets("Orders Forms") Set DWs = Worksheets("Orders")
Be careful to paste the following line at the head of the code module when you copy the code into your workbook.
On the Orders sheet, mark orders to print, then click the Print Orders button.
- Make a list of orders in a worksheet, leaving the first column empty. Put an x in column A for the orders you want to print when you are ready to do so.
- Further, click on Print Orders.
- This will open a window of Print Preview.
- Now, click on Print.
- After completing the procedures, a Microsoft Excel dialog box will appear showing the number of printing order confirmations.
Read More: How to Create Data Entry Form in Excel VBA
How to Use Form Command in Excel?
An Excel form is a data-collecting tool. In essence, it is a dialog window with fields for records. Let’s look at the steps to utilize the command in Excel.
- To begin with, right-click on the ribbon.
- Then, select Customize the Ribbon.
- This will open the Excel Options dialog box.
- Go to Customize Ribbon.
- Further, select Command Not in the Ribbon from the Choose commands from the drop-down menu.
- Scroll down a bit and choose Form.
- Next, click on New Tab. (If you want to add this command to a group then click on New Group).
- This will add a New Tab on the ribbon.
- Click on Rename button.
- Subsequently, the Rename dialog box will appear.
- Name the tab on the Display name field.
- Then, click on the OK button.
- Now, select the New Group under the Data Form tab which we just created.
- Further, click on Add.
- Finally, click OK to complete the procedure.
- You can see that the Data Form tab will be added to your Excel ribbon.
- Make a column list to create the form perfectly, and select the cell where you want to use the form.
- This will open a Microsoft Excel msg. Click OK.
- And, there you go!
- The Data Form dialog box will show up, you can add records as per your requirements.
Download Practice Workbook
You can download the workbook and practice with them.
The above methods will assist you to Create a Printable Form in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.
- How to Design Form in Excel
- How to Create an Excel Data Entry Form That Includes Checkboxes
- How to Make an Excel Spreadsheet Look Like a Form
- How to Populate an Excel Spreadsheet from a Web Form
- How to Create an Autofill Form in Excel
- How to Create Data Entry Form in Excel
- How to Create Data Entry Form with Drop Down List in Excel