How to Create a Printable Form in Excel (2 Easy Ways)

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.

STEPS:

• 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.

STEPS:

• 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.

VBA Code:

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.

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.

myAdd = Array("D5", "D6", "C10", "D25", "C16", "E16", "E16")

Set FrmWs = Worksheets("Orders Forms")
Set DWs = Worksheets("Orders")

• 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.

STEPS:

• 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.

Read More: How to Create an Excel Data Entry Form Without a UserForm

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

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.

Related Articles

• 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

<< Go Back to Data Entry Form in Excel | Data Entry in Excel | Learn Excel