Multi-range selection copy & paste

Share Button

New in Go-OO Calc is the ability to copy and paste multi-range selection, that is, you can select separate cell ranges that are not connected with each other, and copy and paste them in one single action. This was not possible previously; when you tried to copy multiple ranges, you would get an error message telling you that copying of multi-range selection is not supported (or something to that effect).

When pasting a multi-range selection, all the copied ranges get consolidated into a single range when pasted into a destination location. For example, copying this multi-range selection

and pasting it into another sheet will paste the data as follows

You can’t just copy a random set of ranges of varying sizes, however. Because the data gets pasted as a single range, all copied ranges must have either equal column size, or equal row size, or else you’ll be greeted with an error message.

About Kohei Yoshida

LibreOffice hacker, spreadsheet nerd, and machine learning beginner.
This entry was posted in and tagged , , . Bookmark the permalink.

15 Responses to Multi-range selection copy & paste

  1. James Suvian says:

    Wow, that’s what I wished for! Kudos Kohei! Keep up your excellent work.

  2. Silvio says:

    Hello kohei,

    I must confess I do appreciate this new feature :-)

    I have one really dumb question (sorry about that).
    In short, I would like to know if you can copy and paste columns (not only separate rows as you explained).
    Reading carefully your message I do suppose the answer is yes but, at present, I don’t have Go-OO to verify this.

    In addition, I would like to know as well when can we expect to have this new feature in OOo.
    Reading this blog I discovered this decision is not completely up to you.
    Maybe we can hope for the 3.2 release (in late September)?

    Untortunately, at work, I can only install OpenOffice (windows version) and not Go-OO because I don’t have any privilege as administrator.
    As a result, I must use a version of OOo portable, e.g. http://portableapps.com/apps/office/openoffice_portable or the more updated version (3.1) at http://www.winpenpack.com/main/download.php?list.37

    In conclusion, it is really rewarding to see OpenOffice Calc becoming every day more powerful.
    In my honest opinion Microsoft Excel is still a bit better. For instance, regarding this new option for Calc, with Microsoft Excel 2000 (a really old version…), you could copy more separate and random cells (beside rows and columns of course) and paste them wherever you like.

    I do hope Oracle will put some money (and developers) into OpenOffice Calc, even though I am not so confident this will be possible…
    I am saying this because the gap between Calc and Excel is more and more negligeable.
    Nonetheless, when someone has used Microsoft Excel for years there are many features he can’t find in Calc yet.
    Needless to say, these options are not really important for an “average user” but they can do the difference when you use a datasheet every day (in terms of time and results).

    Best regards and THANKS A LOT for your work :-)

    Silvio

  3. Silvio says:

    Hello Kohei,

    I would like to make a precision about my previous mail.

    Actually, with Microsoft Excel 2000 is not possible to copy and paste some cells when they are SEPARATE and random (I even tried with Microsoft 2007 and it seems not possible as well).
    Today, at work, I verified this (at home I don’t have Microsoft Excel because I use OpenOffice 3.1).

    I am really sorry for this misurunderstating of mine :-(
    I don’t know why I thought it was possible…

    Best regards and thanks a lot for your work with Calc

    Silvio

  4. Kohei Yoshida says:

    Silvio,

    Upstreaming, unfortunately, is not entirely within my control. There are many other factors that influence how long it takes to integrate a feature from Go-OO to the upstream Sun version. The very time consuming upstream QA process has recently been a large obstacle in upstreaming any patches from Go-OO. Though I *do* know that the QA guys work as hard as they possibly can, the amount of patches that need to go upstream is much, much more than the current process can handle in a timely fashion. Given that, upstreaming this feature for 3.2 may or may not be feasible. But I will try. There are many other, and much older patches that I’d like to upstream before I do this one, so don’t be surprised if I can’t make it for 3.2.

    BTW, I have researched how Excel handles this same functionality, and tried to emulate that in Calc. So my implementation of this feature should not be any more constraining than how Excel does it. If you find anything that we do not allow but Excel does, please let me know.

    >I do hope Oracle will put some money (and developers) into OpenOffice Calc, even though I am not so confident this will be possible…

    What I do hope Oracle will do is *not* necessarily to put even more developers into OOo but simply bring the project back to community’s hands, without the total ownership clause, and without the total control of the project by one single commercial entity. Time will only tell whether Oracle will actually do that…

  5. Silvio says:

    Hello kohei,

    I have been playing a bit with the Go-oo version installed on my computer with Windows xp home edition (sp 2)
    The Go-oo build for windows I have downloaded is 9399 of 2009-05-27.

    1) I have tried the new multi range copy-paste option and it works fine for Cells, rows, columns.
    One “bug”, maybe worth mentioning (sorry to call it like that), occurs when you copy, for instance, 3 cells.
    When you try to paste them in Calc in a NEW data-sheet everything works fine (all of them are pasted correctly)
    On the contrary, when you try to paste them in Microsoft Excel 2007 only the first cell is pasted (the other 2 are “lost”).
    Currently, I know there are many problems of compatibility between the 2 softwares….

    2) I have tried other new options in Calc like the one allowing to colour the tab regarding the sheet and it works fine for me.

    At present, the option to click with the right button of the mouse on the arrows (the ones to move forward and backward among the sheets) is still absent.
    With Excel 2000 and subsequent versions you can right-click on the arrows and select the sheet you want to go.
    This is really helpful when you have plenty of sheets because you are not forced to go up and down with the arrows in the sheets in order to select the one you wish to work.
    This last scenario is really time-consuming with dozens of sheets…

    3) All in all, I must confess Go-Oo is really nice. Especially Calc is more powerful: thanks, in particular, to you ;-)

    In my opinion, the only option Calc needs is the one which in Excel, from 2000 version onwards, allows to compare two or more sheet sheets, belonging to DIFFERENT data-sheet putting them aside (e.g. vertically or horizontally).
    At work, in my laboratory, we use a lot Excel with this option This and other minor options “force” us to stick with Excel…
    Unfortunately, I suppose this feature is probably a bit hard to implement :-)

    4) In conclusion, I would like to ask where can I find the list with all the other new options you added in Go-oo for Calc in order to test them?
    I discovered many of them reading your blog but, maybe, there are other features you didn’t list here.
    I didn’t find this list in the Go-oo web-page but, probably, I am not looking for correctly

    Thanks a lot for your hard work

    Silvio

  6. Kohei Yoshida says:

    Silvio,

    Can you give me more details of the stuff about “comparing two sheets”? Are you talking about this?

    http://www.openoffice.org/issues/show_bug.cgi?id=66052

    I’ve made note of the context menu for sheet switching. I’ll see how much work it would be to get that implemented. I can’t make promises, but, it seems like a nice enhancement & should not be too difficult to implement it.

    >I would like to ask where can I find the list with all the other new options you added in Go-oo for Calc in order to test them?

    Well, we don’t really have a user-readable list of enhancements in Go-OO. We have a list of patches that we apply to Go-OO, but that’s mainly for geeks. This page

    http://go-oo.org/discover/

    is supposed to be the page to show all the differences, but it’s severely outdated and it’s not comprehensive. The closest one we have right now is this:

    http://lists.go-oo.org/pipermail/dev-go-oo.org/2009-June/001428.html

    That one is probably the most detailed list available right now, though even that doesn’t list all the differences because I just don’t remember them all. ;-) Hopefully we can do a better job at providing a better list of differences in the future.

  7. Silvio says:

    Hello kohei,

    Kohei wrote:
    >Can you give me more details of the stuff about “comparing >two sheets”? Are you talking about this?
    >http://www.openoffice.org/issues/show_bug.cgi?id=66052

    That’is it!
    Actually this feature was already present in Microsoft Excel 2000 (your link refers to Microsoft Excel 2003).
    This is an option we use all the time in our laboratory when we work with different data-sheets (to compare data)

    At present with Calc (or Writer) you have to resize the windows with your mouse and put the data-sheets aside.
    In short, you click with the right button of the mouse on the icon of Calc (on the very upper left) and you selecet the option resize; afterwards, you reduce or increase with your mouse the two or more windows regarding the different data-sheets (dragging them).
    Obviously this is really a time-consuming task…
    Compared to Excel is a bit ridicoulus…

    Reading the link you provided I totally agree it would be great to have this option in writer as well :-)
    But, as I supposed, it is not so easy to implement…

    THANKS a lot for all the links you suggested me :-)

    All the best

    Silvio

  8. Silvio says:

    Hello Kohei,

    I would like to precise a little more my previous statement where I wrote:

    >At present with Calc (or Writer) you have to resize the windows with your mouse and put the data-sheets aside.
    >In short, you click with the right button of the mouse on the icon of Calc (on the very upper left) and you selecet the option resize; afterwards, you reduce or increase with your mouse the two or more windows regarding the different data-sheets (dragging them).

    Actually, this is the “dummy” way of resizing two or more windows with different data-sheets (e.g. Calc1; Calc2 etc).

    The “cool” method, on Windows 2000, Xp or Vista (I don’t know if it works on Linux as well) is another one and it is faster:
    In a nutshell, you should:
    1) Open a new-data sheet (or a writer document)
    2) Open a second data-sheet etc by clicking on Start, Programs, Calc (not from inside Calc). The different data-sheet are showed side by side at the bottom of your display.
    3) Keep the button CTR pressed on your keyboard and, at the same time, with the RIGHT button of your mouse, click on the tab of the second data-sheet (e.g Calc2.ods). This must be the last one on the right (at the bottom of your display).
    4) A new windows pops up. As a result, you can choose among many options to put, side by side, your documents in order to compare them.

    I hope this trick is handy for someone reading this blog: I am sure Kohei already knows it ;-)

    Best regards,

    Silvio

  9. Silvio says:

    Hello Kohei,

    I promise this is my last post :-)

    I would like to correcty one of my previous messages where I wrote that in Calc is not possible to select a sheet fast (when you have created plenty of them).

    More precisely I wrote:
    > At present, the option to click with the right button of the mouse on the arrows (the ones to move forward and backward among the sheets) is still absent.
    >With Excel 2000 and subsequent versions you can right-click on the arrows and select the sheet you want to go.

    In fact, with Calc, the method is present but it is a bit different.
    In short, you have to:
    1) press F5 on your keyboard (a new window pops up);
    2) click on the + to show all sheets (obviously you do this when you have plenty of sheets and not only the usual 3);
    3) Finally, you select the sheet you want to go by double-clicking on it.

    That’s it! Pretty much like Excel….

    All in all Calc is really awesome :-)

    You guys at Novel and Sun rock :-)

    Best regards,

    Silvio

  10. Kohei Yoshida says:

    Hi Silvio,

    I’m glad you found an alternative solution for your need. Just to be clear, it’s okay to make a criticism of Calc against Excel as long as you don’t use a tone of voice that’s too harsh, offending etc which you didn’t. We always look for ways to improve Calc’s ergonomics, so constructive criticism is always welcome.

  11. Silvio says:

    Hello Kohei,

    >Just to be clear, it’s okay to make a criticism of Calc against Excel as long as you don’t use a tone of voice that’s too harsh, offending etc.

    I am really sorry if I have offended you :-(

    Actually, it is a bit strange because at the end of my last message I wrote:

    >All in all Calc is really awesome :-)
    >You guys at Novel and Sun rock :-)

    I compared Calc with Excel: they are both great softwares in my view…
    In my opinion, Calc is really a nice software (“awesome” means also great in slang).
    I do think Calc is really an impressive software (thanks, in particular, to your work with the Go-oo edition).
    As I wrote, I do think you guys at Novell and Sun (now Oracle) are really good…

    In conclusion, I am really sorry and disappointed for every misunderstanding related to my previous messages.

    Plese, accept my sincere apologies…

    Best regards,

    Silvio

  12. Kohei Yoshida says:

    Silvio, You completely mis-read my last comment. :-)

    I didn’t mean to say you offended me because you didn’t. :-) I meant to say that you didn’t use an offending tone of voice, which is a very good thing, and something I appreciate. I hope I made it clear this time.

    Sorry about the confusion, and I really appreciate your feedback you’ve given me so far. :-)

    Kohei

    P.S. I’ve edited my last comment to make my point clear.

  13. Silvio says:

    Hello Kohei,

    > Sorry about the confusion, and I really appreciate your feedback you’ve given me so far. :-)

    Well, I am really happy about that :-)
    Sorry again for the misunderstanding (my English is not so good…)

    In the past days, I have asked some of my colleagues to try to work with Calc instead of Excel 2003.
    They are all physicists and know really well Excel (some of them even write short macros in visual basic).

    The “criticism” they pointed out are really trivial.
    They tried to use the last Go-oo edition (N. 9399 of 2009-05-27).
    For instance, they liked a lot your new option to copy and past cells, rows and columns which are not close together (this is really a must for us).

    Befor going on, it is important to explain that they are used to work with Excel since a long time ago. As a result, they are a bit “biased” :-)

    In short, the result of this “survery” with theirs “remarks” are:

    1. The option to delete cells (or whatever) is a bit “time-consuming”.
    At present, for deleting some cells, you press the button Canc on your keyboard and you have to confirm a second time to delete.
    In most cases, you have to do TWO actions instead of one (and this many times every day…).
    Obviously, the space button on the keyboard to delete the cells is what one is looking for because it is faster.
    You could even change Calc Options by going to the Menu: personalize, shortcut etc.
    This being said, I checked the gnumeric software and it behaves like Excel, that is, the button Calc is used as the space button of Calc.
    I suppose even Koffice (with Kchart) has the same option as Excel and Gnumeric.
    I know this is really a “controversial” topic. It has been discussed many times on forums and mailing-lists…
    Nevertheless, it is important to remark that in Italy there are really few books in Italian about Open-office.
    None of them is updated to Ooo 3 or 3.1 (you have to buy some books in English on the web).
    Unfortunately, there are even less books available for Calc.
    As a result, it is often difficult to find a good documentation to study and discover all the tricks :-)

    2. Maybe we are wrong but it seems to us with Calc (and OOo as well) you are forced to use the shortcuts already present in the Menu: “personalise”, “shortcuts”.
    It is true you have plenty of options, but with Excel you can choose whatever shortcut you like (if they are available, that is, they are not already used for other tasks).
    On top of that, with Excel you can choose your personal short-cut BEFORE registering a macro.
    With Calc, if I am not wrong, you have to select your short-cut in a second time and in a different menu (Personalise, shortcuts).
    This is not always easy to understand and apply.

    3. It would be great to have a short-cut to paste a cell under another one: with Excel there is the option Ctr + ?
    More precisely, with Excel, when you want to copy the Cell A1 into the Cell A2 you move the cursor of your mouse in A2 and you simply press Ctr + ?
    We know you can press Ctr + C (copy) and Ctr + V (or dragging the Cell A1 in A2) but short-cuts are always faster :-)
    Maybe this shortcut already exist in Calc but we didn’t find it in our books or PDF documentation.
    We know you can register a macro and assign a shortcut to it but…
    For example, at work we have more than 50 computers with Excel installed and it would be a bit long to do this for every computer for every short-cut :-(
    Furthermore, with Calc, at present, every three months or so, you are supposed to install a new version and your personal short-cut are lost…

    4. It would be great to have a short-cut to select a certain amount of cells (NOT everything, that is, the option you have pressing Ctr + A).
    For instance, with Excel you press Ctr + Shift + * and you select them (e.g. The cells between A1:D10).
    With Calc the fast way to do this is to press Shift, buttom arrow (to select the first column with active cells) and the right arrow to select everything (all columns with active cells on the right).
    Maybe this shortcut already exist in Calc but we didn’t find it in our books or PDF documentation.
    In order to do this, I know you can register a macro and assign a shortcut to it but…

    5.We think it is important to have some lines (a rectangle in short) around the cells you decide to copy.
    At present, with Calc, in OpenOffice 3.1, they are not visible.
    This is important in order to take a look at what you have just copied.
    Imagine you copy something, suddenly, a colleague ask you something.
    When you return to Calc and you want to paste your previous selection you are not 100% sure if you had copied what you wanted before pasting it (nothing is visible)…
    In short, I am talking about the blue lines you introduced in your last improvement (for copying and pasting).
    With Excel or Gnumeric, for example, when you copy something the lines are always present and they are “flashing” all the time.

    6. We really like the extension: data-form. The link is: http://extensions.services.openoffice.org/project/DataForm
    With Excel we use this option all the time.
    In my view, it is really helpful because it “transforms” a bit Calc in a sort of database (I know you should use SQL for this but bear with me…).
    I know you can add this data-form, thanks to the above-mentioned extension which, on top of that, is even open-source.
    Nevertheless, it is really a pity not having this feature by default.
    With extensions you have to know that they do exist (and this, for most users, is not always the case…).
    Besides, when you have many computers, you have to install them one by one and this is really time-consuming (suppose, for instance, you have two netbooks at work and one desktop at home).
    Furthermore, with Calc, every three months or so, you are supposed to install everything from scratch (some extensions are lost in the process).
    Most “average-users” who work with Calc (or Excel) don’t know Base (or Access).
    On the other hand, they do need some Database options.
    This extension provide them.

    Best regards (and sorry for the long post).

    Silvio

  14. Kohei Yoshida says:

    Silvio, sorry I couldn’t reply sooner. I was a bit tied up.

    >1. The option to delete cells (or whatever) is a bit “time-consuming”.

    I agrees with you. :-) In Go-OO, the Delete key does NOT launch that ugly delete dialog, thus saves you time.

    >5.We think it is important to have some lines (a rectangle in short) around the cells you decide to copy.

    In Go-OO, copied range is marked with blue border. :-)

    >6. We really like the extension: data-form.

    In Go-OO, we already have data form, in Data -> DataForm. :-)

    I couldn’t quite understand your points 2, 3 and 4 since I couldn’t find those shortcuts in Excel. Perhaps those shortcuts you mentioned are specific to Italian version of Excel? I would need more information on them in order to help you.

    Anyway, you should definitely try Go-OO if you are concerned about familiarity with Excel. You won’t be sorry you did. :-)

  15. Silvio says:

    Hello Kohei,

    Thanks a lot for your answers and explanations.
    It is really a pleasure for me reading them because I appreciate a lot all your past work on Calc (both in the OOo and in the Go-oo version).

    >Anyway, you should definitely try Go-OO if you are concerned
    >about familiarity with Excel. You won’t be sorry you did. :-)

    We tried the DataForm and the other options in Go-oo and everything worked great.
    The Go-oo version is more “user-friendly” for an Excel user and this is really useful in my opinion because Excel is a must in Italy (and everywhere in Europe).
    In the public and private services (University, Hospitals etc) everybody uses it.

    >I couldn’t quite understand your points 2, 3 and 4 since I
    >couldn’t find those shortcuts in Excel.

    About the shorcuts, probably, it is a problem related to the Italian version of Excel.
    For example, the Excel shortcut CTR + * (asterisk), to select all cells in a certain range no matter where your cursor is placed in that selection, works even in OOo 3.1 but I noticed you are foreced to press X (the button to multiply, not the X letter)
    In Excel, as I explained, you must press Ctr + * (see http://office.microsoft.com/en-us/excel/HP011116591033.aspx).

    Probably I am wrong, but I noticed the only trivial problem about shortcuts (both in OOo and Go-oo) is that you can’t use a “personal” shortcut.
    You can only choose among a list of shortcuts already available and listed in Go-oo.
    To do so, you select “tools”, “customize”, “shortcut” and, through the modify and delete button, you add or modify them.

    For instance, if I wish to have the Excel shortcut CTR + ? (Italian version) to copy and paste a single cell when my cursor is beneath the cell I want to copy (e.g. I wish to paste A1 into A2) I can’t use this shortcut because it is not available in the list in Go-oo (or OOo 3.1).
    In short, when I try to press Ctr + ? in the blank rectangle (titled “keys”), nothing occurs.
    In the end, I am forced to use one of the shortcuts proposed by Go-oo.
    Strangely enough, the shortcut Ctr + ? is not even proposed by Go-oo :-)
    Obviously, this is really trivial as a problem so bear with me for remarking it ;-)

    In conclusion, I think Calc (in the Go-oo version) is really complete as features.
    Probably it is not so powerful as Excel but it does its job wonderfully :-)

    I wish all your recent improvements will be available in the upcomping Linux distros.
    In particular, Ubuntu 9.10 (Karmik Koala) and, needless to say, Opensuse 11.2.
    Both distros are supposed to being released around October 2009 or so.
    If I am not wrong all Linux distros install the Go-oo version (instead of the OOo version by Sun).
    As a consequence, I do hope all your recent improvements and bug fixe on Calc are going to be there ;-)

    Best regards and THANKS a lot for your work.

    I do appreciate the work Novel (and you) is doing for every Excel user :-)
    Thanks to this work I do hope these Excel users will be, in some future, Go-oo (or OOo) users…

    Best regards,

    Silvio

Comments are closed.