Introduction to Data Types

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 levels.

Data types determine what sort of data can be in a cell and allow you to go beyond the typical alphanumeric characters and formulas. For example, you can add attachments inside cells, assign users who get notified when rows change, set a predetermined list of options for users to choose from, select from colored icons, or even relate data in one row to data from another workbook.

You can change data types at any time, including overriding column data types by cell or range-level data types, allowing you to put any data anywhere you want.

Setting data types opens up your workbook to be more than letters and numbers. Turn your spreadsheet into a hiring tracker with resumes, candidate ratings, assigned recruiters, and more all in line. Or track storyboarding of your team's next commercial, monitoring the status of tasks, due dates, and images from design meetings. Or use a spreadsheet for bug tracking that new software you're developing, setting columns up to store error messages, assign owners, prioritize the severity, and more.

List of Data Types

There are over 25 data types you can choose from in Spreadsheet.com, five of which are System Data Types. Each data type is listed below with a brief description. Click on the title of each data type to learn more.

Featured

  • User: The User data type is for assigning users to rows. Quickly add existing users and invite new users. Optionally you can choose whether or not to notify added users.
  • Attachment: The Attachment data type is for attaching images, documents, and other files within cells. Attachments are displayed as clickable thumbnails. Attached files can be previewed and downloaded by users. Read more
  • Select: The Select data type is for selecting a single value from a predefined set of options. Read more
  • Multiselect: The Multiselect data type is for selecting multiple values from a predefined set of options.
  • Icon set: The Icon set data type is for selecting from a predefined set of symbols, such as Red/Yellow/Green, Harvey Balls, and other types of indicators.

Relational

  • Related row: The Related row data type is for linking to rows in another worksheet. Create relationships to rows in any workbook you have access to, even workbooks in different folders. The Related row data type lets you link rows like tables in a relational database.
  • Related row lookup: The Related row lookup data type is for looking up the value of a cell in a related row. You can configure which related worksheet column to lookup values from.
  • Related row rollup: The Related row rollup data type allows you to perform calculations across linked rows in other worksheets and return aggregate data. Instead of returning raw data from related rows, Related row rollups can return summaries, or aggregates, of the related data.

Standard

  • Automatic: The Automatic data type automatically converts entered data to the most appropriate data type. Similar to the default behavior of traditional spreadsheets, Automatic is the default data type for cells in Spreadsheet.com.
  • Text: The Text data type is for adding a short amount of text.
  • Number: The Number data type is for inputting numbers as integers or decimals with specific formatting defined by you.
  • Currency: The Currency data type is for inputting currency values with a specific currency symbol and formatting defined by you.
  • Percent: The Percent data type is for inputting percent values with specific formatting defined by you.
  • Checkbox: The Checkbox data type is for toggling a true/false state (i.e., binary or boolean).
  • Date: The Date data type is for inputting date values in a specific format defined by you.
  • Date & time: The Date & time data type is for inputting date and time values in a specific format defined by you.
  • Duration: The Duration data type is for representing a period of time in a specific format defined by you (e.g., the number of days elapsed between starting and ending dates).
  • Email: The Email data type is for email addresses. Clicking opens a new email in your default email client.
  • URL: The URL data type is for URLs that open in a separate browser tab when clicked.
  • Phone: The Phone data type is for phone numbers, which you can format as links that open in your default voice calling application.
  • Rating: The Rating data type is for assigning a ranking using a specific style (like stars, thumbs up, hearts, or more) and rating scale defined by you.

Dynamic

Dynamic data types are column-only and restrict all data in that column to only this data type.

  • Autonumber: The Autonumber data type is for automatically assigning a non-editable incremental value to uniquely identify rows. Optionally, configure a prefix, suffix, fill format, and starting number. This data type can only be applied to columns, not individual cells or cell ranges.
  • Column formula: The Column formula data type is for showing the result of a formula expression that computes a value based on other cells in the same row. Unlike cell formulas, column formulas do not require an "=" prefix. Also, column formulas can only contain references to columns, not individual cells or ranges. This data type can only be applied to columns, not individual cells or cell ranges.

System

System data types are column-only and restrict all data in that column to only this data type.

  • Created at: The Created at data type is for displaying a non-editable date and time value showing when the row was created. This data type can only be applied to columns, not individual cells or cell ranges.
  • Created by: The Created by data type is for displaying a non-editable user value showing who created the row. This data type can only be applied to columns, not individual cells or cell ranges.
  • Updated at: The Updated at data type is for displaying a non-editable date and time value showing when the row was most recently updated (i.e., when a cell in the row was most recently changed). This data type can only be applied to columns, not individual cells or cell ranges.
  • Updated by: The Updated by data type is for displaying a non-editable user value showing who the row was most recently updated by (i.e., who changed a cell in the row most recently). This data type can only be applied to columns, not individual cells or cell ranges.

How to Set the Data Type for a Column, Cell, or Range of Cells

In Spreadsheet.com, you can set data types at three levels: Columns, a Cell, or a Range of Cells. Begin by selecting the column, cell, or range of cells for which you want to change the data type. Right click on your selection and select "Edit data type..." from the dropdown. Or, with your selection highlighted, click the Edit data type button from the right hand side of the toolbar. Note that the icon will change depending on the range's current data type.

M-EditCellDataType.jpg

Either option will open the Edit data type dialog, where you can select the cell's new data type from the "Type" field dropdown.

Different data types will have different possible settings, though all data types (except Automatic) allow you to restrict the data in that range to only data of that data type. For instance, restricting an Attachment cell prevents users from typing text in addition to adding files.

Once you've specified the cell's new data types and configured it to your needs, click the blue Update button in the bottom right corner of the dialog and the cell will update to reflect the new data type.

When you change the data type for a columncell, or range of cells that includes strict data or the primary column, you may encounter some unique behavior and restrictions. Learn more about navigating this in each of the corresponding articles.

The Default Data Type: Automatic

New cells and columns default to the Automatic data type, unless the Automatic data type has detected and set a different date type for that cell. For instance, entering data that looks like a date causes the Automatic data type to change that cell's data type to the Date data type, even though the column itself is still set to Automatic.

For more information, see the article on the Automatic data type.

Data Types in the Primary Column

The primary column only supports alphanumeric data types. The data types that can be used in the primary column are:

While editing the data type of the primary column, you can change its data type to other alphanumeric data types only. Conversely, a column with any of the following data types cannot be designated as a primary column: Attachment, Related row, User, Checkbox, Select, Multiselect, Rating, Icon set, Related row lookup, and System columns (like Updated by and Created at).

Converting Between Data Types

When changing data types, you may convert existing data to the new data type. For more information on how data of one type converts to another, see our article on Converting the Data Type of Existing Data.

Data Validation with Strict Columns and Cells

In Spreadsheet.com, all data types (other than Automatic) include the option to restrict cells or columns only to the specified data type. This setting is represented by the Strict option in the Edit data type dialog.

Strict can be used to restrict or validate the type of data into a cell. For instance, if you use Spreadsheet.com for bug tracking, you might wish to restrict a column for Due Date to only be the Date data type. That way, no one can input vague entries like "five days from now."

To learn more about working with strict data, check out our article on Data Validation with Strict Columns and Cells

@Mentions

All data types also support @mentions, the ability to refer to users in a cell alongside other data.

Take a look at each data type's corresponding article to learn more about using different data types in your worksheets. Or, read on to learn more about Converting the Data Type of Existing Data, Data Validation with Strict Columns and Cells, or changing the data type of a column, cell, or range of cells.