As you can see the formulas are preserved and reference the transposed cells correctly. Transpose with formatting and values. If you want to automatically convert formulas to values, you have to use a different method. First, copy the table as before, and then, instead o pasting it as transposed, click the Paste Special button. Copy And Paste Formulas As Values. After using the Excel formulas to change the case of our text, we may want to convert these to values. This can be done by copying the range of formulas and pasting them as values with the paste special command. You might choose to replace the original set of numbers with the results from the VALUE formulas, after which you can clear the VALUE formulas from the spreadsheet. Figure 2: You can use the VALUE function to convert numbers stored as text to usable values. A somewhat simpler approach involves using the Paste Special, Multiply command.

Every data is made of a text. Every output is retrieved using a formula. Every formula that might be inserted as a text can be converted as a value. But, ever tried to convert formula to value using Excel shortcuts and functions?. This article helps the user with the required inputs to convert formulas to values in Excel

Synopsis

Assuming a data that contains the order details generated by a dairy products manufacturing company where orders were placed by customers from various region. With this data, the result could be retrieved by infusing few steps as mentioned in the scenario.

Scenario- To Convert formula to value with Paste Special Option.

• From the given data, the values just remain as a formula text as shown in the image.

• By selecting the cell values with formulas, the user should right-click and should select the ‘paste special‘ option from the menu.
  

• From the ‘paste special’ option, the user should select ‘paste values’ from the menu.
  

• The selected cells will be converted as values as shown in the image.
  

Convert All Formulas To Values In Excel Vba

Scenario 2- Converting formulas to values using keyboard shortcuts.

• By selecting the cells, the user should copy the column that have to be converted as value using the shortcut
  CTRL+C.
  

• The user could convert the formulas to text by using the shortcut CTRL+ALT+ V.

• When the ‘Paste Special Dialogue Box‘ opens, the user should select ‘values and number formats’ from the ‘paste‘ menu and click OK.
  

• The cells will be converted to values as shown in the image.
  

Hope you understood how to convert formula to value using Excel shortcuts and functions. Dont forget to try it out with your data and if there are any queries or suggestions drop it in the comment box.
For more details, visit our youtube channel.
For a hands-on experience, download the files given below.

Converting Formulas To Values In Excel

Practice File