Auto Complete vs Data Validation
Greetings to all,
Most of us are familiar with challenges filtering data containing inconsistent spellings or typos. Here we explore the pros and cons of Excel's Auto Complete against Data Validation tools.
Auto Complete (Alt + DownArrow) - Displays list from contiguous existing data residing above and below active cell but cannot restrict entering of different data.
Data Validation (Alt + a + v + v) - Displays list from predetermined listing or criteria. Allows input restrictions of different data and can display messages when selecting / confirming cell.
Following is a summary of some features or lack thereof between the 2:
1) Alphabetical sorting
Auto Complete automatically sorts listing from A to Z while Data Validation follows the order in source list.
2) Auto updates new entries
Auto Complete listing automatically includes different/fresh entries while Data Validation requires listing to be manually updated.
3) No prior setup required
Auto Complete is accessible through Alt + DownArrow by default while Data Validation requires input cells to have Data Validation applied before use.
4) Removes duplicates in listing
Auto Complete only lists unique data while Data Validation shows duplicate data if within source list.
5) Search by leading characters
Auto Complete jumps to possible matches within list based on leading characters entered while Data Validation does not.
6) Allow blanks in-between rows
Data Validation allows blanks in source list while Auto Complete can only list contiguous data above and below active cell. Contiguous data on immediate left or right columns allow continuation of Auto Complete in active cell. Alternatively, fill blank cells with data like "Not Applicable" to ensure contiguous data.
7) Allow different source column
Data Validation allows source list to reside in different column as active cell while Auto Complete can only list data from the same column.
8) Control user input
Data Validation allows messages to be shown when cell is selected or display error messages that restrict users from confirming data not from source list or within defined criteria. Auto Complete cannot restrict user input.
9) Includes numbers and dates
Data Validation allows source list to contain numbers and dates while Auto Complete ignores numbers and dates. Alternatively input numbers and dates with a leading single quote (') to convert data to text string.
10) Retain row order (User defined)
Data Validation listing follows the order as per source list (user defined) while Auto Complete automatically sorts listing in alphabetical order.
If you need other users to stick to your predefined listing, Data Validation would be more appropriate (beware of users pasting over cells with Data Validation), while Auto Complete would be better suited for non restricted data entry due to its search by leading characters ability.
Let us know in the comments below what are your favoured methods of increasing consistency during data entry.
