Excel: Using the Validate Data Feature

I began writing this series of blogs on Excel for Microcontroller Central. When it was shut down, Brad Albing (then editor of Planet Analog) agreed to copy the existing database across as a base for the new blogs that I had up my sleeve. When I gave him the list, I obviously forgot this one, which is essential to complete the series. It was in fact the very first one I wrote. Please forgive my oversight.

In this blog I will attempt to show how to use the data validate feature to simplify data input to a worksheet. Often when you create a worksheet for more than one use, the user is expected to input data into a cell or a number of cells on the worksheet. Even if you are going to be the only user, remembering the format of data that is needed can be challenging. Through Visual Basic for Applications (VBA) it is possible to use many different input methods, but this requires some programming knowledge and some time. The Data Validate feature in Excel covers most input requirements without any need for programming. It also covers a bunch of input data forms.

To start, click on any cell in the sheet. Then select the Data tab on the ribbon and follow up with the Data Validation option. Click on the arrow in the “Allow:” field. The dialog in figure 1 should appear. The drop-down lists the type of data that will be contained in the cell. If you choose a number (decimal or whole), you can define the range of numbers permitted by using the minimum and maximum settings that appear contextually in the dialog. As you can see there is also the option for other numeric formats such as time and date. You can also select text and restrict the number of characters that are entered.

Another great option is one to create a quasi “spinner,” in which there is a drop-down list of options that the user can choose from, with no other options possible. The way this works is that you define your options as a series of consecutive cells (horizontal or vertical) somewhere on the sheet, preferably away from where the action takes place. Then choose the “List” option on the menu. Once selected the contextual field, “Source” appears, as well as the “In-cell dropdown” option. I have experimented with the latter, but if it is not checked the feature doesn’t seem to work. If you remember my blog Allocating MCU Resources Accurately I describe a method of allocation possible functions to a pin. These options are contained in cells G11 to G15 in Figure 1. Once you select the “List” option you will see the dialog in Figure 2.

You can click the icon at the right of the “Source” filed and block the list that you want. There is probably a way the make this list expand dynamically, but I have not experimented with that. If I feel that I am likely to need additional entries, I simply define the range to include some blank cells at the bottom of the range. The text in the list can be adjusted as you like without any additional compilation steps. With the in-cell option checked, the cell (E8 in this case) will have a drop-down arrow that will display the entire list when clicked.

The nice thing about this feature, like most of the cells in Excel, is that it is easy to copy. In the example I described in my earlier blog, there were quite a number of pins of the microcontroller that could be configured in this way. Once you are happy with the way one pin has been developed, simply copy the cell to all the other associated cells.

But wait there's more! You will notice the additional tabs in the Data Validation dialog. In the tab marked “Input Message,” you create a functionality whereby when the user clicks on a cell, you can have a message appear with a title and other information, such as what the user is expected to input.

Finally the “Error Alert” tab in the Data Validation dialog allows you to customize the message that is displayed if an erroneous value is attempted. It also defines how you want Excel to react.

All in all, I have found this feature to be extremely flexible and extremely easy to use. I hope you managed to gain something from this.

4 comments on “Excel: Using the Validate Data Feature

  1. eafpres
    June 5, 2014

    Hi Aubrey–another great topic and writeup, thanks.  I have commented before that one of the issues with using Excel for critical analysis is that most worksheets/workbooks have errors that go undetected.  The discipline to use validation on the input obviously decrease the possible sources of problems, but requries work.  I completely agree with you that once you get the hang of it, it is easy to replicate.

  2. antedeluvian
    June 5, 2014

    When I sent this blog to Steve, I skipped through it. Perhaps I should have been more thorough. As I mentioned this blog originally appeared on Microcontroller Central (MCC) and It actually refers to another blog which went into the ether along with MCC.

    The nice thing about this feature, like most of the cells in Excel, is that it is easy to copy. In the example I described in my earlier blog , there were quite a number of pins of the microcontroller that could be configured in this way

    That long, lost blog referred to a design idea of mine “Program “excelerates” microcomputer-I/O allocation” and the point I am making here would be just as valid by referring directly to the design idea.

  3. Sachin
    June 30, 2014

    @Aubrey, thanks for the post. I am curious to know if I can refer to two lists simultaneously ? Can I concatenate the data lists when I am referring to different lists ?


  4. SunitaT
    June 30, 2014

    @Aubrey, thanks for the post. Adding eror message will be useful when you handover the excel with the other team members, where they can find the information on the go.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.