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.

Value can also be used in the sense of 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 in order 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 just 2.

Error Values

The term value is also associated with error values, - such as #NULL!, #REF!, or #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 as they can be included as arguments for some worksheet functions.

An example can be seen in cell B3 in the image, because 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, so the result is the error value #DIV/0!, since 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 a text and numbers.

More specifically, this error value is shown 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 - add, subtract, multiply, or divide - using at least one arithmetic operators: +, -, *, or /.

An example is shown in row 4 where 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 the #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 makes it 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.

This use has been largely replaced by the term number data, 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.