Simplify—And Standardize—Data Entry By Creating An Excel Form

If you are like most Excel users, you probably use the application primarily to enter and store data. And you probably type all your data, right? There’s a better way! With just a bit of know-how, you can create user-friendly forms that will make data entry much simpler—and error-free—thanks to drop-down lists and subtotaling tools.

And you don’t have to spend money on expensive dedicated software, notes technology expert Mike Thomas in his AudioSolutionz webinar “How to Build Powerful Form-Based Applications.”  Excel can empower you to create sales order forms, invoices, loan agreements, surveys, and more.

Start here: When you share a spreadsheet with colleagues, don’t allow others to insert any old data they’d like in a cell. Instead, create a dropdown list that allows you to maintain greater control and standardization over inputs—while avoiding misspellings as well.

To create drop-down lists, follow these steps:

1. Open a new worksheet and type the entries for your dropdown list in a single column, one entry per row.
2. Select all of your entries, and then right click on the selection and choose Define Name from the shortcut menu.
3. In the resulting New Name dialog box, type a name for your dropdown list in the Name text box. The name cannot have any spaces.
4. Click OK.
5. Click in the cell in your worksheet where you want your dropdown list to appear.
6. Select the Data tab from the main ribbon, and in the Data Tools group, click Data Validation.

Important: If Data Validation isn’t clickable, your worksheet might be protected, shared, or linked to a SharePoint site. Remove the sharing or protection to continue.

Add an Error Message

To help people decide which drop-down list option to select, and to alert them that a cell contains a drop-down list, you can create a message that appears when they select a cell. Follow these steps to add an alert:

1. In the Data Validation dialog box, on the Settings tab, select List from the Allow list box.
2. In the Source text box, type the equals sign and then your list’s name.
3. Select the In-Cell Dropdown check box. Click the Input Message tab. If you want a message to appear when a user clicks on the list, enter a title and message. If not, deselect the Show Input Message When Cell Is Selected check box.
4. Select the Error Alert tab.
5. Select the Show Error Alert After Invalid Data Is Entered check box.
6. Select Stop from the Style list box, then enter a title and error message. The title is optional (Microsoft Excel will appear if you leave the title blank). An error message might state, “Invalid value. Please select a value from the dropdown list.”
7. Click OK to create your dropdown list.

Set Up Auto-Subtotaling Capabilities

When your data contains a set of values or categories, it can be useful to create automatic calculation functions. For instance, you can set an Excel sheet to automatically calculate a subtotal for each category in a table. To create automatic subtotals:

1. First you need to sort the data based on that category, then use the Subtotal feature to save yourself the trouble of adding up each subtotal yourself. Let’s say you have a list of sales completed by reps from your Central, East and West offices. We will sort the list based on region (Central, East, and West) and calculate the total sales for each region.
2. Place the insertion point in your table and click on the Home tab on the main ribbon. In the Editing group, click on Sort And Filter.
3. Choose Custom Sort from the resulting dropdown list.
4. Sort the table based on the column containing the categories you want subtotals for.
5. In our example, you would sort based on the column containing the region. Let’s say this is Column E.
6. To create the subtotals, ensure your data has a header row.
7. Click on any cell in Column E.
8. Click on the Data tab on the main ribbon.
9. In the Outline group, click on Subtotal. When the warning pops up, click OK to indicate your data has a header row.
10. In the resulting Subtotal dialog box, select the title for Column E.
11. Click OK. Excel automatically calculates your subtotals as well as a grand total.

Build Form-Driven Applications and Spreadsheets

Do you often have to create forms to capture data and perform your daily work? Whatever the form type , Excel has the tools to help you customize it to your needs.

In his webinar, Thomas shows you how to build professional, eye-catching form-driven applications and spreadsheets using techniques such as: naming cells, data validation, formatting, formulas and functions, and simple automation—all with an eye toward making your work easier, less time-consuming, and free from errors.