Data Validation and Form Controls

I often describe Excel spreadsheets as having an American Wild West atmosphere where anything goes. Type anything you want, anywhere you want. Many users combat this by protecting certain worksheet cells as I’ll discuss later in this chapter. However, worksheet protection only restricts whether a cell can be edited or not.

Data Validation allows you to restrict the type of data that can be entered into specific cells. In addition, Data Validation enables you to create on-demand documentation that appears on screen when a user clicks on a cell.

Even better, Data Validation can provide immediate feedback if a user doesn’t comply with the rule. Determined users can circumvent Data Validation, so we’ll offer two defenses. The first is a means to audit invalid entries. The second approach will enable you to raise the bar by using Form Controls to restrict inputs.

In this chapter, the following topics will be covered:

...

Technical requirements

Everything in this chapter will work the same in Excel for Windows and macOS. You will need to enable the Developer tab of Excel’s ribbon, which we’ll describe in the Exploring Form Controls section.

The workbook that we used in this chapter is available for download from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter05.

Introducing Data Validation

The Data Validation feature allows you to create rules that limit the type of data that can be entered in one or more worksheet cells. The rules can limit users from entering numbers, list items, dates, or times, and you can create custom rules. You can also craft a data entry prompt that appears when the user clicks on a cell that contains Data Validation and follow up invalid entries with an error prompt. Let’s begin by comparing cells C4 and C5 on the Data Validation worksheet of this chapter’s example workbook as follows:

1. Select cell C4 as shown in Figure 5.1.

Figure 5.1 – Example data for this chapter

2. Choose Data | Data Validation to display the Data Validation dialog box shown in Figure 5.2.

Figure 5.2 – The Settings tab of the Data Validation dialog box

3. Notice how the Allow field on the Settings tab of the dialog box is set to allow Any value. This...

Implementing Data Validation rules

As you can see, Data Validation rules limit the types of entries that users can make within worksheet cells. This section will discuss each of the Data Validation rules that you can assign to worksheet cells.

Any value

As I discussed earlier in the chapter, Any value means that the user can type any value that they wish. Clearing Data Validation rules as we did in the preceding section resets the cell(s) to allow any value. Every cell in a worksheet defaults to Any value.

Tip

You can choose to fill in the Input Message tab for any cell while leaving the validation rule set to Any value. This allows you to create documentation that appears on demand when a user clicks on a worksheet cell, and it is an alternative to adding a note or comment to a cell. In this context, you would leave the Error Alert tab blank since there’s no rule in place that would trigger an error prompt.

The next rule on the Allow list is Whole Number.

...

Protecting Data Validation cells

A downside of Data Validation is that users can purposefully or inadvertently remove rules from cells by copying and pasting. Excel doesn’t ask, are you sure?; it simply erases any Data Validation settings. The following steps show you how to protect your Data Validation rules:

1. Select any cell on the Data Validation worksheet, and then choose Home | Find & Select | Data Validation to select all cells that have Data Validation rules assigned.
2. Press Ctrl+1 (+1) or choose Home | Format | Format Cells | Protection.
3. As shown in Figure 5.28, clear the Locked checkbox and then click OK.

Figure 5.28 – Unlocking cells

4. Choose Review | Protect Sheet, optionally enter and confirm a password, and then click OK.

Tip

You must use the Protect Sheet command to protect your Data Validation settings. Unlocking the cells is only a precursor step that on its own does not offer any protection...

Auditing Data Validation cell inputs

The Circle Invalid Data command in Excel enables you to identify cells where a user bypassed Data Validation settings by pasting information into a cell. The following steps show you how to perform the audit:

1. Choose Review | Unprotect Sheet and enter the password to unprotect the worksheet if necessary.
2. Choose Data | Data Validation drop-down menu | Circle Invalid Data, as shown in Figure 5.29.

Figure 5.29 – The Circle Invalid Data command

Red circles will appear around any cells where the user has entered invalid data. No feedback will be offered if all cells comply. Keep in mind, though, that the red circles vanish when you save the file, or when you choose Data | Data Validation drop-down menu | Clear Validation Circles.

Now that you have a sense of the limitations of Data Validation, let’s explore a more robust alternative known as Form Controls. We’ll first need to unhide a hidden...

Enabling the Developer tab

The menu across the top of the Excel screen is known as the ribbon. As you may have noticed, certain tabs appear and disappear from the ribbon based on context. For instance, a Table Design tab appears when you click on a Table in your workbook and vanishes when you click on any cell that is not part of a Table. The hidden Developer tab has commands related to creating macros, working with XML files, and creating Form Controls. You must enable the Developer tab if you wish to create Form Controls, as follows:

1. Choose File | Options | Customize Ribbon.
2. Click the Developer checkbox on the right-hand side, as shown in Figure 5.30, and then click OK.

Figure 5.30 – Enabling the Developer tab

Now that you have enabled the Developer tab in Excel’s ribbon, we can compare Form Controls to Data Validation when additional control or different interactivity is warranted.

Exploring Form Controls

Form Controls are objects that you can use to add interactivity to an Excel worksheet. Unlike Data Validation, users can’t circumvent Form Controls by pasting over the data. Figure 5.31 shows the Developer | Insert command and displays three of the types of Form Controls that are available.

Figure 5.31 – Insert command and Form Controls

I’ll demonstrate the following Form Controls:

• The Combo Box in cell D5
• The Checkbox in cell D7
• The Option Button in cell D9

I don’t have space to go very deep on Form Controls, but mastering the preceding three types will give you a good sense of how Form Controls work.

Nuance

As you can see in Figure 5.31, Excel offers both Form Controls and ActiveX Controls. Typically, Form Controls will enable you to create the interactivity that you’re seeking. ActiveX Controls are similar but more sophisticated. For instance, you must enable Design...

Checkboxes and Option Buttons

Checkboxes and Option Buttons enable you to allow the user to make choices within a spreadsheet. Users can tick as many Checkboxes as they would like, whereas Option Buttons (sometimes referred to as radio buttons) allow a single choice within a group. Let’s compare the differences between Checkboxes and Option Buttons.

Creating Checkboxes

Cell D7 of the Form Controls worksheet contains a Checkbox that is linked to cell K7. When you click the Checkbox, Excel puts the word TRUE in cell K7. If you click the Checkbox again, Excel puts the word FALSE. If you wish to reset the Checkbox, simply erase cell K7.

Now let’s see how to add a Checkbox to cell D8 as follows:

1. Choose Developer | Insert and then click the Checkbox command, which is the third command on the first row under Form Controls, as shown in Figure 5.31.
2. Click in cell D8 to create the Checkbox.
3. If needed, right-click on the Checkbox to display the handles...

Summary

In this chapter, you learned how to use Data Validation to limit users’ responses within specific worksheet cells. The default rules you can establish include Whole Number, Decimal, List, Date, Time, and Text length. As you saw, you can also create custom rules to establish any sort of limit that you may need.

Data Validation is an imperfect feature that users can bypass by pasting data into cells that contain validation rules. Furthermore, any data that is already in cells that you apply Data Validation to is exempted from the rules. Fortunately, you can audit Data Validation entries by having Excel draw red circles around the invalid inputs.

Finally, you saw that Form Controls offer an alternative to Data Validation that creates restrictions that users cannot bypass by pasting data. In short, Data Validation and Form Controls are all about adding interactivity to spreadsheets.

In the following chapter, you’ll see how you can use What-If Analysis tools...