How to Convert Formulas to Values in Excel? 6 Easy Methods

(Note: This guide on how to convert formulas to values in Excel is suitable for all Excel versions including Office 365)

Excel has a variety of functions and formulas in its repository. The purpose of a formula is to perform certain operations and to arrive at a resultant value. 

In Excel, when you click on a cell that holds a resultant value which is a product of a formula, it shows up as the original formula. However, in some cases, you might need to convert the formula to static values for ease of operation and better functionality of the workbook.

In this article, I will tell you 6 ways to convert formulas to values in Excel. 

You’ll Learn:

• Why Convert Formulas to Values in Excel?
• Things to Keep In Mind Before Converting Formulas to Values in Excel
• How to Convert Formulas to Values in Excel?
  • By Copying and Pasting as Values 
  • Using the Function Keys
  • Paste as Values Using a Keyboard Shortcut
  • Using Shift and Function Keys
  • By Dragging and Dropping the Cells
  • Using a Power Query

Watch our video on how to convert values to formulas in Excel

Related Reads:

How to Convert XML to Excel? – 2 Easy Methods 

How to Convert Excel to Word? 2 Easy Methods

How to Convert Text to Numbers in Excel? 5 Easy Methods

Why Convert Formulas to Values in Excel?

Before we learn how to convert formulas to values in Excel, let us understand why we need to convert them.

• It is better to hide the formulas when sending them to others to maintain confidentiality. You would not want others to know the formula which you used to arrive at the particular value.
• Some functions like =RAND() keep fluctuating and changing their values when any change to the sheet is made. You can convert the formulas to values in Excel to prevent such fluctuations.
• It is easier to copy cells or a group of cells between worksheets when they are stored as values rather than formulas. This will help the user perform operations with ease. 
• You can also convert the formulas to values if you don’t want the end-user to alter or edit one value which might change the other values. You can also hide the formulas and lock them to prevent the user from altering or editing them. 
• To prevent data from changing or being changed by the end-user when any linked cell values are changed.
• When there are many complex formulas and you want to improve the performance of the sheet, you can convert the formulas to static values.

Things to Keep In Mind Before Converting Formulas to Values in Excel

• When replacing formulas with values, it is better to keep a copy of your current workbook. When you replace any values, you can always have the original workbook for reference. 
• When replacing formulas with values, make sure there are no cells connected to the current cell you are replacing. If you do so, then the other cells which derive values from the formula might throw an error.

How to Convert Formulas to Values in Excel?

By Copying and Pasting as Values 

This is a very common way of converting the formulas to values in Excel. This method works on single cells and even on a range of cells. You just have to copy the cells and paste them as values. 

Select the cell or a group of cells that have the formulas that you want to convert into values.

Next, copy the selected cells by right-clicking on the selected cells and selecting Copy. Or, you can use the keyboard shortcut Ctrl+C. Once you copy, the cells appear with a moving dots animation.

Now, you need to paste the copied cells. You can either select another destination cell or paste it into the same cell. When pasting, right-click on the destination cell and paste them as Values(V).

There is another way to paste the copied cells as Values. Select the destination cell. Then, navigate to Home, under the Clipboard section, click on the dropdown from the Paste option and select the Values.

This pastes the copied values with formulas as values. When you click on the pasted cell, only the values show up on the formula bar. 

Note: You can copy and paste the cells as values across sheets. However, it does not work if the sheets are protected and the cells are locked.

Using the Function Keys

The Function keys ranging from F1 to F12 help in performing various operations at ease. Depending on the program and the software you use, the function keys have different uses.

Function keys serve a very major role in Excel helping you perform certain operations at the touch of a button. When you want to convert formulas to values in Excel, you can easily do it with the help of function keys. However, this method only allows you to convert formulas to values for individual cells and not for a group of cells.

To convert the cells with formulas to values using the function key, first, select the cell. 

Press the function key F2 to enter the edit mode. Once you press F2, the formula shows up in the destination cell place. When you enter the edit mode, the formula shows in both the cell and the formula bar instead of the value.

Now, press F9 to permanently convert the formula into a value.

Note: Be cautious when converting formulas to values using this method. This method replaces the value in the place of the formula. You might not be able to retrieve them after certain operations. 

Paste as Values Using a Keyboard Shortcut

This is another easy way to convert formulas to values. This method has the same functionality as copying and using the paste special option to paste them as values. But, an advantage of using shortcut keys is that it improves efficiency and saves time.

First, select the cells with formulas you want to convert as values.

Then, copy the cells you want to convert. Once you copy them, you can see the cells appearing with a moving dots animation.

Now, select any destination cell. The destination cell can be an empty cell or the current array of cells you are willing to replace. If you don’t select any destination, the cells with formulas will be replaced by their corresponding values.

Press Alt+E. This activates the keyboard shortcut options and their corresponding keys tied to certain operations.

When you press Alt+E, the Home main menu gets highlighted.

Now, press S. This opens up a Paste Special dialog box.

Now, press V which highlights the paste option “Values.”

Press Enter. This converts the cells with formulas to values.

Also Read:

How to Calculate Percentile in Excel? 3 Useful Formulas

How to Select Non Adjacent Cells in Excel? 5 Simple Ways

How to Add a Secondary Axis in Excel? 2 Easy Ways

Using Shift and Function Keys

Another easy way to convert formulas to values by pasting them as values is by using the Shift and Function keys. 

Select the cells you want to convert.

Copy the selected cells.

Now, by holding the Shift key, press F10. This is similar to right-clicking the cell and selecting the Paste Special as Values. But, this method is even faster and more effective.

Now, press V. The values will replace the cells with formulas.

By Dragging and Dropping the Cells

This is yet another effective way to convert formulas to values in Excel. You can easily convert the selected cells by just dragging and dropping them.

First, select the cells you want to convert to values.

Move the cursor to the edge of the selected cells. You can see the default mouse pointer turn into a four-sided pointer. Now, right-click the selected cells and drag them to the desired place. You can either drag and drop them at the same destination or move them to another destination.

Once you let go of the right mouse button, a dialog box opens. Select Copy Here as Values Only from the dialog box.

This converts all the selected cells to values.

Note: When you replace formulas with values in Excel, sometimes the formulas may be permanently deleted or replaced. To retrieve the formulas, immediately undo the selection or press Ctrl+Z.

Using a Power Query

Though copying the cells with formulas and pasting them as values might be an effective way, you can use an alternative way to keep the original sheet. In some cases, you might want to keep the formulas as such and have the converted cells with formulas onto a new cell or worksheet. 

In such cases, using a Power Query can help you convert the formulas to values onto a new worksheet.

To create a power query, first, select the cells.

Then, navigate to Data. Under the Get & Transform Data section, select From Table/Range.  

This opens up a Create Table dialog box asking you to select the data for your table. Since we have already selected the cells, the cell range automatically populates. Click OK.

This opens the Power Query Editor which shows the selected cells converted into values.

Click on Close & Load.

Once you click on Close & Load, a new worksheet is created in the existing Excel workbook, and all the selected cells with formulas are converted into values.

This is a much easier and faster way to convert the formulas to values without overriding the existing formulas. Just navigate to the other worksheet and you can retrieve or cross-check the original intact data.

Suggested Reads:

How to Copy Only Visible Cells in Excel? 3 Easy Ways

How to Wrap Text in Excel? With 6 Simple Methods

How to Use Excel Split Screen? 3 Simple Ways

Frequently Asked Questions

Closing Thoughts

In this article, we saw how to convert formulas to values in Excel using 6 easy ways. You can either copy and paste the cells as Values, use the function keys, or create a power query to convert the cells with formulas to values. Choose the method that suits your needs.

Please visit our free resources center to know more about the date and time functions. 

We have high-quality Excel guides and you will be really excited to know how handy and useful these functions are.

Simon Sez IT has been teaching Excel for over ten years. For a low monthly fee, you can access 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!