Extend selection in Calc

English
, ,
#1

I have just switched to LO from OO and am missing one very useful feature in Calc keyboard shortcuts .

I have data in column A of a spreadsheet and write a formula in B1 to process A1. Now I want to replicate that formula in column B to process all the data in A. In OO I can copy the cell, go down to B2, shift-left to A2 and shift-cmd-down (I’m on a Mac) to extend the selection to the bottom of the data, then shift-right so only the relevant cells in column B are selected for pasting. In LO, shift-cmd-down doesn’t stop at the bottom of the data in column A but instead goes to the bottom of the spreadsheet.

If I select A1 and shift-cmd-down then the selection does stop at the last cell with data in column A, so I think LO is basing its criterion for where to stop on the original column selected (B in the example above) rather than the current cell when shift-control-down is pressed, which seems counter-intuitive (and is different to OO and possibly Excel).

Am I missing something? Is there a different shortcut that does what I want? Thanks for any help.

#2

and is different to … possibly Excel

No, Excel behaves just as Calc in that regards

#3

Understood, but for me OO Calc is smarter in this regard than either LO or Excel. There’s a helpful workaround in the next answer but it has some limitationss and needs more keystrokes. The OO implementation is slicker and if you actually want the whole column then you need only press shift-cmd-down a second time. (Doesn’t make up for the fact that OO crashes all the time, though!)

#6

You can select Key Bindings OpenOffice.org legacy under Tools → Options → Calc → Compatibility to get that old selection behaviour.(that was a wrong assumption). However, easier is to use the AutoFill feature, i.e. after having entered the formula in B1 make it the active cell again and double click the handle in the bottom right corner (the mouse cursor becomes a cross when hovering over it). That fills the consecutive range in column B where there is data in column A.

Or with keyboard travel Left to column A, then hit Ctrl+Down, Right, Shift+Ctrl+Up, Ctrl+D to AutoFill the selection with the formula in B1, which is similar to pasting a previously copied cell B1 onto the selected range.

#7

Thank you! The AutoFill feature is, as you say, much easier. Thanks a lot for pointing me in that direction.

The OO legacy setting doesn’t alter the behaviour on my machine and Frequently asked questions - Calc - The Document Foundation Wiki suggests that only a few keystrokes are remappped. But one of them makes Backspace delete rather than open the edit/delete dialogue, which is good.

Much appreciated.

#8

Hum… indeed, I thought it would also affect the selection behaviour but apparently doesn’t, seems I confused that. I’ll strike that through in my answer…

Regarding Backspace key, the setting just swaps Delete and Backspace key, in OOo Delete popped up the dialog and Backspace deleted, while in LibreOffice Delete just deletes and Backspace pops up the dialog, which IMHO is more “natural”.

#9

I’m sure you’re right but I use a Mac which doesn’t have a Delete key so am happy to use Backspace without the extra step!

#10

Seriously? Mac doesn’t have a Delete key? Ah well, it surely must be a superior system where one will never have to differentiate between possibilities; after all, they got along with one mouse button as well…

SCNR :wink:

So it seems it might have one… at least function-wise, as The Mac OS X Delete Key: It Goes Both Ways tells some key combinations.

#11

Sorry, I should have said it doesn’t have a separate Delete key but Fn+Backspace serves the purpose, a triumph of design over usability! But I like having it the OO way with no modifier needed for a straightforward delete and I can still get to the delete dialog that I didn’t even know existed - I’m learning lots!

#4

poor man’s workaround: just an idea, i’m not on a mac, select A1, shift-cmd-down, select the B cell right to the last cell with data in col. A, shift-cmd-up (should select up to B1), paste … works in ex$el 2010 and calc 7.1 win,

it has limitations when the data area isn’t docked to a border but ‘free floating’, thus i’d support an improvement request that jumping to the end (or beginning) of the data (better to the next change from data to empty or v-v) in the current! column or row would be better and more user friendly than the ‘Ex$el-like’ behaviour now,

as the cursors control needs a revision anyway - e.g. after pasting a range neither shift-cursor, nor ctrl-cursor, nor shift-ctrl-cursor work as a normal ‘stupid’ user would expect intuitively (ver 7.1 winx64) - maybe this could be easily implemented … ???

#5

Thank you, that works! Clunkier than OO Calc but it does what I need in a sheet with 13000-odd rows of data. Now I just need to train my fingers to a new way of working…much appreciated.

#12

Hi bvisick,

I have another solution that is a little closer to what you were used to under OO:
Copy the cell, go down to B2, shift-left to A2, press Tab key, shift-cmd-down to extend the selection to the bottom of the data, then shift-right so only the relevant cells in column B are selected for pasting.

After pressing the Tab key, the current cell is changed to A2. Then the selection stops at the end of column A.

best regards

Jürgen

#13

Thanks, Jürgen, another good technique - the tab key trick is very cool. I’ve got many answers to my problem, apprecate all the help from everyone.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.