Do you want your data to look consistent at all times?
Do you require dates in specific consistent formats?
Do you need the text case to be consistent?
Need to prevent or remove extra spaces from text cells?
All of the above items are things that are regularly inconsistent in worksheets, especially when there are multiple users accessing and adding data.
Over the next few blog posts we will be covering creating a worksheet that will prevent inconsistency and also discovering how to correct incorrectly added data efficiently.
The upcoming blog posts will cover the following areas:
- Prevention using formatting
- Prevention using validation
- Correction using formatting
- Correction using formula
This post will demonstrate examples of common incorrect data input and explain a little of how excel works and why data looks the way it does.
Default Excel Settings
All excel cells are set with the formatting of General which means that excel will look at the type of data entered and make a decision on what the data is and therefore how it should be formatted.
e.g. The table below gives examples of how specific data is identified by excel.
Common data entry issues
Containing /
As shown above if you are required to enter data containing a / then excel can read this in a multitude of ways depending on the numbers entered. As 12/1 contains a / Excel believes this maybe a date and will automatically convert it to the appropriate date format.
Containing leading 0
When dealing with number entry if there are no text characters or punctuation etc. then excel will know it is a number. However Excel does discard leading zeros e.g. entering a phone number of 07741236412 excel will remove the 0 as these are not required in a number. This may be avoided by entering a telephone number with a space e.g. 07741 236412 as Excel will read the space as text and treat it as text only as above.
Containing full stops in dates
This is a very common issue. Users will sometimes enter dates into Excel as follows: 12.1.18 because this contains full stops Excel will assume this is text only and you will lose all the functionality of a date cell it can also result in inconsistency throughout.
All of these issues and many more can be both prevented and corrected using a combination of Formatting cells to the value type you would like and using alignments etc to display appropriately. Where basic formatting cannot be pre-set and data must be entered correctly then data validation will prevent incorrect entry, forcing users to enter in the required manner.
See Excel Producing Consistent Data – 2 Prevention using Formatting which is the next in these series of short guides.
