8

I haven't been able to find an answer online for this or in the Sheets documentation. What data types does a single cell in Google Sheets support?

For example here is what I've seen so far:

enter image description here

Is there a list of acceptable data types that Google Sheets accepts?

David542
  • 104,438
  • 178
  • 489
  • 842

4 Answers4

4

Like you I was unable to find a definitive list of datatypes. However, through personal experience and testing, I verified that Google sheets supports the following data types:

  • Decimal (up to 15 digits, scale from +308 to -308, also used to represent integers)
  • Double precision floating point numbers
  • String (max length: 50,000 characters)
  • Boolean (TRUE and FALSE)
  • Error code (e.g. #N/A, #DIV/0!, and #REF!)

Note: Dates, Times, and Datetimes are stored as Decimals.

Arrays can also be used in Google Sheets. Arrays behave similar to the way they do in Excel, but Sheets does not restrict the user from editing cells in an array the way that Excel does.

Note on Decimals vs Double: It's important to note: Decimals are just 15-digit integers that can have a decimal point in one of 617 positions; the underlying data structure uses integers. On the other hand, a Double follows the definition of the IEEE 754 double-precision floating point numbers, which are prone to losing precision during calculations.

Austin
  • 8,018
  • 2
  • 31
  • 37
  • thanks for this. When does Sheets use a Decimal and when does it use Double? Also, could you please show an example of how to enter in an array? – David542 Jun 14 '17 at 02:24
  • Sheets seems to have a preference hierarchy of types, preferring Decimal > Double > String. I'll update my answer tomorrow, but for the moment see the following link for an example of an array in Sheets: https://support.google.com/docs/answer/3094292?hl=en – Austin Jun 14 '17 at 03:05
  • 1
    @David542 - to enter an array you need to enter a formula, e.g. ={1, 2, 3} – ttarchala Jun 14 '17 at 07:10
  • 1
    @David542 According to the Sheets API documentation Google Sheets use double value for numbers and dates (see my [answer](https://stackoverflow.com/a/56536952/1595451)) – Rubén Jun 11 '19 at 05:33
  • 2
    @Rubén The API is a programmatic way to access cells, but it does not cover all the data types used in a spreadsheet. So, although numbers/dates may use doubles via the API, the Sheets application uses Decimals to store these values. To demonstrate, type a high-precision (Decimal) number into a cell, convert to date, then convert back to decimal; if doubles were used to store dates, then you would lose precision, but in practice you don't. – Austin Jun 16 '19 at 23:24
  • @AustinD I :) know what an API is, I understand that the API could have some restrictions, limitations, etc., that the actual app hasn't. Also I'm not saying that your answer is wrong, actually I upvoted it. – Rubén Jun 17 '19 at 00:13
  • Related [Google Spreadsheet comparison using TIME function for 10:40:00 fails](https://stackoverflow.com/q/30227850/1595451) – Rubén Jun 17 '19 at 04:03
  • @AustinD I think your distinction between "decimal" and "floating point" is specious. What's particularly suspicious is that 15(-ish) digits of precision and a range of -308 to +308 is basically what [IEEE double precision floats](https://en.wikipedia.org/wiki/IEEE_754-1985) support. Also, in your later comment, what do you mean by "converting" between number and date? As you've said, dates are just stored as numbers, so converting doesn't seem to make sense. Formatting, perhaps yes; but that would not demonstrate/reveal anything about the internal representation, I would think. – user98761 Oct 16 '20 at 21:47
  • @user98761 The distinction is important. A "floating point" number is an IEEE double precision floating point number; a Decimal is not. In sheets, a Decimal is stored as a 56-bit integer (it's not floating point precision) with an additional value specifying the number of digits in front of or behind the decimal point. This is similar to the Java BigDecimal class (but with limited precision). Thus, a Decimal is suitable for calculations on (say) currency values less than ~$9 trillion; but a float is NOT. – Austin Nov 09 '20 at 03:13
  • @user98761 re: Dates. I said specifically "Dates are stored as Decimals", to distinguish them from being stored as Doubles. If the underlying data structure of the Date was an Double (i.e.an IEEE 754 double precision floating point number), then you would expect that going from Decimal > Date (which would be double) > Decimal would cause a loss of precision; these data types are not equivalent. But since there is no loss in precision when switching between Decimal >Date > Decimal, that means the underlying representation of a Date must be the same as the Decimal, i.e., not an IEEE 754 double. – Austin Nov 09 '20 at 03:35
  • @AustinD I still don't see what you mean by "switching between Decimal > Date > Decimal." A Date is not a distinct date type in Sheets (I believe); it is merely a way of formatting a number. What specific, exact operations on the Sheets UI are you talking about when you say "switching [or converting]" between Decimal and Date? Because as far as I can tell, there is no such thing. (I would like to try replicating the experiment that you describe.) – user98761 Nov 11 '20 at 00:27
4

According to the Google Sheets API v4 (ref) the cell values could be (emphasis mine):

ExtendedValue

The kinds of value that a cell in a spreadsheet can have.

  • numberValue number

    Represents a double value. Note: Dates, Times and DateTimes are represented as doubles in "serial number" format.

  • stringValue string

    Represents a string value. Leading single quotes are not included. For example, if the user typed '123 into the UI, this would be represented as a stringValue of "123" .

  • boolValue boolean

    Represents a boolean value.

  • formulaValue string

    Represents a formula.

  • errorValue object( ErrorValue )

    Represents an error. This field is read-only.

Rubén
  • 34,714
  • 9
  • 70
  • 166
1

A good corollary is that the Date, Time and Datetime are represented, and can be processed as, numbers (I don't know whether Decimal or FP). In this case the date is represented as the integer part, as number of days since Dec 31, 1899, and time as the fractional.

ttarchala
  • 4,277
  • 2
  • 26
  • 36
1

According to the documentation of the TYPE function:

  • number
  • text
  • boolean
  • error
  • array
  • other
user98761
  • 475
  • 3
  • 8