If you use Microsoft Excel in your daily work, you might already know it’s a powerful tool for organizing data and generating reports. But did you know you can take Excel a step further by creating fillable forms with submit buttons, similar to what you can do in Word? Not only is it possible, but by using Excel this way, you can automatically pull in data from other worksheets within the same workbook to populate part of the form—without your user even knowing the data exists. This makes Excel a highly flexible tool for creating forms, especially when linked to a database. Let’s take a look at how this works.

Why Create a Fillable Form in Excel?

You might be wondering why anyone would bother with a fillable form in Excel when Word already exists. Excel has a hidden superpower when it comes to forms: data inputs from other sheets. You can have a database sitting in another worksheet and have parts of your form automatically filled in based on selections made by the user. This feature gives Excel a unique edge over Word when your form needs to interact with a larger dataset.

Plus, with Excel, you can add dropdowns, checkboxes, and even submit buttons that automatically trigger email submissions through Outlook, simplifying processes like HR surveys, employee forms, attendance records, or even lunch menu selections.

Here’s how to create a fillable form with a submit button in Excel, step by step:

Step 1: Format Your Form Layout

Start by adjusting your Excel sheet to look more like a document with a submit button and less like a grid of data points.

  1. Set Page Size: Go to the “Page Layout” tab and select “Size.” Make sure the page is set to 8.5 x 11 inches (letter size). This creates a clear canvas for your form and identifies where the content will fit.
  2. Hide Gridlines: Remove Excel’s grid using “Page Layout” > “Gridlines” > Uncheck “View.” Now, it starts looking like a blank document!
  3. Set Your Header and Logo:
    • Expand row 1 for your header and merge columns A to J.
    • Add a main title such as “Employee Appreciation Lunch” and make it stand out with larger font size (e.g., 24pt).
    • Insert your logo (go to “Insert” > “Illustrations” > “Pictures”) and place it nicely beside your text.

RELATED TOPIC: How to Create a Fillable Form with a Submit Button in Word

Step 2: Add and Format Form Fields

Your fillable form with submit button in Excel will likely require fields like a name, manager, or department. Here’s how to create them:

  1. Create Input Areas: In specific cells, highlight the places where your users will enter data (like “First Name”, “Last Name”).
  2. Auto-Populate Fields: Some basic fields, like titles and departments, can be auto-filled based on the user’s choice of manager by using Excel formulas like VLOOKUP.

To make things easy for your users, consider coloring the cells to show where they need to input responses, while leaving other cells untouched for automated data inputs.

Step 3: Add Dropdown Lists

Dropdowns can help narrow down selections and make the form much easier to fill out. You can create two types:

1. Dropdown From Within the Form

Let’s say you need a dropdown for meal selections:

  • Select the cell where the dropdown will be.
  • Go to “Data” > “Data Tools” > “Data Validation.”
  • In the “Allow” box, choose “List.” Then, in the “Source” box, type out the options you want separated by commas. Example: Chicken,Pork,Steak.

2. Dropdown Connected to Database

For dropdowns like a manager selection, it’s better to reference data in another worksheet:

  • Format your database as a table by pressing Ctrl + T on the sheet where the data (e.g., managers list) resides.
  • Go back to your form, select the cell for manager selection, and repeat the data validation steps. However, this time, instead of typing the list manually, reference the column from your database that contains the manager names.

RELATED TOPIC: How to Perform Basic Calculations in Word

Step 4: Auto-Populate Using VLOOKUP

For fillable form fields in Excel like Manager Titles, Departments, and Email, you can make Excel automatically fill in these details when someone selects a manager from the dropdown.

  1. Use VLOOKUP to link the manager’s name to the appropriate database table.
  2. In a field like Manager Title, type:
    =VLOOKUP(Selected_Manager_Cell,Database_Table,Column_Number,FALSE)
    Where:
    • Selected_Manager_Cell is the cell where the manager dropdown is,
    • Database_Table is your reference table,
    • Column_Number is the column from the table you’re pulling from.

This ensures that once a manager is selected, the form auto-populates with the corresponding department, title, and other data.

Handling #NA Errors

You might run into #NA errors if a field is left blank. Fix this by combining VLOOKUP with IFNA like this:

=IFNA(VLOOKUP(…),””)

This will prevent your form from showing errors, leaving the cell blank if no manager is selected.

Step 5: Conditional Messaging Based on Selection

Sometimes, you might want instructions to appear only if certain conditions are met (like if someone answers “Yes” to a medical question).

Use an IF statement:

=IF(Epipen_Cell="Yes","Please set up a meeting with HR", "")

This formula will display a special message in the instruction box when necessary, keeping your form clean and dynamic.

RELATED TOPIC: How to Use the IFERROR Function in Excel

Step 6: Add Checkboxes for Options

For selections like “Time of Attendance,” where users can pick from predefined lists (like 11 AM, 12 PM, or 1 PM), use checkboxes:

  1. Go to the Developer tab (you may need to enable it under “Options” > “Customize Ribbon”).
  2. Click “Insert” > “Checkbox” and add checkboxes into place.
  3. Format them to fit properly by resizing or removing unnecessary labels.

Step 7: Hide Extra Rows and Columns

To make the form look professional, hide unnecessary rows and columns:

  1. Select the extra columns to the right of your form, press Ctrl+Shift+Right Arrow, and hide them.
  2. Do the same for the extra rows below.

This way, the user will only see the form and no other part of the spreadsheet.

Step 8: Make the Submit Button

Now let’s get to the exciting part—adding the Submit button to your fillable form!

This only works with Microsoft Outlook. Both you and the user need to have Outlook for the email functionality to work.

  1. On the Developer tab, click “Insert” > “ActiveX Controls” > “Command Button.” Draw your submit button.
  2. Right-click the button, go to “Properties” and change its name to something like “Click Here to Submit”.
  3. Customize its colors and fonts to match your form’s design.
  4. Next, open the button’s Visual Basic editor by double-clicking it. Copy and paste the VBA code provided below. Be sure to adjust the email address and subject in the code to ensure the form is sent to the right place.
  5. Save the workbook as a Macro-Enabled Workbook (.xlsm) to preserve all the functionality.

Here is the the Visual Basic (VBA) code needed to create the submit form button to submit the form to email.  You can also use this VBA code to send the completed form from to multiple recipients.  **Be sure to update the Subject, Body, Email To, Email CC, Email BCC, and additional Text with your information.**

Private Sub CommandButton1_Click()

Dim xOutlookObj As Object

    Dim xOutApp As Object

    Dim xOutMail As Object

    Dim xMailBody As String

    On Error Resume Next

    Set xOutApp = CreateObject(“Outlook.Application”)

    Set xOutMail = xOutApp.CreateItem(0)

    xMailBody = “Type the body or your email message here” & vbNewLine & vbNewLine & _

              “Use this if you want a separate line of text” & vbNewLine & _

              “Use this if you want another separate line of text”

                  On Error Resume Next

    With xOutMail

        .To = “youremail@email.com”

        .CC = “”

        .BCC = “”

        .Subject = “Enter the Email Subject Here”

        .Body = xMailBody

        .Attachments.Add ActiveWorkbook.FullName

        .Display   ‘or use .Send

    End With

    On Error GoTo 0

    Set xOutMail = Nothing

    Set xOutApp = Nothing

End Sub

RELATED TOPIC: How to Create Lists in Microsoft Lists for Beginners

Step 9: Test and Protect the Form

Before distributing the form, protect it from any unwanted changes:

  1. Highlight the cells users can interact with (e.g., dropdowns, text input cells), then unlock those cells from the “Format” menu.
  2. Protect the document under the Review tab by selecting Protect Sheet.

Test the form by emailing it to yourself. Make sure that:

  • Dropdowns and checkboxes work.
  • The Submit button sends correctly via Outlook.
  • All auto-populations are correct.

Conclusion

Creating a fillable form with a submit button in Excel isn’t just a neat trick—it’s a powerful tool to streamline workflows, especially if you need forms populated with data from existing sheets. With built-in functionality like dropdown menus, auto-filling via VLOOKUP, and the ability to automatically send the form through Outlook, Excel can create forms that are as effective as they are user-friendly.

Once you’ve mastered these steps, you can create versatile, professional-grade forms for almost any scenario. Just be sure both you and your users are on Outlook before adding the submit button. Got any questions? Drop a comment below! And don’t forget to subscribe for more Excel tips and tricks.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR