Excel Mini Episode: Data Validation

Intro
Hello, and welcome to the CPA Australia Podcast. Your weekly source for accounting, education, career and leadership discussion.

Neale Blackwood CPA:
Another useful option that's on the data ribbon tab in Excel is the data validation icon. It's a dropdown and so it has three options.

Neale Blackwood CPA:
So the first option in the dropdown is actually data validation again. This is a very popular feature. It is the technique you can use to insert a dropdown in a cell, which is a frequently used feature. There is a keyboard shortcut to access data validation as well, and this is pressed in sequence. So you press Alt A V V, that's V for Victor, and you press that in sequence, and that will open up the data validation window. So there's three tabs on that window. The settings tab is how you set everything up. There's an input message tab and an error alert tab. Now the input message tab allows you to put a message in there so that whenever the cell is selected, a little box pops up and you can actually have a description of what's required for that cell. Just a heads up on that particular feature, it can become annoying after a while. So just use it sparingly. If someone's only using the file one or two times, it's okay, but if you're using that file every day and the message keeps popping up, it does get a bit old.

Neale Blackwood CPA:
The error alert tab, however, is excellent because when you use data validation, and if you do enter an invalid entry, a generic error message pops up. And so you can actually create a descriptive error message using that error alert tab. So you can describe what is expected from that cell, which can be a little bit better than having the input message pop up all the time. So there are a number of different data validations that you can apply. There are number based validations. So, you can select a whole number or a decimal, and you can specify a minimum, a maximum, a few other options are there as well.

Neale Blackwood CPA:
Now you can type in the minimums and maximums, or you can link them to a cell on the spreadsheet. So that means you can have some formulas that specify what is expected in certain cells. So that can be quite flexible. You can also do dates. So to make sure a date is entered. And again, you can have a high date and a low date. So a from and a two, so you can specify those and also link those to the spreadsheet as well, if needed.

Neale Blackwood CPA:
Now, by far the most popular type of data validation is a list. So that is the dropdown in the cell. So when you create it, there's a little dropdown pops up on the right hand side of the cell, so the user can select from the list. So it saves having to type an entry. You can still type an entry, but you must enter something that is on the list. It's not case sensitive, by the way.

Neale Blackwood CPA:
It's really easy to create. You just click the list option in the allow dropdown, and in the box that opens up at the bottom, you just specify where the range is that has the list of entries that you want to display. If you've got a short list, what you can do is just type it into the box. So for example, if you had a simple yes/no dropdown that you wanted, you can just type in yes, comma, no, and that will display it. You don't need to put any quotation marks or anything like that around it, just type in yes, comma, no, and that will display the two entries. If you wanted more, you just put more commas. Each comma is basically a line break. And so you can type as well as link to an existing list in the sheet.

Neale Blackwood CPA:
Now, there is a limitation with data validation. You think you can't get an invalid entry into the cell, but it is possible unfortunately. If someone uses paste values, that can override the data validation and you can get an invalid entry into a data validation cell. So that means you do need to have a separate validation calculation performed if you really want to be certain that you have valid data. So keep an eye out in the black, I have written an article that does cover some techniques to work with data validations. There is an option under the dropdown for circle invalid data. The problem is it doesn't tell you if there is any invalid data. And if there are, it doesn't tell you how many. So it's something that isn't really that helpful because you actually have to check each individual cell to see if there's a circle around it.

Neale Blackwood CPA:
It also doesn't update. So if you correct it, the circle doesn't go away. So you actually have to physically turn off the circles when it comes to the invalid data. So data validation is a useful feature to add to your spreadsheets. As I said, the list feature tends to be the most popular, but you certainly can use the other ones. By the way, I should mention there is a custom validation that is at the end of the selections. And that allows you to put a formula into the data validation. And so that one can be fairly complex and you can build some fairly useful restrictions. As a simple example, let's say cell A1 contains the word yes or no. It could even be a dropdown. And what you could do is you could change that cell. So if, if the cell says, yes, then you could have a range that people are allowed to make entries into.

Neale Blackwood CPA:
And in the data validation for that range, now you can only do one type of data validation. So, in this case, you wouldn't be able to limit what they put in, but you can limit whether they make an entry in this case, because we're going to use the custom. And it would be a very simple custom formula. It would just be equals A1 equals yes. And the yes would have to be in quotation marks. And so that custom formula would allow people to make entries in the cell as long cell A1, had the word yes in it. But if you change that to no, then when someone tries to input, it won't let them. So, it's a little like protection. Again, it's not infallible because you can still use place values on that range. But it does limit people from making an inadvertent entry. If you do need to be able to say, turn off and turn on the ability to make an entry. It's a sneaky way to do a protection on a cell without actually applying a password or anything like that. That's worthwhile checking out. But again, you can use the formula to create some fairly complex restrictions on what is entered into the cell.

Neale Blackwood CPA:
Okay. I hope you've found that useful in the data validation feature. As I said it is quite popular and have a play with it and see how you go.

Outro:
Thanks for listening to the CPA Australia Podcast. For more information on today's episode, please visit the show notes at www.CPAaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify or Stitcher.