Copy like a cat – paste like a pro : 17 excel pasting tricks you must know

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?

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

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.

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/

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.

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!

Hi there! I’m trying to paste an entire sheet of contacts (all text) from an Excel database into a Google docs database. Unfortunately, google pastes in ” into certain cells for what appears to be no apparent reason and also leaves blanks cells where there shouldn’t be. I’m also losing all formating for the text in each cell (bold, italics, hyperlinks, cell shading) along the way.

Is there a way to copy and paste easily into a google database with everything remaining exactly as it is in the Excel one?

Thanks for any help in advance!

Hi! i’m trying to use the “Skip blanks” option where i only choose the cells with data from a column while omitting the blanks and then pasting the values into another column and maintaining the spaces in between each data cell.

Is it possible?

Thanks very much!

I can’t believe i did not notice that simple option! I feel like such an a-hole. lol!

I’ve just finished creating a macros that works and i got this wonderful news.

You’ve made my day! thanks a lot

Chandoo i have one more question for you.

if i want select an entire range of non-contiguous cells in a column and i found on the microsoft help page that i can use this example:

ActiveSheet.Range(”a1:” & ActiveSheet.Range(”a65536″). _
End(xlUp).Address).Select

How do i change the “a1″ & “a65536″ so i can use it with activecell. I’ve tried changing it for activecell.value but it doesn’t work.

Thankyou!

Chandoo, i’ve got one more question for you.

I want to select an entire range of non-contiguous cells in a column so i found this microsoft example:

ActiveSheet.Range(”a1:” & ActiveSheet.Range(”a65536″). _
End(xlUp).Address).Select

I want to change the “a1″ & “a65536″ for activecell. I’ve tried changing it to activecell.value but it doesn’t work.

Can you help me please?

Thanks!

Hello, hope everything’s well!

I’m interested in copying an entire range (say A1:C10) into a single column. Ideally, A1 will paste into Cell1, B1 will paste into Cell2, C1 will paste into Cell3, A2 will paste into Cell4, etc.

Is there any way to do this?

I guess that I can figure out a way for a formula to work if the range is always the same but I don’t seem to figure a way to copy an entire sheet into another one. I would like to copy Sheet1 and paste it on Sheet2 starting at A1 and going down from there.

I was in a live meeting at work and typed all the action items in the comments of each cell. I would like to create a separate column for all the comments so they’re viewable in the spreadsheet. Any thoughts on an easy way to copy/paste all the comments?

@Chandoo Thanks for the response relating to the Google Docs issue. It is glitchy going back and forth between the two which we need to do often amongst our team, but until they’re more compatible, we will keep re-uploading our worksheets like you said. Thanks again!

Does anyone know how I can paste special everything (including borders) without the formulas? Very frustrating that I cannot figure this out. Thanks for your help!

Krista,

as far as I know there is no built-in way to do this in one step, but you can simply paste special it twice. First time paste special|values and then paste special|formats.

Help. I was pasting text into excel and did some data sort type parsing to separate the text into columns using a space delimiter. Well, now for some reason every time I paste text into excel it automatically parses the data using a space delimit so that each separate word appears in a separate column. I need to know how to turn this “feature” off… it does it even in new spreadsheets I open. So somewhere there must be a property that I accidentally turned on.

Excel helpfully remembers the last text-to-columns settings that you have used. You can only reset it indirectly.

Select a cell, go to Edit menu > Text To Columns, Delimiters, uncheck everything except Tabs.

I am hoping someone can lead me in the right direction. I am trying to copy text from a WORD document and paste it into an Excel document. The paste is not displaying visible data. It appears that the data is pasted because the cursor is placed several spaces down as if the data is there but the data in the cell is not displaying/visible. I have pasted WORD text into this same Excel document before but all of a sudden I cannot paste any WORD text from any WORD document into this Excel document and it display. Any ideas?

Im having difficulty extracting data from a large spreadsheet so it is able to be sent in outlook. The main s/sheet exceeds the maximum outlook attachment size able to be sent. so what im trying to do is copy part of the spreadsheets formats into a new workbook and then copy the values (eliminating formulas) into the copied formatted cells. the only problem is that the file size of the new workbook still majorly exceeds the maximum outlook attachment capability, thus i have to compress the file still. i need to avoid compressing the file as some of the companies i deal with wont allow compressed files through their firewall. the data that is being copied is no more than a few hundred lines so i dont see why the files are always so large. for info, the main s/sheet is on excel 2007 and im converting the new workbook to 2003 before sending it.

Hello,

I am trying to translate text from one worksheet of an excel document to another worksheet using the “formula” function of excel, which usually does a good job of automatically moving text from one sheet to another, or from one cell to another. However, I always lose my formatting when I do this. I want to maintain the italic, bold, etc. formatting of the original cell when I move the contents using the formula. Is this possible?

Dylan -

Formulas have no idea about formatting. But try this procedure:

Copy the original range.
Select the top left cell of the new range.
Paste Special – Links
Paste Special – Formats

Show Paste Options button must be enabled under the Edit tab under Tools> Options to access the Text Import Wizard.

@Neil… When you want to stop Excel from automatically pasting text across cells, you just need to find a cell with text in it and click through the Text to Columns wizard (on Data tab)unchecking the option boxes. What’s happening is Paste Special picks up the last choice made to parse text. So…in the wizard pop up window….Step 1 Choose “Delimited” radio button then Next. Step 2 Uncheck all the boxes then click Finish.

@Neil… When you want to stop Excel from automatically pasting text across cells, you just need to find a cell with text in it and click through the Text to Columns wizard (on Data tab)unchecking the option boxes. What’s happening is Paste Special picks up the last choice made to parse text. So…in the wizard pop up window….Step 1 Choose “Delimited” radio button then Next. Step 2 Uncheck all the boxes then click Finish. When you next paste text it will not be parsed.

How to paste link with preserving format? When I choose paste link, format disapear. When I try paste format, I can not push paste link button. Is there any procedure how to paste link include source cell formating?

@Chandoo

Problem is much bigger then I thought I wil have to use VBA probably …

Im making excel tool for creating some paper documents according to excel data
My problem is that in one part of the document I need to put content which may have various number of lines. This could be solved by formula like this:
=”1.”&B1&IF(A1>2;CHAR(10)&”2.”&B2;”")&IF(A1>2;CHAR(10)&”3.”&B3;”")…and so on
A1 contains how many parts have to be assembled
B1, B2, … BN contain this parts
CHAR(10) makes every part in new line
My huge problem is when I need to have different format for several parts, because I need to have headings of this parts in bold

My first attempt was made out two files .xls and .doc
Everything was working perfectly since I put formatted(RTF) links into text block which was automaticly changeing height according to content

problem was when I move this two files to another computer so paths to this files has changed and links were broken. I decided to use relative paths for this links. This trick was working for all links except those in text blocks Word considers this links as broken.. I dont know why.

Thats the reason why I decided to put document to another sheet inside the excel and after then my new problem occured

@ Chandoo

I am linking a cell from one sheet to another which can be done eaily the tricky part is that in second sheet i would like to link cell from first sheet which is 26 cloumn ahead of the first linked cell as i have to link many cells which are exactly 26 columns ahead of previous one i cannot copy paste the link created at first can anobody guide be of any formula or shortcut to resolve this problem.
Thanks
Moiz

I want to create a tool that will allow the user to copy successive strings of text into the same cell. This would concatenate text, for example, DOG from one cell and CAT from another cell and MOUSE from another cell, each time adding to the prior text so that the first action places DOG in the cell and the next copy into that cell produces DOG CAT and the third produces DOG CAT MOUSE. The “add” function seems to only work on numbers.

Thank you for your consideration, ML

Mark -
 
You want the concatenation operator, the ampersand. You can concatenate using cell addresses or text within double quotes.
 
=A1&B1&C1
 
or if you want spaces between each cell’s text:
 
=A1&” “&B1&” “&C1

Concat: I appreciate the two suggestions, but I may not have explained properly.

If I have in cell A1 a value and want to add to the existing value in A1, what operator do I use?

I can see that I can concatenate several existing cells, but I want to be able to paste with the “add” operator if the values are numbers, but how do I paste into an existing cell an additional concatenation?

Does that sound like a different question or does your solution address this as well?

ML

@Mark
Copy the value you want to add
On the cell you want to add it to, Right Click, Paste Special, Add

@Mark
Except that only works for Values

Yes, the problem is, the “add” operator seems only to work for numbers (”Except that only works for Values”).

I will continue to explore options. Thank you,

ML

Hi Chandoo…your blog is fantastic and an excellent resource!

I am wondering if there is a simple solution to my problem. I need to copy a function into specific cells down a column of ~16000 rows. The problem is that the distance between the selected cells is not the same (i.e. paste needed every ~9 cells but sometimes more or less). The space differences occur because the group of reference data is not always the same size. Is there are way to paste a function adjacent to the same line for every cluster of data even though the size of the data cluster varies?

Thanks!

@Nadia, can you give us some idea of the formula you want and what happens to the data when there is a gap? ie: is it a blank row or 0’s etc

Hi Hui,
The formula is simple (=IF(E2>(AVERAGE(E3:E7)),1,-1)) and is entered to the top right of the data block in a separate column. The corresponding data spans from E2 to E8(which is a row of sum values) . Then there is an empty row followed by the next block of data. If the formula is entered into a row other than the top line of the block, the results omit important cells or include unwanted values. If it was zeros or blanks in between I would be able to deal with it…but alas not.
Any help is appreciated.

Nadia -

You need to use absolute references:

=IF(E2>Average(E$3:E$7)),1,-1)

Jon, thanks for the input, but I need the reference range to change with the corresponding block of data and not paste anything in between. If I didn’t have 16000 rows of data I would paste manually every ~9 rows. I can’t bring myself to copy/paste for hours…there must be a way to solve this?!

Nadia
I would first add a helper Column to the Right of Col E
I would put a formula in that so that it puts incremental values next to each block
In F3: =IF(E3=”",”",IF(E2=”",MAX($F$1:F2)+1,F2)) and copy down
so you will end up with 1 next to the first block 2 next to the second etc
This will also allow for gaps of more than 1 Row

Next I would put the following in G3 and copy down
=IF(E3>SUMPRODUCT(1*($F$1:$F$17000=F3),($E$1:$E$17000))/SUMPRODUCT(1*($F$1:$F$17000=F3)),1,-1)

Nadia
This is what the second equation should be
=IF(F3″”,IF(E3>(SUMPRODUCT(1*($F$1:$F$17000=F3),($E$1:$E$17000))/SUMPRODUCT(1*($F$1:$F$17000=F3))),1,-1),”")

Hi Hui,
That`s fantastic and it works! I have one more snag…I have a second stipulation from my original query that I thought I could deal with separately but it appears it would need to be included:
From my original query:(=IF(E2>(AVERAGE(E3:E7)),1,-1))
I then need to sort the results a second time with =IF(E8<0.05).
E8 is a row of p-values from a t-test comparing E2 to the average of E3:E7
I realize now that I should have nested this into the first query…
Can I make and extra column and work from the result of the second equation you gave me or does the second equation need to include this parameter.
Thank you for the expert help, you`re a lifesaver.

Nadia
Can you elaborate on
“I then need to sort the results a second time with =IF(E8<0.05)."?
Can you just copy column E and G as values and sort as appropriate ?

My apologies…to clarify:
My goal is that I need to know wheather the value in E2 is significantly greater or less than the average of the values in E3:E7. I completed a t-test for this comparison and the result is in listed in E8(or the last row in every block of data). I want to then count the total number of instances where E2(or equivalent row in any block) is signifcantly different from the average of E3:E7, AND either less than (denoted by ”-1”) or greater than (denoted by ”1”).
If I just try to sort the data in column G, I run into my original problem again…pasting the function per block of data down the column.
Does that make sense? Thank you for your patience.

So the last value in each block is the t-test result ?

yes that is correct.

I’ve got the E2 compared to E3:E7 bit

What should happen if E8 is > 0.05
What should happen if E8 is < 0.05

Sorry, I should have made that more explicit. Since I am looking for only the significant results (ie. E8 < 0.05). Originally I had one cell (in column Q) that answered =IF(E8Average(E$3:E$7)),1,-1).
I then count the number of results =SUM((Q2:Q16851=”1″)*(R2:R16851=”1″)) OR =SUM((Q2:Q16852=1)*(R2:R16852=-1)).
Would it help if I emailed you a part of the spreadsheet?
Thanks!

Nadia
Can you post part of it somewhere ?

Hui:
See download excerpt file at
https://www.Sendable.com/ReadMessage.aspx?id=638374848

Nadia, Either your link is wrong or Can you post it somewhere which doesn’t require registration?

Ugh…technology is not my friend lately…I thought the link would not be secured.
At the link I sent input:
user: nx_mykytczuk@laurentian.ca
pass: 2bcoff
it’s a temporary account I set up just to send the file.
You can retrieve the file: “sample output for Hui.xls”
Let me know if it doesn’t work and I’ll try something else.

Hi,
I have an issue regarding paste.i have a table with 4 rows and 9 columns.i want to change that in to a single row in order.is it possible or not?
Thank you

Fazil,
If you merge the cells you will only be left with the Upper Left value.

You can Concatenate the cells together eg: =Concatenate(A1, A2, A3…I4) and then copy and paste as values
or
Have a look at Jives Post at the towards the bottom of
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/