Page 129 - Excel 2013 All-in-One For Dummies

By my count, completing the data entry in this Client Name table required me to make a total of 26 keystrokes, 20 of which were for typing in the first, middle, and last name of the first client along with the initial letters of the first, middle, and last name of the second client and the other six to com- plete these entries. If Column A of this Client Name table contains the full names of hundreds or even thousands of clients, this 26 keystrokes is insig- nificant compared to the number that would be required to manually enter their first, middle, and last names in their separate First Name, Middle Name, and Last Name columns or even to edit down copies of the full names in each of them.
Keep in mind that Flash Fill works perfectly at extracting parts of longer data entries in a column provided that all the entries follow the same pattern
and use same type of separators (spaces, commas, dashes, and the like).
For example, in Figure 1-11, there’s an anomaly in the full name entries in cell A9 where only the middle initial with a period is entered instead of the full middle. In this case, Flash Fill simply enters M in cell C9, and you have
to manually edit its entry to add the necessary period. Also, remember that Flash Fill’s usefulness isn’t restricted to all-text entries as in my example Client Name table. It can also extract parts of entries that mix text and num- bers, such as ID numbers (AJ-1234, RW-8007, and so forth).
Limiting data entry with Data Validation
The Data Validation feature in Excel can be a real timesaver when you’re doing repetitive data entry, and can also go a long way in preventing incor- rect entries in your spreadsheets. When you use Data Validation in a cell, you indicate what type of data entry is allowed in the cell. As part of restrict- ing a data entry to a number (which can be a whole number, decimal, date, or time), you also specify the permissible values for that type of number (a whole number between 10 and 100 or a date between January 1, 2012, and December 31, 2012, for example).
When you restrict the data entry to text, you can specify the range of the minimum and maximum text length (in characters) or, even better, a list of permissible text entries that you can select from a pop-up menu (opened by clicking a pop-up button that appears to the right of the cell whenever it con- tains the cell cursor).
When using Data Validation to restrict the type of data entry and its range of acceptable values in a cell, you can also specify an input message that is automatically displayed next to the cell when you select it and/or an error alert message that is displayed if you try to input the wrong type of entry or a number outside the permissible range.
Data Entry 101 111
Book II Chapter 1
Building Worksheets