Simplify & Standardize: Data Entry via Excel Forms

2 tricks help you control the way your data appears in any Excel sheet.

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 excel 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.  Excel can empower you to create sales order forms, invoices, loan agreements, surveys, and more. So who’s scared of data entry now?

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 your entries, 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 alert users that a cell contains a drop-down list and help them decide which option to select, 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. After Invalid Data Is Entered check box select the Show Error Alert .
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.

Snap quiz: What’s an Absolute reference in Excel?

(This post first appeared in a ProfEd blog)

By Kimberly G