How to Create a Form in Excel: Step-by-Step Guide

PDF download Download Article
A quick and easy guide to creating forms in Microsoft Excel
PDF download Download Article

Did you know that you can create forms in spreadsheets using Microsoft Excel? It's handy if you don't want to use multiple documents to attach a separate form to your spreadsheet. Read on to learn more.

Quick Steps

  1. Open a blank spreadsheet in Excel.
  2. Enable the Developer tab.
  3. Enter data for the form.
  4. Go to the Developer tab and select a form button.
  5. Protect the sheet when you're done.
Section 1 of 2:

Creating a Custom Form

PDF download Download Article
  1. Click or double-click the Excel app icon, then click Blank workbook . It's in the upper-left side of the page.
  2. The Developer tab is where you'll find the option to insert form buttons, but it isn't included in Excel by default. To enable it, do the following: [1]
    • Windows — Click File , click Options , click Customize Ribbon , check the "Developer" box, and click OK .
    • Mac — Click Excel , click Preferences... , click View under the "Authoring" heading, and check the box next to Developer tab . You can then close the window.
    Advertisement
  3. Type in whatever data you want users to be able to select in your form.
    • This step will vary depending on the information you want to use in your form.
  4. It's at the top of the Excel window.
  5. This option is in the "Controls" section of the Developer toolbar. Clicking it prompts a drop-down menu to appear.
    • Skip this step on a Mac.
  6. Click the type of control you want to use for your spreadsheet.
    • For example, if you want to add a checkbox to your form, you would click the checkbox icon.
  7. Doing so will place your control button on the spreadsheet.
    • You can click and drag your control to the location in which you want to anchor it.
  8. A drop-down menu will appear.
    • On a Mac, hold down Control while clicking the icon.
  9. It's at the bottom of the drop-down menu.
  10. Depending on the button you selected, your options will vary; in most cases, you'll be able to select a cell range or a target cell by clicking the arrow to the right of the "Cell range" or "Target cell" text box and then selecting cells (or a cell) that contain your form's data.
    • For example, if you wanted to create a drop-down menu with a list of numbers, you would click the arrow to the right of the "Cell range" text box and then click and drag your mouse down a column of numbers in your spreadsheet.
  11. It's at the bottom of the window. Doing so saves your settings and applies them to your spreadsheet.
    • At this point, you can proceed with adding other form buttons to your spreadsheet.
  12. Once you've finished adding form buttons to your spreadsheet, you can prevent people from moving or removing the buttons by protecting the spreadsheet:
    • Windows — Click Review in the Excel toolbar, click Protect Sheet , and make sure that any options other than "Select locked cells" and "Select unlocked cells" are unchecked. Enter a password to unlock the document, then click OK . You can then re-enter the password when prompted to finish locking the sheet.
    • Mac — Click Tools at the top of the screen, select Protection , click Protect Sheet in the pop-up menu, and make sure that any options other than "Select locked cells" and "Select unlocked cells" are unchecked. Enter a password to unlock the document, then click OK . You can then re-enter the password when prompted to finish locking the sheet. [2]
    13. Advertisement
Section 2 of 2:

Tips to Creating User-Friendly Forms

PDF download Download Article
  1. 1
    Wrap the text. You want any text to be legible, and wrapping it will ensure that all the text fits in the cell.
  2. 2
    Merge or split cells. Merge cells to make things look cleaner, like in the title cell of your form. This is just a formatting tip that makes your form more readable.
  3. 3
    Hide and show columns and rows. Use these tools to hide spreadsheet data if they are distracting. Alternatively, show cells that are important to the form.
    4. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      10 Proven Ways to Reduce Excel File Size on Windows & Mac
      How to
      Learn Spreadsheet Basics with OpenOffice Calc
      New to Excel? Here's Super Easy Tricks to Get You Started
      How to
      Use Google Sheets: The Ultimate Guide for Getting Started
      How to
      Copy the Same Text to Multiple Cells in Excel: Desktop & Mobile
      4 Easy Ways to Add the Time and Date Automatically in Sheets
      Easily Embed Documents and Files in an Excel Spreadsheet
      How to
      Add Custom Calculated Fields to Pivot Tables in Excel
      3 Easy Ways to Get Rid of Dotted Lines in Microsoft Excel
      How to
      Create an IF-THEN Formula in Excel: Basic Syntax, Multiple Conditions, & More
      How to
      Change an Excel Sheet from Read Only
      How to
      Add Rows to a Pivot Table
      How to
      Make a Google Spreadsheet: Beginners Guide
      How to
      Create a Formula to Increase a Date by 1 Month in Excel
      Advertisement

      References

      1. ↑ https://support.office.com/en-us/article/create-forms-that-users-complete-or-print-in-excel-fd6797c2-30b6-42d8-9921-0feb123f5ca7
      2. ↑ https://support.microsoft.com/en-us/office/create-forms-that-users-complete-or-print-in-excel-for-mac-fd6797c2-30b6-42d8-9921-0feb123f5ca7

      About This Article

      Article Summary X

      1. Open a blank workbook.
      2. Add the "Form" button to Excel.
      3. Create column headers.
      4. Select the column headers.
      5. Click Form .
      6. Click OK .
      7. Enter the first row data and click New .
      8. Enter additional rows. 9. Click Close .

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 459,768 times.

      Reader Success Stories

      • Anonymous

        Jul 17, 2016

        "I was trying to manually make a worksheet that could act as a form. I had no idea Excel had a hidden ..." more
      Share your story

      Is this article up to date?

      Advertisement