Before spreadsheet software was invented, the term “value” in relation to a spreadsheet meant only numeric data. In the case of digital spreadsheets, like Microsoft Excel, the term “number data” is used to describe numeric data instead.
Types of Values in Excel
If someone refers to a value in Excel, they may be referring to any of the following types of data:
Text: String data, such as “High” or “Low.“Dates: A calendar date, such as “20-Nov-2018.“Numbers: Numeric data, such as 10 or 20.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 view only data that you care about. For example, you may set a filter to see only 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, and the result When you view an Excel spreadsheet, you’ll see only 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. A formula uses various Excel functions to perform a calculation. The result of that calculation is displayed in the cell. 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 error 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, as 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.
