Rafiul Haq
Sometimes, we format our values in a cell, however, Excel does not consider that while calculating. We can make Excel calculate differently. In this article, we will show you 3 quick methods of Excel return value of cell not formula.
Download Practice Workbook
3 Ways to Return Value of Cell Not Formula in Excel
To demonstrate our methods, we have picked a dataset consisting of 5 columns: “Product”, “Pound”, “Kilogram”, “Unit”, and “Total”. Basically, there are 6 products in our dataset given in pounds and we are converting it to kilograms.
Afterward, we multiply it by the number of units, therefore we get the total product weights. We can also see here that the numbers are in one decimal place but there are eight digits after decimal in the output.
Our aim here is to make Excel take the display value from column D and make calculations based on that. For example, the value of cell D5 is 3.2 and multiple it by 13 to get 41.6 as the output instead of 41.27732922.
1. Using ROUND Function to Return Value of Cell Not Formula in Excel
For the first method, we will use the ROUND function to return the value of the cell not the formula in Excel. Before jumping on the steps, let us see how our dataset is defined. 1 kilogram equals 2.2046 pounds. Hence, we have divided it by that number to convert it.
Now, if we multiply the weight with the number of units, then we will get a different output from our intention. We want the output to be 41.6, however, we will get 41.27732922 as the output.
Steps:
- First, select the cell range F5:F10.
- Next, type the following formula.
=ROUND(D5,1)*E5
Formula Breakdown
- First, The ROUND function returns rounded numbers up to a certain decimal place.
- Here, we are rounding the value from cell D5 to the first decimal place.
- So, 7 divided by 2.2046 will be 3.2.
- Lastly, we multiplied it by the number of units sold.
- Therefore, we will get our desired output of 41.6.
- Finally, press CTRL+ENTER.
This will AutoFill the formula to the rest of the cells. Thus, we have returned the value of the cell not the formula in Excel.
Read More: How to Show Value Instead of Formula in Excel (7 Methods)
2. Use of Combined Formula to Return Value of Cell Not Formula
For the second method, we will use the TEXT, REPT, RIGHT, and CELL functions to create a combined formula. Then, using this formula we will return the value of the cell, not a formula. Without further ado, let us show you the steps.
Steps:
- First, select the cell range F5:F10.
- Next, type the following formula.
=E5*TEXT(D5,"#."&REPT(0,RIGHT(CELL("format",D5),1)))
Formula Breakdown
- First, Our formula has several parts. The main part of the function is the TEXT function. This function takes the cell contents as it is.
- RIGHT(CELL(“format”,D5),1)
- Output: “1”.
- The CELL function returns the characteristics from a cell in Excel. Hare, we have defined the “format” property of cell D5. So, we will get the output “F1” from that, which means numbers after one decimal place.
- Then, the RIGHT function works. This returns the first string from the previous output from the right side. Therefore, we will get the number of decimal places by using this formula combination.
- Then our formula reduces to -> E5*TEXT(D5,”#.”&REPT(0,”1″))
- Output: 41.6.
- The REPT function repeats a value. We have set it to repeat 0, exactly 1 time. Then our TEXT function kicks in and sets our value from cell D5 to take one decimal point. Lastly, using this value we multiply it by the units.
- Finally, press CTRL+ENTER.
This will AutoFill the formula to the rest of the cells. Thus, we have shown you yet another formula to return the value of the cell not the formula in Excel.
Read More: How to Convert Formula Result to Text String in Excel (7 Easy Ways)
Similar Readings
- How to Stop Formula to Convert into Value Automatically in Excel
- Putting Result of a Formula in Another Cell in Excel (4 Common Cases)
- How to Convert Formulas to Values in Excel (8 Quick Methods)
- Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)
3. Applying Precision as Displayed Feature to Return Value of Cell
For the last method, we will turn on the “Set precision as displayed” feature to achieve our objective in this article. We have already multiplied to get the total weights of our products. When we enable the feature, these values will change automatically.
Steps:
- To begin with, press ALT, F, then T to bring up the Excel Options window.
- Next, from the Advanced tab >>> under the “When calculating this workbook:” section >>> select “Set precision as displayed”.
- Then, press OK.
- A warning message will appear, press OK.
- After this, it will change our values.
- In conclusion, we have shown you the last method of returning the value of a cell not a formula in Excel.
Read More: Convert Formula to Value in Multiple Cells in Excel (5 Effective Ways)
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Conclusion
We have shown you 3 quick methods to Excel return value of cell not formula. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
