Did you know that Copy and Paste in Excel offers more than basic functionality? Have you ever encountered unexpected results when copying and pasting cells or data ranges?
Under certain conditions, you may experience errors when copying and pasting cells or data ranges. Maybe you think copy and paste not working in Excel.
For example, there is a data range that has a formula and you also apply cell formatting to that range.
If you want to copy a formula without applying cell formatting, copying it the simple way will not produce correct results. You’ll copy everything in that cell.
To give you an idea, this guide will teach you the 15 main copy and paste options available in Microsoft Excel and how to use them which is perfect for beginners.
You will also learn advanced tricks for using paste special, other ways to copy and paste, and keyboard shortcuts that can be used to speed up your work.
Table of Contents:
Examples of Copy and Paste in Excel
In this guide, as a solution to various problems you may experience, I use the following examples:
The data that will use the copy-paste feature is data in cells A2 to A10. Each cell has a data type, uses a cell formatting, and is filled in a different way as shown in the following table:
| Cell | Formula | Formatting |
|---|---|---|
| A2 | No. Manual Input. | Center Alignment |
| A3 | No. Manual Input. | Format Cell: Yellow |
| A4 | No. Manual Input. | Format Cell: Green, Font: Italic, Bold, Underscore, and center aligned. |
| A5 | No. Manual Input. | Font: Subscript for H2O |
| A6 | No. Manual Input. | Font: Superscript for 102 and Border |
| A7 | Yes. =A3+A2 | Center alignment and Format Number |
| A8 | Yes. =76.5*10 | Conditional Formatting: Data Bars |
| A9 | No. Manual Input. | Conditional Formatting: Data Bars |
| A10 | No. Dropdown List | Data Validation: Dropdown list. |
If you have difficulty copying the data above, please download the following Excel Copy-Paste file:
With this, you can test all special copy-paste features in Excel.
Before continuing with the example, make sure you know how to open the options and categories available in Paste Special in Excel first.
If you already understand it, please skip to the first example. If not, let’s learn about it first.
Steps to Use Copy and Paste in Excel
The way to use Paste Special in Excel isn’t just to use the Ctrl + C and Ctrl + V on your keyboard, but you have to open the paste-special option in the following way:
Note: I will not repeat this method in examples 1, 2, 3, 4 and so on. I hope you can understand this part first.
- Select the data range to copy. In this example the range A2:A10.
- Press the Ctrl + C keys on the keyboard simultaneously. This is the keyboard shortcut to create a copy of the range A2:A10 in Excel.
- Click your destination cell. For example, if you want to paste into cell C2, then click cell C2.
- Click the Home Tab.
- On the Paste Menu, click the Down Arrow Icon.
- There are 4 categories. Note: You can change steps 4 to 6 by pressing Alt, H, V on the keyboard
As you can see, Paste Special in Excel (currently) is divided into 4 categories, namely Paste, Paste Value, Other Paste Options, and Paste Special.
Important! You can also open the paste options dialog with a keyboard shortcut. First, use Ctrl + C to copy, then use Ctrl + V to paste. After that, press the Ctrl button 1x. You will see a paste option dialog like the following image:
15+ Examples of How to Use Copy and Paste in Excel
Previously, you already knew that Excel has 4 categories of paste options. Each category has several types and uses.
In this guide, examples 1 through 8 cover the “Paste” category. Example 9 – 11 for “Paste Values”. Example 12 – 15 for “Other Paste Options”. And Example 16 for “Paste Special”.
Let’s study them one by one.
#1 – Copy and Paste in Excel with Exactly Results, Except…
Please repeat steps 1 to 5 above, then select the first icon in the Paste category. In this example, I paste it into cell C2. The result:
It turns out that Excel copies all data in the range A2:A10, including cells that use Formulas, Cell Colors, Fonts: Italic, Bold, Underscore, Subscript (H20), Superscript (102), Border, Formula, Format Number, Conditional Formatting, and Data Validation: Dropdown List.
All data is copied exactly except for cell width and height. It can be seen that Column C doesn’t have the same width as Column A.
However, it is this first example (Paste) that is most often used. Ctrl + C and Ctrl + V.
#2 – Paste Formulas – Just Copy and Paste Excel Formulas
To copy a formula in Excel, please select the second icon – Formulas in the Paste Options Dialog. Then paste it into Cell D2 and see the results:
Well, you can see the difference, right? This paste type only copies exactly the formula in the source cell (Cell A7 to Cell D7).
Meanwhile, for other cells, Excel only copies the values. Doesn’t include Cell Formatting (Font, Number, etc.), Conditional Formatting, or Data Validation.
#3 – Copy and Paste Formulas & Number Formatting
As the name suggests (Formulas & Number Formatting), Excel will only copy “Exactly” cells that use Formulas and Number Formatting.
Additionally, Excel will use the default format. Take a look at the following results:
Excel copies the formula in cell A7 to cell E7 and also copies the Number Format. Meanwhile, Excel doesn’t copy the Font Format (Center aligned).
For other cells, the differences are clearly visible. Including Cell E10 no longer uses Data Validation.
Important! There are many types of Cell Formatting including Font and Number. If you don’t understand Cell Format, I recommend studying the Format Cell Guideline in M Jurnal.
#4 – Copy and Paste in Excel with Keep Source Formatting
Keep Source Formatting means that Excel will use the same format as the data source including Cell Format (Font, Border, Number, etc.), Conditional Formatting, Data Validation, and Formulas. Try to see the results:
So far, I haven’t seen any difference between Keep Source Formatting and General Paste ( Ctrl + V ).
Everything looks the same, including the formula for Cell A7. It even includes the column width, because the Excel paste results do not use the same column width as column A.
#5 – Copy Paste No Borders: Copy Everything Except the Border
It’s clear that this type of paste doesn’t copy the Borders Format from the data source. You can see this in cell A6 (which uses a Thick Border).
So far, Excel only excludes Border Formats as well as cell width and height.
#6 – Keep Source Column Widths: Copy Paste Exactly
Different from General Paste and Keep Source Formatting, Keep Source Column Widths copies all formats and formulas, conditional formatting, data validation, cell height and width, etc.
This is what is called “Copy Paste Exactly” or there is no difference. Excel copies everything including the cell height and width.
#7 – Copy and Paste in Excel with Transpose
Copy Paste Transpose will change data in columns into rows (and vice versa).
Apart from changing columns to rows, all Cell Formatting, Conditional Formatting, Data Validation, and Formulas are also copied.
It’s just that Excel doesn’t copy the cell width and height as shown in the following image:
Important! More on how to convert columns to rows (and vice versa) – aka copy transpose – I’ll cover in a separate guide.
#8 – Copy and Paste in Excel with Merge Conditional Formatting
The Paste Merge Conditional Formatting type will only be available if your data range uses Conditional Formatting.
As far as experiments go, I’ve not found any difference between the results of Paste Merge Conditional Formatting vs. General Paste and Keep Source Formatting.
They all look the same. Even the column width also uses the default width.
Note: The 8 examples above will copy the formula from the data source. Most types of paste only provide almost the same results as general Paste. The paste differences are only really visible if you use Paste Special: Formulas, Formulas & Number Formatting, No Borders, and Transpose.
Well, this is different from examples 9 – 11. These 3 examples will not copy the formula from the data source. Let’s try to prove it…
#9 – Just Copy and Paste the Values in Excel
Please select the Value icon in the Paste Special dropdown. In this example, I used a new Worksheet and pasted it into cell C2. Results:
At first glance, there is no visible difference between Paste Special Value Only and Paste Special Formulas.
Paste Special Value Only will “Only” copy all values except copying cell width and height, Cell Format, Conditional Formatting, Data Validation, and even Formulas.
Take a look at the formula bar in the cell C7 paste result. The value is exactly (35), which is the result of the formula =A3+A2 (20+10=35).
This type of Paste Value Only is also one of my favorites.
#10 – Copy and Paste Value & Number Formatting
Value and Number Formatting means just copying the value and its number formatting. This type is the opposite of Paste Formulas & Number Formatting.
Let me remind you again, that the Format Number feature is located on the Home Tab > Group Number.
So in this Paste Value & Number Formatting type, Excel will not copy other formats such as Format Font, Borders, Conditional Formatting, etc.
#11 – Copy and Paste Value & Source Formatting
This type is also the opposite of Paste Keep Source Formatting. The results are more or less like the following:
Note: The only difference is this: Keep Source Formatting = Copy the Formula too. Meanwhile, Value & Source Formatting = only copies values (not formulas) and doesn’t copy Subscript and Superscript.
All three examples for the Paste Value Category are complete. Please adjust which Paste Special type is most suitable for your data.
Now let’s try Paste Special which is more advanced. You’ll learn how to copy and paste in Excel with auto sync (beginner’s term).
#12 – Copy Formatting in Excel
To copy formatting in Excel, you can use Paste Special Formatting.
However, Paste Special Formatting doesn’t make the paste results synchronized (connected) with the data source.
Instead, it only copies the format from the data source without copying the formula or value, as shown in the following paste:
Note: The Paste Special Formatting type only copies Cell Formatting (Color, Border, Font, Alignment, Number, etc.), even Conditional Formatting. Meanwhile, Excel doesn’t copy Values, Formulas, Data Validation, Superscripts, and Subscripts.
Is‘t true? Let’s try to prove it:
Paste Special Formatting Doesn’t copy Format Fonts (subscript and superscript), formulas, and values
For example, I filled Column C with several different data (typed manually) as shown in the following image:
Cell C2 uses the same font format (center aligned), cell C3 uses the same cell color (yellow), and cell C4 uses the same font format (italic, bold, and underline) and cell color (green) as the source.
So far, Format Cells is completely copied. However, it’s different for the special Subscript and Superscript Format Fonts in cells C5 and C6.
Why doesn’t Excel copy the format?
Because Subscript and Superscript are created manually. You need to select the text or characters that will be Superscript or Subscript via the Formula Bar.
However, in Cell C6, Excel also copies the Border from Cell A6.
For Cell C7, this is clear, because in Paste Special Formatting, Excel does not copy values or formulas.
What I can discuss is only Alignment Format (Center Alignment). It looks like Excel is copying it from the Data Source.
Paste Special Formatting Also Copies Conditional Formatting
In cells C8 and C9, I use smaller numbers from the data source (A8 and A9). It turns out that Data Bars is still available with shorter bars.
That means, Excel uses the same Conditional Formatting Criteria as cells A8 and A9.
Paste Special Formatting Does Not Copy Data Validation
Lastly about Data Validation (C10). In the source data (Cell A10), I used the Female and Male with Dropdown lists.
If Paste Special Formatting also copies Validation Data, then I shouldn’t be able to fill in Cell C10 with data other than the List in the Dropdown.
But in reality?
In Paste Special Formatting, Excel does not copy Validation Data from the Data Source.
#13 – Paste Special: Link Type (Auto Sync)
Paste Link is one of the automatic synchronous pastes as I mentioned before (Did you forget? Click here to read again).
So what does auto-sync paste mean?
For example, try copying the range A2:A10, then use Paste Special Link to cell D2. Look at the results first:
So far it appears that Paste Special Link doesn’t copy the Source Data Format, such as Format Cell, Conditional Formatting, or Data Validation.
However, all values look exactly, except Subscript and Superscript (D5 and D6).
The contents in column D are the main key (synchronized copy and paste). Excel only makes cell references as formulas in cells A2 to A10 for the results of pasting links.
All Paste Link results use cell references. For example, cell D2 contains the formula =A2, cell D3 contains the formula =A3, and so on.
So when you change the contents of cell A2 to A10, the values in cell D2 to D10 will automatically change too.
How, interesting isn’t it? This is one of my favorite Paste Special features.
#14 – Paste Special as a Picture in Excel
Paste Special Picture will convert the data range you copied into an image.
You can change the image size from the Paste Picture results as you would setting the image in Excel as usual.
There’s not much I can discuss this Paste Special type, because the following Paste Special Linked Picture feature is much more interesting…
#15 – Paste Special as a Linked Picture (Auto Sync)
Paste Special Linked Picture doesn’t just produce an image. This feature is like a combination of the Paste Special Link and Picture features.
Of course, you can imagine how good this Paste Special Feature is…
There is an image where the image can change when you change the contents of the Source Data.
After using Paste Linked Picture, you will see an image that is the same as the Data Source.
Now try changing the Data Source (cell A2 to A10). It’s up to you to change it into whatever you want. And see the results:
Interesting right? I just discovered the benefits of this feature and it has become one of my favorite special paste features.
Then why is the image connected to the data source?
Because there is a cell reference in the image, try clicking on the image and then looking at the Formula Bar.
Steps to Advanced Paste Special in Excel
If none of the 15 examples of Copy Paste Special above suit your goals, then this example could be an alternative.
But, for this 4th category (Paste Special), some types of paste are examples of the 15 types above. There are just a few different types.
To make it clearer, please open the Special Paste Window in the following way:
- Select the data range to be copied (A2:A10).
- Copy source data ( Ctrl + C ).
- Click the destination cell to paste. For example C2.
- On the Home Tab, click the Down Arrow Icon in the Clipboard Groups.
- Click Paste Special to open the Paste Special Window.
In the Paste Special Window, please focus on the Paste section. I have discussed most of the available paste options above, such as Formulas, Values, Formats, etc.
However, some haven’t been discussed, such as Paste Comments and Validations.
To Paste Comments, Excel will only copy comments to cells that have comments.
Likewise, Paste Validations will only copy cells that use Data Validations. Meanwhile, cell formats, formulas, or other values are ignored.
Important! You can use the Operations section in Excel’s Special Paste Window to calculate Addition, Subtraction, Multiplication, or Division without using Formulas/Functions. I have discussed this guide specifically in each sub-chapter. Curious? Try visiting the subsections Subtract (for subtraction), Multiply (for multiplication), and Divide (for division). View the Shortcut Guide (Click Here).
So what about the Skip Blanks option?
In general, if you use Paste Special Skip Blank, then Excel will copy only the containing cells from your data range.
In other words, Excel will not copy blank cells. I will discuss more details in a separate guide.
Bonus: Other Ways to Copy and Paste in Excel
I also often encounter situations where when we want to copy columns that are filtered or there are hidden rows/columns.
Meanwhile, I only want to copy visible cells.
For this purpose, you can’t just use Paste Special Excel. Instead, use the Only Visible Cells feature of Go To Special. – Next Time for this Guideline or learn more about the tutorial in Indonesian. Click here –
There’s another situation here. Copy and paste data in the form of a formula, but don’t want the formula reference to change.
As seen, cell C2 contains the formula =B2-B8. This means that my goal is to calculate the result of the Score minus the Average Score.
Then I copy cell C2 to cells C3 to C7, depending on whether you want to use Autofill or the Paste Special example above.
Of course, the results were wrong and not what I wanted. What caused it?
The reason is that the Cell Reference used in the formula is incorrect.
Take a look at cell C3, the copy-paste formula changes to =B3-B9 which should not be B9 but B8 (data for Average Score).
This is just a simple example. You will experience this situation often. The only solution for this situation is to understand Cell Reference first.
Keyboard Shortcuts for Copy and Paste in Excel
Generally in Excel, you can use the Keyboard shortcut Ctrl + C to copy and Ctrl + V to paste.
Remember! That’s not enough to use Paste Special.
There are 2 methods of using keyboard shortcuts for Paste Special, namely the keyboard shortcut used before and after you paste ( Ctrl + V ).
This is easy! You just need to choose the one you like.
So, Please use the keyboard shortcuts to Copy and Paste in Excel in the following table:
| Option | Before Paste | After Paste |
|---|---|---|
| Paste (Default) | Alt, H, V, P | Ctrl, P |
| Formulas | Alt, H, V, F | Ctrl, F |
| Formulas & Number Formatting | Alt, H, V, O | Ctrl, O |
| Keep Source Formatting | Alt, H, V, K | Ctrl, K |
| No Borders | Alt, H, V, B | Ctrl, B |
| Keep Source Column Widths | Alt, H, V, W | Ctrl, W |
| Transpose | Alt, H, V, T | Ctrl, T |
| Merge Conditional Formatting | Alt, H, V, G | Ctrl, G |
| Values | Alt, H, V, V | Ctrl, V |
| Value & Number Formatting | Alt, H, V, A | Ctrl, A |
| Value & Source Formatting | Alt, H, V, E | Ctrl, E |
| Formatting | Alt, H, V, R | Ctrl, R |
| Link | Alt, H, V, N | Ctrl, N |
| Picture | Alt, H, V, U | Ctrl, U |
| Linked Picture | Alt, H, V, I | Ctrl, I |
| Paste Special Dialog | Alt, H, V, S or Ctrl + Alt + V | – |
In conclusion: After learning this guide, you already know that copy and paste in Excel isn’t only for copying values, formulas, formats, or cells. Excel has more advanced features. You can copy data ranges and paste them as linked images to data (auto sync).
It’s very very advanced…
So, there are still many skills that you can learn on the M Jurnal site. Let’s start with the following guide shortcuts
Guide Shortcuts: Excel Formulas and Functions
Previous Chapter: Share Excel
- Formulas & Functions: Basic examples of using Excel formulas & functions in the Formula Bar using the equal sign (=) and the Insert Function feature.
- Paste Options: You’re here!
Next Chapter: Cell References
