How to Use Paste Special in Google Sheets (8 Examples)

Copying data from another source and pasting it into Google sheets is a common task that all users perform. You may sometimes need to copy and paste values only, without formulas, and without formatting. The Paste special feature of Google Sheets offers several options to paste the copied data. In this article, you will learn the uses of paste special options of Google Sheets.

Table of Contents

Paste Special Options in Google Sheets

Let’s assume a dataset of salaries of employees before and after increments. Now we will copy the dataset and paste it onto a new sheet.

Now copy the selection that you want to paste into your sheets.

Then right-click on the cell you want to paste the selection. Then click Paste special. You can also do that from Edit >> Paste special.

Finally, choose the options according to your need.

8 Examples to Use Paste Special in Google Sheets

While we copy-paste data or values to Google Sheets, we need to paste more things rather than the value or data. Google sheets Paste special option enables us the facility. There are eight different options to copy and paste according to your want. Below we will discuss the options of Paste special along with examples.

1. Paste as Values

This option is used to copy-paste the values only. Any kind of formatting and formulas will not paste while using this option.

First, copy the data that you want to paste.

Next, select the cell where you want to paste the copied data. After that right-click and select Paste special.

Afterward, choose the option Values only to paste only the values of the copied selection.

Finally, the Values only option will only paste the values from the copied data without any formatting.

Note: The shortcut for the Values only option is CTRL + SHIFT + V.

2. Copy and Paste Format Only

When you want to copy-paste the formatting from a source you have to use the option Format only. Any kind of formulation, data, or values will not paste while using this option.

In the beginning, select the data to copy the formatting.

Now select the cell where you want to paste the formatting. And then, select the options Paste special >> Format only.

In the end, the final output in the spreadsheet will look like the screenshot below. For our case, as we can see using this option only pasted the formatting of the selected cells.

Note: The shortcut for the Format only option is CTRL + ALT + V.

3. Paste as Formula

You can use this option when you want to copy formulas from a source. Only the formula will paste. Formatting or values of the source will not paste.

To begin with, select data to copy the formulation. Here the formula in the Total Salary column returns the product of the Salary and the Increment columns.

Next, select a cell to paste the formulation. After that, select options Paste special >> Formula only.

In conclusion, the final output will look like the screenshot below. Using this option, any kind of cell formatting did not paste. If you change the values in the Salary or Increment columns, the pasted values will also update accordingly.

4. Paste Conditional Formatting Only

We can format cells based on the criteria using conditional formatting. You can even copy-paste the conditional formatting from another sheet using the Paste special option. Follow the steps below to copy-paste the conditional formatting.

First of all, select the cells from where you want to copy the conditional formatting. In our case, cell E6 and cell E7 are marked with red colored backgrounds. The conditional formatting criteria of the selected cells is to color the cells red if the values are greater than $12,000.

Now select the options Paste Special >> Conditional formatting only after selecting the cells to paste.
In the end, the final output in the spreadsheet will look like the screenshot below. The Total Salary (February) column is formatted according to the criteria copied. Cells F5, F6, and F7 are marked red as the values are greater than $12,000.

5. Copy and Paste Data Validation

The Paste special option in Google Sheets enables one to copy-paste the data validation. The data validation option limits the input data to specific cells by using criteria.

Firstly select the data to copy the data validation criteria. For our case, the data validation criteria are to input numbers between 10000 to 15000. Here, cell E7 is showing Invalid as the inputted value is not between 12000 and 15000.

Now, we will paste the data validation criteria in the column Total Salary (February). After selecting the options Paste special >> Data validations only the output will look like the following screenshot. Here the values in the February column are different from those in the January column. Yet cell F6 is showing invalid as the value is not between 10000 and 15000. This confirms that only the validation criteria are pasted.

6. Paste as Transposed

This option is used to copy data and paste it into transposed form. Follow the below steps to copy data and paste it into transposed form.

In the beginning, select cells to copy the data.

Then right-click on the cell to paste the data. After that, go to Paste special >> Transposed.

In the end, the final output will look like the screenshot below. The copied values from columns are transposed to rows. The cell formatting and formulas are also pasted.

7. Copy Column Width Only

This option enables us to copy-paste the column width of the dataset.

To start with, select data to copy the column width. For this case, copy the range B5:C5.

Then right-click on the range D5:E5 and select Paste special >> Column Width Only.

Lastly, the final output will look like the following screenshot. Using this option, only the width of the column is pasted. No data, format, or formula is pasted.

8. Paste All Except Borders

We use this option to copy-paste data without cell borders. Values, format, formula everything will paste except the cell borders.

Firstly, select the data to copy. Here we have copied the Total Salary column from the Dataset sheet.

Then, right-click on the cell where you need to paste the data without borders and select Paste special >> All except borders.

In the end, the below screenshot depicts the final output in Google Sheets. Here, everything including values, format, and formula is pasted except the cell borders.

Things to Remember

You must select the target cell before using the Paste special options in Google Sheets.
The shortcut for Values only is CTRL + SHIFT + V and for Format only the shortcut is CTRL + ALT + V.

Conclusion

This article covers all the options that you will find while using Paste special in Google Sheets. Hopefully, following the article, you will learn how to use these options. Please comment below for further queries or suggestions. You can find more Google Spreadsheet-related blogs on our OfficeWheel blogs.