Paste Special in Google Sheets

What Is Paste Special in Google Sheets?

The Paste Special in Google Sheets is used to paste the copied content with specific formatting or options such as only value, only formatting, only column width etc. As we all know, the copy and paste features are essential in Google sheets to past data along. However, to copy the other features, we require the Paste Special.

Here’s how you can use Paste Special in Google Sheets. In this simple example, we have three cells labeled red, blue, and green and formatted with their respective colors. When you use a regular copy-paste, you get the values as in Column B with their formatting copied as well. However, in Column C, we needed only the values.

You can go to Edit – Paste Special – Values only. This pastes just the values and not the entire formatting.

Paste-Special-in-Google-Sheets-Definition
Key Takeaways
  1. You have the Paste Special in Google Sheets, which helps paste a required part of the copied content (e.g., only value, only formatting, or transposed, etc.). It also helps manage and apply data and formatting in Google Sheets.
  2. Some of the Paste Special in Google Sheets options include Paste Format Only, Paste All Except Borders, Paste Column Widths Only, Paste Formula Only, Paste Data Validation Only, etc.
  3. To apply Paste Special, go to Edit-> Paste Special and select the option you need from the list.
  4. Shortcut keys are available for “Paste Format Only” and “Paste Values Only.”

Paste Special Shortcuts in Google Sheets?

The Paste Special feature in Google Sheets is versatile. It allows you to paste data according to your requirements. This helps you manage the data effectively by controlling formatting to ensure effective data duplication as required. Let us look at the different paste special shortcuts in Google Sheets.

Paste Values Only

  1. To paste values only, select a cell or a range to copy.

For Windows, press “Ctrl” + “C” 

‍For Mac, press “Cmd” + “C” 

  • Select the cell to which you want to paste the data.
  • For pasting only values

For Windows, press “Ctrl” + “Shift” + “V” 

‍For Mac, press  + ”Shift” + “V”

Paste-Values-Only

Paste Format Only

  1. To paste format only, select a cell or a range to copy. Let us use the same sample as above.

For Windows, press “Ctrl” + “C” 

‍For Mac, press “Cmd” + “C” 

  • Select the cell to which you want to paste the data.
  • For pasting only values

For Windows, press “Ctrl” + “Alt” + “V” 

‍For Mac, press “Cmd” + ”Alt” + “V”

Paste All Except Borders

Here, we do not have any keyboard shortcuts for paste special, but this is simple to implement.

  1. To paste all data except borders, select a cell or a range to copy.

For Windows, press “Ctrl” + “C” 

‍For Mac, press “Cmd” + “C” 

  • Select the cell to which you want to paste the data.
  • For pasting all the things except borders:

For Windows, go to Edit -> Paste Special -> All Except Borders, and click on it.

Paste-All-Except-Borders

Paste Column Widths Only

Again, there are no keyboard shortcuts, but it can be done easily both in Windows and Mac.

  1. To paste column widths only, select a cell or a range to copy.

For Windows, press “Ctrl” + “C” 

‍For Mac, press “Cmd” + “C” 

  • Select the cell to which you want to paste the data.
  • For pasting column widths only:

Go to Edit — Paste Special – Column Width Only and click on it. You can see that the width of Columns A and B has been pasted into Columns D and E.

Paste-Column-Widths-Only

Paste Formula Only

There are no keyboard shortcuts; here’s how it is done.

  1. To paste Formula only, select a cell or a range to copy.

For Windows, press “Ctrl” + “C” 

‍For Mac, press “Cmd” + “C” 

  • Select the cell to which you want to paste the data.
  • For pasting Formula only:

Go to Edit -> Paste Special -> Formula Only, and click on it.

Paste-Formula-Only

Paste Data Validation Only

The “Paste Data Validation Only” option in Google Sheets on both Windows and Mac allows you to paste only the data validation rules from a range or cell. It does not affect the cell contents or formatting.

  1. Select the cell or range containing the data validation rules you want to copy. Here, we want Column C to contain values greater than 50; otherwise, it will give a warning.
Paste-Data-Validation-Only
  • The data validation was applied via. Data – Data Validation on Column C.
Paste-Data-Validation-Only-1
  • Select the Destination cells. Here, we copy column C and paste it into Column E as follows.

Remember, we are pasting only the Data Validation part. Go to Edit -> Paste Special -> Data Validation Only and click on it.

Paste-Data-Validation-Only-2
  • Now try pasting a value less than 50 in Column E, you can observe the “Data Validation” being pasted and you get a warning.
Paste-Data-Validation-Only-3

Paste Conditional Formatting Only

To apply only the conditional formatting rules from one range to the destination range without altering any data, you can click on the range that has the conditional formatting you want to copy.

  • Windows: Press Ctrl + C or right-click and select “Copy.”
  • Mac: Press Command + C or right-click and select “Copy.”
  1. Click on the cell or range, here Column D, where you want to apply the conditional formatting.
  2. In both Windows and Mac, go to Edit-> Paste special -> Paste conditional formatting only.Top of Form

Bottom of Form

Paste-Conditional-Formatting-Only

Paste Transposed

In Google Sheets, paste transposed allows you to switch rows and columns to reorganize data to fit specific requirements.

  • To transpose paste, first select your current range and press Ctrl + C.
  • Go to the target range. Go to Edit -> Paste Special -> Transposed and click on it.
  • You can find your rows and columns swapped.
Paste-Transposed

Examples

When using the Paste special in Google Sheets, you can easily and effectively manage and apply data and formatting in Google Sheets. Let us look at it with some interesting examples below.

Example #1 – Paste Values Only

We have Bangkok’s monthly average temperatures over a year. Let’s look at the details, as shown below.

Paste-Special-in-Google-Sheets-Example-1

Step 1: To apply conditional formatting for temperatures greater than 25, select the entire range and go to Format – Conditional formatting.

Example-1-Step-1

Step 2: In the “Conditional format rules” pane, choose the option “greater than” and enter the value as 25. We choose to format the cells in green as shown below.

Example-1-Step-2

Step 3: Once the conditional formatting is applied, we are required to paste only the temperature values in Column E.

Example-1-Step-3

Step 4: Now, select range B2:B14. Press Ctrl + C. Place the cursor in cell E1. Go to Edit – Paste Special – Values only and click on it.

Example-1-Step-4

This pastes just the values and not the entire formatting as well.

Example-1-Step-4-1

Example #2 – Paste Conditional Formatting Only

Now, let us try to paste the conditional formatting alone. Consider the same example above. We have the temperature of Another city in Column D. Let us apply the same conditional formatting rules to Column D for temperatures greater than 25.

Paste-Special-in-Google-Sheets-Example-2

Step 1: Select B2:B14. Press Ctrl + C. Select the range D2 to D14. Go to Edit- Paste special – Paste conditional formatting only. It is applied to Column E.

Example-2-Step-1

Example #3 – Paste Data Validation Only

If you want to copy just the data validation rules from range to another in Google Sheets without copying the data or formatting , you can use this option. Let us look at a drop-down list in A1. We have applied the data validation drop down through Data – Data Validation in the “Data Validation Rules” pane.

Paste-Special-in-Google-Sheets-Example-3

Step 1: You can now see how it is applied to A1.

Example-3-Step-1

Step 2: You wish to apply the same data validation rules to B1 in your sheet.

Click and select cell A1, whose data validation we wish to copy.

Press Ctrl + C. Then, click on B1 to select the cell where you want to apply the data validation. Place the cursor in B1.

Example-3-Step-2

Step 3: Now, go to Edit – Paste Special – Data Validation Only, and click on it.

Example-3-Step-3

Step 4: The data validation rules are also copied in cell B1, as seen below.

Example-3-Step-4

Example #4 – Paste Transposed

Let’s look at a range of numbers and copy-paste them transposed. For the uninitiated, transpose means swapping the row and column of the copied cells. Thus, if you have a long data set horizontally, it can be changed vertically, and so on.

Paste-Special-in-Google-Sheets-Example-4

Step 1: Select the cells from A1 to F1. Press Ctrl + C. Now, place the cursor in A2 and go to Edit – Paste Special – Transposed.

Example-4-Step-1

Step 2: Click on Transposed and check how the output is displayed in a transposed manner.

Example-4-Step-2

Important Things To Note

  1. Using “Paste special” options like “Paste values only” overwrites existing data in the target cells. Hence, exercise caution when using this option.
  2. When using Paste Special in Google Sheets, it is a good practise to ensure than the source and target range are of the same dimension to avoid unexpected results.
  3. Remember that using the “Paste Formula Only” option causes the formula to change when you use relative references. Only absolute references fix the reference when you use the “Paste Formula Only” option.

Frequently Asked Questions (FAQs)

1. What are some of the useful Paste Special options available in Google Sheets?

Some of the useful Paste Special options available in Google Sheets include:
1) Paste Values Only: This option pastes only the values from the copied cells without the formulas and formatting.
2) Paste Format Only: It copies only the formatting or conditional formatting to the destination cells.
3) Paste Conditional Formatting Only: This option pastes only the conditional formatting rules in the target cells.
4) Paste Formula Only: This pastes only the formulas from the copied cells without any formatting.

2. How Do You Copy Data Value Without Formulas in Google Sheets?

Usually, when you copy a cell that contains a formula, Google Sheets, by default, pastes the formula itself rather than the result. Doing so into another cell may cause errors as the formula cannot locate the same cells, and the relative reference will change while pasting.
To copy and paste only the value, you can use the Paste Special in Google Sheets option to choose only the value to paste into your cell.
For this, right-click in the cell where you want to paste the value.
In the menu, go to Paste Special and select Values Only.
Your cell will now contain just the copied value and not the formula.
You can also use the following shortcut keys:
• Ctrl + Shift + V on Windows
• Cmd + Shift + V on Mac.

3. What are some errors you might encounter when using Paste Special in Google Sheets?

1) When you paste values or formats, Google Sheets will overwrite the existing data in the target cells. When you paste only conditional formatting, the rules might not be applied as expected in the destination cell if it has different formatting.
2) There may be errors if the source range has merged cells while the target range does not.
3) Pasting formulas may cause errors if the references used in the formulas are incorrect in the new cell.

Download Template

This article must be helpful to understand the Paste Special in Google Sheets, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Paste Special in Google Sheets. We learn how to use the Paste Special feature in Google Sheets to paste specific details with examples and a working template. You can learn more from the following articles.

Word Count in Google Sheets

Running Total in Google Sheets

PV in Google Sheets

Reader Interactions

Leave a Reply

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