30+ Excel Copy & Paste Tricks You Must Know – Mouse Keyboard Autofill Paste Special and More!
Ctrl+C & Ctrl+V – The Saga continues!
For sure Ctrl+C and Ctrl+V is one of the most famous keyboard shortcut in windows environment and one big reason for it become famous beyond limits is that it can be used in almost every Windows application software including Microsoft Excel.
You might know how to do copy and paste in many ways like using mouse right click or ribbon or keyboard. But not many know that this feature is productivity heaven and it can be used in more than 30 possible ways to do things super easily and super fast!
I will start with the very basic use of copy/paste feature and then move on to explain other variations.
Basic Copy paste with mouse or keyboard shortcut
1. Mouse #1 – Right click copy + Right click paste
I assume you already know that you first have to copy the data and then you can paste it. To do this using mouse:
Step 1: select the data > press right mouse button and the context menu will open > select copy
Step 2: Go to the cell where you want to place the data > press right mouse button again > and this time select paste
Your data will be pasted
2. Mouse #2 – Using ribbon buttons
Step 1: Select the data you want to copy. Go to Home tab > Clipboard group > Copy button
Step 2: Go to cell where you want to place the data and again home tab > clipboard group > but this time press paste button
3. Mouse # 3 – Mouse + Keyboard combo
Holding down Ctrl key helps you duplicate the data easily by simply moving the selection to new location. Following steps explain:
Step 1: Select the data and move the mouse just above the selection border. Cursor will change to quad-directional cursor
Step 2: Press and hold down Ctrl key. This will change the quad-directional cursor to normal cursor with plus sign and press and hold left mouse button and drag the selection to the place where you want to paste it.
4. Bonus tip: Shift + Mouse
If you use Shift key and move the selection it will Cut and paste instead of copy paste. And not just that it basically insert the selected data in place. Normally if you copy/paste or cut/paste the pasted data replaces the existing data. But with Shift key pressed down it will rather Cut and “Insert” instead of Cut and “Paste”
Step 1: Select the data and move the cursor just above the selection border, cursor will change to quad-directional cursor.
Step 2: Hold down Shift key on the keyboard and press and hold left mouse button and drag the selection and leave the mouse button where you like to insert the data.
5. Keyboard #1
Step 1: Select the data holding down Shift key or pressing F8 once and hit Ctrl+C key combination.
Step 2: Use directional keys to navigate to the right place where you want the data pasted and hit Ctrl+V to paste the data
6. Keyboard #2
Step 1: Select the data using Shift key or F8 key command and press context menu button on the keyboard. It is usually found on the right side of space bar. On pressing it a context menu will open. Use directional keys to move to copy command and press Enter key to copy the data.
Step 2: Use directional keys to navigate to right place in Excel worksheet and press the contextual menu button again and this time select the paste command by moving to paste option using directional keys and pressing Enter key.
7. Bonus Tip: Use in-menu shortcuts
If you are playing close attention you might see that if menu is invoked in every command a specific letter is underlined. This underline letter basically works as keyboard shortcut. For example once the menu is invoked to copy the data instead of moving the selection highlighter using directional keys simply hit “C” button on the keyboard and data will be copied.
Using fill handle
Do you notice a tiny box at the bottom right corner of the selection? This is fill handle. This little feature has so much to offer. Basically it helps fill the data mostly a series automatically. Due to the same reason it is also called autofill handle. To learn more about autofill and its use read this article:
But we can use it to copy and paste the data easily.
8. Copy/paste or Fill single cell repeatedly
Make that cell active and simply drag the fill handle down the column or across the column to fill or paste the same data in other cells.
9. Copy/paste or Fill multiple cells repeatedly
Select the cells you want to copy and paste. Press and hold Ctrl key on the keyboard and now drag the fill handle. Excel will fill the cells with the same data one cell at a time.
10. Bonus: Shift key + Fill handle
Instead of holding down Ctrl key if you press and hold Shift key, Excel will not only copy/paste the data but also add additional rows or columns in place. So it acts more like copy/insert instead of copy/paste.
11. Bonus: Double click fill handle to fill quickly
Instead of dragging the fill handle you can even double click the fill handle and Excel will fill the range of cells instantly for you. However, to do this Excel needs certain data to be present around the active cell otherwise it won’t work.
Paste Special – Excel’s Very Special!
If you are one of the regular readers of our Excel articles, you must have seen paste special many times in action. This feature catapults pasting to a whole new level. Follow along as I discuss each and ever thing it can do while doing copy-paste!
12. Paste special – Copy paste just values
Normally when you copy and paste the cell Excel copy and paste everything including text color, cell color, number formatting, formula and everything else. However, if you only want to copy and paste the value and leave everything behind then following these steps:
Step 1: Select the data using mouse or keyboard and copy it
Step 2: Paste the data where you like. The moment you paste the data, you can see Ctrl smart-tag or smart-menu. Click on it and from paste value group select the first option to paste just values and leave everything behind.
Alternative#1 to Step 2
Once the data is copied then go to cell where you want to paste the data and then go to Home tab > Clipboard group > instead of pressing paste button click the bottom half of button i.e. drop-down button > And from paste values sub-group select the first option i.e. Values.
Alternative#2 to step 2
Once the data is copied go to cell where you want to paste the data. Press right mouse button and you get several options in place of paste. Select the one that says “123” and you will paste only values.
Alternative #3 to step 2
Once the data is copied go to cell where you want to paste the data. Hit Alt+Ctrl+V (yes! this is paste-special shortcut) and this will bring paste special dialogue box. From paste group select values and click OK. And this will paste just the values from the copied cells.
13. Paste special – copy paste just formats
Sometimes instead of pasting values you want to copy and paste the format settings. Format includes, the text style, size, text color, fill color, number format setting etc. To do this simply copy and hit Alt+Ctrl+V and from the dialogue box select formats radio button and click OK. This will skip the values from copied cell and paste the format rules to the destination cell. This option is good if you already have a certain data in destination cell and you want only format settings to be pasted.
14. Bonus tip – Format painter
To copy and paste just the format settings, Excel comes with dedicated feature called format painter. This basically copies the format settings of one cell and apply/paste/paint them to specific cell or cells.
To copy the format settings of a single cell or multiple cells simply select them and then go to Home tab > clipboard group click format painter button and then go to destination cell(s) and hit format painter button again to paste the format settings.
15. Paste special – copy paste only values and number format
As said earlier formatting involves so many things text color, text size, cell fill color, number format etc. However, if you want to drop everything but just want to copy the value and the number format of one cell to the other cell.
To do this copy the cell and then go to cell where you like to paste the data and hit Alt+Ctrl+V to bring the paste special dialogue box up and select values and number formats option and click OK. This will paste the value with number format setting.
16. Paste Special – Copy paste just formula and number formatting
Have a formula in cell that you want to copy just that in another place? Its super easy with paste special. Simply copy the cell that contains the desired formula and go to place where you want to paste the formula and hit Alt+Ctrl+V to activate paste special dialogue and select formula and number formatting option and click OK. This will paste formula and also number format settings.
17. Bonus tip – Paste special – Copy paste just formula
I observe that if you paste using context menu then you get somewhat additional options that are not available in paste special dialogue box. For example to paste just the formula in cell, copy the cell with formula and go to cell where you want to paste. Press right mouse button and from the context menu select formula option or from the keyboard click (F).
You can also have such additional options if you paste it using ribbon button.
18. Paste Special – Copy paste just comments
Adding comments to each cell is hectic and having the same comments for other cells is even more hectic as you will have to type comment for each separately. But if you know how to copy and paste just the comments then its not a problem any more. To copy just the comments simply copy the cell containing desired comments and go to cell to paste the comment hit Alt+Ctrl+V to activate paste special dialogue select comments option and click OK. This will paste only the comments and leave everything behind.
19. Paste Special – Copy paste just column widths
Sometimes you have certain columns with specified width. If you want to adjust the width of other columns to be exactly the same then its a hectic process as you have to first look for width in units and then adjust it. However, you can have this done with paste special facility too.
Select the cell or cells of which you like to copy the width, copy them and go to cells to adjust the width hit Alt+Ctrl+V and from dialogue box select column width option and click OK.
20. Paste Special – Copy paste all except borders
Accountants often face this problem as they have to copy and paste the figures of sub totals or grand totals calculated. But accountants often put borders around such figures. For example subtotals are represented by single border and grand totals are represented by double borders.
To copy/paste everything except borders then after copying the data use paste special dialogue box to select the option all except borders to paste everything except any kind of border styles.
21. Paste Special – Copy paste data validation
Data validation lists are great feature in Excel. However if you like to copy and paste the data validation rules then simple copy/paste does work but it paste the data validation lists with active data in the copied cell. However, if you like to copy/paste only the data validation rules then copy the cell containing data validation rules and go to cell where you like to paste hit Alt+Ctrl+V to open paste special dialogue box and from it select validation and click OK.
22. Paste Special – Copy paste link or cell reference
Most of the time we copy and paste the data because you want the same data at another place as well. However, some situations require the source and destination cells to have the same data but linked in a manner that if values in source change the values in linked cells should also change. This is possible easily if you put cell references in the destination cells. But for this you have to insert the manually. But you can do this easily using paste special feature to paste link to source cells.
To do this simply copy the cells and go to cells where you like to paste the cells hit Alt+Ctrl+V and from the paste special dialogue box click the paste link button. This will insert the cell reference of the copied cell or cells instead of values in the cells. This way if source cell’s value changes the destination or linked cell will update automatically.
23. Paste Special – Copy paste picture of data range
Ever fancied that you put the picture of selected cells? Having the picture of certain data range is easy to move, rotate and give other picture effects. But for this you really don’t have to go outside Excel’s environment to do print screen and crop picture to fit your needs.
You can do this easily with paste special but somehow this facility is not available in paste special dialogue box (or I am not aware of it) however, you can do this via right click contextual menu or drop down options of paste button in the ribbon under home tab.
24. Paste special – Copy paste linked picture
Yes pictures are boring if you are a number nerd. Because they are too static without life at all. But we might face a dilemma that we need a picture that updates if the source data range changes. Think that is impossible? Not anymore.
Now you can have linked picture that means you will get a picture however if the source data changes the data in the picture will update too. Believe me thinking that picture can change is really scary for me…
25. Paste special – Skip blanks
In some situations you have data in source data range and destination range as well. Normally when you copy a range that contains blank and paste it then blanks are preserved and if data is copied over another range then blanks existing data will be replaced with blank cells. However, if you use skip blank option while pasting the data then blank cells will not replace existing data value in the destination cell. To understand how skip blank option works watch the following animations
Paste Special – Conducting mathematical operations using copy/paste
Amazed? Yes it is totally cool! I too never imagined when I first learnt about it that how can we do mathematical operations like add/subtract/multiply or divide by simply copying and pasting. But it is totally possible with paste special facility. To learn how to do it read on.
26. Add
Have a number that you want to add in any cell and copy that cell. Then select the cell or range of cells to which you want to add and hit Alt+Ctrl+V and from the dialogue box under operation group select Add option and click OK. You are done 🙂
27. Bonus Tip – Add multiple numbers to multiple cells
In above example we added a single number to a single or multiple cells. However, if you have several numbers to add to several other cells then you can do this too using the same feature. Following animation explains:
28. Subtract
Have a number in any cell that you want to subtract from cell or cells. Copy it. Select the cells on which you want to perform subtract operation and bring paste special dialogue box up and this time select Subtract and click OK button.
The bonus tip discussed under Add applies for subtract as well.
29. Multiply
Get a number in cell that you want to multiple to select cell or cells. Copy it. Go to cell or cells with which you want the product, select them, open paste special dialogue box and select Multiply option from Operations group and click OK button.
Again, the bonus tip discussed under Add operation above applies to this operation as well.
30. Divide
Type the number with which you want divide the values of specific cells and copy it using keyboard or mouse. Select the values of desired cells on which you want to apply Divide operation and open paste special dialogue box using Alt+Ctrl+V shortcut and under operations group select Divide radio button and click OK. Your values will be factored!
Bonus tip discussed under Add applies for this math operation too.
Paste Special’s Special Extras!
31. Transpose
Few weeks back I discussed what transpose is in great detail and also discussed Paste special as one of the ways to achieve this feat. I highly recommend reading that article as I discussed numerous other ways to get the transposition done.
Transpose is simply a process of converting rows to columns or columns to rows. This can be done manually if data is small. But for large sets of data it is really difficult and almost impossible. But paste special let you do that by simply copying the data that you want transposed then go to the cell where you want to place the data and hit Alt+Ctrl+V and mark Transpose check box and click OK button. Now your rows will become columns or in other words columns will become rows. Awesome!
32. Merge conditional formatting
Conditional formatting is another cool feature but this is not simply about colors rather coloring of cell depends on set of rules. Although you can apply conditional formatting rules to another cells using simple copy paste or pasting just formats but if you have conditional formatting done in the destination cell as well then this facility let you merge the rules of conditional formats from source cell with the rules in the destination cell. That way existing conditional formatting of destination cell will also be preserved.
33. Using Clipboard to copy paste paste paste!
Not many know about clipboard feature of Excel and thus most of the time rely on windows’ clipboard and this is probably because we often don’t need it. But think of a situation that you have certain values to copy and paste and you have jump many times from cell to cell to do copy and then paste. It is frustrating as you have to copy the data again if you need it again as window remember only the latest item you copied.
But with Excel’s clipboard you don’t have to copy one data set over and over again every time you need it. Just copy all the data once by repeating the copy action and Excel clipboard will stack the copied data separately. Later simply click the data you want to paste in different cell and you don’t have to run between source and destination cells again and again.
34. Refreshable web query
Excel’s awesomeness is not bound to Excel itself. It can even cater things from outside pretty well. One of such example is importing data webpages that can refresh or update at specific intervals so that you can always have the latest information.
This tool is really handy for those people who have are in forex or currency exchange rate business and want latest information every 60 seconds. Therefore instead of having a static data copied from the webpages you can copy refreshable data from internet and plant it inside Excel and then conduct real time analysis using graphs etc.
To do this go the webpage from you want to copy the data table. Select it and copy. Come back to Excel and instead of pasting it go to home tab > click paste button drop down and select Refreshable Web Query.
An explorer window will open inside Excel with special features and the same page will be loaded from where you copied the data. Scroll to the intended data and select the data table you want to import by marking the relevant check box and then click Import button. Excel will take some time, depending on the data and HTML rules and paste the data inside Excel.
As this data is of special nature and does come with several options. That remains the topic of discussion for our future article 🙂
35. Restricting copy paste
Last but not least, we often want the data not get copied by strangers and unauthorized personnel. What you can do is you can completely restrict the function but let the user edit and do every other thing easily.
As per my knowledge up to this point, you can’t simply disable copy/cut paste function only. And if you are really after this you have write some really complex code in VBA which is situation dependent so there is no universal solution for this. However, you can disable the selection of cells which will automatically disable the copy/cut paste functionality too. This is done by protecting the worksheet or workbook. Follow these steps to get this done:
Step 1: Go to Review tab > Changes group and click Protect worksheet button.
Step 2: In the dialogue box mention the password and select the options that you want to allow to the users without password. Make sure select locked cell option is unchecked. Click OK. Excel will ask to confirm the password retype the password you entered before and click OK. Now your worksheet is protected and thus the copy/paste functionality too.
To renable it simply go to Review tab > changes group click Unprotect sheet button and put the password and all the functionality will be restored as normal.
Loved it? Pin it
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.