This post is going to show you all the methods you can use to copy and paste everything except the formatting in Google Sheets.

You might normally use the keyboard shortcut to copy and paste contents from one location to another when working in Google Sheets. In many cases, this method gives you the result you need.

Duplicating cells this way will paste everything, including any formats present in the copied cell. This makes the normal pasting procedure inadequate in situations where you only want to paste the contents of the copied cell without the formatting.

If you want to duplicate only the cell contents without any of its formats, you must use the Paste special options.

This feature allows you to paste individual structures of a cell content such as cell values, data validation, formulas, and column width.

You will learn how to use the Paste special feature to paste all of these data types without any format.

Get your copy of the example workbook to follow along with this post.

Paste Without Format from Edit Menu

The Edit menu contains all the tools you would need to change or reshape cell contents in your spreadsheet.

Go to the Edit menu and select the Paste special option to reveal all the special pasting options.

From the sample data, cell B6 contains data validation and some text formatting.

To paste only the data validation in the cell, follow these steps.

1. Select cell B6 and copy it using Ctrl + C or use the Copy command from the Edit menu.

2. Select an empty cell then go to the Edit menu and select Paste special, then choose the Data validation only option.

You have now copied the data validation in the cell without the formatting. The dropdown arrow in the cell indicates that the data validation is now present in that cell.

When you click on the dropdown arrow, you can select any of the options present in the data validation.

Whatever option you select, it wouldn’t carry the text format used in the cell from which it was copied.

The Paste special feature provides a lot of flexibility for duplicating cell contents.

When you have data similar to this sample where the range contains different types of data structure, you don’t have to go through the cells individually to apply the specific special pasting option one after the other.

You can select and copy the range and then apply special pasting consecutively.

If you want to do this with the sample data, select and copy the range with Ctrl + C or use the Copy command from the Edit menu.

In an empty cell, go to the Paste special option and select Column width only.

This will paste only the column width of the selected range only.

While the pasted range is selected, go back to the Edit menu and select the Paste special option but this time, select Data validation only.

Now the dropdown menu will appear in the cell, indicating that the cell now contains data validation.

We’re not done yet. Some cells in the parent range contain formulas also.

To paste the cells use the Paste special option, and select Formulas only. This will copy all the values of the parent cells, and the formulas behind the values, into the previously empty range.

If you look closely at the result of this paste operation, you will notice that cells D7 and D9 appear differently. But, based on their corresponding parent cells, both values are of the date datatype.

The reason for the difference between both results is this. The data value in cell A7 was generated using the DATE formula. Whereas, in cell A9, a numeric value was entered into the cell and then formatted as a date.

When the Formulas only paste option is applied to a cell that only contains a hardcoded value, it will paste the value. In this case, the value is a date and the serial number of the date with no date formatting is returned.

If you copy and paste the date value in cell D7 using the Values only option from Paste special, you will also get the serial number for that date with no date formatting.

Paste Without Format from Right-click Menu

The Edit menu is a great tool for manipulating contents in your spreadsheet. But you can also get most of the options in the Edit menu by using the right-click button on your mouse or trackpad.

You can use the Paste special option from the right-click button to do everything described earlier. This makes it way easier to carry out all the steps described.

Conclusions

Anytime you copy and paste a cell or a range of cells, you copy all the content in that cell including its format.

Unfortunately, there is no option to paste everything except the format In Google Sheets.

The Paste special feature allows you to control exactly what parts of the cell you paste. This is what makes the Paste special feature a fantastic tool. It gives you the freedom to choose exactly what it is you want to paste.

It’s super easy to access and use the Paste special options from either the Edit menu or the right-click menu.

You can then use the Paste special feature multiple times in succession to paste all values, formulas, column width, and data validation but avoid pasting the cell format.

Did you know this Paste special trick to avoid copying the formats? Let me know in the comments below!