Spreadsheet.com goes beyond text and numbers with support for over 25 rich data types that you can choose from at the cell, range, and column level. You can change the data type of any cell, column, or range of cells. When the data type of a cell is changed, Spreadsheet.com converts existing data to the new data type. Below are some examples of how your data might change when converted.
Do I Lose My Data and How to Undo?
When converting between data types, Spreadsheet.com tries to retain your data. For instance, if you switch a cell from a number data type to a text data type, Spreadsheet.com retains the digits, but changes to text formatting (bottom and left alignment).
However, many of the conversions from one data type have no obvious corollary in a new data type. For instance, if you convert a cell from a date data type to an attachment data type. In cases like these, Spreadsheet.com empties the cell.
You can always revert the conversion by selecting undo in the toolbar or with the keyboard shortcut Ctrl + Z (CMD + Z for Mac users).
What happens to Formulas?
Converting cells or columns with formulas may replace the formula with the output of the formula, especially when you restrict the column or cells to a data type. For instance, converting a column with values and a sum at the end (like
=Sum(A1:A10)) Strictly to the Number Data Type will replace that formula with the formula's output.
Example Data Type Conversions
While some conversions empty the cells, other conversions retain some part of the data. Some examples of conversions include:
|From||To||Behavior||Example From||Example To|
|Attachment||Files will convert to the file name(s) of the attachment(s).|
|Previous data will convert to the matching text.|
|Cells that are only numeric will be preserved. If Strict is not selected, non-numeric text will also be preserved.|
|Cells that are numeric will be preserved. However, only the first selected option will convert to a number.|
|Text, Automatic, and other data types||User||Spreadsheet.com looks for both email and name (First name [space] Last name) matches to do the conversion.|
|User||Text, Automatic, and other data types||User values get converted to the user's name (First name [space] Last name).|
|User||User values convert to the user's email address.|
|Text, Number, URL, User, Email, and other data types||Select||Each distinct cell value is converted into a select option and cells are assigned the select option they previously contained.|
|Text, Number, URL, User, Email, and other data types||Multiselect||Each distinct cell value is converted into a select option, including comma-separated options. Cells are assigned the select options corresponding to the values they previously contained.|
|Rating||Text, Number, and other data types||Preserves the rating you provided.|
|Text, Number, and other data types||Rating||Converts from numbers to the designated rating|
|Text, Automatic, Number, and other data types||Checkbox||Boolean values "true" and "false" are converted to checked and unchecked boxes, respectively. "1" and "0" are converted to checked and unchecked boxes.|
|Checkbox||Text, Automatic, Number, and other data types||Boxes are converted to the boolean "true" or "false".|
|Text, Automatic, Number, and other data types||Icon Set||Values matching the symbol name will be used to convert to the appropriate symbol.|
Related Row Conversions
When converting to a Related row data type, Spreadsheet.com will attempt to match existing values with values from the primary column of the target worksheet to automatically establish relationships.
When this conversion happens, commas (,) are considered separators so converting "North,South" to a related row cell would result in an attempt to map two separate values "North" and "South" to respectively named rows in the target worksheet.
|From||To||Example From||Example To|
|Text, Automatic, Number, and other data types||Related Row|
Converting Existing Data to A Restricted Data Type
When converting existing data to cells, columns, or cell ranges with Strict Columns and Cells, Spreadsheet.com requires all existing data to match the new data type, or the data is emptied. For instance, converting a range of cells that contain include text and numbers to a Number data type will empty cells that contain text (even if a given cell also contains numbers).
Note: if you try to convert or change data types of cells that are already restricted you will get an error like the following:
There are many more Data Types and behaviors when converting between them. But for more information on how to change data types, check out this article, or get started with one of our templates to see sheets with multiple data types in action.