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.

## Return Value of Cell Not Formula in Excel: 3 Ways

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 multiply 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 by 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.

### 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. Here, 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.

### 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

## Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

**Download Practice Workbook**

## 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.

## Related Articles

- How to Stop Formula to Convert into Value Automatically in Excel
- How to Convert Formula to Value Automatically in Excel
- How to Convert Formula Result to Text String in Excel
- Excel VBA: Convert Formula to Value Automatically
- Convert Formula to Value Without Paste Special in Excel
- Putting Result of a Formula in Another Cell in Excel

**<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel**

Hello Rafiul,

Got a problem with the IF function not returning a time value hh:mm but either returning the formula or the cell number where I tried to use a workaround.

Problem:

1. Lock off 23:15

2. Lock on 04:15

3. Lock off 05:05

4. Lock 0n 07:00

Total time is the difference between 1. and 4. but if 3. and 4. did not take place the Total time is between 1. and 2.

=IF(MOD(B43-B30,1))>(MOD(B35-B30,1)),“MOD(B43-B30,1)”,“MOD(B35-B30,1)”)

Dear

Peter SummersThanks for visiting our blog and sharing your problem. You wanted to return a time value with “hh:mm”. To do so, you have to enhance your formula with the help of the

TEXTfunction:Hopefully, you have found the idea helpful; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy