- Discover different definitions of “cost” in Excel and why it matters
- Value types in Excel
- Displayed value vs. actual value
- Error Values
- #MEANING! errors
- Constant values
- VALUE function
Discover different definitions of “cost” in Excel and why it matters
In an Excel spreadsheet, values can be text, dates, numbers, or booleans. The type of value depends on the data it refers to.
Before the invention of spreadsheet software, the term “value” in relation to a spreadsheet meant only numerical data. In the case of numerical spreadsheets such as Microsoft Excel, the term “numeric data” is used instead of describing numerical data.
The definitions in this article apply to Excel 2019, Excel 2016, 2013, 2010; Excel for Mac, Excel for Office 365, and Excel Online.
Value types in Excel
When someone refers to a value in Excel, they can refer to one of the following data types.
- Text . String data, such as “High” or “Low”
- dates . Calendar date, for example November 20, 2018.
- Numbers . Numeric data, such as 10 or 20.
- Boolean : result of a logical comparison, e.g. TRUE or FALSE
“Value” can also refer to a condition that you define in a spreadsheet filter to view only the data you are interested in. For example, you can set a filter to only see rows whose value in column A is an employee’s name, such as “Bob.”
Displayed value vs. actual value
There are three things that determine the value displayed in a cell.
Formula for this cell
When you view an Excel spreadsheet, you only see the result in each cell. However, if you click on a cell, you’ll see the formula for that cell in the formula box at the top of the worksheet.
Formulas and formatting determine which results are displayed differently in each cell.
- Formula : The formula uses several Excel functions to perform calculations. The result of this calculation is displayed in the cell.
- format . You can format cells in Excel to display values in financial, decimal, percentage, scientific, and many other formats. You can also set the number of decimal places to display.
For example, a formula for cell A2 dividing numeric values by A1 and B1 would be “=A1/B1” with a result of 20,154. However, you can format cell A2 to display only two decimal places. In this case, the value in A2 would be $20.15.
The term “value” is also associated with error values such as: #ZERO! , #ref! and # DIV/0! that appear when Excel detects problems with formulas or data in the cells they reference.
They are considered values because you can include them as arguments to some Excel functions.
For example, if you create a formula in cell B3 that divides the number in A2 by the empty cell A3, it results in the value # DIV/0! .
This is because an empty cell is treated as zero, resulting in an error value # DIV/0! . This error value means “divide error by zero”.
Another error meaning is # WHERE THE!
This value occurs when a formula contains cell references that contain data types that are incorrect for the formula you are using.
If you use formulas that perform arithmetic operations, such as addition, subtraction, multiplication, or division, but you reference a cell that contains text instead of a number, the result is an error value #WHERE THE! .
For example, if you enter the formula “=A3/A4”, where A3 contains the number 10 and A4 contains the word “Test”, the result is #WHERE THE! . This is because Excel cannot divide a number by a text value.
Excel also has a set of special functions that return fixed values. These formulas do not require references to other cells as arguments.
Here are some examples:
- PI : returns a constant value Pi (3.14).
- TODAY : returns today’s date
- EDGE : 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.
Another definition of the term “value” in Excel refers to the VALUE function.
The VALUE function converts text to a number if the text represents a number in some form. The input argument to the VALUE function can be text that is inserted directly into the function, or you can convert references to a cell in the worksheet that contains the text you want.