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 many variations to paste data to your excel sheets? Surprised? Well, read this post to become a master paster 🙂
After copying your data, You can activate Paste Special by
- Pressing right mouse button > Paste Special
- ALT + ES
- CTRL+ALT+V
The post is divided in to 2 parts,
- Basic Pasting Tricks
- Pasting while Manipulating Data
Basic Copy Pasting of Data thru Excel Paste Special
-
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 context menu.
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
-
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. -
Paste a linked image
If you want an image of your data, but live image (ie it should change if your data changes), then use the Paste Special > Linked picture option.
-
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 interested in knowing your favorite pasting hack. So tell me, what is your all time favorite paste?
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.
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.
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.
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.
For example, if you have in Row 1 – 
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.
252 Responses to “Excel Paste Special Tricks & How to”
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?
[...] Original post by Chandoo [...]
@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.
[...] in to five parts this needs to be split. There is a simple way to do this in excel, using “import text” option. But this is not always preferable, especially if you need to split text as part of a [...]
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.
[...] Paste like a Pro - 17 excel pasting tricks you should know | Pointy … (tags: excel) [...]
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.
[...] is not preserved or the file is too huge. Here is a simple hack that you may already know. Use paste special whenever you are pasting the charts / tables to PowerPoint / word or outlook and select [...]
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.
[...] Master Excel’s Paste Special - Learn these cool tips [...]
[...] and select one of the options shown (copy here as values only). You can do this and much using excel’s paste special features [...]
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
[...] our excel paste tricks post, I have mentioned a paste special feature called “skip blanks” that can apparently [...]
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!
[...] 16 more excel copy pasting tricks [...]
[...] Tip 55. To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e… Get Full Tip 56. To save data filter settings so that you can reuse them again, use custom views… Get Full Tip [...]
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!
@John B: Google spreadsheets is not 100% compatible with excel. that is why you are not able to preserve formats when pasting data. Here is a suggestion you may want to try: save the workbook and upload it to your google docs. Now open the document using google spreadsheets. This should preserve formats and cell shadings to a large extent.
I had problems with copy past from excel to google docs. Something don't allow me to make a graph on google docs. It's only works if I type all the data manually
Do you have any idea of what is happening and how to solved it ?
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!
@Ruben: Welcome to PHD.
If the cells are already blank, then you can copy the entire range and use paste special with skip blanks option to paste only values and leave the target cell values where source is blank. You dont need to select only cells with value. If you do so, pasting will put all the values in continuous cells....
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?
@Ruben.. are you able to resolve this. Sorry I have noticed it a little late... I am not sure ActiveCell.value would work. If you want to select a range of non-contagious cells and paste them as a contagious range, you might want to use some vba code to loop through or use the data filters to omit blanks and copy and paste the values.
@Alex.... you can use formulas to do this (if you want to only values). Otherwise you can write a simple macro to loop through input range and paste the values in single column in the output. Let me know if you are not able to implement 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.
@Alex... you can copy and paste sheets by right clicking on the sheet name and selecint move/copy option.
@Holly.. You can use a simple user-defined-function to get comments from input cell. Here is one I wrote that works for one cell at a time.
Function getComment(incell) As String
' aceepts a cell as input and returns its comments (if any) back as a string
getComment = incell.Comment.Text
End Function
@Krista, also, you can write a simple macro if you end up doing this a lot. Otherwise, just learn to type ALT+esv ALT+est very fast.
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.
@Bruce: Sounds strange.. what version of excel you are using? When you paste the text, excel should show an paste icon to let you make changes to the way it pasted the data. Otherwise you can always launch the text-to-columns tool once you have the data in clipboard.
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?
@Janet... I would check the following...
> is the font color set to White?
> Are you able to paste the word text anywhere else? (say in a notepad file?)
> Does the word content has too many page breaks or special characters (or even tabs) in the beginning? If so, the content might have been pasted, only somewhere in the bottom or right.
> Copy the word content, come to excel, press ALT+E then S. Now select "text" option. See what happens.
> Alternatively, paste word content in a notepad file, now copy that and paste it in excel.
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.
@Chadlar... excel 2003 file sizes are more than excel 2007. If the sheet has only data, I am sure the size of file should be reasonable.
[...] 15 more tips on pasting data in excel | Transpose Charts | More Quick Tips | More Excel Video Tutorials [...]
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
[...] Bonus: 15 more ways to use Excel’s paste special. Do you know all of them? tweetmeme_source = 'r1c1'; tweetmeme_style = [...]
[...] Excel Paste Special is one of my favorite features. So I was naturally thrilled when I discovered that you can use paste special to paste formatting from one chart to another. [...]
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.
[...] are a ton of cool paste features buried in the Paste Special Options in earlier versions of Excel. MS has bought all these to fore-front with Paste Previews feature in [...]
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?
@luckylooke... try this:
1. copy the value
2. now paste link (ALT+ESL)
3. now paste formats (ALT+EST)
@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
@Mark.. or CONCAT() UDF which can take a whole range of text values and then add them up, like =CONCAT(A1:C1)
You can find it here: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
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/
[...] 16 more tricks on how to use paste special [...]
Nevermind, got it sorted. THere was a problem with the dates I was copying. THe column needed to be hit with Data > Text to Columns before I copied it!
favorite paste: CTRL-semicolon pastes the date into you cell
CTRL-shift-semicolon pastes the time of day
Sir I have problem in Excel than I copy any sheet column wise and past in excel sheet but that is not proper past that's became in one column. What cammond I use after that my sheet proper past.
[...] 16 Paste Special Tips & Tricks [...]
This really isn't a "shortcut" per se - but one of the most powerful features of excel is the ability to paste a picture or bitmap of something on my monitor into excel that I can then manipulate using the available tools. In addition - excel makes it easy to re-scale for printing, whether I need wallet size cards or wall posters.
Pasting Borders
I'm on Excel 2003 and Ive searched in vein to find a way to paste borders only. I know there are styles and other approaches but sometimes you have the borders nicely formatted for sub totals etc and you just want to replicate it in the next column without changing number formats or fonts etc.
I also have the same problem. can someone help
R/Sir, I am working in a college as clerk. I want to convert PDF files into Excel Sheet. Please tell me about any software converts it.
I need to paste large amounts of text from other applications like Word, and from web pages into excel but want to paste the text into one cell. Instead it takes the lines of text from the applications I am copying from and puts each line in a separate row. This is very frustrating and I cannot find a way to avoid it. I have tried copying the text to word and text edit and removing the formatting, then pasting but the same thing happens. Can anyone help me please - this is urgent for a big job!! thanks
@Raman... You may want to search PDF to Excel on google. There are lots of tools that can do this.
@Karen: You can write a simple macro to copy the word content in to Excel cells. Visit our forums http://chandoo.org/forums and post a question. Someone can help you out.
Karen -
Copy the text, select the cell, press F2, which makes the cursor flash in the cell, and then paste the text.
thanks for the feedback everyone, feeling very stupid now as I found the way to do it and realise I already knew but had forgotten. So for anyone else, you need to copy the text, then on the spreadsheet, click in the cell you want to paste to, but paste where the cursor is flashing on the formula bar at the top of the sheet. Pressing F2, and copying into the cell did not work for me....I'm using Excel for Mac so not sure if that makes a difference.
[...] how your data is oriented? Not sure exactly how to phrase what you’re trying to do? Just Paste Special and check the Transpose box and your data will magically be transformed from horizontal to [...]
Quick question from a newbee. How do you pros copy and paste a column from a text pad to an excel sheet that has been sorted to paste only in the visible rows.
Sorry, not sorted...I meant filtered.
[...] is right. A simple paste special can remove the data validation rules for us quickly. Here is [...]
Chandoo, how to paste only number without words.
Eg. I copy 7.56 seconds, and i just want the number only not "seconds".
any idea? Really appreciate your help.
@sham,
You can paste the whole thing, then select column and click "Data/Text To Columns" on the Menu Bar, select the Delimited option (in Step 1), specify the space character as a delimiter (in Step 2), (in Step 3) select the second column in the table (click the header) and then select the "Do not import column (skip)" option, then click the Finish button. That should result in a column of numbers only when done.
@sham
If every cell within the column that you are copying contains a number and then the word 'seconds', then i would suggest the easisest way to do this is copy all the data into your target column and then highlight the column and replace (CTRL + H) the replace with box should appear and just enter 'seconds' within the 'find what' box and then leave the 'replace with' box blank. Click replace all and it should then remove all the wording of 'seconds' from all your highlighted cells leaving you with your required numbers.
Name Mane Sut
1 A AA
1 B BB
1 C CC
1 D DD
1 E EE
2 A1 AA1
2 B1 BB1
The above date should appear like this
how to work on this using excel
1 A AA b BB C DD E EE
2 A1 AA1 B1 BB1
@Rick Rothstein , @Chadlar
Thank you very much. Both tip work great! before this I delete it one by one. You save my time 1 hour work to 10 minute! Really appreciate your help.
What about copy and pasting to an external program outside of excel? For example, I use a plain text file to manipulate data. But even if it was rich text formatted textfile, what i want to do is copy a row or adjacent cells of values, and paste them into the text program. But the pasted text should be comma-delimited. The rich text format textfile will copy/paste all recognized formatting from excel 2007. Plain text file pastes the values as tab-separated, with no additioal formatting, which is closer to what I want. But I must re-highlight those pasted values in the plain textfile and do a "find/replace" operation where I replace the tab (spaces) with a "," (comma). It works, but I would prefer to get this in one swoop from Excel to the external textfile.
Is this possible?
I like to paste images into excel sheets for certain projects. Sometimes I click a cell, type ctrl+v OR right click>paste and it pastes the image on top of the selected cell. Sometimes I paste and the image appears in the upper left corner of the sheet. I'm getting sick of dragging images where I want them. Why is this inconsistent and how can I fix it?
Note: I do NOT want the image IN the cell. I want the image on top of the cell.
Great Tips! Very helpful.
Thank you
I love you.
I just figured out that if you have done a lot of conditional formatting in one sheet (like different colors for specific text strings, for example), you can paste >all< of the conditional formatting to another sheet by selecting any cell in the first sheet and doing the ALT+E S T trick in any cell in the other sheet.
Im a MAC User with excel 2011. I really would like to know the equivalents for the pasting shortcuts above, especially point # 13 pasting columns into rows.
Thanks!
Hi, I have a querry and apologise if the reason I can't find the answer here is because it is too obvious, but here goes. Is there any way to paste a column of data into a single cell which then pushes all of the data below that cell down the appropriate number cells so that nothing is deleted, I know I can do this manually before pasting but I am having to do this operation a large number of times with always differing numbers of rows of copied data.
Thanks
@Tom - once you have copied the data you want to paste in the columns, right click on the cell which you want to be the top cell. Choose "Insert Copied Cells". A pop up will appear where you can choose "Shift Cells Down". Hope this helps.
Is there a way to copy and paste a text string from cell A1 of sheet1 of a workbook to
cell A1 of sheet2 of the same workbook by writing a formula in cell A2 of sheet 1? This
seems like such a silly question but I want to shoot out the text string in cell A1 to many
different locations and not go through the tedious task of copy and pasting for each
occurance of transferring a text string from one location to another. In a way, this
would be somewhat analogous to conditional formatting only it would be "conditional
pasting". The formula would test whether a condition has been satisfied and if so, the
text string would be pasted into a single cell or a specified range of cells. I would greatly
appreciate any help that anyone could provide on this issue.
Don
Sometimes I needed to paste a multilne text, or a text with returns into one cell, but when I tried, excel copied it into many rows. Then after looking in google, I found a way to put multiline texts into one cell: Just double click the cell before you paste, this will bring the cursor, then paste and you'll have all the multiline text in a single cell. Magic!
Is there a way to copy consecutive rows in two columns, and paste them in different columns with rows spaced in between them? Or perhaps, cells from one column pasted in one column on a new sheet with rows between them?
ie.: copy cells A1,A2,A3,A4,A5 etc and paste in G1, G5, G9, G13, G17, G21 etc?
I have other data that I want to be able to place in the other added rows.
Hi! very curious! I just have made one of this tricks here:
http://runakay.blogspot.com/2012/01/separating-one-cell-text-in-columns.html
Couple Specific Paste Questions:
1) If I copy formatted text from a text box within excel (e.g Bold, blue color, Times New Roman font) and paste it into an empty, new text box with the intention of editing the text, the text will default to regular, Calibri font, black color. Is there a way to copy/paste from text box to text box and retain the text formatting?
2) Since for my work, many other people contributed by filling out forms. All have the same text boxes and general paper layout; however, they used different fonts, point size, colors, etc. Is there a way I can select all text boxes and make the font all one global setting? Can I have a separate global setting for text boxes as opposed to typical cells?
Thank you
hey there!...........is there way of copying text from a cell & pasting it as comment directly instead of using insert comment & then pasting.
Thanks!
@Rocky
Not without a small VBA Script like below
Sub Macro1()ActiveCell.AddComment ActiveCell.Text
End Sub
I use a free tool called Clipomatic by Mike Lin. It's a clipboard cache tool with the ability to setup a permanent clipset.
For Tip No. 15, can we have each letter/alphabet pasted in each cell? e.g. Alpha as A L P H A in 5 cells
but how i can choose a destination format while pasting using only keyboard? or any setting for default option of destination format
Very informative and helpful. Thanks
Ok I have one for you -
If I have references from sheet 2 to sheet 1 and I copy sheet 2 cells into a new workbork, how do I get it to reference sheet 1 in my new workbork? Instead it wants to reference sheet 1 in the workbook I pasted it from. HELP!
Thanks!
For some reson, in one particular workbook, when I copy & paste a formula with spaces in it the formula is pasted over multiple cells (breaking on the spaces).
How do I turn this off?
thanks v much
Hi everyone and Guruji,
Pls tell me what is the command to be used if I have copied one cell's data now I want to paste it in different cells. so now what i want is any cell i will click now the data should get pasted in that i.e. I click one cell the data should get paste then i click on any other cell the same data should get paste this is done by activating some command I dont know wat exactly is that. Pls dont tell me to select all the cells in which i have to paste the data using CTRL and then paste.
Thnx
Hi everyone and Guruji,
Pls tell me what is the command to be used if I have copied one cell's data now I want to paste it in different cells. so now what i want is any cell i will click now the data should get pasted in that i.e. I click one cell the data should get paste then i click on any other cell the same data should get paste this is done by activating some command I dont know wat exactly is that. Pls dont tell me to select all the cells in which i have to paste the data using CTRL and then paste.
Thanx
Dont completely follow and I'm sure you found your answer but you can select the cell and goto the bottom right corner and you will see a + once you see the cross sign double click it and it will automatically drag everything down to the last cell.
As an example
A B
1 3 =A1*2
2 6
3 9
If you double click B1 on the cross it will fill everything down. It also works when you have multiple columns selected. Hope it helps....
Nice website, I learned something new... BUT, you missed one of the most useful ones that I know of -
F4 Button
This repeats the last action you made as an example if you turn a cell to red and want to do it again and again hit F4. I wrote VBA /excel addons in C all professionally and it took me 4 years to find out about that one... Excel is powerful and microsoft should advertise all these things so even the novice user can use them. It's a shame they make you google to find things that make life so much easier.
Anyway, thanks for the site.
I just realized that I was the one that missed something 🙂 As that command is outside the scope of the article... But none the less hopefully it helps someone.
I'm attempting to cut-n-paste a web page into spreadsheet. The problem is I have device addresses -- like 02:95 -- being converted to a number value, 0.14931. Is there a way to prevent excel from converting this?
I do payroll for a large agency and frequently need to track hours/overtime/etc for a large number of employees and then type up a report for anything out of the ordinary.
I have spreadsheets with information like the number of regular hours worked and hourly rate, etc, and I have to include all of that information in a note section that might read something like this: "Employee worked (OT HRS) hours of overtime. Weighted pay = {(REG1 x PAY1) + (REG2 x PAY2) / (TOT REG) = (WP)."
Is there a way to insert excel values INTO the paragraph, so that they are linked to the cell with that information? So that as long as I have the items in parentheses entered in their proper cells, the description cell will automatically fill in (e.g. "Employee worked 2.5 hours of overtime. Weighted pay = {(17 x $14.80) + (25.5 x $9.50)} / 42.5 = $11.62.")?
Thanks
@Leah,
For the solution, I set up the data in columns A to E
REG1, PAY1, REG2, PAY2, OT HRS and this formula in column F
=IF(ISNUMBER(E3),CONCATENATE("Employee worked ",E3," hours of overtime. Weighted pay = {(",A3," x ",DOLLAR(B3,2),") + (",C3," x ",DOLLAR(D3,2),")} / ",A3+C3," = ",DOLLAR(((A3*B3)+(C3*D3))/(A3+C3),2),"."),"")
Is it possible to paste information in a sheet, so that the rows are automatically inserted, instead of overwriting the previous values
@Stefaan,
Select the ROW that you will insert above.
Right Click | Insert Copied Cells
Stefaan,
Select row and Ctrl + Shift + "+"
Ctrl + Alt + V then V
I have a column of numbers as follows
1
[blank]
[blank]
3
[blank]
4
[blank]
[blank]
Is there a way to copy the 1 into the blanks that follow until I get to the three, then copy the 3 below that (till I get to the next number), then copy and paste the 4 below? Currently I am copy and pasting each series below the next. My excel sheet is 8000 rows long.
Thanks!
@Jessica
I think Chandoo wrote a post about this a month or so ago, but I can't remember specifically
If your data is in Column A, put this in B2: =IF(A2<>"",A2,B1)
Copy down
Then copy Column B and paste Values over itself.
Delete Column A
Amazing!!!! Thank you so very much!
[...] http://chandoo.org/wp/2008/07/02/17-excel-paste-special-tricks/ Like this:LikeBe the first to like this. [...]
Hi there,
I am using the import text function in Excel to import a range of text converted from a PDF doc and am having an issue with the data being separated correctly into columns but then also rows. How do I force the import wizard to continue across with cells and not also rows?
Thank you
It might work for pdfs too
You do control-c and control-v, if you want to copy from a website into excel. Then you right click on the tiny clipboard next to the copied text and then click on Match Destination Formatting
I have copy/pasted from an HTML report into Excel. The result is some checkboxes in my Excel file that I cannot select, so I can't delete them, and deleting the rows they are in just moves them up to the next row. How can I remove these boxes?
i need to update my spreadsheets regularly but only want people to access a single worksheet from on a number of worksheets ........
how do i copy a single worksheet to another workbook via paste special ?
i have tried this but the paste special does not work and says the data is too large?
please help 🙂
Great tricks! Thank you very much!
I used to copy data from a Hyperion (Brio) results grid and paste it into excel. Excel used to understand the grid and fill in multiple columns of data. Now since going to win 7 and excel 2010 the paste treats the same data as a text string and plops it all into column A. the text to column tool is useless in this case due to no delimiter and variable length fields by row. any help is appreciated.
Is it possible to paste Spreadsheet XML into Excel?
I have a Flex program that can write the content of a DataGrid as Spreadsheet XML to a file.
Excel can open and display that file just fine.
But it would be so much more convenient if the user could just copy the XMLSS from the clipboard into Excel.
Is that possible?
how can i paste data from sites directly into excel sheet column wise without using paste button into excel file... is there any way by juct clicking one time n i can paste into excel automatically????? plz help me out as soon as possible.....
regards
shoai
@Shoaib
Your question is difficult to answer.
When the web started everyone used HTML tables to put data onto sites
Now people use all sorts of odd ways to put data onto web sites
The problem is that each web site can have different functionality to enable you to copy data or even restrict you from copying data
It can only be automated using VBA, but manually selecting blocks is normally the easiest
Try and select blocks in One column or One row
Don't select data across page breaks or table breaks
Some times just select the data and not the headers
Just try different ways and see what works in your instance
[...] Source: http://chandoo.org/wp/2008/07/02/17-excel-paste-special-tricks/ [...]
I need help. I've linked Excel data to Word doc. However, Word adds a return before and after the link so formatting is totally messed up. I've tried both Paste/Link options (keep source formatting and merge formatting) and they both have same problem. I need to retain the link as data in Excel will be frequently updated. I've scoured Microsoft and web and can't find answer. Can ne body help me?
Have you tried seeing the document in page break view and removing the line breaks? For this, just press CTRL+* in word.
i can fix the formatting in the word doc after the linking, but it doesnt retain the fix when updating the link. ive inserted quite a few links so this is very time-consuming for the end user. it defeats the time-saving purpose of the linked data. also, since i 1st posted i did a test in 2003 word & excel on a old pc & the problem did not replicate. so its a problem in 2010 only. unfortunately i cannot just create docs in 2003 as i tested compatibility with 2010 & doc did not retain link in compatibility mode. at this point, im giving up on linked docs & having end-user do manual input. but i am open 2 other suggestions 🙂 and i thank u 4 ur time!
Hi
I want to paste a block of text into an excel cell and only see the first line, unless I click on the cell. I've done this by accident, but have no idea how I did it
Thank you. This subtle tweak will make my life a happy place
After pasting the text, just adjust row height to show only one line. When you want to see full info, select the cell and press F2.
Hello,
I need to compare two set of data in an array using linear regression. Is it possible to do is directly with the array? If not, is ther a way I can paste each set of data into a single column? Thanks.
Hi Luo,
I am not sure what you mean by compare using linear regression. Can you elaborate?
Hey Chandoo,
Love the site; I use it all the time. Saved my butt countless times while sitting in the office google searching solutions.
One of THE most useful tools/tricks I have ever used was with copy and not so much paste. After selecting a range of cells, press ctrl+c then hit F5 the click "special". I use the "visible cells only" ALL the time to be able to copy only needed cells and paste to where I need. Used this countless times to grab only the rows, columns, I need by hiding the rest.
Hope this helps some people out!
Sorry if this question has been asked and answered already. I want to copy from excel into word several rows and columns. Some of the cells have comments in them or highlighted text or different colored text. i want the version pasted into word to not have the different colored text (change everything to black), no highllighting, and no comment boxes. is there a way to do that? thanks
followup to my question...i want to paste as a picture into word...thanks
I am creating a macro for a monthly report. I do several different filters then paste the results on to another sheet. The problem I am running into is that the number of results vary from month to month. I need to know how to tell the macro how to paste the results in the next available row, not, for example, A20. Please keep in mind I know next to nothing about VBA. Thanks.
@Matt
Can you upload a sample of what you have done so far?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Hui...
http://db.tt/Y3ckoSrC - Template with Macro
http://db.tt/RvOnevw9 - Old one I did manually
What I want to happen is take the data on sheet 1 from the old one, copy it into the template, and run the macro to create the report. The data will always be formatted the same way but the number of filter results will never be the same. Any help would be awesome. Please let me know if you have any questions.
@Matt
Can you describe your work flow
What is the source sheet?
What are each filter based on ? Field and values
I think you simply need to use a Named Formula as the source data rather than a fixed range
The Named Range will be smart enough to adjust to the changing data
Thanks I think I figured that one out. I have another question that is somewhat similar... When I filter data from a particular column, I then take those results, select all the data (multiple columns and rows), and copy them over to another sheet. What do I need to tell macro so it selects all the filtered data but does not include column headings? It needs to be able to work no matter what amount of data results from the filter. Thanks.
I have data in excel need to copy and paste in a webpage that have a boxes to fill in the data from excel. How to paste it?
Paste Special with skip blank is not working. Please help.
i want to know how to convert data in word to matrix format...example
coloum a coloum b
name joe b
place uk
prof business
[…] is not preserved or the file is too huge. Here is a simple hack that you may already know. Usepaste special whenever you are pasting the charts / tables to PowerPoint / word or outlook and select […]
Huge thanks for especially nr 15.
15.Paste text by converting it in to columns
Is nice, but can it be done within Excel?
For example, csv's wil often open in Excel.
In WinXP I used to be able to copy multiple rows from MS SQL in 'Results to Grid' mode that had cells with imbedded paragraph marks (i.e. ¶) and Excel would ignore the ¶ and paste the result from one SQL result cell into one Excel cell and if, I copy 100 such cells in a column from SQL the paste to Excel would result in 100 cells with imbedded ¶ in a column but, with Win7 for every ¶ in the source cell when, I paste to Excel I will get multple cells in Excel. Can the clipboard in Excel be formatted to ignore the ¶?
Awesome work! I will treasure this for my PC Excel work - but...what are the Mac equivalents? I need to paste in a '1' to every row but this left me none the wiser! Anything around for Mac peeps?
Thanks,
John L
Hello there! I know this is kind of off topic but I was wondering which blog platform are you using for this website?
I'm getting tired of WordPress because I've had problems with hackers and I'm looking at alternatives for another platform.
I would be fantastic if you could point me in the direction of a good platform.
[…] When copying data from another source using Paste Special – Values will leave all the unwanted formatting, formulas, etc behind and keep your data nice and clean. Here are loads more things you can do with copy & paste tricks like this. […]
Hi expert , i have a question suppose i want to paste data in excel sheet which is filtered . after first row then 5 row reflecting then 15th then 25th row and so on , if i want to paste data on that fillterd sheet most of the data gets hide , is there any possiblities that we paste the data on, only on the visible cells ,,????
When you copy and paste in Excel, if you do anything at all between doing the copy and paste, Excel loses record of what you copied. Is there some way to get it to always remember what you copied?
Is there a way to copy a name and paste it into two separate cells.
I would copy Robert Walters and be able to paste into two cells(columns) so I can have a column for first name , last name ?
@Robert
Only In Excel 2013 onwards if you have a Table with names like Robert Walters in A2 with other names below that
In B2 if you type Robert Excel will split out the First Names for you
In C2 if you type Walters Excel will split out the Surnames for you
This is Called Flash Fill
Refer: http://chandoo.org/wp/2013/04/03/best-new-features-in-excel-2013/
or for a great examples including your issue:
http://www.tech-recipes.com/rx/48997/a-beginners-guide-to-flash-fill-in-
Thanks so much and I do have 13...
@Robert
If it is disabled, simply goto
File, Options, Advanced
Tick Enable Autocomplete
Tick Enable Flash Fill
I'm so sad becouse some of this tricks are a little diferent in PT-BR, but thanks you anyway.
Hello,
We would greatly appreciate your help -- even our techies at the univerity where I teach could not answer these two as we worked with recording our students' final info:
1.
How do we paste multiple, adjacent, text-filled columns in between two other text-filled columns?
E.G.
A B C D E F G H I J K L M N O P Q
1
2
3
4
5
6
7
8
9
How do I copy everything in columns B C D E F G H I J K
And paste it in 10 new columns between columns N and O?
We know how to do it the long way -- count the number of columns desiring to duplicate -- 10 in this case.
Then, "Insert column" -- 10 times -- between the two columns where we want the duplicate info to go -- between N and O in this case.
Then, "Paste Special - Keep Column Widths"
Then, "Paste"
Is there no way to accomplish this with only a couple of commands?
2.
How do we paste several rows of text into another Excel document -- but paste by skipping two rows in between?
E.G.
A B C
1 dog cat rat
2 hat bat fat
3 Jil Joe Bll
Copy rows 1-3 above.
Paste in as below, skipping two rows in between each -- so the above info falls only in rows 1,4,7
A B C
1 dog cat rat
2 How are you?
3 I am just fine
4 hat bat fat
5 What's up Friday?
6 I have no idea.
7 Jil Joe Bll
8
9
Thanks so much,
Lee Harrington
713-523-9000 cell and text
Hi,
Is there a way to copy several columns and paste them with an extra column between each?
E.g. Jan Feb Mar Apr May June ---> Jan Jan1 Feb Feb1 Mar Mar1 Apr Apr1 May May1 June June1 (each with columns full of data, but no data in the Jan1 etc. columns)
@Aine
This can't be done directly
It could be done using some VBA
Is there a way to specify the format of a cell in pasted text.
For example, I am pasting the current row of tab delimited text
12(tab)121,151,131(tab)Bingo
So:
12 in the first column,
121,151,131 in the second column
Bingo in the third column
The issue is the value 121,151,131 is a list not a value, and excel converts the list into a number 121151131. Is there any way to set the format of a cell in the pasted text on the clipboard without the user having to change the format of the cell themselves?
Thanks in advance
Hi,
I want to copy data from one col(col A) to other col (col B) only with data in Col A but not blanks. Desination col has values as well. I don’t want to disturb other values in destination col (Col B), except for the values in col A.
Thanks in Advance
needed this badly
First, I truly apologize if this question has been asked already. This is along, but very, very useful topic! Thanks for this post!!
Here is my question: I have over 900 lines of text data listed in an excel column. Each line is on a separate row and does not have the same characters in the cell/row. I need to paste the same ending text to each line. Is there a way to do this without having to place my cursor at the end of the text in each cell and hit paste? I am trying to avoid having to paste individually 900+ times.
EX
@Stacey
Can you post the question in the Chandoo.org Forums
http://forum.chandoo.org/
please attach a sample file
Dear Sir,
I am using Advanced filter and everything is working well.... However what I can't figure out is how to make the advanced filter I have setup search for a certain word or letters within text.
For Example: I type the word (butter) in the Criteria range, and I want to search for any transaction that conatins this word, However currently it returns no value, Unless I type the exact search as it is listed in the data list precisely...
How Can I overcome this Problem and be able to enter a word or any letters and return results that contain any of this using Advanced filter.
Note: I know how to do this using Regular filter and drop down, but I want to be able to do it with advanced filter. Please anyone can tell me simply how to do it?
@Mark
Use a filter like: *butter*
or ="=*butter*"
using the same advanced filter, I would like to be able to search for items inbetween two dates or greater than one date? I would like to know what command or formula should I use in the date Criteria to be able to do the above, ofcourse again I don't want to use regular filter.. Many thanks again
@Mark
You can add multiple lines to a Advanced Filter criteria area
eg:
Date
>=1/1/2016
<1/2/2016
Dear Hui,
I tried your advice, however the problem is that xl won't recognize any command with > or > for date, it works for other criterian but it doesn't work for date. I tried (>=1/1/2016 or <1/2/2016) as you recommended, but when used in criterian as above it returns no results at all, I am using xl 2007. what could be the reason?
Try
Date Date
=">=1/1/2015" ="<1/2/2015"
In adjacent cells
or
=">="&E1 etc
where the date is in E1
Dear Friend,
Unfortunitly all 3 alternatives didn't work, only when I put a plain date it give the results fo that date, however all those formulas didn't work.
@Mark
Can you email the file to me?
Okay Sir, will do it, many thanks and I hope we can resolve it...
Regards
Mark
@Mark
I have responded to your email already
Hui...
Dear Hui,
yes I got the email, many thanks for your extremly fast replys it is well appreciated, I am waiting for the Dates resolve, I formated the dates to USA dates only but it didn't work either.
again many thanks and waiting your date resolve
hello, I have this vba code I made
Sub INVENTORY2()
Range("C4:AN4").Select
Selection.COPY
Range(ActiveCell).Select
End Sub
Anyone can tell me please how to complete this code to be able to paste the value of the selected celss into the cell I have selected before running the macro, in other words I want to select the cells I want to paste into before I run the macro, and the macro will paste only values and only into the previousely selected cells before running the macro.
Many thanks
@Mark
Try:
Sub INVENTORY2()
Range("C4:AN4").Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
Select an area
then run this
C4:AN4 will be copied in as values
Many thanks Hui, this worked great, and your fast reply is greater...
Anythings happened with the date issue we tried to resolve last week?
Hello guys, anyone can help?
I am trying to have excel say "just great" if the value in cell b6 in sheet1 is greater than 0 after I am done with enterning my data in the worksheet.
I wrote those two codes but no success and nothing happens, anyone can help?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$b$6" Then
Call macro100
End If
End Sub
Sub macro100()
If Sheets("sheet1").Range("B6") > "0" Then
Application.Speech.Speak "JUST GREAT"
End If
End Sub
many thanks in advance
hi, i have a q ,whether" ALT+E S T"="ALT+E+S+T" or not?
I use paste special a lot for converting formulas to the actual values. Instead of using ALT+E+S, I use the "right click" button on my keyboard. That saves one keystroke. So it's like this
Copy your cell
hold down the "right click" button which brings up the right click menu
press S
press V
Easy Peasy!
When I set up a worksheet and put the titles of the columns at the top of each column, how can I copy all those titles across the top, and paste them into the next 11 months of sheets? It always tells me it cant do that across multiple cells. It seems the ability to copy titles across the top of the cell columns would be a pretty straight forward need....I have yet to figure this out. I am a newbie, living in a wilderness area still have to keep books and have no idea how this works. Thanks
have a spreadsheet w/ multiple tabs.
TAB#1, the first tab, contains static data;
TAB#2 (thru X) has columns/cells that either contain formulas which link to the static data found in TAB#1 and or are blank and need to have data entered; the columns/cells with the formulas & the columns/cells needing to be filled in intermixed;
have CSV files that are laid out in accordance w/ the columns in TAB#2 (thru X).... for the columns that contain formulas, the spot in the CSV file is null; the spot in the CSV that corresponds to the column needing the data has values pulled from other sources;
e.g. TAB#2 column A is a formula linking to TAB#1 data, column B needs data, column C is a formula linking to TAB#1 data, column D needs data;
a sample CSV record for TAB#2 is ",ABC,,123"
i can open the CSV using Excel & get the data in... column A is blank, column B contains "ABC", column C is blank & column D contains "123";
but when i copy/"paste-special ... values only" the data from the CSV to the defined layout, it is wiping out the formulas in the columns ...A & C in my example .... even though the CSV filled data is blank for those columns.
any ideas?
I like reading an article that can make people think.
Also, thank you for allowing me to comment!
Well I like to use the following combination for paste special.
ctrl+alt+v & desired option for paste special 🙂
Best Regards,
Its really interesting and worth learning. Learnt some new things in formating a worksheet. Love the website.
thanks,
All your blogs that I have read are XLlent, including this one.
Why did you leave India?
hello!,I really like your writing very much! share we communicate extra about your article on AOL?
I require a specialist on this space to resolve my problem.
May be that's you! Having a look ahead to peer you.
hello,
pls guide -how to paste value of copied data on the same place(cells) while it was copied from filltered data, without effecting the formula in hidden cell (row) in excel
Valuable information. Fortunate me I discovered your web site by chance, and I am stunned why
this coincidence did not took place earlier!
I bookmarked it.
Hi Chandoo, when using paste special transpose, it does not paste the images in the copied cells! (Excel 2007). Is there a way to do this please? Paste images with their parent cells is already ticked in options. I have a feeling it might need a macro?
Hi fellow kiwi 🙂
I think you do need a macro as the images do not belong to cells, but they just hang in a separate layer.
hello i often paste columns of numbers that have many spaces inbetween i copy and paste special the formulas and do 1000 cells at a time. i dont want the zeros to show up where there is no information. How do i do that
formula cell1+cell2-sum(cells3+4)
formula cell1+cell2-sum(cells3+4)
(nothing)
formula cell1+cell2-sum(cells3+4)
do you undersatnd
how can you return directly to marked cells after pasting a copy cell?
Hi Chandoo,
I have a problem that lots of people may have.
I have set up an excel workbook with a master sheet as the first tab.
It has a logo and then some format boxes and the like.
I them right mouse click on the master tab and "move/copy" and I copy the sheet.
This sheet generates without the logo that has been placed on the master sheet.
How do I fix this, so that the picture comes with the sheet and I am not having to copy and paste this independently.
Also I have tried to embed the item, import the item, add it as a vector.
Do I have to create a form to have this work?
Please if you have any input or an out that would be great.
thanks
danni
Dear Mr Chandoo,
whenever i copy a selection from one excel sheet to another, where i need all details & formats, it tends to open a promt for paste special and ALL FREEZES, can you please advise
regards
I have data in Excel Table form from rows 1 to 1000, which I copy and paste from another tab. Next month I may have data of 800 rows only, instead of 1000. So my question: Is there any way to prompt me that I should be deleting those extra rows that are from last month? I often do mistake like that and the result is wrong.
I want to take my cell that has number of holiday hours remaining for fiscal year (i.e., 4000) and I want to copy this number each time it is manually changed to another cell. The issue is I do not want it to post the number hours remaining as hours, but rather as minutes
For example 40 hours = 2400 minutes.
The next time I reduce the hours down to 32 hours the other cell should now change to 1920 minuts.
Thank you for your assistance,
Shelly
Hi there, I have this code to paste an image from the clipboard to the active cell as comment. I would like to improve it, by checking if what you have in the clipboard is an image and make it as add-on, so it can be available everytime I open my Excel 365.
Here is the code:
Option Explicit
Sub PictureExport()
Dim TempChart As String, Picture2Export As String
Dim PicWidth As Long, PicHeight As Long
Dim ActSheet As String
ActSheet = ActiveSheet.Name
ActiveSheet.Paste
Selection.Name = "PrintScreen"
Picture2Export = Selection.Name
'Store the picture's height and width in a variable
With Selection
PicHeight = .ShapeRange.Height
PicWidth = .ShapeRange.Width
End With
'Add a temporary chart in sheet1
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActSheet
Selection.Border.LineStyle = 0
TempChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)
With ActiveSheet
'Change the dimensions of the chart to suit your need
With .Shapes(TempChart)
.Width = PicWidth
.Height = PicHeight
End With
'Copy the picture
.Shapes(Picture2Export).Copy
'Paste the picture in the chart
With ActiveChart
.ChartArea.Select
.Paste
End With
'Finally export the chart
.ChartObjects(1).Chart.Export Filename:=ThisWorkbook.Path & "\tmp.jpg", FilterName:="jpg"
.Shapes(TempChart).Cut
End With
ActiveCell.AddComment
ActiveCell.Comment.Shape.Fill.UserPicture ThisWorkbook.Path & "\tmp.jpg"
ActiveCell.Comment.Shape.Width = PicWidth
ActiveCell.Comment.Shape.Height = PicHeight
ActiveSheet.Shapes("PrintScreen").Delete
Kill ThisWorkbook.Path & "\tmp.jpg"
Application.ScreenUpdating = True
End Sub
Thanks in advance for your help.
KR
Andrés López
Is there a shortcut to paste data into excel when there is no space?
For example if I want to paste 5 rows of data when there is only 1 row available. I know I can manually add rows to make space. But I want to know if there is a shortcut to auto fit the pasted rows into the space where I want to paste? Ie automatically add in more rows depending on how many rows I'm pasting?
Is there a way to only paste the format of a cell only into cells with a specific value? The value of the original cell is not a concern. Using a macro builder I got this to first copy rows of formatting, then columns, but I only want the column format to copy into the cells equal to 1. Loops take enough time to be a problem. Is there a way to limit copying in the 2nd half to only cells with a value of 1?
Range("A2:A15").Select
Selection.Copy
Range("B2:R15").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("B1:R1").Select
Selection.Copy
Range("B2:R15").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False