The Meaning of Value in Excel and Google Sheets

Definition and Use of the Term "Value" in Excel and Google Spreadsheets
Ted French

In spreadsheet programs such as Excel and Google Spreadsheets, values can be text, dates, numbers, or Boolean data. As such, a value differs depending upon the type of data it is referring to.

  1. For number data, value refers to the numerical quantity of data, such as 10 or 20 in cells A2 and A3.
  2. For text data, value refers to a word or string, such as "Text" in cell A5 in the worksheet.
  3. For Boolean or logical data, value refers to the state of the data—either true or false, as in cell A6 in the image.

A "value" also can refer to a condition or parameter that must be met in a worksheet for certain results to occur. For example, when filtering data, the value is the condition that data must meet to remain in the data table and not be filtered out.

Displayed Value vs. Actual Value

The data that is displayed in a worksheet cell may not be the actual value that is used if that cell is referenced in a formula. Such differences occur if formatting is applied to cells that affect the appearance of the data. These formatting changes do not change the actual data stored by the program. For example, cell A2 has been formatted to show no decimal places for data. As a result, the data displayed in the cell is 20, rather than the actual value of 20.154, as shown in the formula bar.

Because of this, the result for the formula in cell B2 (=A2/A3) is 2.0154 rather than simply 2.

Error Values

The term "value" is also associated with error values, such as #NULL!, #REF!, and #DIV/0!, which are displayed when Excel or Google Spreadsheets detects problems with formulas or the data they reference. They are considered values and not error messages, because they can be included as arguments for some worksheet functions.

For example, in cell B3 in the image, the formula in that cell is attempting to divide the number in A2 by the blank cell A3. The blank cell is treated as having a value of zero rather than being empty; the result is the error value #DIV/0!, because the formula is attempting to divide by zero, which cannot be done.

#VALUE! Errors

Another error value is actually named #VALUE!, and it occurs when a formula includes references to cells containing different data types such as text and numbers.

More specifically, this error value appears when a formula references one or more cells containing text data instead of numbers, and the formula is attempting to carry out an arithmetic operation—addition, subtraction, multiplication, or division—using at least one arithmetic operator ( such as +, -, *, or /).

For example, in row 4, the formula =A3/A4 is attempting to divide the number 10 in cell A3 by the word "Test" in A4. Because a number cannot be divided by text data, the formula returns #VALUE!.

Constant Values

"Value" is also used in Excel and Google Spreadsheets with constant values, which are values that change infrequently, such as a tax rate, or do not change at all, such as the value Pi (3.14). By giving such constant values a descriptive name, such as "TaxRate," it's easy to reference them in spreadsheet formulas.

Defining names in such instances is probably most easily accomplished using the Name Box in Excel or by clicking Data > Named Ranges in the menus in Google Spreadsheets.

Previous Use of "Value"

In the past, the term "value" was used to define numeric data used in spreadsheet programs. The term "number data" has largely replaced it, although both Excel and Google Spreadsheets both have the VALUE function. This function uses the term in its original sense, since the purpose of the function is convert text entries into numbers.