Cart 0

How to convert Excel formulas to values when transforming data

Posted by Roger Hyttinen on

When you’re cleaning up your Excel data such as concatenating two columns, removing spaces from cells or otherwise transforming data using formulas or functions, you typically end up with two columns: one containing the original values and other containing the formulas used to transform the data. Once you’re happy with the results of your formulas, you can then replace the original data with the transformed data.

Here’s how:

To replace original values with transformed values:

  1. In a new column, enter the formulas that you want to use to transform your data.
  2. Once you’re happy with the results, select all of the cells containing the formula.
  3. Press Ctrl + C to copy the data to the clipboard. 
  4. Select the cells containing the original data.
  5. Click the Home tab on the Ribbon.
  6. Click the arrow on the Paste button and click Paste Special.
    Paste Special option on Paste button
  7. In the Paste Special dialog box, click the radio button next to Values. 

    Values radio button
  8. Click OK.
  9. You can now delete the column containing the formulas.

Looking for more Excel tips? Check out our new book Excel 2016:Up To Speed available as an ebook on Amazon, Kobo, and Apple iBooks

Share this post

← Older Post Newer Post →