Spreadsheet design is part art and part science. It involves not only technical expertise but also attention to aesthetics. There are two types of errors which can happen while working with spreadsheets: syntax and logical. While making the conversion of different data types of data in excel or in any other tools with a specific kind of output/display data has become a huge task for the spreadsheet users to overcome the syntactical and logical errors. The imported or typed/pasted data into a cell of any row/column takes some default data type according to the first cell or the data type entered in that row. To convert such columns of data into different intended output generates many hurdles and errors in the sheet where data loses its integrity or identity and does not providevalidated data now .
The commons data types are text, number, date, time, currency, percentage, fraction, scientific etc. and by performing conditional formatting the data can be converted into more reasonable meaningful output.
I dwell in excel so much about some of these points :
1. In excel for a column having all the text data cannot be converted to email or link with ease because of the data type which is present in the column
2. Cells/columns having numbers data type cannot be converted into time or other formats
3. Converting a number into date(long or short) generates a different year than the intended and other formats of dates can be generated by conversion
4. Converting the number into percentage or currency is not so impressive while converting in excel
5. There are more errors like #DIV/0!, #N/A, #VALUE!, #REF!, and #NUM! which is a serious issue in excel for the data entry
Any many more…
After a prolonged research I found a wonderful tool called CollateBox which handles many of queries on its own, while importing the data, CollateBox will cleans the data and defines the data type of that column definitely , and later, in case we need to change the data type of a particular column, CollateBox displays a contextual options.
Few examples are
1. The free text data type can be converted to either “email” or “link” and reverted back as free text
2. The number data type can be converted to “Currency” or “Percentage” or “decimal” or “integer”
3. Date data type can be changed to “Us date” or “UK date” or “month& date” or ” month& year”
4. The converted columns can be moved to any location in the sheet for easy comparisons between the columns
5. The converted data cells can be coloured(6 colours) by using “format” option to make an identity of the converted columns
6. The data entry in the cells after the conversion is taken care in CollateBox and same can be downloaded which was converted
7. Make filters, sorting and search on the converted columns to generate expected output
8. The informative &simple tool tip text will help you better to understand the data types to be typed/pasted in the columns when wrong data is entered
9. The shared user will experience the converted columns data in his view and he can enter the same or relevant data in the shared view
10. Column lock or hide or transposing of columns can be done even after the conversion of data in any of the columns
11. The “Time data” type can be made as 12 hours or 24 hours
12. All the text with long data or special characters will be supported for conversion
13. All the pages in paging functionality will get updated for the data conversion in any of the columns