How to Convert Formulas to Values in Google Sheets (Complete Guide)

In this article, we will learn how to convert formulas to values in google sheets.

Most times we need this feature for further execution with our data. Let’s say you have calculated the percentage of the students or the salary of the employees using simple multiplication and division formulas, now you need to paste them somewhere else but not in the form of a formula but the form of values so that no one can further manipulate the formula. There is another reason, we many times need to hard-code the values. First, we calculate them and then we hard-code them using values instead of formulas. This helps us in many ways, and we need this feature many times when working with Google sheets.

Use cases of Converting Formulas to Values in Google Sheets

We need to have values to use as plain values. We do not need the formula for further execution. Let’s say I have calculated the value of percentage, and now I want to use these values in another formula to combine the percentages of all subjects. Here, some formulas or functions may not receive the percentage values if they are written in the formula form, so we need to know how to convert formulas to values to convert those formulas into values and hard-code them to use in any other function or formula and reduce the chances of errors.

Therefore, we need to learn how to convert formulas to values in google sheets, one is to use the values further into some function or formula, and the second reason is to hard-code values so that no one can manipulate the formula further.

How to Convert Formulas to Values in Google Sheets

Here, we will see how it works. We will see how to convert formulas to values in google sheets. There are two simple methods for doing it.

How to Convert Formulas to Values in Google Sheets – Copy Paste Method

In this section, we will learn how to convert formulas to values in google sheets using copy-paste method. In this method, we simply copy the entire formula-driven values and paste them as paste special. This method is very common and mostly used to convert formulas to values in google sheets.

Step 1

Sample data

Step 2

Make some calculation

Step 3

Now the result values are not values they are formulas.

Step 4

Copy the formula-driven values

Step 5

On the same column, right-click > Paste special > values only

Step 6

Now you can check the formula is now converted to values, as we have pasted them as Values

This is how to convert formulas to values in google sheets.

How to Convert Formulas to Values in Google Sheets – Copy Paste Method (Keyboard Shortcut)

In this section, we are going to learn how to convert formulas to values in google sheets using copy-paste method (keyboard shortcut). Now, this method is the same as we used in the above section, the difference is that it’s a keyboard shortcut and you can imagine how fast it can be. Let’s see this method using the sample example dataset.

Step 1

Select the formula-driven values (Mouse Drag)

Step 2

Copy (Ctrl + C)

Step 3

Keep the same cell selected and paste (Ctrl + Shift + V)

Step 4

The formula-driven values are converted into hard-coded values.

Step 5

If you have done anything wrong, you can undo the changes using Ctrl + Z

This is how you can use the keyboard shortcuts to convert formulas to values in google sheets.

How to Convert Formulas to Values in Google Sheets – More Paste Special Features

In this section, we will learn about some more paste special features. In Google sheets we have many kinds of pastes, we can use them as per our needs. In the above sections, we have used values only, which is a type of paste special. There are some more features in paste special let’s see each of them.

We have eight paste special features in Google Sheets (Similar features are also found in Microsoft Excel)

1: Paste Special Values only

This feature only pastes the values. When you copy the values in any form either formula-driven values, if you use values only it will paste only the values in hard code.

2: Paste Special Format only

This feature only pastes the format, no values are pasted by this feature. When you copy some cells (rows and columns) and paste them into another location using Format only then only the format (border, font color, font size, text-align, font family, etc.) will be pasted.

3: Paste Special Formula only

This feature only pastes the formula, if there is no formula then the values will be pasted. And if there is any formula, then the formula will be copied. Note that the default cell reference is relative, which means that the cell addresses will be changed when you copy the formula to another cell it will take new cells based on the offset of the selected cells. If you don’t want to lose the original cells binding, then you can use $A1 dollar notation, it will lock your cells and an absolute cell reference will be used overwriting the default cell references.

4: Paste Special Conditional formatting only

This feature only pastes the conditional formatting rules. If you have copied some content that has conditional formatting, then it will be pasted by using the Conditional formatting only feature, or if there is no conditional formatting rule on the data you have copied, then nothing will be pasted.

5: Paste Special Data validation only

This feature only pastes the Data validation (similar to the previous feature “conditional formatting only”), If the copied content has no Data validation, then nothing will be pasted, and understandably, if there is some Data validation on the copied content, then it will be pasted.

6: Paste Special Transposed

This feature pastes a transposed copy of the data you have pasted. This means that if you have selected a range and then copy it, so it will paste it in transposed form (row to columns and columns to row).

7: Paste Special Column width only

This feature only pastes the column width. Sometimes we make changes to the column width based on the size of the text or heading of the content. So we change and give different widths to different columns, to copy the width only we can copy the columns and paste anywhere else using the Column width only feature to copy only the width of a column (no content or formatting will be pasted)

8: Paste Special All except borders

This feature is self-explanatory, it works like a normal paste (Ctrl +V), but it does not paste borders.

Important Notes to Convert Formulas to Values

Download/Copy Google Sheets Practice Workbook

Note: do not request the editor’s access. Simply make a copy and edit yourself in your file.

Frequently Asked Questions

Why do we need to convert formulas to values in Google Sheets?

We need to convert formulas to values in Google Sheets to hard-code the values, to use them further in other formulas or functions. We also use this feature to hard-code the values and don’t show other users that the values are coming from formulas.

What are the other ways to convert formulas to values in google sheets?

We can only use paste special to convert formulas to values in google sheets. There is no other native method to do this. We may have some add-ons, and app script code to do this. But in Google Sheets’ native environment, we don’t have any other method to date to convert formulas to values.

What are the keyboard shortcuts for other paste special features?

We have keyboard shortcuts only for 2 features (out of 8).

The keyboard shortcut for paste Values only is Ctrl + Shift + V

The keyboard shortcut for paste Format only is Ctrl + Alt + V

Other paste special features have no keyboard shortcuts, although you can set the shortcut keys for them if you need to use any of them frequently. Keyboard shortcuts can be assigned by going to the Help > Keyboard Shortcuts tab from the main menu.

Conclusion

Wrapping the process of converting formulas to values in google sheets, we have learned how to convert formulas into values in google sheets using a simple copy-paste method. The paste needs to be special, and it is the paste values only. We have many more paste features which we have briefly discussed in the last section of this article. Other than this we have also seen a keyboard shortcut to perform paste values only (Ctrl + Shift + V). This was a very quick and comprehensive guide on how to convert formulas to values in google sheets. I hope you find this article helpful and that you have got a clear picture of converting formulas to values. Now you know why we need to convert formulas to values in google sheets.

That’s all. I will see you soon with another learning tutorial about google sheets. Till then take care of yourself and your family. Don’t forget to like, share, and subscribe to our blog. Thank you. Keep learning with Office Demy.