How-To

How to Copy a Value Not a Formula in Google Sheets

Spreadsheet

In Google Sheets, you may find that cells aren’t pasting in the right format. Learn how to copy a value not a formula in Google Sheets

If a cell in Google Sheets contains text or numerical values, you can easily copy it. When the cells contain formulas, however, then things can start to go wrong. You may want to paste the result of a formula, rather than the formula itself.

Thankfully, it’s possible to choose whether Google Sheets copies and pastes a formula or just pastes the result of that formula as a value. Doing so is quick and easy, once you know how.

If you’re unsure how to copy a value not a formula in Google Sheets, follow the steps below.

Why You Might Want to Copy a Value and Not a Formula in Google Sheets

When you’re copying a cell that contains a formula, Google Sheets will default to pasting the formula itself rather than the result. This can lead to issues, as moving the formula to a different location may mess up the cell references.

For example, suppose you have a formula that creates a sum of a list of values. The cell itself shows the result of the sum, but if you click on the cell, you can still see the formula.
google sheets sum

When you copy this cell and paste it into another location, the formula gets copied. Since the cell is now in a different location, the formula cannot locate the same cells to sum up, leading to an error.
google sheets error

It is possible, however, to copy and paste the result of the formula, rather than the formula itself. This is what copying the value achieves.

How to Copy a Value in Google Sheets Using Paste Special

If you use the standard paste function when you copy a cell containing a formula, the new cell will contain the formula but not the value. In order to copy and paste the value, you can use the Paste Special function to choose exactly what gets pasted into your cell.

To copy a value using Paste Special in Google Sheets:

  1. Right-click on the cell that you want to copy.
  2. Select Copy from the context menu.
    google sheets copy
  3. Right-click in the cell where you want to paste your value.
  4. From the context menu, hover over Paste Special and select Values Only.
    google sheets paste special values
  5. Your cell will now contain just the copied value and not the formula.
    google sheets values

How to Copy a Value Using Keyboard Shortcuts

You can also achieve the same result by making use of keyboard shortcuts. The standard method of using Ctrl + V (or Cmd + V on Mac) will not work, however.

This will paste the formula rather than the value, so you’ll need to use a different keyboard shortcut.

To copy and paste a value using keyboard shortcuts in Google Sheets:

  1. Click the cell that you want to copy.
  2. Press Ctrl + C on Windows or Cmd + C on Mac to copy the cell.
  3. Click the cell where you want to paste the value.
  4. Press Ctrl + Shift + V on Windows or Cmd + Shift + V on Mac.
  5. The new cell will now contain a value, not a formula.
    google sheets values

How to Copy a Value Using a Formula in Google Sheets

There is another way that you can make the value from one cell appear in another. Ironically, this method involves inserting a formula into the cell where you want the value to appear. The beauty of this method is that if the value in the original cell changes, your new cell will also update to match.

To copy a value using a formula in Google Sheets:

  1. Click in the cell where you want your value to appear.
  2. Type = and then click the cell you want to copy.
    google sheets cell reference
  3. Press Enter and the value will appear in your new cell.
  4. If you click on this cell, you can see that it contains a formula, rather than a value.
    google sheets cell formula
  5. If the original cell is updated, your new cell will always display the same value as your original cell.
    google sheets cell updated

Making Changes to Google Sheets Spreadsheets

Figuring out how to copy a value not a formula in Google Sheets may not be completely obvious at first, but once you know how, it’s simple to do. There are plenty of more features of Google Sheets that are equally easy to use once you’ve learned how.

If you want to see all of the formulas in your spreadsheet instead of their values, you can get Google Sheets to show formulas by enabling formula view. If you have key data in another sheet of your document, you can query another sheet in Google Sheets. You can also take control of the format of your cells by creating custom number formats in Google Sheets.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top