6 Ways To Convert Formula to Values in Excel

Most of us use formula in excel. Formula is one of the best things excel offers us. But the problem arises when we try to copy the formula results; it brings the formula with it. Another problem arises when we need to delete the source data. Suppose you have values in cell A1 and You have a Formula in B1 that is using A1 as source. Now if you delete cell A1 there will be and error in B1. So, to solve this you need to change formula to absolute value. In this tutorial, I’ve Six ways to replace formula with value automatically.

Trick 1: Copy the formula cell and paste it in notepad. Now copy again and paste it on the original cell in excel. It will remove the formula for you keeping only value. This will convert formula to value automatically.
Trick 2: Copy the cells with formula. On the “Ribbon” under the “Home” tab inside “Clipboard Group” you’ll find paste dropdown menu. Click on the dropdown and select “as value” this will convert formula in excel.
Trick 3: Here again I’ll use the paste as value option but from the right click menu. Copy the range with formula and right click. Select “As values” and Done.
Trick 4: Here copy the data range with formula and press keyboard shortcut Alt + E + S + V and then click ok. This will tell excel to convert formula to value automatically.
Trick 5: Another Keyboard shortcut is to press Shift + F10 and then V. done. This will also show formula as text in another cell.
Trick 6: Last one is the most amazing tip. Select the data range where you have formula to be converted. Now hover your mouse at any side of the selection. Your cursor will be converted into a four directional sign. The By pressing Right-Mouse-Button move the mouse and place where you want the converted data. Then release the mouse-click and a menu will arise. Select “Copy As Values” from the list and done. This will only reference cell value not formula.

#Excel #Convert #FormulaToValue

Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Here goes the most recent video of the channel:

Playlists:

Social media:

[ezzatmesbah]

how I can do these without copy by formula only

[ujjwaldeep]

Last method is really awesome!!! Thanks

[kilasuelika]

If there are 1000 xlsx, how can you manually copy and paste one by one?

[meniporat]

The simplest method:
1. copy the data: CTRL+C
2. paste as values: CTRL+SHIFT+V

[nawnomadnan]

Thanks for the great video! Help me a ton!

[justinlim]

Hi sir, my main question is SHOULD I convert it to a value?

For my purposes, I use xlookup to find the cost price of my ingredients from another table. Use the results and save to another table for recipes. The main concern is if ingredient changes price and the data is saved as a value, then the table(recipes) wouldn't be updated. How should I go about this problem?