For the past few weeks, I’ve been talking about methods of keeping your Excel data clean. If you’re using Excel as a database tool, you know that users can develop bad habits that result in missing, duplicate, or erroneous data. In recent posts, I’ve talked about validating U.S. states and phone numbers. Today we’ll be discussing ways you can ensure that your numeric fields stay numeric.
Whenever you ask users to enter a numeric value, you can guarantee that sooner or later, they’ll break the rule. Take a look at the Credit Limit column in this example:
Our well-meaning users have entered a variety of values in the Credit Limit field. If we plan to use this column in formulas, many of those values are going to cause errors.
To head off those problems, you’re going to want to put some rules in place that will ensure that only valid numeric values are entered into this column.
The Easy Way
The easiest way to do that is by using the Data Validation rule in Excel. You can simply set the column to allow only whole numbers or decimal numbers, as you can see here:
Once I apply that rule, I will only be able to add numbers (either with or without decimals, depending on which option you chose). You can also specify a range of valid values, if that’s appropriate. I’ll set up a “Whole Number” validation on this column, since I want users to enter only dollars, with no cents. I’ll also specify that I want the number to be between $5,000 and $100,000. Here’s what that looks like:
After applying the rule, I am only allowed to enter numbers, along with applicable punctuation, such as dollar signs, commas and periods.
Mostly Numbers...
This validation works great, and my data will be much cleaner for it. But what about the scenario where you need to allow some text in this cell. Let’s say, for example, that we will allow users to type in “Pending”, meaning that our credit department is evaluating, and has not yet set a credit limit. The user can only type in “Pending”, or a valid number. This makes our data validation more complicated.
We’re going to have to use two rules. One of these two rules must be true:
1. Value is “Pending”
- OR -
2. Value is numeric
Since we’re only allowed a single validation rule on each cell, we’ll have to combine these two statements into a single formula. First, let’s see what the two formulas are, and then we’ll nest them.
Rather than using the built-in Whole Number or Decimal validation, we’re going to check for a numeric value with the “IsNumber” function. My data validation for that would look like this:
Notice that the “Allow” option has been changed to “Custom”. This will allow me to type in any valid Excel formula as the criteria.
The formula I’ve entered uses the “IsNumber” function to determine if cell D1 is a number. Does it seem odd to you that I’m only check cell D1? I’m not, actually, thanks to relative references. With relative references, the row number will automatically be adjusted relative to whichever row we’re typing on. So, if I’m typing a value in cell D11, the formula for that cell will be “=ISNUMBER(D11)”.
This works great, and the column now allows any valid number. Now let’s look at the validation that would allow only “Pending”:
Once again, the reference to cell D1 is relative – it’ll adjust automatically for whichever row we’re working on. This formula might be a bit confusing, because of the two equal signs. The first equal is necessary so that Excel understands that we’re writing a formula. The second is the check to see if the cell in question is equal to “Pending”.
This validation is not case sensitive, so it’ll accept “PENDING”, “pending” and “Pending”, among others.
Now I need to combine the two validations. I’ll do that by embedding them in an “Or” condition. It looks like this:
The only problem with this formula is that it allows any number, in addition to “Pending”. Earlier we had limited it to only $5,000 to $100,000. If we want to include that, our formula becomes more complex. In English:
Must be “Pending”
– OR –
Must be a number AND must be >= 5000 AND must be <= 100000
My new formula is long, and I have to expand my dialog box to see it all. It looks like this:
This now works great allowing me to enter “Pending”, or any number between 5,000 and 100,000.
Next week, I’ll finish out the series on Excel data validation by looking at how to prevent duplicate values. If you’d like to be notified when that post goes live, subscribe using the form below.
Are there other validations you’re interested in? Post them in the comments below, and I’ll extend this series to cover them.
Let me know if you’d like to set up a phone call or Zoom to talk over your Excel data issues. I love working with small businesses, and I’m always generous with my time. If I can answer a quick question to get you moving forward, I’m happy to do it. If your project is more extensive, let’s set up a one-on-one consultation. Reach me via email (kmartin@northportsoftware.com) or phone (978-582-9550).
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.
#northportsolutions #learningneverstops #dataanalysis #smallbusiness #excel #microsoftexcel #msexcel #excelformulas #exceltips
© North Port Solutions, LLC, 2022. All rights reserved.