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

how to Convert Formulas to Values in Google Sheets 29

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.

See also  How to Open an Excel file in Google Sheets [Easy Guide]

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

how to Convert Formulas to Values in Google Sheets 1

Step 2

Make some calculation

how to Convert Formulas to Values in Google Sheets 2

Step 3

Now the result values are not values they are formulas.

how to Convert Formulas to Values in Google Sheets 3

how to Convert Formulas to Values in Google Sheets 4

Step 4

Copy the formula-driven values

how to Convert Formulas to Values in Google Sheets 5

Step 5

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

how to Convert Formulas to Values in Google Sheets 6

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 7

See also  How to Sort in Google Sheets (Complete Guide)

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)

how to Convert Formulas to Values in Google Sheets 8

Step 2

Copy (Ctrl + C)

how to Convert Formulas to Values in Google Sheets 9

Step 3

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

how to Convert Formulas to Values in Google Sheets 10

Step 4

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

how to Convert Formulas to Values in Google Sheets 11

Step 5

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

how to Convert Formulas to Values in Google Sheets 12

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

See also  How to Insert a Text Box in Google Sheets [Complete Guide]

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.

how to Convert Formulas to Values in Google Sheets 13

how to Convert Formulas to Values in Google Sheets 14

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.

how to Convert Formulas to Values in Google Sheets 15

how to Convert Formulas to Values in Google Sheets 16

how to Convert Formulas to Values in Google Sheets 17

how to Convert Formulas to Values in Google Sheets 18

 

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.

how to Convert Formulas to Values in Google Sheets 19

how to Convert Formulas to Values in Google Sheets 20

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.

how to Convert Formulas to Values in Google Sheets 21

how to Convert Formulas to Values in Google Sheets 22

how to Convert Formulas to Values in Google Sheets 23

how to Convert Formulas to Values in Google Sheets 24

how to Convert Formulas to Values in Google Sheets 25

how to Convert Formulas to Values in Google Sheets 26

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.

how to Convert Formulas to Values in Google Sheets 27

how to Convert Formulas to Values in Google Sheets 28

how to Convert Formulas to Values in Google Sheets 29

how to Convert Formulas to Values in Google Sheets 30

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).

how to Convert Formulas to Values in Google Sheets 31

how to Convert Formulas to Values in Google Sheets 32

how to Convert Formulas to Values in Google Sheets 33

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)

how to Convert Formulas to Values in Google Sheets 34

how to Convert Formulas to Values in Google Sheets 35

how to Convert Formulas to Values in Google Sheets 36

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.

how to Convert Formulas to Values in Google Sheets 37

how to Convert Formulas to Values in Google Sheets 38

how to Convert Formulas to Values in Google Sheets 39

See also  How to Change Text Color Using Conditional Formatting in Google Sheets

Important Notes to Convert Formulas to Values

  • Paste values only feature is the only method to convert formulas to values in google sheets
  • The keyboard shortcut is (Ctrl + Shift + V) to paste values only.
  • There are currently no other methods to convert formulas to values in google sheets till the upload of this article.
  • When I say there are no other options, so I only talk about the built-in native features of google sheets. I don’t talk about the add-ons, app script code, or any other third-party plugin.

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.

See also  How to Insert a Diagonal Line in a cell in Google Sheets [3 Methods]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general