Last week I discussed using Excel effectively, in order to ensure it best serves your small business. I’m going to expand on that today, giving you some ideas about how you can use Excel features to validate the data that is being entered into your Excel sheet.
Are you capturing data in Excel, but finding that the quality of that data isn’t great? Maybe someone types in their name in the phone number field, or they type a date in a field where you’re expecting a dollar amount. That’s common with Excel, since it’s not really intended for use as a database tool. Microsoft Access and Microsoft SQL Server both have some built-in features that help to protect the quality of data entered. Excel, though, is kinda the little brother of those tools. While it can be used as a database tool, the controls to protect your data aren’t there by default.
You can fix some of those issues, adding some simple (or not-so-simple) validation to your Excel sheet. For example, let’s say you have a column where you are collecting a dollar amount. You can set up that column to accept only numbers, throwing an error if someone types in text.
In this blog, I’m going to explain some of the most common types of validation that you can turn on in Excel. I won’t go into the details of each method, since there are many resources already available to do that. Instead, I’m going to let you know what’s available in Excel. After that, it’ll be up to you to search for details on the one you’re interested in.
Numeric values
You can specify that only numbers can be entered into a cell – no letters. You can choose to allow whole numbers only (no decimals), or numbers with decimals. If the user breaks this rule, you can choose to give them a warning or an error. Here’s an example of a warning:
How? Use Excel’s built-in Data Validation feature. This is available on your “Data” ribbon. For more info, search for Excel Data Validation Whole Number or Excel Data Validation Decimal, or check Excel’s help.
Drop-down list
You can give the user a drop-down list, and force them to choose one of those values. The valid values can be a static list (which is easy) or a dynamic list (much harder). With a static list, you simply type in a list of possible values in another area of the workbook:
In this example, I set up the list of options in column F, and set up a drop-down in column B. Of course, this is a weird example. You normally wouldn’t put the list of options on the same sheet as the drop-down. In most cases, I would put this type of list on a separate worksheet. I only included it on the same sheet so that you could quickly see it in action.
How? For the simple static drop-down list, use Excel’s built-in Data Validation feature, available on your “Data” ribbon. For more info, search for Excel Data Validation List, or check Excel’s help. To create the more complicated dynamic drop-down list which changes as users enter data, you’ll have to use some VBA code, or use the “INDIRECT” function in conjunction with the built-in data validation. Here’s a great example of that: https://www.contextures.com/xldataval02.html.
Date or time
You can limit a given cell to only allow a date to be entered, or only a time. If you choose Date, Excel will ensure that a valid date is entered. It’s smart enough to know that February 31 is not valid, for example. It’s even smart enough to know about leap years. For Time values, the user can type in the time in any recognized time format: 4:08 am, 14:29 and 02:30 PM would all work, for example. In this screen shot, I violated the Time rule, and you can see the resulting error message:
How? Use Excel’s built-in Data Validation feature, available on your “Data” ribbon. For more info, search for Excel Data Validation Date or Excel Data Validation Time, or check Excel’s help.
Pattern
Sometimes your data has to fit into a specific pattern. For example, maybe your customer number is formatted as: 99-ABCD (two numbers, followed by a dash, followed by four upper-case letters).
How? If you’re well versed in complex Excel formulas, you can do this with the built-in Data Validation feature. The following website shows an example of this: https://bettersolutions.com/excel/data-validation/particular-format.htm. This can also be accomplished with VBA code, and if you’re familiar with VBA, this is probably the simpler solution.
Required Field
With this validation, you can ensure that the user enters a value in a specific cell. If they leave it blank, they’ll get an error message.
How? This one is surprisingly difficult. You may think that using the built-in Data Validation with the Text Length would work. This option would allow you to specify that the text length must be greater than 0. Unfortunately, it doesn’t work that way. Even if you un-check the “Ignore Blank” option, this logic will ONLY run if the user types something into the cell. If they simply skip the cell, this won’t work. Instead, you’re going to have to use a little bit of VBA code to do this.
There are many other scenarios that you could implement in your Excel file. The possibilities are only limited by your imagination and the amount of time you have to invest in learning/developing these tasks. I hope that this article has given you some ideas of how you can effectively use data validation to ensure that your Excel sheet contains useful and accurate data.
Good luck, and don’t hesitate to reach out to me if you need help implementing these features.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2021. All rights reserved.