Data Validation with Excel Formulas

Today’s post continues the discussion on data validation, introducing a new approach that utilized Excel formulas. This approach provides an additional level of flexibility without the need for complex macros or VBA.

DifficultyIntermediate
Time Est.30 Minutes
VBA/MacrosNo
Template Files2016-10-19-excel-validation-template
Solution Files2016-10-19-excel-validation-functions

Spreadsheet Design Exercise 6

This exercise within the Spreadsheet Design Design series continues the focus on Data Management.

Incorporating Excel’s built-in functions into your project will let your application provide dynamic visuals and feedback to the user. When used in conjunction with Excel’s built-in validation tools and forms elements, you can create professional and responsive forms.

Step 1: Build user input form.

This exercise will start with the same user input form used in the previous exercise. You can download the template here (.xls), or copy the form as presented below. The download files also includes the tabs containing the state list and price list, which will be used in this exercise.

Start by thinking about how each field should be validated. Are there some fields that can be any value? Are there some that should be numbers or currency only?

  • Name: An alpha field that should not contain any numbers or special characters.
  • Address: An alpha field that should not contain any special characters
  • City: An alpha field that should not contain and number or special characters
  • State: In this exercise, only 3 states will be permitted.
  • Phone: A numeric field that should contain 10 or 11 numbers.
  • Item: In this exercise, 5 items can be entered.
  • Quantity: Must be a whole number.

This exercise will include two layers of validation. In addition to verifying the user inputs as permissible, formatting will be done to ensure consistent entries.

Step 2: Set up data validation formulas.

The base of the validation process will be an IF function in the formula bar. This blog will dive into detail on IF functions at some point in the future, but to summarize for now, the formula parameters are as follows:

IF([Test Condition],[Value if True],[Value if False])

The key in this supersize are the arguments which will be passed through the [Test Condition] parameter. If a test returns the value TRUE, the value is sufficient. If test returns FALSE, the value is not acceptable.

Name

This test should check if the value entered into the name field only contains letters. Using the FIND function, Excel will search to see if specified characters exist in a value. This formula incorporates an array to store all the unallowable values. The FIND function returns a digit representing the placement of the character in the value, it’s nested within the COUNT function which counts each value > 0. Note that special characters are will needed to be surrounded in quotes, as many of these characters are operators within Excel. The COUNT function is also nested inside a NOT function for readability, as the boolean now appears in the proper True/False parameter.

=IF(NOT(COUNT(FIND({0,1,2,3,4,5,6,7,8,9,”!”,”@”,”#”,”$”,”%”,”^”,”&”,”*”,”(“,”)”,”>”,”0,TRUE,FALSE)

Address

An address can contain a combination of numbers and letters, along with a few special characters (“.”,”#”, etc). The approach will be very similar to Name, but with some omissions in the array.

=IF(NOT(COUNT(FIND({“!”,”@”,”$”,”%”,”^”,”&”,”*”,”(“,”)”,”>”,” 0),TRUE,FALSE)

City

City will be almost identical to Name, but will omit the dash (“-“) special character form the array, as dashes do exist in some city names. Manchester-by-the-sea is lovely.

=IF(NOT(COUNT(FIND({0,1,2,3,4,5,6,7,8,9,”!”,”@”,”#”,”$”,”%”,”^”,”&”,”*”,”(“,”)”,”>”,” 0),TRUE,FALSE)

State

will use a different approach in than the preceding values. Let’s say WinterWares only sells in 2 states and Washington DC. The form should only accept the values DC, MA, or NY. On Shee2 of the template, a named range, State_List, exists. Using the COUNTIF function, Excel can check to see if the value entered exists on the list. COUNTIF will return the number of instances a value shows up on the list. So, if a user enters NY, 1 will be returned. If NY happened to exist on the list twice, 2 would be returned. As we know that the list only includes one of each value, the formula will test if the function returns 1. The complete formula is as follows:

=IF(COUNTIF(State_List,D7)=1,TRUE,FALSE)

Phone

will introduce us to something quite complicated. Our form asks users to enter digits only, so that tells us that no special characters are allowed. Also, with US number formats, we know that users may enter 1 plus the area code, or just the area code. With that, we will test if the length of the value is 10 or 11 numbers. This will introduce the AND/OR operators and the LEN function. The formula looks like a mess of brackets, but this is what it looks like stripped down:

IF(AND([Symbol Test],OR([Length Test 1], [Length Test 2])), TRUE, FALSE)

And the actual formula:

=IF(AND(NOT(COUNT(FIND({“!”,”@”,”#”,”$”,”%”,”^”,”&”,”*”,”(“,”)”,”>”,” 0),OR(LEN(D8)=10,LEN(D8)=11)),TRUE,FALSE)

Item

will be almost identical to State, only it will check the Item_List. A good thing to note is that COUNTIF can also check ranges without names. If named range Item_List did not exist, the formula could refer to Sheet2!A2:A5. However, given that referencing named ranges is a good practice, we’ll stick with the Item_List.

=IF(COUNTIF(Item_List,Sheet1!D10)=1,TRUE,FALSE)

Quantity

The last and easiest field to validate. This field should test if the number is a number. Excel has ISNUMBER built in to test for that.

=IF(ISNUMBER(D11),TRUE,FALSE)

Price

Utilizing the price list, use a VLOOKUP formula to determine price. The IFERROR function prevents an error from appearing if no value exists for Item. Enter the following formula.

=IFERROR(VLOOKUP(D10,Sheet3!A:B,2,FALSE),””)

Total

Using a simple multiplication formula nested within an IFERROR function, the form can now calculate the Total.

=IFERROR(D11*D12,””)

We have now assigned layer 1 validation rules to all the user input fields. Try filling out the form with your information. All values in column A should say TRUE.

Step 3: Check if field is empty.

The current validation formula has one significant glitch. The Name, Address, and City fields still pass validation if they are left blank

This exercise will add one more layer of user-side validation by testing if the first validation test has been passed, and checking to make sure that the user actually has content in the field. This step will also incorporate Excel’s built in conditional formatting feature to provide professional feedback and responsiveness.

For the first part of this process, the 2nd validation layer will return a value of TRUE or FALSE in column E. If the field is blank or the first validation check failed, a value of false will be returned. If the field contains a value and the first validation check passed, a value of TRUE will be returned. This will be accomplished with the following formula.

=IF(AND(NOT(ISBLANK(D4)),A4=TRUE),TRUE,FALSE)

This formula can be copied down column E, next to the remaining 6 fields. With the ISBLANK function, Excel tests if the cell is empty. As we want to check the combination of a value in a cell, the conditional includes the AND modifier to check if the 1st validation layer has a value of True.

Step 4: Provide user feedback.

The current state of the validation process WinterWares order form leaves something to be desired. Simply providing the user with “True” values does not suffice as good or intuitive feedback. By utilizing Excel’s built in conditional formatting, we can provide the user with professional looking validation feedback.

With this approach, a conditional format will return a green checkmark, or a red X. To activate Conditional formatting, click on cell E4, then click Home > Conditional Formatting > Icon Sets, Then click on the checkmark/x combo.

Set up conditional formatting as data validation feedback.
Set up conditional formatting as data validation feedback.

After selected, a green checkmark will appear in cell E4. While cell E4 is still active, return to the Conditional Formatting menu, then select Manage Rules. The following window will appear.

The Conditional Formatting Rules Manager

Click on Edit Rule. The following window will appear.

Edit Formatting Rule dialog box.

You’ll need to update the conditional criteria. Make changes as outlined in red below.

Edit Formatting Rule dialog box.

Press OK – the window will close. You can now fill out the form and see the full conditional formatting features.

Data validation features are now responsive to user inputs.
Data validation features are now responsive to user inputs.

Step 5: Final data cleaning.

With the data now validated, it’s a good idea to provide a final scrub to get data in a clean format for final entry or display. While the validation process ensures that data meets minimal criteria, there are still a few anomalies that could come through. For example, a user could enter their name in all caps. Likewise, we can utilize Excel formulas to properly format phone numbers, or concatenate fields, just to name a few.

For the purpose of this form, the data will be put in the format of a shipping label or invoice. For this, the application will:

  • Put Name in proper format.
  • Concatenate Address, City, and State.
  • Add a standard format for phone number.
  • Draft a summary sentence for the purchase.

The formulas are as follows:

Name

=PROPER(D4)

Address

=PROPER(D5) & “, ” &D6& “, ” &D7

Phone

=”+1 (” & IF(LEN(D8)=10,LEFT(D8,3),RIGHT(LEFT(D8,4),3)) &”) “&LEFT(RIGHT(D8,7),3)&”-“&RIGHT(D8,4)

Order

=”Purchased ” &D11 & ” ” & D10 & ” for ” &TEXT(D12,”$#,###.00″)& ” each, totaling ” &TEXT(D13,”$#,###.00″)

A comprehensive overview of these formulas will be provided in a future post, but for the purposes of this exercise, you can copy these formulas directly into the sheet.

Finally, it’s a good idea to hide “mechanical” columns, such as column A, which includes the layer 1 validation booleans. The final result should look as follows:

Fully validated data.

Screen Captures

2016-10-19-excel-validation-functions

Other Points

  • The techniques presented in this post are to emphasize approaches to validation using Excel functions. Two fields in this example present serious flaws: State and Item. In this example, it is relied upon the user to manually type in a word that exists on a list located elsewhere. A new user to this form would likely have no idea what values are allowable. With that said, in these circumstances, it is strongly advised to utilize Excel’s built-in drop-down capabilities, as explained in the previous post. This will present the user with a list of options to select.
  • In this example, data will begin appearing in the formatted summary (below row 15) before the user is done completing the form. For a more professional look, it is possible to include conditionals which will wait until all data is valid before presenting the formatted summary.
  • If this data were to be added to a dataset or database with a VBA script, the script could either check if each field has a TRUE value, or–more efficiently–a final validation status formula could return a TRUE value once all fields are TRUE.
  • It would have been possible to include both the layer 1 validation and the layer 2 validation (for empty cells and conditional formatting) in the same cell. However, this approach could have further complicated some already complicated formulas. For the easy of explanation and maintenance, the formulas have been separated.

Drawbacks/Bugs

  • While this approach does offer more flexibility than Excel’s built in data validation features, it is not as secure. Tips to add security to your workbook will be provided in a future post.
  • Complex validation requirements require elaborate formulas which can become unwieldy. Likewise, there are some limitations with Excel formulas, such as a limit of 3 nested IF functions. These issues can be circumvented in validation techniques with VBA, which will be discussed in the next post.

Leave a Reply