Google Sheets Copy Values Not Formulas (Fix)
When you copy and paste in Google Sheets, the default action copies the formula, not the calculated value. To paste only the value, use “Paste special” → “Values only” (or the keyboard shortcut Ctrl+Shift+V on Windows/ChromeOS, Cmd+Shift+V on Mac). This article explains why that happens, every method to copy values instead of formulas, and how to avoid common mistakes.
Why Google Sheets Copies Formulas by Default
Spreadsheet programs are designed to keep formulas live so that when your source data changes, the pasted cells update too. That’s useful when you’re building a model, but it becomes a problem when you want a static snapshot — for example, a list of prices that shouldn’t change when the original sheet does. Google Sheets follows the same convention as Microsoft Excel: a normal copy/paste transfers the formula, formatting, and data validation. The formula references are also adjusted relative to the new location, so =A1+B1 copied one row down becomes =A2+B2. That behavior is intentional, but it often surprises new users who expected just the number they saw.
Method 1: Paste Special → Values Only
This is the most straightforward way. After copying a cell or range, right-click the destination cell and choose Paste special → Values only. The pasted cells will contain the calculated results as plain numbers, with no formulas. You can also use the menu bar: Edit → Paste special → Values only. The keyboard shortcuts are:
- Windows/ChromeOS: Ctrl+Shift+V
- Mac: Cmd+Shift+V
Note that this method also removes formatting (like bold or cell colors) from the pasted data. If you want to keep formatting but drop formulas, use “Paste special” → “Paste values only” is not enough — you need to paste values and then reapply formatting, or use the next method.
Method 2: Copy and Paste with Ctrl+Shift+V (Quick Keyboard)
If you prefer keyboard shortcuts, select the cell with the formula, press Ctrl+C (or Cmd+C on Mac), then click the destination cell and press Ctrl+Shift+V (or Cmd+Shift+V). This does exactly the same as the menu option: it pastes only the values. It’s the fastest way when you’re doing a quick copy-paste. One caveat: if you have multiple cells selected, the shortcut pastes the entire array of values, which is what you want. If you accidentally press just Ctrl+V, you’ll get the formula again — just undo with Ctrl+Z and retry.
Method 3: Drag and Drop (with a Trick)
Dragging a cell with the mouse normally moves the formula. To copy values only via drag-and-drop, hold down the Ctrl key (Windows) or Option key (Mac) while dragging the cell’s border. A small plus sign appears, indicating you’re copying. When you release, the new cell contains the formula, not the value. Unfortunately, this method does not offer a “values only” option — you still get the formula. So drag-and-drop is not a good way to copy values; use the paste special methods instead. Some users think holding Ctrl changes the behavior, but it only copies the formula, not the value.
Method 4: Using Find and Replace to Remove Formulas
If you have a whole sheet full of formulas and want to convert them all to values, you can use a clever trick with Find and Replace. Select the range (or the entire sheet by clicking the rectangle above row 1 and left of column A), press Ctrl+H (Cmd+H on Mac), leave “Find” and “Replace with” empty, and click “Replace all.” This will remove all formulas and leave only the values. However, this also removes any text that isn’t a formula, so it’s risky. A safer approach is to copy the entire sheet and paste it as values into a new sheet, then delete the original. That way you keep the layout but lose the formulas.
Common Problems and Misconceptions
- “I copied values but the cell still shows a formula.” You probably used the regular paste (Ctrl+V) instead of paste special. Check the formula bar: if it shows
=, you pasted a formula. - “The pasted values are wrong.” If the source formula referenced other cells, the values you see are the results at the moment of copying. If you later edit the source cells, the pasted values will not update — that’s the point. If you wanted them to update, you should have kept the formula.
- “I want to paste values and keep formatting.” Google Sheets’ “Paste values only” drops formatting. To keep formatting, copy the range, then use Edit → Paste special → Paste format only after pasting values, or copy the range, then right-click → Paste special → Paste values only, and then separately copy the original formatting and paste it over.
- “The shortcut doesn’t work on my laptop.” Some keyboards require you to press Fn+Shift+V or use the on-screen menu. Check your keyboard layout; on some non-US layouts, the shortcut may be different.
How to Choose the Right Method
For a single cell or a small range, use the keyboard shortcut Ctrl+Shift+V (or Cmd+Shift+V) — it’s the fastest. For a large dataset where you also want to keep column widths and other formatting, use the menu path Edit → Paste special → Values only, then reapply formatting if needed. If you’re converting an entire sheet to values, copy the sheet (right-click the tab → Duplicate), then in the duplicate, use Find and Replace to strip formulas. That way you keep the original formulas in the first sheet for reference.
Alternatives: Using Formulas to Extract Values
If you need a dynamic snapshot that updates when the source changes, you might not want to paste values at all. Instead, use a formula like =A1 in the destination cell — that will show the value of A1 and update automatically. But that’s a formula, not a static value. For a static value that never changes, you can use the VALUE() function to convert text to a number, but that’s rarely needed. Another alternative is to use Google Apps Script to write a custom function that copies values, but that’s overkill for most users. The simplest alternative is to use the “Copy to” feature: right-click the sheet tab, choose “Copy to” and select an existing spreadsheet. This copies the entire sheet with formulas, but you can then use Find and Replace to remove them.
Frequently Asked Questions
Does Ctrl+Shift+V work in Google Sheets on Mac?
Yes, on Mac it’s Cmd+Shift+V. If that doesn’t work, try Ctrl+Shift+V or use the right-click menu. Some Mac keyboards have a Fn key that may interfere; press Fn+Shift+V if needed.
How do I copy values without copying formatting?
Use “Paste special” → “Values only” (Ctrl+Shift+V / Cmd+Shift+V). This pastes only the numbers or text, stripping all formatting. If you want formatting too, you’ll need to paste values first, then copy the original formatting and paste it over.
Why does my pasted cell show #REF! error?
This usually happens when you copy a formula that references cells that don’t exist in the new location (e.g., you copied a formula that referenced row 1, but you pasted it into row 1, so the reference becomes row 0). To avoid this, paste values instead of formulas, or adjust the formula references manually.
Can I set Google Sheets to always paste values by default?
No, Google Sheets doesn’t have a built-in setting to change the default paste behavior. You can use the keyboard shortcut every time, or create a macro (via Apps Script) that pastes values only, then assign it to a custom menu or button.
What’s the difference between “Paste values only” and “Paste format only”?
“Paste values only” pastes the calculated numbers or text, discarding formulas and formatting. “Paste format only” pastes only the formatting (colors, borders, number formats) and leaves the content of the destination cells unchanged. They are two separate options in the Paste special menu.
Does copying a cell with a formula and pasting it into another sheet also copy the formula?
Yes, unless you use “Paste values only.” The formula will be copied, and references will adjust relative to the new location. If you want the exact value, use paste special.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.