SubscribeCopy like a cat - paste like a pro : 17 excel pasting tricks you must know
Paste or Ctrl+v is probably the most effective productivity tool available to us. But how well do you know “Paste”?, do you know that there are at least 17 different variations to paste data to your excel sheets? Surprised? Well, read this post to become a master paster 
The post is divided in to 2 parts,
- Basic Pasting Tricks
- Pasting while Manipulating Data
-
Paste Values
If you want to just paste the values from copied cells, just hit ATL+E followed by S and V. Very useful when you want to strip away existing formatting and work with plain data. -
Paste Formats (or Format painter)
Like that sleek table format your colleague has made? But don’t have the time to redo it yourself, worry not, you can paste formatting (including any conditional formats) from any copied cells to new cells, just hit ALT+E S T. -
Paste Formulas
If you want to copy a bunch of formulas to a new range of cells - this is very useful. Just copy the cells containing the formulas, hit ALT+E S F. You can achieve the same effect by dragging the formula cell to new range if the new range is adjacent.
-
Paste Validations
Love copy those input validations you have created but not the cell contents or anything, just press ALT+E S N. This is very useful when you created a form and would like to replicate some of the cells to another area. -
Adjust column widths of some cells based on other cells
You have created a table for tracking purchases and your boss liked it. So he wanted you to create another table to track sales and you want to maintain the column widths in the new table. You dont have to move back and forth looking for column widths or anything. Instead just paste column widths from your selection. Use ALT+E S W.
-
Grab comments only and paste them elsewhere
If you want to copy comments alone from certain cells to a new set of cells, just use ALT + E S C. This will reduce the amount of retyping you need to do. -
Of course you want to paste everything
Just use CTRL+V or ALT+E+P or one of those little paste icons on the tool bar
Manipulating with copied data while pasting
-
Add while pasting
For example, if you have in Row 1 - 1 2 3as values and in Row 2 -7 8 9as values and you would like to add row 1 values to row 2 values to get -8 10 12, you can do this using paste special. Just copy row 1 values and use ALT + E S D.
-
Subtract while pasting
Just use ALT + E S S
-
Multiply while pasting
Just use ALT + E S M
-
Divide while pasting
Just use ALT + E S I
-
Skip blank cells while pasting
This is very useful when you are pasting data from that contains lots of blank cells. Instead of using filters or some type of if()s you can simply use ALT+E S B. This will remove all the blank cells from the copied cells before pasting it. Remember you can combine this option with other types of pastes (For eg. you can skip blanks while pasting values alone or skip blanks while adding the copied cells to paste area.)
This is wrong. Skip blanks is instead used to paste only data cells and not blanks (thus retaining any data in the paste range corresponding to the blank cells in copied range) -
Convert rows to columns or columns to rows while pasting data
For example you have large list of values in column A and you want to move (or copy) these values to row 1 across. How would you do that? Of course you can rely on trusty paste-special to do that little job for you. Just use ALT + E S E. This will transpose copied values before pasting, thus converting rows to columns and columns to rows. -
Paste reference to original cells
If you want to create references to a bulk of cells instead of copy-pasting all the values this is the option for you. Just use ALT+E S L to create an automatic reference to copied range of cells.
-
Paste text by converting it in to columns
This option is very useful when you are pasting data from outside. For example, if you want to paste few lines of this blog post in an excel sheet but would like to see each word in a separate cell, you can copy the content here (CTRL+C), go to your excel sheet and use CTRL+V to paste the data and then click on the paste icon that appears at the bottom of the pasted cell and select “use text import wizard” option. This will launch the mildly powerful text import wizard of excel using which you can convert copied text to columns by defining some simple parsing conditions. The default options split text into words (by using space as a delimiter). You can use this option to convert most types of text including comma separated values, fixed width values. The text import wizard dialog looks somewhat like this.
-
Paste live data from web
If you want to import live data from web to your worksheets, you can use Excel web queries. This is a powerful tool using which you can create worksheets that can fetch data from web (or network resources) and display in your worksheet for you to process them. You can findout more about these by learning to create a mutual fund portfolio tracker sheet.
-
What is your favorite paste trick?
There are many more paste tricks that are hidden in excel, like pasting live xml data to your sheets, pasting images, objects, files etc. But I am more interesting in knowing your favorite pasting hack. So tell me, what is your all time favorite paste?
Basic Copy Pasting of Data
Interested to learn more? Show temperature using these thermometer charts, Get stock quotes to your workbook with one click, Master your ifs and buts - learn these 6 tips on if(), Project plan in 60 seconds, your time starts now!, Honey! I shrunk the bar charts and much more
| Delicious | Stumble it |
« Prev | Home | Vodafone - creativity gone awry »
Have an Excel Question?
Trackbacks & Pingbacks
- Pingback by Copy like a cat - paste like a pro : 17 excel pasting tricks you … - Learn Excel on July 7, 2008 @ 6:31 am
- Pingback by Splitting text in excel using spreadsheet formulas | Pointy Haired Dilbert - Chandoo.org on September 8, 2008 @ 3:45 pm
- Pingback by links for 2008-09-17 « Schedelbeuk on September 17, 2008 @ 3:02 pm
- Pingback by 15 tips on customizing MS Excel to become more productive | Pointy Haired Dilbert - Chandoo.org on October 16, 2008 @ 12:55 am
- Pingback by Happy New Year to all PHD Readers | Pointy Haired Dilbert - Chandoo.org on December 31, 2008 @ 3:45 pm
- Pingback by Weekly Links - Even More Downloads Edition | Pointy Haired Dilbert - Chandoo.org on February 12, 2009 @ 12:12 am
- Pingback by Skip Blank doesnt skip blank cells ?!? | Learn Excel | Pointy Haired Dilbert - Chandoo.org on March 19, 2009 @ 7:39 pm
- Pingback by Use Enter to Paste Copied Values in Microsoft Excel [Quick Tip] | Learn Excel | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 27, 2009 @ 4:50 pm
- Pingback by 100 Excel Tips & Resources for Everyone | All Time Hits | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 30, 2009 @ 2:10 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
Join Our Community
There are several things in it that I did not know, bookmarked for future purposes
Great Tips….Excel Guru…
This is awesome! I never knew about many of these undocumented features until now.
Your Excel tips rock, dude.
Nice one machhi
.. awesome!!
Any suggestions for how to paste text into a wordpress blog post while still keeping formatting?
I just want to paste into a post without losing bolded cells and centered text. Basically I want the page to look the same as if I did save as HTML. I don’t need interactive cells or anything like that.
Any ideas?
@JEF… sorry if the response is late and useless.. But you can save the sheet as html file and refer to it in your wordpress post using html embedding, could be painful as Excel save as html writes a ton of css code.
The best thing you can probably do is, upload the document to google docs, and then embed selected cells to your wordpress post using their embed html iframe code. its pretty cool and works on all the browsers (doesnt work on the rss readers though)
If the content need not be text, you can try pasting the excel tables as images. Let me know if this helps.
Hi there
Any ideas on the best way of taking an Excel table and embedding on a web-blog? I am trying to do this on Blogger….I am running Excel on a Mac, should that make any difference?
Many thanks
@supersava : welcome to PHD blog, hope you like our excel tips.
wrt, taking the excel table to web
for static tables your best way is to embed them as a picture, as it preserves the layout.
for dynamic content you have limited ways of doing it.. one is to save the excel sheet as a web page and embed it in an iframe or something on the webpage, the negative with this is that ms save as web page creates a ton of xml / stylesheets to it and may not work in all browsers.
I would suggest using google docs if your sheets need constant web sharing.
This information is very helpful, but (there’s always a but) I need to shut off the text-to-table formatting. I am trying to paste text into a worksheet, and Excel insists on breaking each line into separate columns (like tip # 5, Paste text by converting it to columns). That’s a fine thing when you want it, but how can I make it NOT do that? I searched through the Excel Help and found no answer. Thanks very much.
@Neil
I am not sure whether I understand your request correctly: You copied some text and you want to paste it into one cell in Excel? If so, simply go to the formula bar and use ctrl-v to paste it there.
@Neil.. do you want to paste the table in to rows but in one column instead of multiple columns?
I would probably do that by first pasting the table in notepad or something and then copy pasting that in to excel. This way excel pastes each line in the notepad to one row.
Ah, I understand.
I assume Neil’s text has tabulators as seperators between the words instead of blanks and I am not sure whether copying to notepad would help in this case.
If the text is coming from a table in Micrsoft Word you could convert the table to text in Microsoft Word and use a blank as the seperator. If you copy this text to Excel afterwards, the text will be inserted in Excel in several rows (one row = one line of text), but only one column.
How do I copy text and paste it into an Excel comment field? I have tried and each time the paste icon disappears when I place the cursor in the comments box.
@Charles
did you select a cell (with text in it), copied the cell and tried to paste it into a comment?
This will not work.
You have to go to the cell, go to the formula bar, select the text, copy it and then paste it into the comment.
Hi, can I do the opposite of trick 12: skip blank cells while pasting? I want to paste a column of data to another column and insert blank cells in. So the new column would be something like this: one cell with data followed by 3 blank cells, followed by another cell with data, and then 3 more blank cells, etc. Do you have any suggestions? Thanks!
@Mary
I don’t know a way of doing this by copy and paste, but you could use formulae to achieve want you want to:
Assume you have the data in cells A1 to A10 (e.g. 100 in A1, 200 in A2, etc.) and you want to copy this to column B with three blank cells between each data point (e.g. 100 in B1, 200 in B5, 300 in B9, etc.).
Insert the following formula into cell B1 and copy it down:
=IF(MOD(ROW(B1)-1;4)=0;OFFSET($A$1;(ROW(B1)-1)/4;0);”")
If you don’t want to keep the formulae, you could do this once and then copy column B and paste special / values.
That’s it.
Handy paste trick: Sometimes when you import data from another application it is treated as text, not as numerical data. To convert the data back to numbers, copy a blank cell (i.e., value of zero), select the cells you need to convert, and use paste special > add. By telling Excel to add zero, it forces Excel to evaluate the text as a number.
Text to columns also makes this conversion, more reliably in fact, but copy blank > paste special > add is quicker.
Here’s another useful pasting trick I learned from Bob Umlas — use your mouse to paste values. If you have a group of formulas you want to paste in as values, right click and drag them on top of themselves, then choose “Copy here as values only” then the formulas will overwrite themselves with their values.
Enjoy,
JP
I just watched WALL-E the Pixar film…. It’s brilliant. PS. Thanks for the good tips.
http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2/
@Matt: Thank you for sharing the link.
I have one cell containing a paragraph of text. If i select a line containing some characters having the text formatting as ‘Bold’. i copy that line and paste into another cell. The text formatting gets removed. i.e ‘Bold’ has now become normal. Please guide me if there is a work around for this.
MD -
That’s normal behavior. If you want to copy the contents and the formatting, you have to copy the entire cell, not the contents of the cell.
Many thanks, Jon Peltier.
I forgot to mention that the destination cell also contains a different paragraph of text, so copying the entire text is not required and only a line from the original paragraph. sorry.
Another thing i noticed is that, when we try to ‘Find and Replace’ some text in a paragraph containing some previous text formatting like ‘Bold’ or ‘Italic’, after replacement of a single word, the whole paragraph loses its original formatting. Please guide me if there is a work around for this one also.
M D -
Let’s see, multiple paragraphs in a cell, bold or italic formatting….
If you need to manipulate and format sizable blocks of text, the workaround is to use a word processor.
@MD: You are right, find replace does this. Also, unfortunately there is no way (that I know) for copying partial contents along with formatting to another cell. Excel is not the right tool if you want rich text formatting for cell contents, as Jon says. I guess you are trying to do some heavy text formatting with excel.
Anyways, I dont know an efficient way to do this. So we are offering a donut to whoever figures this out…
Hi, found this page while searching for an answer to a problem with Excel pasting. I work at a help desk and can’t solve a recurring problem; hoping someone can share some insight.
Using Office 2003, SP3 in Windows Vista Business, SP1.
Have a user who continually has issues with copying/pasting cells from one worksheet to a clean worksheet in another workbook. The pasted cells show up as a picture without using any special keystrokes to copy, simply highlighting the cells, right-click and copy, then going to the destination page, right-clicking and paste. Same if using CTRL+C/CTRL+V. When we remotely view this person’s PC, the issue cannot be duplicated…she can copy/paste without any problem. Have tried detect/repair and uninstall/reinstall, but the issue comes back up randomly.
Again, just hoping someone may have an idea why this may be happening.
Thanks, and love the tips given on this page!
To PHD,
I tried to skip blank cells while pasting in Excel 2003 and it does not seem to work. I used your example in item 7 of your tips publication. I replicated what you had in the example, highlighted it, then edit-copy, then went to another cell and did the Alt, E, S, B, and it pasted, but it did not “skip the blanks”, but made an exact copy.
Maybe I’m doing something wrong.
Thank you for your help.
Bruce
To not include blanks, use Ctrl+Mouse to select multiple regions, omitting any blanks. If the selected range is well-formed, you can copy it, then when you paste it, the values are pasted into a contiguous region without the spaces.
Don’t include blanks means for example if you copy five cells:
1,2,blank,4,5
and paste onto this range using Don’t Paste Blanks:
A.B.C.D.E
all but the blank are pasted, and the result is
1,2,C,4,5
Thank you so much for the tip in number 15.. It really saves so much time.
I love you man….thanks a ton!