How to Use the Paste Special Options in Google Sheets

Join 425,000 subscribers and get a daily digest of news, geek trivia, and our feature articles.
By submitting your email, you agree to the Terms of Use and Privacy Policy.
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read more…

There’s more to pasting data into spreadsheets than simply inserting values copied from elsewhere. Google Sheets offers several Paste Special options. With these, you can paste formulas, conditional formatting, and more, in addition to values.

Access Paste Special in Google Sheets
Paste Special Options in Google Sheets
  Paste Values Only
  Paste Format Only
  Paste All Except Borders
  Paste Column Widths Only
  Paste Formula Only
  Paste Data Validation Only
  Paste Conditional Formatting Only
  Paste Transposed

As a refresher, you can copy data in Google Sheets in a few easy ways. Once you do, you’ll then access Paste Special.
To copy data:
Click Edit, Copy in Google Sheets
To open Paste Special:
Click Edit, Paste Special in Google Sheets
While some of the paste special options in Google Sheets seem clear enough, others may not. And whether you’re copying data, formulas, or formatting, it’s important to know the correct paste option to select.
Let’s look at each of the eight paste special options currently available and what they do.
You can think of Paste Values Only as the plain text paste you’d use in Microsoft Excel. This action pastes only the text without formatting. If the data you’re copying includes a formula, Paste Values Only will paste only the result of the formula.

If it’s not the data you want to paste but the formatting instead, Paste Format Only is the action you want. This is an alternative to the Paint Format tool because it will not change any data and only paste formatting.
Example of pasting Format Only
You may have data with borders, formulas, and other formatting. Paste All Except Borders does exactly what the name implies. It pastes everything you copy including data and formatting except cell borders. This option helps you avoid reformatting your borders when moving data around.
Example of pasting All Except Borders
When you take time to size columns in Google Sheets the exact sizes you want, you may want to carry those sizes elsewhere in your sheet. With Paste Columns Widths Only, all that’s pasted to your selected cells are the widths of the columns and nothing else.

The opposite of Paste Values Only where you only see the result of a formula you copy, Paste Formula Only actually pastes the formula. A good example use for this paste special option is when adding totals to rows or columns. You can simply copy and paste the formula to the remaining cells.
Example of paste Formula Only
Data validation in Google Sheets can keep improper data from being entered into your sheet. So if you set up a data validation rule, you can use Paste Data Validation Only to copy and paste that same rule to additional cells in your sheet. Nothing else will be pasted but the data validation.
Example of copying and pasting Data Validation Only
With conditional formatting in Google Sheets, you can do things like highlight blanks or errors and apply color scales based on values. If you create a conditional formatting rule that you want to apply to other areas of your sheet, copy and then paste with Paste Conditional Formatting Only. Nothing else will be pasted but the conditional formatting.
Example of copying and pasting Conditional Formatting Only
The last, and to many the handiest, of the paste special options is Paste Transposed. With this feature, you can copy cells in a column and paste them into a row or vice versa. Essentially, it allows you to convert a column to a row or row to a column.
Example of Paste Transposed
When entering data into a spreadsheet, anything that can speed up the task is welcome. So the next time you want to copy and paste, data, formulas, or formatting, remember your paste special options in Google Sheets.
RSS Feed
The Best Free Tech Newsletter Anywhere
By submitting your email, you agree to the Terms of Use and Privacy Policy.


Digital Strategist Chris Hood

Leave a Reply

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

© 2021 SHAQ HAX - Proudly powered by theme Octo