The Definition of a Value in Excel

Learn the different definitions of "value" in Excel and why it matters

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

In an Excel spreadsheet, values can be text, dates, numbers, or Boolean data. The type of value depends on the data it's referring to.

In the past, the term "value" defined only numeric data used in spreadsheets. Today, the term "number data" is used to describe that numeric data instead.

Types of Values in Excel

If anyone refers to a value in Excel, they may be referring to any of the following types of data.

  1. Text: String data, such as "High" or "Low".
  2. Dates: A calendar date, such as "20-Nov-2018"
  3. Numbers: Numeric data, such as 10 or 20
  4. Boolean: A result of a logical comparison, such as TRUE or FALSE

A "value" might also refer to a condition you define in a spreadsheet filter to only view data that you care about. For example, you may set a filter to only see rows where the value in column A is the name of an employee, like "Bob".

Displayed Value vs. Actual Value

There are three things that define the value that gets displayed in a cell.

  • The formula for that cell
  • Cell formatting
  • The result

Whenever you view an Excel spreadsheet, you will only see the result in each cell. However, if you click on the cell, you'll see the formula for that cell in the formula field at the top of the spreadsheet.

Formulas and formatting determine what results get displayed in each cell in different ways.

  • Formula: A formula uses various Excel functions to perform a calculation. The result of that calculation is displayed in the cell.
  • Formatting: You can format cells in Excel to display values in financial, decimal, percentage, scientific, and many other formats. You can also set how many decimal points get displayed.

For example, the formula for cell A2 that divides number values in A1 and B1 would be "=A1/B1", with a result of 20.154. However, you may set the formatting for cell A2 to display only two decimal points. In this case, the value in A2 would be $20.15.

Error Values

The term "value" is also associated with error values, such as #NULL!, #REF!, and #DIV/0!, which are displayed when Excel detects problems with formulas or the data in the cells they reference.

These are considered values because you can include them as arguments for some Excel functions.

For example, if you create a formula in cell B3 that divides the number in A2 by the blank cell A3, this results in the value "#DIV/0!".

This is because the blank cell is treated as zero, which results in an error value "#DIV/0!". This error value means "divide by zero error".

#VALUE! Errors

Another error value is #VALUE!

This value occurs when a formula includes references to cells containing data types that are incorrect for the formula you're using.

If you use formulas that perform an arithmetic operation such as addition, subtraction, multiplication, or division, but you reference a cell with text instead of a number, the result will be the "#VALUE!" error value.

For example, if you type the formula "=A3/A4", where A3 contains the number 10 and A4 contains the word "Test", the result will be "#VALUE!". This is because Excel can't divide a number by a text value.

Constant Values

Excel also has a set of special functions that return fixed values. These formulas don't require you to reference any other cells as arguments.

Some examples include:

  • PI: Returns the constant value of Pi (3.14)
  • TODAY: Returns today's date
  • RAND: Returns a random number

The value data type returned by these functions depends on the function. For example the TODAY() function returns a date value. The PI() function returns a decimal value.

The VALUE Function

One more definition of the term "value" in Excel is in reference to the VALUE function.

The VALUE function converts text into a number, so long as the text represents a number in some form. The input argument for the VALUE function can be text inserted directly into the function, or you can reference a cell in the spreadsheet that contains the text you want to convert.

The Definition of Value in Excel

As you can see, any time someone uses the term "value" in reference to an Excel spreadsheet, they could be referring to any of the features listed above.