Value paste keeping formulas intact in Excel

Hi all, Hope you are well and keeping yourself safe from the outbreak. Most of you are probably working from home, few are on leave. The attendance has reduced everywhere. During this tough situation its better to stay at home and gain knowledge of some awesome tricks that I will explain below. Let's jump into something very interesting.

Suppose you are in a situation where you have large amount of data in a column. During initiation, all of them are formulas. You want to paste values of them but not all, you want to keep few of them as formula and for rest of them you want to do alt+e+s+v. The question is how to achieve this goal. To know this trick, you must be aware of a very useful excel function namely "formulatext". This function has only one argument, which is the reference cell from which you want to extract the formula. The role of this function is to give you back the formula in text format. Let's see an example. In the below picture, we have formulas in the column B and hypothetical growth rates in column A. The formula in column B is simple, if we have value in column A greater than 3, the result will be "Modest", else the result will be "Low".

If we use the formula text function in column C, e.g. cell C2 referring B2, we will have "=IF(A2>,"Modest","Low") in cell C2.

We will manipulate this function to achieve our goal. Suppose, we are in a situation, where we have to paste values of column B into column C if the value in column B is "Low", else we will copy the formula. Now, this can be done using VBA, but we are not looking for a complicated solution. We will just use simple if function and text to column to achieve this goal. We will first write =IF(B2="Low",B2,FORMULATEXT(B2)) in the column C, and will get exactly same result as of column B.

But here is the catch, if we now do alt+e+s+v in column C, the cell with value "Low", will be converted to "Low" but rest of the cells will have formulas in text format. Something like the below image.

Now select the column C, go to Data, choose text to column, delimited, next, next, finish. Voila you have made it.

Below is the final outcome.

Hope you have enjoyed this piece. Do let me know if you are looking for any specific solution to your specific problem. Stay safe and healthy.