Our expert trainers can help you get the best results.
If you would like any further information please contact one of our training advisors.
Things are not always what they seem in Excel!
Introduction to Automatic Data Type Conversion
The underlying cause of many of the Excel support calls that we received is down to a failure to recognise that data is not always what it seems to be!
As a result some of the issues caused are:
Excel has many situations in which it will implicitly convert from one data type to another or data may not be stored as you are expecting. Excel does not always bring this to your attention.
Data Type Conversion
Unlike with databases and programming, Excel users are not obliged to define what type of data they are entering, pasting or importing into their workbooks. On the whole Excel makes a pretty good job of guessing. Understanding what assumptions Excel makes with input data and how we can quickly detect the unexpected is essential if we are to avoid downstream errors and problems.
Data Types
At a very basic level everything you enter into Excel will be interpreted as either text or a numeric.
Numeric: As far as Excel is concerned if input data looks numeric it will treat it as such and unless you have already formatted the cell it will try to apply the nearest numeric format to match what you have entered. This could include currency symbols, percent symbols, date/time formatting,thousand separators and decimal places.
Entering a Numeric as a Code: If you require that your numeric data is actually to be stored as text then you need to preformat the cell as text. (Format cell > Numeric Tab > Category = Text or use a label prefix which is an apostrophe (?) as the first character.)
You would wish to do this for the following reasons:
If you have a precise number of digits in your code then you could store it as a value and use a custom numeric format such as 00000 to force 5 digits.
Dates – The Big Issue: Anything resembling a date may be converted to a date serial number and formatted with the nearest date / time format according to your Windows regional & language settings. You need to take control of dates and whilst you can enter them in a variety of styles according to your regional & language settings it is best to preformat the cells to display the date/time as you require. Once converted and stored as a date serial number (Which is a count of the number of days since 1 Jan 1900. e.g. 22 Sep 2017 is 43,000) then you are able to change the format as required and confidently proceed to use dates for filtering, sorting, PivotTables, grouping, charting and calculating.
Seeing what is really going on behind the scenes to uncover automatic data type conversion.
So Excel’s intelligence often masks just what really ended up in the cell. If only we could remove all the fancy formatting, reveal the formulas and widen the columns we could solve many of these issues. A most useful “switch” to do just that is ‘Show Formulas’ (keyboard shortcut: Ctrl + ` (That’s Grave and is to be found at the top left of a UK keyboard. Sometimes called a backquote or a backtick).In the ribbon > Formulas Tab > Formula Auditing Group > Show Formulas. This is a toggle. Once you have finish your diagnostics be sure to return to a normal view with the same shortcut.
Other ‘Gotchas’ on related to Automatic Data Type Conversion are:
Observe the natural alignment- Unless horizontal alignment has been asserted: