See all How-To Articles

How to Replace Formula With Value in Excel & Google Sheets

This article will demonstrate how to replace formulas with values in Excel and Google Sheets.

formula to values intro

Occasionally you may have some formulas in Excel that you wish to replace with values.   You can of course manually replace the formula with the correct value – but if you have a large worksheet, this can be rather time consuming and leave you at risk of making errors!   To prevent this and to speed up the process, you can replace formulas with values in Excel using an Excel function called Paste Values.

Copy – Paste Values Selected Cells

Highlight the cells that contain the formulas that you wish to replace with values.

formula to values highlight cells

Then, in the Ribbon, select Home > Clipboard > Copy. 

formula to values copy cells

Then, in the Ribbon, select Home > Clipboard > Paste > Paste Values.

formula to values paste cells

The formulas wll now be replaced with values.

formula to values result

Paste Values can also be found in Paste Special.

Instead of using Paste Values as described above, select Paste Special from the Paste drop down menu.

formula to values select paste special

Then in the Paste Special Dialog box, select Values and click OK.

formula to values paste special

 

Copy – Paste Values Entire Worksheet

To replace all the formulas in your entire worksheet with values, you can highlight the entire worksheet.

Click in the top left hand corner of your worksheet to select the entire worksheet.

formula to values highlight sheet

Then, in the Ribbon, select Home > Clipboard > Copy.

formula to values copy sheet

Without clicking anywhere else, in the Ribbon, select Home > Clipboard > Paste > Paste Values.

NOTE: It may take a long time to paste the values if you have a large worksheet with lots of formulas.

How to Replace Formulas With Values in  Google Sheets

We can copy formulas and replace them with values in Google Sheets by using Paste Special.

Highlight the cells that contain the formulas you wish to replace.

formula to values gs copy

Then, in the Menu, select Edit > Copy and then immediately select Edit > Paste special > Values only.

 

formula to values gs paste special

Your formulas will then be replaced with values.

 

formula to values gs paste