Paste special options in Google Sheets - Step-by-Step Guide

Introduction

Paste special options let you paste copied data in different ways. You can choose to paste only values, formulas, formatting, or other parts. This helps when you want to keep some parts of the data but not others.

When you want to paste only the numbers without the original colors or fonts

When you want to copy a formula but keep the destination cell's formatting

When you want to paste only the formatting from one cell to another

When you want to add or subtract numbers by pasting with a math operation

When you want to transpose rows into columns or columns into rows

Steps

Step 1: Copy the cells you want to paste

- Select the cells and press Ctrl+C or Command+C

The selected cells are copied and show a moving border

💡 Make sure you copy the exact cells you want to paste

Step 2: Select the destination cells where you want to paste

- Click on the first cell where you want to paste

The cell is selected and ready for pasting

Step 3: Open the Paste special menu

- Click Edit menu > Paste special

A list of paste special options appears

Step 4: Choose the paste special option you want

- From the Paste special submenu, click an option like 'Paste values only'

The copied data is pasted according to the chosen option

Step 5: Verify the pasted data looks as expected

- Check the destination cells

Only the selected parts of the copied data appear in the destination

Before vs After

Before

Copied cells have blue background, formulas, and numbers like =A1+10

After

Pasted cells show only numbers without background or formulas when using Paste values only

Settings Reference

Paste values only

📍 Edit > Paste special > Paste values only

Pastes only the numbers or text without formulas or formatting

Default: No

Paste format only

📍 Edit > Paste special > Paste format only

Pastes only the formatting like colors, fonts, and borders

Default: No

Paste formula only

📍 Edit > Paste special > Paste formula only

Pastes only the formulas without changing formatting

Default: No

Paste transpose

📍 Edit > Paste special > Paste transpose

Pastes data switching rows to columns or columns to rows

Default: No

Paste values and number formats

📍 Edit > Paste special > Paste values and number formats

Pastes values and keeps number formatting like currency or dates

Default: No

Common Mistakes

Pasting normally when you want only values

This pastes formulas and formatting, which may change the destination look or cause errors

Use Edit > Paste special > Paste values only to paste just the numbers or text

Using Paste format only but expecting numbers to change

Paste format only changes colors and fonts but does not change cell content

Use Paste values only or normal paste to change numbers or text

Summary

Paste special lets you paste only parts of copied data like values, formulas, or formatting.

Use it to control how data appears and behaves after pasting.

Remember to choose the right paste special option to avoid unwanted changes.

Practice

1. What does the Paste special > Values only option do in Google Sheets?

easy

A. It pastes the formulas but not the formatting.

B. It pastes only the numbers or text without any formulas.

C. It pastes the formatting but not the data.

D. It pastes the entire cell including comments and notes.

Solution

1. Step 1: Understand what 'Values only' means

   This option pastes just the final results (numbers or text) from copied cells, not the formulas behind them.

2. Step 2: Compare with other paste options

   Other options paste formulas, formatting, or comments, but 'Values only' ignores these and pastes only the visible data.

3. Final Answer:

   It pastes only the numbers or text without any formulas. -> Option B

4. Quick Check:

   Paste special 'Values only' = Paste data without formulas [OK]

Hint: Use 'Values only' to paste results, not formulas [OK]

Common Mistakes:

• Thinking it pastes formulas too
• Confusing it with 'Paste formula only'
• Expecting formatting to be pasted

2. Which of the following is the correct way to paste only the formatting of copied cells in Google Sheets?

easy

A. Edit > Paste special > Paste format only

B. Edit > Paste special > Paste values only

C. Edit > Paste special > Paste formula only

D. Edit > Paste special > Paste transpose

Solution

1. Step 1: Identify the paste special option for formatting

   The option 'Paste format only' pastes just the cell colors, fonts, borders, and other formatting without data or formulas.

2. Step 2: Eliminate other options

   'Paste values only' pastes data without formatting, 'Paste formula only' pastes formulas, and 'Paste transpose' changes orientation.

3. Final Answer:

   Edit > Paste special > Paste format only -> Option A

4. Quick Check:

   Paste special 'Format only' = Paste formatting [OK]

Hint: Choose 'Paste format only' to copy styles without data [OK]

Common Mistakes:

• Selecting 'Paste values only' instead of formatting
• Confusing 'Paste formula only' with formatting
• Using 'Paste transpose' for formatting

3. You copied a range of cells with formulas and then used Edit > Paste special > Paste formula only into a new location. What will be the result?

medium

A. The formulas will be pasted and adjust relative references automatically.

B. The data will be pasted transposed (rows become columns).

C. The formatting of the original cells will be pasted without formulas.

D. Only the calculated values will appear, no formulas.

Solution

1. Step 1: Understand 'Paste formula only'

   This option pastes the formulas exactly as copied, adjusting cell references relative to the new location.

2. Step 2: Consider what does not get pasted

   Formatting and values are not pasted; only formulas are pasted and recalculated in the new cells.

3. Final Answer:

   The formulas will be pasted and adjust relative references automatically. -> Option A

4. Quick Check:

   Paste special 'Formula only' = Paste formulas with adjusted references [OK]

Hint: Paste formulas only to keep calculations dynamic [OK]

Common Mistakes:

• Expecting values instead of formulas
• Thinking formatting is included
• Confusing with 'Paste transpose'

4. You tried to use Edit > Paste special > Paste transpose but the data did not switch rows and columns as expected. What is the most likely cause?

medium

A. The original data contains formulas that prevent transposing.

B. You used 'Paste values only' instead of 'Paste transpose'.

C. You copied only a single cell, so transpose has no effect.

D. Transpose only works on text, not numbers.

Solution

1. Step 1: Check the size of copied data

   Transpose switches rows and columns, but if only one cell is copied, rows and columns are the same, so no visible change.

2. Step 2: Understand transpose requirements

   Transpose requires multiple cells in a row or column to switch orientation; single cell copy won't show effect.

3. Final Answer:

   You copied only a single cell, so transpose has no effect. -> Option C

4. Quick Check:

   Transpose needs multiple cells to switch rows and columns [OK]

Hint: Copy multiple cells before transpose paste [OK]

Common Mistakes:

• Trying transpose with single cell copy
• Confusing transpose with paste values
• Believing formulas block transpose

5. You have a table with numbers and colors in cells A1:C3. You want to copy only the colors and paste them onto D1:F3 without changing the numbers. Which Paste special option should you use?

hard

A. Paste values only

B. Paste transpose

C. Paste formula only

D. Paste format only

Solution

1. Step 1: Identify what needs to be pasted

   You want to copy only the colors (formatting) without changing the numbers (values) in the destination cells.

2. Step 2: Choose the correct paste special option

   'Paste format only' copies cell colors, fonts, and borders without changing the data or formulas in the destination.

3. Final Answer:

   Paste format only -> Option D

4. Quick Check:

   Paste special 'Format only' copies colors without data [OK]

Hint: Use 'Paste format only' to copy colors without data [OK]

Common Mistakes:

• Using 'Paste values only' which overwrites numbers
• Choosing 'Paste formula only' which pastes formulas
• Using 'Paste transpose' which changes orientation