Remove Formula to Get Only Values in Google Sheets (3 Methods)

It is a given that most official spreadsheets contain a lot of data and in turn contain a lot of formulas. The thing is, having a lot of compound formulas like VLOOKUP or INDEX-MATCH may increase the processing times of the spreadsheet making it slow to load. Especially knowing that Google Sheets is a browser-based application.

In such cases, it can be a great idea to remove the formula from Google Sheets to not only make the spreadsheet faster but can also help to bolster security as no other collaborator can hamper the formula.

Let’s have a look at a few ways with which we can do this.

3 Ways to Remove Formula but Keep Values in Google Sheets

For our examples, we will use a simple worksheet with a column containing a basic formula:

The advantage of the methods we will discuss is that they can be used for any type of formula of varying complexities.

1. Using the “Paste Values Only” Paste Special Option

Google Sheets allows its users to carry over different forms of data when they are copy-pasting. When you copy cell data in Google Sheets, there are generally three things that are taken:

1. Value
2. Underlying formula (if there are any)
3. Cell formatting (if there are any)

Each of these data can be individually pasted into a cell. But in this article, we will focus on the value data.

So, to remove the underlying formula and get only the values in Google Sheets, follow these steps:

Step 1: Copy the cell or the range of cells that you want the formulas removed from.

Simply select the cells and right-click on them to open the menu to find the Copy option.

Copying cells in Google Sheets will leave them in the “Copied” state, which can be recognized by the dotted border:

Step 2: Right-click over the cell where you want only the values. It can also be over the copied values. But for this example, we are choosing a different location.

Instead of selecting “Paste” move to the “Paste special” option. Here you’ll find the “Values only” option.

Step 3: Select the “Values only” option to get the values excluding the underlying formula of the copied cells in Google Sheets.

This proves that we can simply use copy and paste to remove any underlying formulas of a cell in Google Sheets.

2. Using a Keyboard Shortcut to Remove Formula and Keep Values in Google Sheets

Google Sheets has a lot of keyboard shortcuts to help users work with spreadsheets more efficiently. So, it is a given that the application will also have shortcuts for the common task of copy and pasting.

While we will use CTRL+C to copy the cells, we will not use CTRL+V to paste since we want a special paste that will remove the underlying formula from the copied cell.

But worry not, we have a keyboard shortcut for Paste Special for Values Only:

Note: You can also use the shortcut over the copied values to overwrite the column values without the formulas.

This keyboard shortcut was already hinted at in our previous method:

3. Using Apps Script to Remove Formula Without Changing Value in Google Sheets

You can further automate the removal of formulas and replace them with their respective values by using Google Sheets Apps Script.

This feature allows users to write a function that can perform multiple tasks at once and be applied to an active worksheet with a click of a button.

Let’s see how we can do this when removing formulas and leaving only the values behind in a worksheet.

Step 1: Navigate to the Extensions tab and click on the Apps Script option.

Extensions > Apps Script

This will take you to a new Apps Script window where you can write your custom script.

Step 2: In the Apps Script console, enter the following script:

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom')
      .addItem('Remove Formula', 'removeFormula')
      .addToUi();

};

function removeFormula() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Step 3: Click on the Save icon to apply.

The script is divided into two parts:

1. The first part works on creating a new tab in the toolbar called Custom. Here we have created a selectable option called “Remove Formula”. Clicking on this option in the spreadsheet will activate the script.
2. The second part of the script contains the function that will remove the formula from the selected cells leaving only the values behind. It follows the simple procedure of copying and pasting values in script form.

Step 4: Return to the active worksheet and refresh the page. A new option in the toolbar should appear called “Custom”.

This option also contains the “Remove Formula” function.

Now, to remove formulas from a cell, simply select the cell or the range of cells that contain formulas and apply the script from the toolbar.

Select cells with formula > Custom > Remove Formula

Alternative Scenario: Show the Formula as Text in Google Sheets

Alternatively, we can remove the power of a formula by converting it into a text value in Google Sheets.

The only thing we have to do is add an apostrophe (‘) before the equal-to symbol (=).

This will transform the cell value to show the underlying formula as a text.

Final Words

That concludes the three simple ways we can use to remove formula in Google Sheets leaving only their values.

The methods are simple enough to use and can prove quite important when trying to secure your spreadsheet or simply make it more efficient.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

• Google Sheets: The FILTER Function (A Comprehensive Guide)
• How to Autofill Formula in Google Sheets (3 Easy Ways)