How to Return Value of Cell Not Formula in Excel (3 Methods)

Dataset Overview:

    • We have a dataset with 5 columns: Product, Pound, Kilogram, Unit, and Total.
    • The data is given in pounds, and we want to convert it to kilograms.
    • Our goal is to calculate the total product weights based on the display value in column D.

Method 1 – Using ROUND Function

1 kilogram = 2.2046 pounds, therefore 7 pounds (Cell C5) = 3.17 kilograms and rounded = 3.2 kilograms (Cell D5).

excel return value of cell not formula intro

If we multiply the weight (3.2kg) by the number of units (13 in cell E5), then we will get a different output from our intention. We expect the result to be 41.6, however, we will get 41.27732922 as the result.


  • First, select the cell range F5:F10.
  • Enter the following formula:


excel return value of cell not formula round

Formula Breakdown

    • The ROUND function rounds the value from cell D5 to one decimal place.
    • So, 7 divided by 2.2046 will be 3.2.
    • We multiplied it by the number of units sold.
    • We will get our desired result of 41.6.
  • Press CTRL+ENTER to autofill the formula for the remaining cells.

This ensures that Excel uses the display value rather than the full formula for calculations.

Method 2 – Combined Formula Approach


  • Select the cell range F5:F10.
  • Enter the following formula:


excel return value of cell not formula combined functions

Formula Breakdown

    • The main part of the formula is the TEXT function, which preserves the cell contents as they appear.
    • The RIGHT function extracts the number of decimal places from the format of cell D5.
    • For example, if D5 displays numbers with one decimal place (e.g., “F1”), the result will be 41.6.
    • The REPT function repeats the value 0 exactly once.
    • Multiply this adjusted value by the units (cell E5) to get the desired output.
  • Press CTRL+ENTER to autofill the formula for the remaining cells.

This method ensures that Excel calculates based on the display value, not the full formula.

Method 3 – Set Precision as Displayed


  • Our goal is to ensure that Excel uses the displayed value (as seen by the user) for calculations, rather than the full formula.

excel return value of cell not formula precision as displayed


  • Press ALT, then F, and finally T to open the Excel Options window.
  • Navigate to the Advanced tab.
  • Under the When calculating this workbook section, select Set precision as displayed.
  • Click OK.

  • A warning message will appear; confirm by clicking OK.

excel return value of cell not formula warning message box

  • Once enabled, Excel will automatically adjust the values based on their displayed precision.

In summary, this method allows you to return the value of a cell without using the underlying formula. By setting precision as displayed, you ensure that calculations consider the rounded display value rather than the full formula.

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 allow you to follow along.

excel return value of cell not formula practice dataset

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

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


    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 16, 2024 at 2:40 PM

      Dear Peter Summers

      Thanks 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 TEXT function:

      1. Choose an empty cell.
      2. Insert the following formula: =IF(MOD(B43-B30,1) > MOD(B35-B30,1), TEXT(MOD(B43-B30,1),"hh:mm"), TEXT(MOD(B35-B30,1),"hh:mm"))
      3. Hit Enter.

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

      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF