Most of these aren’t things you need to know for a basic understanding of MS-Excel, but are worth looking into if you are confused by the various definitions of value in the spreadsheet app. Or just want a more complete understanding of Excel.
Data Values
The first and the most obvious use of values in a worksheet is to refer to data types supported by Excel. Each cell can have a different type of value, limiting the kind of mathematical operations that can be performed on them. These are all the types of values supported by Excel:
Number – Number includes all numeric values you can enter, including things like phone numbers or currencies. Keep in mind that these are often displayed differently, but are converted into pure numbers behind the scenes. Text – Text, obviously enough, means any string entered into a cell. Excel doesn’t particularly care what the text is, and so it will categorize any data not recognized as another valid type as a text value. This includes dates and addresses, though their formatting sets them apart. Logical – The Logical data type only holds boolean values, ie. TRUE or FALSE. While it just appears to be capitalized text, it is treated as a binary value by Excel and can be used in logical operations. Error – Error-values are generated when a function or operation cannot be executed. This type of value will appear in the cell where you expected the final result, informing you about what went wrong. There are multiple types of errors you can see in Excel, one of which we are going to discuss in detail later on.
The VALUE Function
There are many functions that can be used to compose Excel formulas. They range from simple operations like subtraction or finding the average to things like generating random numbers. The VALUE function is another lesser-known function in Excel. Simply put, it converts text into its numeric value, if such a conversion is possible. For example, you can use VALUE to convert a date into a purely numeric value. This works for time values as well using the same syntax. Note that this doesn’t necessarily correspond to any actual value – like the number of days or weeks – but rather a serial number used by Excel to represent a date. But then what’s the use of this conversion? Even if the value generated is meaningless, it can be used to mathematically compare similar types of data. You can subtract these numbers to find the difference, or figure out which one is greater. Something like this: There are two reasons why we don’t see the VALUE function used too often. One, there are very few scenarios in which the function is needed, as you can just enter numeric values when you want to perform calculations. Two, modern-day Excel is actually pretty good at converting strings that represent numbers into numeric values when required. The same example we used above could be written without the VALUE function and will work the same way: If you’re comparing currency values for example, Excel will automatically convert the data to the appropriate format and carry out the calculations even if you omit the VALUE function. This leaves very little reason to learn and use the function.
The #VALUE! Error
We have already discussed error values in the data types section, but one error value needs a further look. That’s because it’s also named #VALUE!. This error is rather easy to understand – if you try to run a mathematical operation on a cell containing the incorrect data type (say a text string), Excel will fail to compute the answer and instead throw a #VALUE error. To fix this error, you need to correct the cell references and ensure that only numeric data is present in them. Blank cells aren’t supposed to trigger the error, but sometimes a cell might have spaces entered instead, which registers as text.
Special Functions
Many functions in Excel are designed to return a useful value. Some are constant, while others depend on certain conditions. For example, you can use PI() to get the fixed value of pi in any calculation. RAND(), on the other hand, generates a random number when used. These values are only created when their respective function is used, and can hence only be inserted through a formula. Once put into a cell, the resultant value acts like a normal data type with a numeric value.
What Is the Most Important Usage of Value in Excel?
Excel has made the term “Value” more confusing than it needs to be by calling multiple things by the same name. To be fair, a spreadsheet application dealing with numeric data has more reason than most applications to overuse the word. For the most part, the only values you need to concern yourself with are the data types present in a cell. The #VALUE! Error is actually not that common, since rarely will you enter text in a field meant for numbers. The VALUE function is even rarer since very few cases will require you to convert a text string into a number. And in most of these cases (especially when dealing with currencies), the conversion will happen automatically.
