How to replace formulas with their values in Excel

Here are good tips to save your time - 2 fastest ways to replace formulas in Excel cells with their values. Both hints work for Excel 2016, 2013 and 2010.

You may have different reasons for converting formulas to values:

  • To be able to quickly insert values to other workbooks or sheets without wasting time on copy/paste special.
  • To keep your original formulas unknown when you send a workbook to another person (for example, your retail markup to the wholesale price).
  • To prevent the result from modifying when the numbers in the linking cells change.
  • Save the result of the rand() formula.
  • If you have a lot of complex formulas in your workbook which make recalculating really slow. And you cannot switch the "Workbook calculation" option to the manual mode.

Converting formulas to values using Excel shortcuts

Suppose, you have the formula to extract domain names from URLs.

You need to replace its results with values.
Excel formula to extract domain names from URLs

Just follow the simple steps below:

  1. Select all the cells with formulas that you want to convert.
  2. Press Ctrl + C or Ctrl + Ins to copy formulas and their results to clipboard.
  3. Press Shift + F10 and then V to paste only values back to Excel cells.
    Shift + F10 + V is the shortest way to use Excel "Paste special - values only" dialog.

That's it! If this way is still not fast enough for you, have a look at the next tip.
Сonverting formulas to values using Excel shortcuts

Replacing formulas with values in a couple of mouse clicks

Have you ever had a feeling that some routine tasks in Excel that can be done in a few clicks, take too much of your time? If so, you are welcome to our Ultimate Suite for Excel.

With this collection of 60+ time saving tools, you can quickly remove all blank cells, rows and columns; move columns by drag-n-dropping; count and sum by color, filter by the selected value, and much more.

With the Ultimate Suite installed in your Excel, here are the steps to get it working:

  1. Select all the cells with formulas you wish to replace with calculated values.
  2. Go to the Ablebits Tools tab > Utilities group.
  3. Click Convert Formulas > To Value.

Replacing formulas with their values

Done!

all the formulas are converted to calculated values.

I do encourage you to explore other features of our Ultimate Suite. I can assure you that it will save 4-5 minutes on one Excel task, 5-10 minutes on another task, and by the end of the day it will save you an hour or more. How much does an hour of your work cost? :)

You may also be interested in:

36 Responses to "How to replace formulas with their values in Excel"

  1. Anonymous says:

    fRANKLY, IT WAS EXACTLY WHAT I WAS LOOKING FOR.
    THANKS A LOT

  2. Constantin says:

    Thank you!!! Exactly what i was looking for. Fastest method founded. Thanks.

  3. Micah says:

    Can anyone help me!?! I have a column with data that is the result of a formula. So, I'd like to create another column next to it that has the exact same data, but just as text values, not as the product of the formula. So, when new values are created on the formula side, the second column is automatically populated with resulting values BUT IN TEXT FORMAT ONLY. The problem I am having is that when my original column of data is populated from the result of the formula, the cells that I would like to be blank are not blank, they are shown as zero. If I can get the information to automatically populate a column of text values only, I can set up conditional formatting that will remove any zeros, which will make my life a lot easier. I appreciate it.

    • Smock says:

      You can't really have a cell automatically populating based on another cell without a formula. If there's no formula, it will be static.

      If you're looking for the 2nd column to be the same as the first but without the 0 values try using =if(A1=0,"",A1) in the 2nd column, or something like that. (This sets the 2nd column value to Null/Empty instead of a zero)

    • Manish Kumar says:

      It is possible to do only with VBA.
      Under--Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      It will be populated in the corresponding column, by placing IF clause. Now you need only value. So convert it into to special paste value from vba script. At last loop will close and selection change process will be deactivated once you click/change value.

      Hope It will help you.

  4. Micah says:

    I'll add that I am not looking for the "paste values" solution. Basically, I need to set up a function/formula that automatically pastes the values over to a new column without the formula formatting. Just text.

  5. Danny Shorten says:

    Formula to value - does what it says on the tin - bang on - cheers

  6. Abhi says:

    how to combine 10-15 files of excel into one file............ please tell e the easy way for that.

  7. Sean says:

    Thanx for helping me..thats very useful to me

  8. Ravinder reddy says:

    please send me excel file numbers covert to words in dollars im try so many times but not i am not get that formuala please send excel file

  9. Ravinder reddy says:

    please send excel send me excel file for numbers covert to words im try so many times but not get formula please send me file

  10. Shad says:

    I installed but not showing up anywhere.
    I 've looked in add-ins and all customizeable menus - and can't seem to figure out how to get it to show up on my menus.

  11. Eliezer says:

    Thanks ! U r great. :)

  12. hawaiie says:

    THANK YOU!!!

  13. NJT says:

    Microsoft Product support has a built-in two-button solution for this. Press F2 to edit the cell and then press F9 to change the formula into its result.

    https://support.office.com/en-us/article/Replace-a-formula-with-its-result-38001951-c0e8-4fbd-9048-37ef81e2891e

  14. Stephanie says:

    HI there
    I have a sheet with raw data. A second sheet with a conversion table. How do I convert the raw data to the converted values, using the conversion table as a ref?

  15. Sherpa Pasang says:

    Is there any way to convert conditioning value to original valve?
    please any assist?

    Thanks in advance.

  16. BARGAVARAMESH says:

    HOW TO ADD MORE THAN 16 DIGIT NUMBERS IN EXCEL

  17. chuda says:

    i had made a sheet called projection with the help of value from different excel file.but while opening that file after some hours in place of value #ref appear how can i resolve it

  18. Dykiri says:

    THANK YOU! Exactly what I needed! :)

  19. Jolyn says:

    I admire the useful info you provide in your content. I&#;l1782l bookmark your weblog and have my children check up here usually. I am very positive they will learn a lot of new things right here than anyone else!

  20. Shadi Nizar says:

    Couldn't leave without thanking you for good job and advice. Thank you

  21. Ajmal says:

    I want to change a cell value.. for exp: I have a value 11 in a cell and I need to change that value to =(11-a number). ( a formula)

    • Heather says:

      Download the Quick Tools add-in. It includes a way to do exactly what you're describing. (Read under the heading "Replacing formulas with values in 2 mouse clicks.")

  22. Heather says:

    The add-in is so awesome! I think Microsoft should buy it from you and make it an official part of Excel!

  23. Habeeb says:

    Thanks for the help guys.

  24. Jayc says:

    How do you reconvert a value into a vlookup formula? I.e. I ran a vlookup and pasted the values. Now I would like to view the formulae again. Thanks!

  25. YH says:

    Is there a way to post a calculated value in one cell to another?

    Thanks

  26. YH says:

    Thanks Svetlana for the prompt reply, I'm afraid I have not stated my question clear enough. By "posting a calculated value in one cell to another" I mean specifying the formula in say A1 in such a way that if the calculated value is x then put x in B1, else in B2. The idea is to give A1 formula the ability to dynamically assign a value to un-predefined cells

    • Excel formulas cannot put a value into other cells, only in the cell where a formula is entered. So, you might need a different formula for each cell where you want to output the result.

      For example, the formula for B1: =IF(A1>=10, A1, "")
      It says "if A1 is greater than or equal or 10, copy the value of A1 to B1, otherwise return an empty string".

      A formula in B2 could be: =IF(A1<10, A1, "")

      Assigning a value to un-predefined cells is not possible with any formulas.

  27. DNG WEB TECH says:

    Very good trick for import string data on csv files Thanks for sharing such a good knowledge for Excel

  28. Gnanamrutha Sagar Sunkara says:

    Dear, I want to link of excel campus add-in(XL campus). Please replay

  29. Kard says:

    great help, thanks a lot.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard