Mixed Data Types in Spreadsheet Cells
# 5 on our "Top 10 Dysfunctions of #Spreadsheets" — mixed #DataTypes, such as text typed into number cells, and garbled up dates, oh my ... 👇
We’ve all been there. Why is this formula giving me the dreaded #VALUE! error code?! (And why does Excel make that error all caps? We hear you; you don’t have to shout at us.)
Sometimes the answer is mixed up #DataTypes. ⛔️
For example, say you are typing up a list of upcoming due dates, and it seems like all of the sudden data entered as 1/15/13 displays in the very next row as "Jan 15th" … what happened here? Is that a legit date? Can I trust it, and can I perform math based on that date, or did my data types get off? 🗓️
Hmm, so you reformat that entire column of data … again, because you don't trust it to "stick."
Or worse yet, sometimes you may encounter data type issues with no visual warning. #SilentKiller!
For example, you may enter a number that seems to displays correctly, but then evaluates incorrectly in a daisy-chained formula. ⌗ 📝
What to do about it?
1. In #spreadsheets, consider defining data types from the outset on your entire column, top to bottom. And add new columns for anything such as text comments or status about a number, rather than mixing text into your number column.
Plan to also create an entire new tab on your spreadsheet for other new data tables, rather than stacking them vertically within the same tab or sheet.
Remember a simple guiding principle: one column, one data type.
2. In a #system such as a #rapidAppDev environment like #claris, you will have a bit more structure to rely upon. Most robust systems will have you explicitly define one and only one data type for a specific field or attribute.
Most will also allow you to define validation rules to go even further in controlling your data input. For example, a survey response may only expect numbers 1 - 5. The user interface may help guide that, but the database may also want to validate that data.
----------------
Getting your data types right will ensure that your data stays clean, regardless of your platform!
