Pretty often when you’re working in Google Sheets, you want to move cell values around, but not their formatting. Bold and underlined totals are great to make a point. But if you need to copy them into a fresh sheet to analyze them, sometimes you just want the data, and not the format.
Pasting values without formatting also comes in very handy if you’ve opened a formatted report from a third party website and you want to put the data into your spreadsheet, without their fonts and formatting.
Luckily, there are easy ways to accomplish just that.
How to paste values only in Google Sheets
To paste into a sheet, remove the formatting and only keep the values, use the following keyboard shortcut:
Ctrl + Shift + v (ChromeOS, Win) or ⌘ + Shift + v (Mac)
Alternatively, select the destination cell and right click, then select Paste special and you will see the following menu which has the option to paste Values only.
How do I remove paste formatting in Google Sheets?
If you’ve already pasted a lot of information into a sheet and you need to remove the formatting, there are quick ways to accomplish that as well.
To remove formatting from cells in a sheet, first make a selection. Select a single cell, or drag to make a selection of cells, or even select the entire sheet by clicking in the top left square where the rows and columns meet (see below).
Once you have your selection, there is a menu command to clear formatting. You can find it at the bottom of the Format menu (see below).
Or, if you want a quicker solution, simply use the keyboard shortcut Ctrl + \ (Windows, ChromeOS) or ⌘ + \ (Mac) to remove ALL formatting.
Warning, using clear formatting will remove everything: fonts, font sizes, font colors, bold, italics, underline, cell borders and cell highlighting. It will just leave you with the data.
Other types of ‘special paste’
You may have noticed that there are other types of paste available when you access the Paste special menu from either a right click or from the Edit menu in the toolbar. I’ll quickly cover those here.
Values only
This will paste only the data and leave all formatting such as bold, italics, highlight, font and color choices out.
Format only
This will paste only the formatting and will format the data in your destination cell in the same way as your source cell. It won’t change the data.
Formula only
Normally, when you copy a cell containing a formula, you copy everything inside it: the value, formula, conditional formatting, and validation rules. This option allows you to paste JUST the formula, leaving the existing formatting and validation as it is.
Conditional formatting only
Conditional formatting allows you to change the appearance of a cell based on formulas or settings. If you want to move, just that conditional formatting you should copy and paste using this option.
Data validation only
Data validation allows you to restrict the contents of cells to certain data types and to fit certain rules. Paste special using this option to duplicate these settings from one place to another.
Transposed
Pasting transposed will turn rows into columns and columns into rows. Copying cells A1, B1, C1 and pasting transposed into cell A1 will cause A1=A1, A2=B1, A3=C1.
I show this below, the values 1, 2 and 3 from A1, B1 and C1, when pasted transposed into cell A1, become rows and highlighted in yellow.
Column width only
If you’ve sized one column perfectly and you need to do the same to other columns quickly, this option is handy. Pasting column width only will just make the destination column the same width as the source column. Nothing else will change.
All except borders
If you want to copy data in a complicated table full of lines and borders, and you want to keep the data and how it looks, but lose the borders. This option is the one for you.