Autocomplete Data Validation in Excel – what is this update to Data Validation and how does it work?
This week’s hint and tip is on the autocomplete data validation feature in Excel. Data Validation is a useful feature in Excel that allows for restrictions to be applied to what can be entered into a cell. Commonly this is used for creating drop down lists to choose options from. This drop down list can be very useful as it can make data entry simpler and quicker for users of spreadsheets. Within the last year or so an update to this feature has appeared called Autocomplete. Data validation itself is covered in our Advanced Excel training course but the update isn’t specifically. So we decided to do a hint and tip on it. We are going to go through it now below.
Data Validation in Excel
Data validation is a feature in Excel that allows for quicker and simpler data entry into spreadsheets. We have done a hint and tip on how to do this previously, but a recap of the steps are found here.
Click into the cell you wish to insert the data validation. Go to the Data Tab, click on the data validation option on the ribbon. From here choose the ‘List’ option from the drop down menu of choices for ‘Allow’ and then in the Source box go to your list and select the cells. You can then add in details for input message and error alert on the tabs in the window.
Once you have finished, click OK. This will have now inserted the drop down arrow into the cell. If you now click on the drop down arrow, you should now have a list of options appear to choose from.
Autocomplete update in Data Validation
In recent years, the data validation feature has had an update to include an autocomplete option. This update allows another way for the options to be selected from the drop down list. As you start to type an entry, a ‘filtered’ drop down list appears with options that match the characters you have typed in. Once the option you want is selected (you can do this by using arrows on your keyboard), hit enter and it will insert that option into the cell for you.
The video below talks through the autocomplete feature with Data Validation and how it can be used in Excel.
Take a look below at the video to find out more and then try them out on your own computer!
We hope you have enjoyed this hint and tip on using the autocomplete data validation feature in Excel. Why not take a look at our previous video hint and tip on creating folders in Outlook?